Automatic MySQL backups using PHP

February 1st, 2008

Okay, as pointed out in a comment on my last post, there are lots of ways to do this sort of thing. Many (most?) real sysadmins would probably choose to write a short shell script. Here’s the deal: I actually like PHP. Anyway, in my last post I shared a way to do site backups using PHP. Here’s a short followup, how to backup a MySQL database using PHP. You can put this script into the same file as the last script, to be run by a cron job, or you can do this at a different time. Like the last one, this script also originated from this site.

I hope someone finds this useful.

Again, sorry for the crossed out lines in the beginning of the script when the system command is called. My blog software uses a double dash to initialize and terminate the strikeout, and for some reason, even though the text is set as “preformatted,” parts are still being struck out. I presume it is because of the presence of quotation marks in the line. The interior of the line should read

mysqldump –user=$dbuser –password=$dbpswd –host=$host $mysqldb

 <?php $emailaddress = "yourname@emailaddress.com"; $host="localhost"; // database host $dbuser="weird_user_name"; // database user name $dbpswd="strong_password"; // database password $mysqldb="important_data"; // name of database $filename = "~/sqlbackup_important_data." . date("d") . ".sql"; if ( file_exists($filename) ) unlink($filename); system("mysqldump user=$dbuser password=$dbpswd --host=$host $mysqldb > $filename",$result); $size = filesize($filename); switch ($size) {   case ($size>=1048576): $size = round($size/1048576) . " MB"; break;   case ($size>=1024): $size = round($size/1024) . " KB"; break;   default: $size = $size . " bytes"; break; } $message = "The database backup for " . $mysqldb . " has been run.\n\n"; $message .= "The return code was: " . $result . "\n\n"; $message .= "The file path is: " . $filename . "\n\n"; $message .= "Size of the backup: " . $size . "\n\n"; $message .= "Server time of the backup: " . date(" F d h:ia") . "\n\n"; mail($emailaddress, "important_data db backup" , $message, "From: Website <>"); ?>

Entry Filed under: General