multi-db backup php cli script. simple

This is a quick backup script that you can run at the command line.
>
php backupdbs.php USER PASSWORD DATABASEPREFIX
It will dump all your database into one dump and compress it.
I use this on slaves. so it turns of the slave so you can get a consistent backup. Then turns it back on.

Not much to it.


<?php
//exit
$dbuser = $_SERVER['argv'][1];
$dbpw= $_SERVER['argv'][2];
$dbprefix= $_SERVER['argv'][3];
//$dbprefix= $_SERVER['argv'][4];

$DateTime = date("Ymd");

$dbconnection = mysql_connect('localhost',$dbuser,$dbpw);
$PullCustomer="STOP SLAVE";
$CsResults = mysql_query($PullCustomer,$dbconnection);
$PullCustomer="SHOW SLAVE STATUS";
$CsResults = mysql_query($PullCustomer,$dbconnection) or die("$PullCustomer");
$SlaveStatus=mysql_fetch_assoc($CsResults);

$Relay_Master_Log_File=$SlaveStatus['Master_Log_File'];
$Exec_Master_Log_Poss=$SlaveStatus['Read_Master_Log_Pos'];
$Master_Host=$SlaveStatus['Master_Host'];
$Master_User=$SlaveStatus['Master_User'];

$PullCustomer="SHOW DATABASES LIKE 'mysiteinc%'";
$CsResults = mysql_query($PullCustomer,$dbconnection) or die("$PullCustomer Not valid order number please check your url.");
while(list($database) = mysql_fetch_row($CsResults)){

$databases="$databases $database";

}
mysql_close($dbconnection);
//--delete-master-logs --master-data[=value]
$Command='mysqldump -u'.$dbuser.' -p'.$dbpw.' --databases '.$databases.' --opt | gzip -c > '.date("Y-m-d").'_'.$dbprefix.'.sql.gz';
$Result=exec($Command);

$Command='mysql -u'.$dbuser.' -p'.$dbpw.' -e "START SLAVE;";';
$Result=exec($Command);
print "backed up";

exit;
?>

  • jozwikjp

    this is executed from the shell. It doesn't really use any ram as it just executes some shell commands.
    I have used a similiar script with 30GB backups every day.

    In order to get a consistent backup I stop the slave and get it's position in the binary log file.
    Then I dump all the data with the log filename info and position in the filename.
    Upon finish I start the slave again.
    This way I don't have to take down a live machine or worry about data inconsistency in the backup.
    I also tar gzip or rar the file usually those backups compress down to 2gb every day.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.