Login   Register  
PHP Classes
elePHPant
Icontem

File: ASHDumper.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  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: 2012-07-26 20:21
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(=> 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();