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

refreshdb.php

#!/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/opt/mariadb@10.11/bin/mysql',
    'mysqldumpExecutablePath' => '/opt/homebrew/opt/mariadb@10.11/bin/mysqldump',
    // Command to find the path of the mysql and mysqldump executables:
    // `which mysql` and `which mysqldump`
    // in Windows use Everything from https://ninite.com/everything/ to search for the files mysql.exe and 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 DEFINER from trigger definitions\n";
    $content = preg_replace('/\/\*!50003 CREATE\*\/\s*\/\*!50017 DEFINER=`[^`]+`@`[^`]+`\*\/\s*\/\*!50003 TRIGGER/', '/*!50003 CREATE TRIGGER', $content);

    echo "Removing COLLATE=utf8[^ ]+_c[is]/m\n";
    $content = preg_replace('/\s*COLLATE[= ]utf8[^ ]+_c[is]/m', '', $content);

    echo "Removing the line /*M!999999\\- enable the sandbox mode */\n";
    $content = preg_replace('/\/\*M!999999\\\- enable the sandbox mode \*\/\s*/', '', $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";
    }
}

In Windows, create a .bat file to a folder in %PATH%:

@echo off
set "PHP_EXECUTABLE=C:\xampp824\php\php.exe"
set "SCRIPT_PATH=c:\users\user\bin\refreshdb.php"

%PHP_EXECUTABLE% %SCRIPT_PATH% %*

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