First published on July 5, 2006
This script will send an e-mail to you with an .sql file attached, thus enabling you to back up specific tables easily. If you have a database-driven site, your mysql info is what is most valuable to you! You could even set up an e-mail account just to receive these backups…
First of all, this script works best if you place it in a non-web accessible folder and run a daily cron job on it. Cron is a server “tool” that can run scripts regularly or at specified times (thus you don’t need to call them in your browser). I used to be scared of cron but it’s really easy. Here are two tutorials if you are interested Cron Tutorial 1 … Cron Tutorial 2. If you have cPanel on your server it’s even easier (see here).
Known limitations of this script: if you have a big database table over 2mb, you will probably run into php timeouts and php mail attachment limits. Check this comment for information about compressing the backup file before sending or storing it.
<?php // Create the mysql backup file // edit this section $dbhost = "yourhost"; // usually localhost $dbuser = "yourusername"; $dbpass = "yourpassword"; $dbname = "yourdb"; $sendto = "Webmaster <email@example.com>"; $sendfrom = "Automated Backup <firstname.lastname@example.org>"; $sendsubject = "Daily Mysql Backup"; $bodyofemail = "Here is the daily backup."; // don't need to edit below this section $backupfile = $dbname . date("Y-m-d") . '.sql'; system("mysqldump -h $dbhost -u $dbuser -p$dbpass $dbname > $backupfile"); // Mail the file include('Mail.php'); include('Mail/mime.php'); $message = new Mail_mime(); $text = "$bodyofemail"; $message->setTXTBody($text); $message->AddAttachment($backupfile); $body = $message->get(); $extraheaders = array("From"=>"$sendfrom", "Subject"=>"$sendsubject"); $headers = $message->headers($extraheaders); $mail = Mail::factory("mail"); $mail->send("$sendto", $headers, $body); // Delete the file from your server unlink($backupfile); ?>
Download Peter’s automatic mysql backup tool (in the zip file, backup.php is the file you need to execute).