Backup/Restore Database MySQL dengan PHP Berkemampuan Multithreaded

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 
 

 
class ASHDumper 
{ 
     
    public static function load() 
    { 
        
        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:'); 
        } 
        
        if (isset($options['help'])) { 
            self::showHelp(); 
            exit(); 
        } 
         
        
        $fields = array( 
            'h'    => array('hostName', 'localhost'), 
            'u'    => array('userName', 'root'), 
            'p'    => array('password', ''), 
            'o'    => array('outputDir', dirname(__FILE__)), 
        ); 
         
        
        $dbInfo = array(); 
         
        
        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; 

        
        if (empty($options['d'])) { 
            self::showHelp(); 
            return; 
        } else { 
            $dbInfo['database'] = $options['d']; 
        } 
         
        if (isset($options['r']) || isset($options['restore'])) { 
            
            self::restore($dbInfo, $options); 
            return; 
        } 
         
        if (isset($options['b'])  || isset($options['backup'])) { 
            
            self::backup($dbInfo); 
            return; 
        } 
         
        self::showHelp(); 
        return; 
    } 
     
     
    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; 
    } 
     
     
    public static function restore($dbInfo, $options) 
    { 
        
        if (isset($options['c']) || isset($options['create'])) { 
            self::createIfNotExist($dbInfo); 
        } 

        
        $tableList = array(); 
        $files = glob($dbInfo['outputDir'] . '*.sql', GLOB_BRACE); 
        foreach ($files as $file) { 
            $tableList[] = str_replace('.sql', '', basename($file)); 
        } 
         
        
        $command = "mysql -u {$dbInfo['userName']} -p{$dbInfo['password']} " .  
        "-h {$dbInfo['hostName']} {$dbInfo['database']} < {$dbInfo['outputDir']}%s.sql"; 

        self::runWorkers($command, $tableList); 
         
        return; 
    } 
     
     
    public static function backup($dbInfo) 
    { 
        
        $tableList = self::getTables($dbInfo); 
         
        
        $command = "mysqldump -u {$dbInfo['userName']} -p{$dbInfo['password']} " .  
        "-h {$dbInfo['hostName']} {$dbInfo['database']} %s > {$dbInfo['outputDir']}%s.sql"; 

        self::runWorkers($command, $tableList); 
         
        return; 
    } 
     
     
    public static function getTables($dbInfo) 
    { 
        $tables = array(); 
        try { 
            
            $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) { 
            
            echo 'Failed: ' . $e->getMessage(); 
            exit(); 
        } 
         
        return $tables; 
    } 
     
     
    public static function createIfNotExist($dbInfo) 
    { 
        try { 
            
            $dsn = "mysql:host={$dbInfo['hostName']}"; 
            $db = new Pdo($dsn, $dbInfo['userName'], $dbInfo['password']); 
            
            $db->query("CREATE DATABASE IF NOT EXISTS `{$dbInfo['database']}`"); 
        } catch (PDOException $e) { 
            
            echo 'Failed: ' . $e->getMessage(); 
            exit(); 
        } 

        return; 
    } 
     
     
    public static function runWorkers($jobCommand, $tables) 
    { 
        
        $threads        = array(); 
        $resultStream    = array(); 
        $tableRealtion    = array(); 
        $options        = array(1 => array('pipe', 'w')); 
         
        
        $tableCount        = count($tables); 
        $doneCount        = 0; 
         
        
        while (true) { 
            
            if (count($tables) > 0) { 

                $table = array_shift($tables); 
                
                $command = sprintf($jobCommand, $table, $table); 

                $pipes = array(); 

                
                $threads[]            = proc_open($command, $options, $pipes); 
                $resultStream[]        = $pipes; 
                $tableRealtion[]    = $table; 

                
                if (end($threads) == false) { 
                    
                    $closeCount = count($threads)-1; 
                    unset($threads[$closeCount]); 
                    unset($resultStream[$closeCount]); 
                    unset($tableRealtion[$closeCount]); 
                     
                    
                    array_unshift($tables, $table); 
                } 
            } else if (count($threads) <= 0) { 
                break; 
            } 
         
            foreach($threads as $sub => $thisThread) { 
                
                $status = proc_get_status($thisThread); 
                
                if ($status['running'] != 'true' || $status['signaled'] == 'true') { 
                    $doneCount++; 
                    $results = stream_get_contents($resultStream[$sub][1]); 
                     
                    
                    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; 

                    
                    fclose($resultStream[$sub][1]); 
                    unset($threads[$sub]); 
                    unset($resultStream[$sub]); 
                    unset($tableRealtion[$sub]); 
                } 
            } 
        } 
    } 
} 

ASHDumper::load();

Anda bisa mengunduhnya disini.

Wagiman Wiryosukiro

Petani Sistem Informasi, tukang las plugin & themes Wordpress. Co-Founder SistemInformasi.biz. Saat ini aktif sebagai Developer & kontributor di OpenMandriva Linux.

You may also like...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: