Backup dan restore database MySQL adalah kegiatan rutin harian seorang administrator. Berbagai tool dan script mungkin digunakan dan dijalankan secara otomatis oleh cron setiap harinya. Semakin besar database yang dicadangkan, semakin lama waktu yang dibutuhkan. Kebanyakan tool backup hanya bekerja tunggal, belum bisa multithreading. Nah, bagaimana jika anda ingin melakukan backup dan restore MySQL sampai pada tataran tabel per tabel? sehingga hanya tabel-tabel transaksi penting yang akan dibackup harian? dan bisa berjalan multithread?

Solusinya ada di ASHDumper, sebuah script PHP buatan om Aziz Saleh Hussain (dari Amerika) yang baru saja memenangkan PHPClasses Inovation Award of The Month (bulan September 2012). ASHDumper sebagai tool backup akan membackup tabel per tabel MySQL dengan 1 thread (1 service) tiap tabelnya, sehingga proses lebih berjalan cepat karena multithreading. Untuk dapat menggunakan ASHDumper, PHP/MySQL anda harus memenuhi syarat berikut:
– proc_open enabled (lihat php.ini)
– akses ke mysql atau mysqldump
– direktori output yang writeable

Cara penggunaan:
1. untuk membackup database:

php ASHDumper.php --backup -h localhost -u root -pPassword -o /home/backup/

2. untuk merestore database, dengan database yang sudah ada (existing):

php ASHDumper.php --restore -h localhost -u root -pPassword -o /home/backup/

3. untuk merestore database, dengan membuat DB jika DB belum ada:

php ASHDumper.php --restore --create -h localhost -u root -pPassword -o /home/backup

Berikut adalah script lengkap dari ASHDumper:

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

Anda bisa mengunduhnya disini.