multi-db backup php cli script. simple

This is a quick backup script that you can run at the command line.
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.

$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");


$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";

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

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


  • 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.