<?php
/**
Backup big database on MASTER/SLAVE process

A backup is a dir name as TYPE-YYYY.MM.DD.HHMMSS, with sql file fo each table (dbname.tablename.sql).

The backup are first place in a process dir which you can define outside the dir where you keep all you backup.
e.g to make an rsync only on a completed backup.

MEMORY tables are not backup
"mysql", "test", "phpmyadmin", "information_schema", "performance_schema" databases are not backup

--type TYPE
[--config FILENAME]
	Filename must be in th same dir of this script

Create an array to define backup options in a file name dbBackupConfig.php

return array(
	'mysql' => array(
		'host'        => "127.0.0.1",
		'port'        => 3306,
		'user'        => "user",
		'pass'        => "password",
		'master'      => 1,                           // If the server is a master else a slave
		'options'     => "--quick --add-drop-table",  // ou "--opt"
		'compress'    => "gzip",                      // Method to compress : none, gzip, bzip2, pigz, 7zx, xz
		'dir'         => "/backup/keep",              // Backup dir
		'max'         => 3,                           // Number of backup to keep
		'processdir'  => "/backup/inprocess",         // In process dir
	),
	'backup type/name' => array(

		'db' => array(...),   //List database to backup - always include a db to list tables
		'rules' => array(     // Rules to include or exclude database and table by name ( preg_match('/^db\.table$/') )
			'in' => array(),
			'ex' => array(),
		),
	),
),

Parallel restore
----------------
cd /to/backup/dir
echo *.sql | xargs -n1 -P 16 -I % sh -c 'mysql -uuser -ppassword -hIP mydb < %'
ls *.sql | xargs -n1 -P 16 -I % sh -c 'mysql -uuser -ppassword -hIP mydb < %'

 */
$shortopts = '';
$longopts = array(
    'type:',
    'config:',
);

$options = getopt($shortopts, $longopts);
if ($options === false || count($options) == 0) {
    echo "Nothing to do...\n";
    exit;
}

$configFile = 'dbBackupConfig.php';
if (array_key_exists('config', $options)) {
    $configFile = $options['config'];
}

if (file_exists(__DIR__.'/'.$configFile)) {
    $config = include __DIR__.'/'.$configFile;
} else {
    echo date('Y-m-d H:i:s')." - No config.\n"; exit;
}

if (!array_key_exists('mysql', $config)) {
    echo date('Y-m-d H:i:s')." - No config.\n"; exit;
}

// --- Define mysql option
$backupMysql = $config['mysql'];

// --- Type backup
$type = strtoupper($options['type']);
if (!array_key_exists($type, $config)) {
    echo date('Y-m-d H:i:s')." - Backup type not found !\n"; exit;
}

// --- Backup Options
$backupParams = $config[$type];

// --- Host
$backupMysqlHost = $backupMysql['host'];
$backupMysqlPort = $backupMysql['port'];
$backupMysqlUser = $backupMysql['user'];
$backupMysqlPass = $backupMysql['pass'];
$backupMysqlMaster = $backupMysql['master'];
$backupMysqlOptions = $backupMysql['options'];

// --- Backup Directory Options
$backupCompress = $backupMysql['compress'];
$backupDir = $backupMysql['dir'];
$backupMax = $backupMysql['max'];

// --- Backup temporary dir
$backupDirProcess = $backupDir;
if (array_key_exists('processdir', $backupMysql)) {
    $backupDirProcess = $backupMysql['processdir'];
}
if (!is_dir($backupDirProcess)) {
    echo date('Y-m-d H:i:s')." - Backup dir not found $backupDir\n"; exit;
}

// --- Master or Slave information
if ($backupMysqlMaster) {
    $filePosInfo = 'position-master-'.$type.'.info';
} else {
    $filePosInfo = 'position-slave-'.$type.'.info';
}

echo date('Y-m-d H:i:s')." - Start Backup $type.\n";

$tInit = microtime(true);

$dirname = $type.'-'.date('Y.m.d.His');
$pathBackup = $backupDirProcess . '/' . $dirname;
mkdir($pathBackup);

// --- Connexion
$link = new mysqli($backupMysqlHost, $backupMysqlUser, $backupMysqlPass, null, $backupMysqlPort);
if ($link->connect_errno) {
    echo date('Y-m-d H:i:s')." - Error can't connect to MySQL : (" . $mysqli->connect_errno . ") " . $mysqli->connect_error."\n";
}

// --- List databases
$result = $link->query('SHOW DATABASES');
$databases = $tables = array();
while ($row = $result->fetch_assoc()) {
    $databases[] = $row['Database'];
}

// --- Stop Slave
if ( !$backupMysqlMaster ) {
    echo date('Y-m-d H:i:s')." - STOP SLAVE SERVER\n";
    $link->query('STOP SLAVE;');
}

// --- Save replication position
putMasterStatus($link, "$pathBackup/$filePosInfo", 'START', $backupMysqlMaster, 0);

// --- Backup for each databases
foreach ($databases as $database) {
    if (in_array($database, $backupParams['db'])) {
        $link->query('USE '.$database);
        $result = $link->query('SHOW TABLES');
        while ($row = $result->fetch_array()) {
            $table = $row[0];

            // --- Don't backup MEMORY table
            $statusResult = $link->query("SHOW TABLE STATUS LIKE '".$table."'");
            $statusRow = $statusResult->fetch_assoc();
            if (strtoupper($statusRow['Engine']) == 'MEMORY') {
                continue;
            }

            // --- Don't backup special database
            if ( in_array($database, array('mysql', 'test', 'information_schema', 'performance_schema', 'phpmyadmin')) ) {
                continue;
            }

            // --- Excluded table
            $stop = 0;
            foreach ($backupParams['rules']['ex'] as $rule) {
                if ( preg_match('/^'.$rule.'$/', $database.'.'.$table) ) {
                    $stop = 1;
                    break;
                }
            }
            if ($stop == 1) {
                continue;
            }

            // --- Included table
            $stop = 0;
            foreach ($backupParams['rules']['in'] as $rule) {
                if ( !preg_match('/^'.$rule.'$/',  $database.'.'.$table) ) {
                    $stop = 1;
                    break;
                }
            }
            if ($stop == 1) {
                continue;
            }

            $files[] = $database.'.'.$table;
            $tDeb = microtime(true);

            // --- Optimize table to remove hole
            if ( $backupMysqlMaster ) {
                if ( $statusRow['Data_free'] > 0 ) {
                    echo date('Y-m-d H:i:s') ." - OPTIMIZE on '$database.$table'\n";
                    $link->query('OPTIMIZE TABLE '.$database.'.'.$table);
                }
            }

            echo date('Y-m-d H:i:s') ." - Backup '$database.$table'";

            // --- Cmd to backup the table
            $cmd = 'mysqldump -h' . $backupMysqlHost . ' -P' . $backupMysqlPort .
                ' -u' . $backupMysqlUser . ' -p' . $backupMysqlPass .
                ' ' . $backupMysqlOptions . ' ' . $database . ' --tables ' . $table . ' > ' .
                $pathBackup.'/'.$database.'.'.$table.'.sql';

            // --- Execute
            exec($cmd);

            // --- Set Master or Slave position
            putMasterStatus($link, "$pathBackup/$filePosInfo", "$database.$table", $backupMysqlMaster, FILE_APPEND);

            $period = round(microtime(true)-$tDeb,3);
            echo " in $period s.\n";
        }
    }
}

// --- Save replication position
putMasterStatus($link, "$pathBackup/$filePosInfo", 'END', $backupMysqlMaster, FILE_APPEND);

// --- Restart slave
if ( !$backupMysqlMaster ) {
    echo date('Y-m-d H:i:s')." - RESTART SLAVE SERVER\n";
    $link->query('START SLAVE;');
}

// --- Close mysql
$link->close();

// --- Compression des fichiers de dump
if (in_array($backupCompress, array('gzip', 'pigz', '7z', 'xz', 'pxz'))) {
    foreach($files as $item) {
        $tDeb = microtime(true);
        switch($backupCompress) {
            case 'gzip':
                exec("gzip $pathBackup/$item.sql");
                break;
            case 'pigz':
                exec("pigz -9 -f $pathBackup/$item.sql");
                break;
            case '7z':
                exec("7za a $pathBackup/$item.7z $pathBackup/$item.sql");
                break;
            case 'xz':
                exec("xz $pathBackup/$item.sql");
                break;
            case 'pxz':
                exec("pxz -9 -f $pathBackup/$item.sql");
                break;
            default: break;
        }
        if (file_exists("$pathBackup/$item.sql")) {
            exec("rm -f $pathBackup/$item.sql");
        }
        $period = round(microtime(true)-$tDeb,3);
        echo date('Y-m-d H:i:s') ." - Compress '$item.sql' in $period s.\n";
    }
}

// --- Rename if process dir is in use
if ($backupDirProcess != $backupDir) {
    rename($pathBackup, $backupDir.'/'.$dirname);
}

// --- Delete old backup
if ($backupMax > 0) {
    foreach ( glob("$backupDir/$type-*", GLOB_ONLYDIR) as $filename ) {
        $date = substr(basename($filename), strlen($type) + 1);
        $backupDelete[$date] = $filename;
    }
    krsort($backupDelete);
    $i = 0;
    foreach ( $backupDelete as $k => $delete ) {
        if ( $i >= $backupMax ) {
            passthru("rm -rf $delete");
            echo date('Y-m-d H:i:s') ." - Delete backup $delete\n";
        }
        $i++;
    }
}

echo date('Y-m-d H:i:s') ." - End Backup.\n";
$period = round(microtime(true) - $tInit);

$hours = floor($seconds / 3600);
$mins = floor(($seconds - ($hours*3600)) / 60);
$secs = floor($seconds % 60);

$message = "Backup Databases in $hours h $mins min $sec s ($period s) :\n";
$message.= print_r($backupParams['db'], 1);
sendMail( array('supportdev@scores-decisions.com'), 'Backup MySQL '.$backupMysqlHost, $message);

// --- End

function putMasterStatus($link, $file, $dbTable, $master, $flags=0) {
    if ( $master ) {
        $result = $link->query('SHOW MASTER STATUS');
        while ($row = $result->fetch_assoc()){
            file_put_contents($file, date('Y-m-d H:i:s').", $dbTable,".$row['File'].','.$row['Position']."\n", $flags);
        }
    } else {
        $result = $link->query('SHOW SLAVE STATUS');
        while ($row = $result->fetch_assoc()){
            file_put_contents($file, date('Y-m-d H:i:s').", $dbTable,".$row['Relay_Master_Log_File'].','.$row['Exec_Master_Log_Pos']."\n", $flags);
        }
    }
}

function sendMail($emails, $sujet, $message) {
    $from = 'supportdev@scores-decisions.com';
    $headers = 'Reply-To: '.$from."\n"; // Mail de reponse
    $headers .= 'From: "Support DEV"<'.$from.'>'."\n"; // Expediteur
    $to = join(' ,',$emails); //recipient
    $mail_body = $message; //mail body
    $subject = $sujet; //subject
    mail($to, $subject, $mail_body, $headers);
}