Skip to content

Instantly share code, notes, and snippets.

@mrwadson
Last active May 22, 2023 18:27
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mrwadson/0e0c5efcf8b33f11d564acd0cbee61b5 to your computer and use it in GitHub Desktop.
Save mrwadson/0e0c5efcf8b33f11d564acd0cbee61b5 to your computer and use it in GitHub Desktop.
Dump and Zip MySql DB
<?php
ini_set('max_execution_time', 600);
ini_set('memory_limit', '1024M');
// --- CONFIG ---------------------------------
const DB_NAME = 'your_database';
const DB_USER = 'root';
const DB_PASS = 'xxxxxx';
const DUMP_DIR = 'dumps';
const DUMP_DB_NAME = DB_NAME;
const HOSTNAME = 'localhost';
$ignoreTables = [];
// --------------------------------------------
// CHECK
$cmd = sprintf('MYSQL_PWD="%s" mysqlshow -u%s "%s" | grep -v Wildcard | grep -o %s 2>&1',
DB_PASS, DB_USER, DB_NAME, DB_NAME);
$r = exec($cmd, $output, $code);
if ($code) {
die((PHP_SAPI === 'cli') ?
"Please check your database credentials\n" : '<p><b>Please check your database credentials</b></p>');
}
if (!file_exists(DUMP_DIR) && !mkdir(DUMP_DIR) && !is_dir(DUMP_DIR)) {
throw new RuntimeException(sprintf('Directory "%s" was not created', DUMP_DIR));
}
$dumpDbFile = sprintf('%s/%s/%s_%s.sql', __DIR__, DUMP_DIR, date('Y-m-d_H-i-s'), DUMP_DB_NAME);
$ignoreCmd = ' --ignore-table=' . DB_NAME . '.';
$ignoreTablesCmd = $ignoreCmd . implode($ignoreCmd, $ignoreTables);
echo (PHP_SAPI === 'cli') ? "Backing up database to $dumpDbFile\n" :
"<p>Backing up database to <b>$dumpDbFile</b></p>";
// DUMP
foreach ([
'MYSQL_PWD="%s" mysqldump -u%s -h %s %s --no-data > %s' =>
[DB_PASS, DB_USER, HOSTNAME, DB_NAME, $dumpDbFile],
'MYSQL_PWD="%s" mysqldump -u%s -h %s %s %s >> %s' =>
[DB_PASS, DB_USER, HOSTNAME, DB_NAME, $ignoreTablesCmd, $dumpDbFile]
] as $expression => $params) {
$cmd = sprintf($expression, ...$params);
exec($cmd, $output, $code);
if ($code) {
die;
}
}
// ZIP
$zipFile = $dumpDbFile . '.zip';
echo (PHP_SAPI === 'cli') ? "Zipping database to $zipFile\n" :
"<p>Zipping database to <b>$zipFile</b></p>";
$cmd = sprintf('zip -j %s %s', $zipFile, basename(dirname($dumpDbFile)) . '/' . basename($dumpDbFile));
exec($cmd);
unlink($dumpDbFile);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment