Last active
March 29, 2024 07:38
-
-
Save henno/14e18a285e5d3287e84dcd09812fc5e6 to your computer and use it in GitHub Desktop.
Cross-compatible MySQL/MariaDB script ideal for quick resets and creating new dumps while developing, featuring automatic database name detection from config files or current directory name
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env php | |
<?php | |
// Set the timer | |
$start = microtime(true); | |
// Set the default timezone from the system | |
date_default_timezone_set(json_decode(file_get_contents('http://ip-api.com/json'))->timezone); | |
// Show the start time | |
echo "Started at: " . date('Y-m-d H:i:s') . "\n"; | |
$config = [ | |
'databaseUsername' => 'root', | |
'databasePassword' => 'root', | |
'databaseHostname' => '127.0.0.1', | |
'configFilePaths' => ['./config.php', './wp-config.php'], | |
'dumpFilePath' => 'doc/database.sql', | |
'useDocker' => false, | |
'dockerContainerName' => '', | |
'mysqlExecutablePath' => '/opt/homebrew/bin/mysql', | |
'mysqldumpExecutablePath' => '/opt/homebrew/bin/mysqldump', | |
// 'mysqlExecutablePath' => 'C:\Program Files\MySQL\MySQL Server 8.1\bin\mysql.exe', | |
// 'mysqldumpExecutablePath' => 'C:\Program Files\MySQL\MySQL Server 8.1\bin\mysqldump.exe', | |
]; | |
$options = getopt('', ['dump', 'restore', 'mariadb-docker', 'mysql-docker', 'help']); | |
$mode = isset($options['dump']) ? 'dump' : (isset($options['restore']) ? 'restore' : 'help'); | |
if (isset($options['mariadb-docker'])) { | |
$config['useDocker'] = true; | |
$config['dockerContainerName'] = 'mariadb-temp-server'; | |
$config['mysqlExecutablePath'] = 'mariadb'; | |
$config['mysqldumpExecutablePath'] = 'mariadb-dump'; | |
} elseif (isset($options['mysql-docker'])) { | |
$config['useDocker'] = true; | |
$config['dockerContainerName'] = 'mysql-temp-server'; | |
// Assuming the MySQL Docker setup uses standard names, adjust if needed. | |
$config['mysqlExecutablePath'] = 'mysql'; | |
$config['mysqldumpExecutablePath'] = 'mysqldump'; | |
} | |
if ($config['useDocker']) { | |
$config['databaseHostname'] = '127.0.0.1'; | |
checkDockerContainer($config['dockerContainerName']); | |
} | |
// If mode is restore, validate that the dump file exists | |
if ($mode === 'restore' && !file_exists($config['dumpFilePath'])) { | |
echo "No dump file found at " . $config['dumpFilePath'] . ". Please create a dump file before restoring the database.\n"; | |
exit(1); | |
} | |
// Check that the dump file is not empty | |
if ($mode === 'restore' && filesize($config['dumpFilePath']) == 0) { | |
echo "The dump file is empty. Please create a non-empty dump file before restoring the database.\n"; | |
exit(1); | |
} | |
// If mode is help, show help and exit | |
if ($mode === 'help') { | |
showHelp(); | |
exit(0); | |
} | |
// Set the database name | |
$configFilePath = ''; | |
foreach ($config['configFilePaths'] as $path) { | |
if (file_exists($path)) { | |
$configFilePath = $path; | |
break; | |
} | |
} | |
if (!empty($configFilePath)) { | |
ob_start(); | |
require $configFilePath; | |
ob_end_clean(); | |
if (defined('DATABASE_DATABASE')) { | |
$config['databaseName'] = DATABASE_DATABASE; | |
$origin = 'config.php'; | |
} elseif (defined('DB_NAME')) { | |
$config['databaseName'] = DB_NAME; | |
$origin = 'wp-config.php'; | |
} elseif (isset($cfg['DATABASE_DATABASE'])) { | |
$config['databaseName'] = $cfg['DATABASE_DATABASE']; | |
$origin = 'config.php'; | |
} else { | |
$config['databaseName'] = basename(getcwd()); | |
$origin = 'current directory'; | |
} | |
} | |
if (empty($config['databaseName'])) { | |
$config['databaseName'] = basename(getcwd()); | |
} | |
// Show database name | |
echo "Database name: " . $config['databaseName'] . " (from " . $origin . ")\n"; | |
function showHelp() | |
{ | |
echo "This script facilitates dumping and restoring MySQL/MariaDB databases, offering unique features like Docker support and pre-processing dump files to minimize differences when switching between MySQL and MariaDB. It's designed to make the database dumps more consistent and to reduce the noise in version control diffs by addressing syntax and default value differences.\n\n"; | |
echo "Usage:\n"; | |
echo " php db-dump-restore.php [--dump|--restore|--mariadb-docker|--mysql-docker|--help]\n\n"; | |
echo "Options:\n"; | |
echo " --dump Create a database dump file, applying transformations to standardize output across MySQL and MariaDB.\n"; | |
echo " --restore Restore the database from the dump file. This option drops the existing database and creates a new one from the dump.\n"; | |
echo " --mariadb-docker Use a Docker container for MariaDB operations. Requires specifying the container name in the script configuration.\n"; | |
echo " --mysql-docker Use a Docker container for MySQL operations. Requires specifying the container name in the script configuration.\n"; | |
echo " --help Display this help message and exit.\n\n"; | |
echo "Configuration:\n"; | |
echo "Edit the script's \$config array to set essential parameters such as database credentials, file paths, and Docker container names. This script supports automatic database name detection from common configuration files (config.php, wp-config.php) or uses the current directory name as a fallback.\n\n"; | |
echo "Special Features:\n"; | |
echo "The script includes several enhancements to improve the compatibility and readability of the dump files between MySQL and MariaDB versions. This includes adjusting character sets, collations, and default values, as well as reformatting to make diffs more manageable. It's tailored to reduce unnecessary noise in version control systems when tracking database schema changes.\n\n"; | |
echo "Docker Support:\n"; | |
echo "For environments using Docker, this script can interact with MySQL and MariaDB containers directly. Specify the container name and adjust executable paths as necessary. This feature simplifies database operations in containerized development or production setups.\n\n"; | |
echo "Ensure that the configuration is correctly set up before executing any operations to avoid unintended consequences. Review and adjust the Docker settings, database credentials, and file paths as per your specific requirements.\n"; | |
} | |
function executeCommand($command) | |
{ | |
echo "Executing: $command\n"; | |
system($command, $result); | |
return $result === 0; // True if command was successful | |
} | |
function processDumpFile($dumpFilePath) | |
{ | |
echo "Processing dump file for more consistent output across MySQL/MariaDB\n"; | |
// Ensure the dump file exists | |
if (!file_exists($dumpFilePath)) { | |
echo "Dump file does not exist: $dumpFilePath\n"; | |
return false; | |
} | |
// Read the content of the dump file | |
$content = file_get_contents($dumpFilePath); | |
if ($content === false) { | |
echo "Failed to read dump file: $dumpFilePath\n"; | |
return false; | |
} | |
// Perform replacements | |
echo "Replacing DEFAULT CHARSET=utf8mb3 with DEFAULT CHARSET=utf8mb4\n"; | |
$content = str_replace('DEFAULT CHARSET=utf8mb3', 'DEFAULT CHARSET=utf8mb4', $content); | |
echo "Replacing SET character_set_client = utf8 */; with SET character_set_client = utf8mb4 */;"; | |
$content = str_replace('SET character_set_client = utf8 */;', 'SET character_set_client = utf8mb4 */;', $content); | |
echo "Removing COLLATE=utf8[^ ]+_c[is]/m\n"; | |
$content = preg_replace('/\s*COLLATE[= ]utf8[^ ]+_c[is]/m', '', $content); | |
echo "Replacing NOT NULL DEFAULT 0.00 with NOT NULL DEFAULT '0.00'\n"; | |
$content = str_replace("NOT NULL DEFAULT 0.00", "NOT NULL DEFAULT '0.00'", $content); | |
echo "Replacing CURRENT_TIMESTAMP with current_timestamp()\n"; | |
$content = str_replace('CURRENT_TIMESTAMP', 'current_timestamp()', $content); | |
echo "Replacing all /*!40101 SET lines to /*!50503 SET lines to eliminate unnecessary differences\n"; | |
$content = preg_replace('/\/\*!40101 SET(.*?\*\/;)/ms', '/*!50503 SET$1', $content); | |
echo "Removing all COLLATE=latin1_swedish_ci\n"; | |
$content = preg_replace('/\s*COLLATE=latin1_swedish_ci/m', '', $content); | |
echo "Removing all column width specifications like \"` (tiny|small|medium|long|int)\(10\)\" to make the output uniform across MySQL and MariaDB\n"; | |
$content = preg_replace('/(^\s*`[^`]+`\s*(tiny|small|medium|long)?int)\(\d+\)/m', '\1', $content); | |
echo "Putting all VALUES lists on separate lines to make the output more readable while taking into account the possibility of already multi-line values\n"; | |
$oldContent = $content; | |
$content = preg_replace('/\),\s*\(/', "),\n(", $content); | |
if($oldContent !== $content) { | |
echo "Difference in size: " . (strlen($content) - strlen($oldContent)) . "\n"; | |
} | |
echo "Replacing int (NOT) NULL DEFAULT '0' with int (NOT) NULL DEFAULT 0\n"; | |
$content = preg_replace('/((int|tinyint|smallint|mediumint|big)?int( unsigned)?( NOT)?( NULL)? DEFAULT) \'0\'/m', '\1 0', $content); | |
echo "Putting newline after VALUES and the first value to make the output more readable (if not already present)\n"; | |
$content = preg_replace('/\bVALUES\s+\(/', "VALUES\n(", $content); | |
echo "Removing DEFAULT NULL from text/blob columns\n"; | |
$content = preg_replace('/(^\s*`[^`]+`\s*(tiny|medium|long)?(text|blob)) DEFAULT NULL,/m', '\1,', $content); | |
echo "Removing ,_binary '\n"; | |
$content = preg_replace('/,_binary \'/m', ',\'', $content); | |
echo "Removing quotes around default values for number columns\n"; | |
$content = preg_replace('/(^\s*`[^`]+`\s*(tinyint|smallint|mediumint|int|bigint|decimal)(\([0-9,]*\))?\s*(unsigned)?\s*(NOT)?\s*(NULL)?\s*DEFAULT) \'([0-9.]*)\'/m', '\1 \7', $content); | |
// Save the modified content back to the dump file | |
if (file_put_contents($dumpFilePath, $content) === false) { | |
echo "Failed to write processed content back to dump file: $dumpFilePath\n"; | |
return false; | |
} | |
echo "Dump file processed.\n"; | |
return true; | |
} | |
if ($mode === 'dump') { | |
dumpDatabase($config); | |
} elseif ($mode === 'restore') { | |
restoreDatabase($config); | |
} else { | |
showHelp(); | |
} | |
// Show the time taken in human-readable format | |
$end = microtime(true); | |
$elapsed = $end - $start; | |
echo "Time taken: " . ($elapsed < 1 ? round($elapsed * 1000) . " ms" : round($elapsed, 2) . " s") . "\n"; | |
function checkDockerContainer($containerName) | |
{ | |
$command = "docker ps --filter name=$containerName --format '{{.Names}}'"; | |
exec($command, $output, $return_var); | |
if (!in_array($containerName, $output)) { | |
echo "Docker container '$containerName' is not running.\n"; | |
exit(1); | |
} | |
} | |
function dumpDatabase($config) | |
{ | |
$execPath = $config['useDocker'] ? "docker exec {$config['dockerContainerName']} " : ''; | |
$hostname = $config['useDocker'] ? '127.0.0.1' : $config['databaseHostname']; | |
$dumpCommand = "{$execPath}\"{$config['mysqldumpExecutablePath']}\" --default-character-set=utf8mb4 -u {$config['databaseUsername']} -p{$config['databasePassword']} -h {$hostname} {$config['databaseName']} > {$config['dumpFilePath']}"; | |
if (executeCommand($dumpCommand)) { | |
processDumpFile($config['dumpFilePath']); | |
} else { | |
echo "Failed to create dump file.\n"; | |
} | |
} | |
function restoreDatabase($config) | |
{ | |
// Execute the drop and create database command | |
if (!executeCommand($config['useDocker'] ? | |
"echo 'DROP DATABASE IF EXISTS {$config['databaseName']}; CREATE DATABASE {$config['databaseName']};' | docker exec -i {$config['dockerContainerName']} \"{$config['mysqlExecutablePath']}\" -u {$config['databaseUsername']} -p{$config['databasePassword']}" : | |
"\"{$config['mysqlExecutablePath']}\" -u {$config['databaseUsername']} -p{$config['databasePassword']} -h {$config['databaseHostname']} -e \"DROP DATABASE IF EXISTS {$config['databaseName']}; CREATE DATABASE {$config['databaseName']};\"")) { | |
echo "Failed to drop and/or create the database.\n"; | |
return; | |
} | |
// Execute the restore command | |
if (executeCommand($config['useDocker'] ? | |
"cat {$config['dumpFilePath']} | docker exec -i {$config['dockerContainerName']} \"{$config['mysqlExecutablePath']}\" -u {$config['databaseUsername']} -p{$config['databasePassword']} {$config['databaseName']}" : | |
"\"{$config['mysqlExecutablePath']}\" -u {$config['databaseUsername']} -p{$config['databasePassword']} -h {$config['databaseHostname']} {$config['databaseName']} < {$config['dumpFilePath']}")) { | |
echo "Database restored successfully.\n"; | |
} else { | |
echo "Failed to restore the database.\n"; | |
} | |
} |
Installation script for Powershell:
# Append $HOME\bin to PATH if it's not already included
if (-not ($env:PATH -split ';' -contains "$HOME\bin")) { [Environment]::SetEnvironmentVariable('PATH', ($env:PATH.TrimEnd(';') + ";$HOME\bin"), 'User') }
# Ensure the bin directory exists
$binPath = Join-Path -Path $HOME -ChildPath "bin"
if (-not (Test-Path -Path $binPath)) { New-Item -ItemType Directory -Path $binPath -Force }
# Download the script to the bin directory
$scriptUrl = "https://gist.githubusercontent.com/henno/14e18a285e5d3287e84dcd09812fc5e6/raw/b03ddf79a6c060b252cd4ef75ac0320e3ca68c5b/refreshdb.php"
$destinationPath = Join-Path -Path $binPath -ChildPath "refreshdb.php"
Invoke-WebRequest -Uri $scriptUrl -OutFile $destinationPath
# Create a wrapper db.bat to avoid having to preceed the php file with the full path to php.exe
Set-Content -Path "$HOME\bin\db.bat" -Value @'
@echo off
set "PHP_EXECUTABLE=C:\xampp824\php\php.exe"
set "SCRIPT_PATH=c:\users\user\bin\refreshdb.php"
%PHP_EXECUTABLE% %SCRIPT_PATH% %*
'@
Restart all terminals and use the script in the folder of your project:
db --restore
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
In Windows, create a .bat file to a folder in %PATH%: