<?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();
|