This class can backup and restore multiple MySQL tables in parallel.
It can take a backup of a MySQL database by running the mysqldump command multiple times in parallel, one time for each database table to backup.
The class can also do the opposite, i.e. restore a MySQL database by running the MySQL command multiple times in parallel to restore all the database tables.
The next step with this class is to make it multiple parallel on table level.
<?php
/**
* ASHDumper.php
*
* PHP version >= 4.30
*
* @author Aziz Hussain <azizsaleh@gmail.com>
* @copyright GPL license
* @license http://www.gnu.org/copyleft/gpl.html
* @link http://www.azizsaleh.com
*/
/**
* ASHDumper
*
* Threaded MySQL backup and restore. To use you must have mysql and mysqldump
* commands in your environment variables. You also need access to the proc_open function.
*
* Usage (run php ASHDumper.php [--help]?(>=5.3) for more info)
*
* To backup a database:
* php ASHDumper.php -b yes -h localhost -u root -pPassword -o c:\abspath\to\output\dir
* PHP >= 5.3
* php ASHDumper.php --backup -h localhost -u root -pPassword -o c:\abspath\to\output\dir
*
* To restore a database:
* php ASHDumper.php -r yes -h localhost -u root -pPassword -o c:\abspath\to\sql\dir
* PHP >= 5.3
* php ASHDumper.php --restore -h localhost -u root -pPassword -o c:\abspath\to\sql\dir
*
* To restore a database creating the database:
* php ASHDumper.php -r yes -c yes -h localhost -u root -pPassword -o c:\abspath\to\sql\dir
* PHP >= 5.3
* php ASHDumper.php --restore --create -h localhost -u root -pPassword -o c:\abspath\to\sql\
*
* @author Aziz Hussain <azizsaleh@gmail.com>
* @copyright GPL license
* @license http://www.gnu.org/copyleft/gpl.html
* @link http://www.azizsaleh.com
*/
class ASHDumper
{
/**
* Load options, show help if needed
* and run restore/backup as specified
*
* @return void
*/
public static function load()
{
// Get options
if (phpversion() >= 5.3) {
$options = getopt('h:u:p:d:o:c:r:b:', array(
'help', 'create', 'backup', 'restore'
));
} else {
$options = getopt('h:u:p:d:o:c:r:b:');
}
// help?
if (isset($options['help'])) {
self::showHelp();
exit();
}
// Options with defaults
$fields = array(
'h' => array('hostName', 'localhost'),
'u' => array('userName', 'root'),
'p' => array('password', ''),
'o' => array('outputDir', dirname(__FILE__)),
);
// Holder for Db info
$dbInfo = array();
// Load optional values
foreach ($fields as $opt => $keys) {
if (isset($options[$opt])) {
$dbInfo[$keys[0]] = $options[$opt];
}
if (empty($dbInfo[$keys[0]])) {
$dbInfo[$keys[0]] = $keys[1];
}
}
$dbInfo['outputDir'] = rtrim($dbInfo['outputDir'], '/\\') .
DIRECTORY_SEPARATOR;
// No database specified
if (empty($options['d'])) {
self::showHelp();
return;
} else {
$dbInfo['database'] = $options['d'];
}
if (isset($options['r']) || isset($options['restore'])) {
// Restore DB
self::restore($dbInfo, $options);
return;
}
if (isset($options['b']) || isset($options['backup'])) {
// Backup DB
self::backup($dbInfo);
return;
}
self::showHelp();
return;
}
/**
* Show Help Message
*
* @return void
*/
public static function showHelp()
{
echo PHP_EOL . '-------------' . PHP_EOL . 'ASHDumper.php' . PHP_EOL .
'--------------' . PHP_EOL .
'Use this script to backup/restore your database' .
' at a table level.' . PHP_EOL . ' Each table will get its own process' .
' to get backed up and restored' . PHP_EOL . PHP_EOL .
'------------' . PHP_EOL . 'REQUIREMENTS' . PHP_EOL .
'------------' . PHP_EOL .
' - You need to have proc_open Enabled' . PHP_EOL .
' - You need to have access to mysql/mysqldump' . PHP_EOL .
' - Output directory must exist and must be writable by you' .
PHP_EOL . PHP_EOL .
'--------' . PHP_EOL . 'OPTIONS' . PHP_EOL .
'--------' . PHP_EOL .
'-h Host Name of MySQL' . PHP_EOL .
'-u User Name of MySQL' . PHP_EOL .
'-p Password of MySQL' . PHP_EOL .
'-d Database Name of MySQL' . PHP_EOL .
'-o Folder of where to store SQL files (backup) ' .
'or located (restore)' . PHP_EOL . PHP_EOL .
'------' . PHP_EOL . 'USAGE' . PHP_EOL .
'------' . PHP_EOL .
'To backup a database:' . PHP_EOL .
'php ASHDumper.php -b yes -h localhost -u root -pPassword ' .
'-o c:\abspath\to\output\dir' . PHP_EOL .
'[PHP >= 5.3] php ASHDumper.php --backup -h localhost -u root -pPassword ' .
'-o c:\abspath\to\output\dir' . PHP_EOL . PHP_EOL .
PHP_EOL . 'To restore a database (DB Must Exist):' . PHP_EOL .
'php ASHDumper.php -r yes -h localhost -u root -pPassword -o ' .
'c:\abspath\to\sql\dir'. PHP_EOL .
'php ASHDumper.php --restore -h localhost -u root -pPassword -o ' .
'[PHP >= 5.3] c:\abspath\to\sql\dir'. PHP_EOL . PHP_EOL .
PHP_EOL . 'To restore a database (Create DB If It Does Not Exist):' . PHP_EOL .
'php ASHDumper.php -r yes -c yes -h localhost -u root -pPassword -o ' .
'c:\abspath\to\sql\dir'. PHP_EOL .
'[PHP >= 5.3] php ASHDumper.php --restore --create -h localhost -u root -pPassword -o ' .
'c:\abspath\to\sql\dir'. PHP_EOL;;
return;
}
/**
* Restore a DB
*
* @param array $dbInfo
* userName MySQL Username
* hostName MySQL Host Name
* password MySQL User Password
* outputDir Directory to write SQL files to
* @param array $options Options Param
*
* @return void
*/
public static function restore($dbInfo, $options)
{
// Create Database if it does not exist
if (isset($options['c']) || isset($options['create'])) {
self::createIfNotExist($dbInfo);
}
// Get files to restore
$tableList = array();
$files = glob($dbInfo['outputDir'] . '*.sql', GLOB_BRACE);
foreach ($files as $file) {
$tableList[] = str_replace('.sql', '', basename($file));
}
// Command
$command = "mysql -u {$dbInfo['userName']} -p{$dbInfo['password']} " .
"-h {$dbInfo['hostName']} {$dbInfo['database']} < {$dbInfo['outputDir']}%s.sql";
self::runWorkers($command, $tableList);
return;
}
/**
* Backup a DB
*
* @param array $dbInfo
* userName MySQL Username
* hostName MySQL Host Name
* password MySQL User Password
* outputDir Directory to read files from
*
* @return void
*/
public static function backup($dbInfo)
{
// Get table list
$tableList = self::getTables($dbInfo);
// Command
$command = "mysqldump -u {$dbInfo['userName']} -p{$dbInfo['password']} " .
"-h {$dbInfo['hostName']} {$dbInfo['database']} %s > {$dbInfo['outputDir']}%s.sql";
self::runWorkers($command, $tableList);
return;
}
/**
* Get list of tables from DB, used in the backup method
*
* @param array $dbInfo
* userName MySQL Username
* hostName MySQL Host Name
* password MySQL User Password
*
* @return array
*/
public static function getTables($dbInfo)
{
$tables = array();
try {
// Connect to db
$dsn = "mysql:dbname={$dbInfo['database']};host={$dbInfo['hostName']}";
$db = new Pdo($dsn, $dbInfo['userName'], $dbInfo['password']);
$result = $db->query("show tables");
while ($row = $result->fetch(PDO::FETCH_NUM)) {
$tables[] = $row[0];
}
} catch (PDOException $e) {
// Any errors, show them to user
echo 'Failed: ' . $e->getMessage();
exit();
}
return $tables;
}
/**
* Create database if it does not exist
*
* @param array $dbInfo
* userName MySQL Username
* hostName MySQL Host Name
* password MySQL User Password
* database Database Name
*
* @return void
*/
public static function createIfNotExist($dbInfo)
{
try {
// Connect to db
$dsn = "mysql:host={$dbInfo['hostName']}";
$db = new Pdo($dsn, $dbInfo['userName'], $dbInfo['password']);
// Create database
$db->query("CREATE DATABASE IF NOT EXISTS `{$dbInfo['database']}`");
} catch (PDOException $e) {
// Any errors, show them to user
echo 'Failed: ' . $e->getMessage();
exit();
}
return;
}
/**
* Run a worker for each table
*
* @param string $jobCommand Command to execute
* @param array $tables List of tables to process
*
* @return void
*/
public static function runWorkers($jobCommand, $tables)
{
// presets
$threads = array();
$resultStream = array();
$tableRealtion = array();
$options = array(1 => array('pipe', 'w'));
// Counts
$tableCount = count($tables);
$doneCount = 0;
// Start workers
while (true) {
// Any tables left to do?
if (count($tables) > 0) {
$table = array_shift($tables);
// Construct the process command with process ID & current db to use
$command = sprintf($jobCommand, $table, $table);
$pipes = array();
// open thread
$threads[] = proc_open($command, $options, $pipes);
$resultStream[] = $pipes;
$tableRealtion[] = $table;
// If this thread started
if (end($threads) == false) {
// If it fails, close the thread & pipe
$closeCount = count($threads)-1;
unset($threads[$closeCount]);
unset($resultStream[$closeCount]);
unset($tableRealtion[$closeCount]);
// Put table back in if failed
array_unshift($tables, $table);
}
} else if (count($threads) <= 0) {
break;
}
foreach($threads as $sub => $thisThread) {
// Get the status
$status = proc_get_status($thisThread);
// If its not running or stopped, close it & get the results
if ($status['running'] != 'true' || $status['signaled'] == 'true') {
$doneCount++;
$results = stream_get_contents($resultStream[$sub][1]);
// Any errors
if (!empty($results)) {
echo 'Error processing table ' . $tableRealtion[$sub] .
': ' . $results . PHP_EOL;
} else {
echo 'Completed Table: ' . $tableRealtion[$sub] . PHP_EOL;
}
echo ($tableCount - $doneCount) . ' Tables Remaining' . PHP_EOL . PHP_EOL;
// Close the pipe & threads
fclose($resultStream[$sub][1]);
unset($threads[$sub]);
unset($resultStream[$sub]);
unset($tableRealtion[$sub]);
}
}
}
}
}
ASHDumper::load();
mr.aziz,
i have a question about your thread,
how to use ASHDumper Class??
i took the code like this,
exec(“D:/xampp/mysql/bin>mysql php ASHDumper.php –restore -h localhost -u root -p123 -o D:/xampp/htdocs/iMhere/dbbmn10.sql”);
but, its not run. .
can you help me??
sorry to my bad english. .
Hi Koeh,
This is a PHP script and not executable by mysql. Assuming your PHP.exe is located at D:\xampp\php\php.exe:
exec(“D:/xampp/php/php.exe ASHDumper.php –restore -h localhost -u root -p123 -o D:/xampp/htdocs/iMhere/dbbmn10.sql”);
If that does not work, let me know the exact error you are getting.