Chef de projet developpeur web lyonnais #geek #apero #ski #sun #php #linux #shell #dev #web
You need to automatise the backup of your mysql databases on a linux system with php-cli, mysqldump and bzip2 ?
So this script is for you ^^
I use this script on my servers to backup my databases every nigth.
All Databases of the 15 last days and 2 by month are stored in bzip2 format in separate files (one file by database).
Set your preferences in this script, add it in your daily cron jobs and enjoy ;)
You can download this script in tar.gz or read the phps.
The script :
<?php
/*
filename : backup-mysql.php
short description : mysql backup utility
desciption : Backup all databases of a specified user.
All databases are stored in separate files.
Store database of the $intNbPastDay past days.
Store database of all day of a month specified in $arrDayOfMonth.
usage : Add 'php /home/phpcli/backup-mysql.php' to your daily cron jobs.
author : Benoît RAUX -> twitter.com/rauxbenoit
revision : 2010-02-04
*/
// CONFIG VARS
$strMysqlServer = 'localhost';
$strMysqlUser = 'user';
$strMysqlPass = 'pass';
$strBackupDir = '/data/backup/mysql/';
$arrDayOfMonth = array('01','15');
$intNbPastDay = 14;
// DISPLAY AND EXEC A SHELL COMMAND
function my_shell_exec($str){
echo $str . "\n";
echo shell_exec($str) . "\n";
}
// LIST DATABASES AND EXEC A 'mysqldump' AND A bzip2 ON IT
$argMysql = mysql_connect($strMysqlServer,$strMysqlUser,$strMysqlPass);
if (!$argMysql) {
die('Warning could not connect to mysql : ' . mysql_error());
} else {
$reqListDb = 'SHOW DATABASES;';
$recListDb = mysql_query($reqListDb) or die(mysql_error());
while($objDb = mysql_fetch_object($recListDb)){
$strDb = $objDb->Database;
$strDbBkp = $strDb . '-' . date('Ymdhis') . '.sql.bz2';
$strCmd = 'mysqldump ';
$strCmd.= '-h' . escapeshellcmd($strMysqlServer) . ' ';
$strCmd.= '-u' . escapeshellcmd($strMysqlUser) . ' ';
$strCmd.= '-p' . escapeshellcmd($strMysqlPass) . ' ';
$strCmd.= escapeshellcmd($strDb) . ' ';
$strCmd.= '| bzip2 > ';
$strCmd.= $strBackupDir;
$strCmd.= $strDbBkp;
my_shell_exec($strCmd);
}
mysql_close($argMysql);
}
// LIST ALL DATABASES FILES IN BACKUP DIRECTORY, AND PARSE THIS LIST
$intTimeRef = strtotime(date('Ymd'));
$arrDbFile = glob($strBackupDir . '*.sql.bz2');
foreach($arrDbFile as $strDbFile){
// EXTRACT FILE'S DATE
$strDbFileName = basename($strDbFile,'.sql.bz2');
$strDbFileDate = substr($strDbFileName,(strrpos($strDbFileName,'-')+1),8);
$intDbFileTime = strtotime($strDbFileDate);
// TEST DATE MATCH STORAGE RULES
$booDel = true;
// IN $intNbPastDay PAST DAYS ?
if(($intTimeRef - $intDbFileTime) <= ($intNbPastDay * 60 * 60 * 24)){
$booDel = false;
}
// SPECIFIED IN $arrDayOfMonth ?
if($booDel == true){
if(in_array(date('d',$intDbFileTime),array('01','15'))){
$booDel = false;
}
}
// DELETE ?
if($booDel == true){
$strCmd = 'rm -f ' . $strDbFile;
my_shell_exec($strCmd);
}
}
?>
I invit you to follow this tutorial to test this script.
1 - Create a directory for your backup (in this example in : /data/backup/mysql/).
mkdir /data/backup/ mkdir /data/backup/mysql/
2 - Upload and extract backup-mysql.php on your server (in this example in : /data/backup/script/).
mkdir /data/backup/script/ cd /data/backup/script/ wget code.rauxbenoit.info/phpcli/backup-mysql.tar.gz tar -xvf backup-mysql.tar.gz rm backup-mysql.tar.gz
3 - Edit the script and set your settings (with vim for example) :
vim backup-mysql.php
$strMysqlUser = 'yourusername'; $strMysqlPass = 'yourpassword'; $strBackupDir = '/data/backup/mysql/';
4 - Test the script
php backup-mysql.php
If you have some error, verify your settings.
If you have the error “Warning could not connect to mysql :…” : verify $strMysqlUser and $strMysqlPass
You need php, php-cli, mysql, mysqldump and bzip2 intalled on your server.
If you have some masseag like :
“mysqldump -hlocalhost -uyourusername -pyourpassword test | bzip2 > /data/backup/mysql/test-20100205032633.sql.bz2”
It works !
5 - Verify the file list
ls /data/backup/mysql/
You must have all your databases in bz2 format
6 - Add to cron :
crontab -e
Add the line :
0 3 * * * php /data/backup/script/backup-mysql.php > /dev/null 2>&1
Means : run all nigth at 3 AM and redirect output to /dev/null (no log) 2>&1 (no mail notification)
You can verify your cron setting with :
crontab -l
If you see the new entry, it’s ok ;)
Now, if you need to restore a previous database
bzcat /data/www/_databases/test-20100205032633.sql.bz2 | mysql -uyourusername -pyourpassword testTweet
