PHP Classes

File: ASHDumper.php

Recommend this page to a friend!
  Classes of Aziz S. Hussain   Multi Threaded MySQL Backup and Restore Daemon   ASHDumper.php   Download  
File: ASHDumper.php
Role: Class source
Content type: text/plain
Description: Main File
Class: Multi Threaded MySQL Backup and Restore Daemon
Backup multiple MySQL tables in parallel
Author: By
Last change: Made it PHP <= 5.3 friendly
Date: 11 years ago
Size: 10,160 bytes
 

Contents

Class file image Download
<?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();