Skip to content

Automatic MySQL backups using PHP

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

2 Comments

  1. ryan

    Just FYI, if you use the MySQL defaults your script will lock up the DB while it’s backing up. If you can live with your backup being slightly out of sync you can add the option "–skip-add-locks", or even better use InnoDB tables and then use the option "–single-transaction" to create a non-locking single point in time backup.

  2. matthew

    Ryan: thank you!

Comments are closed.