Automated off-site MySQL backups with cron and PHP
Posted on February 7, 2010 at 1:20 am
I am the technical administrator for a decently sized vBulletin forum that is hosted on a system offering a lot of performance at the cost of some storage space. I also have hosting and a number of domains on a system that offers less performance, but ridiculous amounts of bandwidth and storage. Lastly, I have a strong need for peace of mind, and off-site database backups seem like a nice way of getting a bit of that back.
The following script is run by this cron job:
|
1 2 |
# runs at 3:30am every day
30 3 * * * /backup/backup_db.sh |
This script only does a few things. First, it creates a ‘filename’ variable consisting of ‘database_’ and then the date. Next it runs mysqldump on the database in question, outputting to /backup/$filename.sql. It sleeps for a bit (gzip didn’t seem to want to work immediately) and then compresses the .sql file into a more manageable size. From there, it opens a connection to an off-site server where I’ve got a ton of storage and bandwidth, and uses a server-to-server connection to transfer this gigantic file in a few minutes. Once that’s complete, it removes the file it created and waits until 3:30 the next morning to do it all over again.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
#!/bin/sh
# Remember to chmod this file to 755 so it can be executed
filename="backup_"<code>eval date +%Y-%m-%d</code>
echo "Beginning MySQL dump."
mysqldump databasename --host=db.yourdomain.com --user=username --pass=password > /backup/$filename.sql
echo "MySQL dump complete. Beginning file compression."
sleep 3
gzip /backup/$filename.sql
echo "Compression complete. Beginning SCP transfer."
scp /backup/$filename.sql.gz username@offsitestorage.com:/home/username/backup
sleep 3
echo "File transfer complete. Cleaning up and exiting..."
rm /backup/$filename.sql.gz |
To get the scp command to work without user input, you have to add the RSA key fingerprint to the list of known hosts. You can do this manually by editing the appropriate file, or you can do it the easy way: Open a terminal and SSH into your web server, and from that session, SSH into your backup server. You will get a warning claiming that “the authenticity of host … can’t be established — are you sure you want to continue connecting?”. Type “yes” and hit enter, and you will see a warning stating that this RSA key was added to the list of known hosts.
When your database backup is a 450MB file, you’ll find that space starts filling up quickly and the directory starts becoming needlessly cluttered. So again, we turn to crontab:
|
1 2 |
# database cleanup script; runs at 4am (the backup process only takes a few minutes)
0 4 * * * /home/username/db_backup/clean.php |
…which dutifully executes the following cleanup script. This scans a directory for all files but itself, checks their ‘last-modified’ date against the current date and if it’s more than 7 days old, deletes them. This ensures that I’ve always got the most recent week’s worth of backups.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
// /home/username/db_backup/clean.php
// If there are any files older than one week in this directory, nuke 'em
$dir = $_SERVER['DOCUMENT_ROOT'].'db_backup/';
/// Don't scan for yourself :P
$script_name = pathinfo($_SERVER['SCRIPT_NAME']);
$script_name = $script_name['basename'];
$script_name = substr($script_name, 0, strrpos($script_name, '.'));
// Get your numbers straight
$time = mktime();
$one_week = 60 * 60 * 24 * 7;
// See how many backups already exist in this directory
$count = count(glob($dir."*.sql.gz"));
// Don't see them all? We should probably send an email...
if ($count < 7) {
$to = 'my-email@my-domain.com';
$subject = 'ERROR: SITE BACKUP FAILED!';
$message = 'Deleted files have been detected. Check to assure script is running properly.';
mail($to, $subject, $message);
die();
// There's enough files? Trim them.
} else {
if (is_dir($dir)) {
if ($dh = opendir($dir)) {
while (($file = readdir($dh)) !== false) {
if ((substr($file, 0, 1) != '.') && (substr($file, 0, (strlen($script_name))) != $script_name) && (filetype($dir.$file) != 'dir')) {
$filetime = filemtime($dir.$file);
if (($time-$filetime) > $one_week) {
unlink($dir.$file);
}
}
}
closedir($dh);
}
}
} |
So, that’s it. Automated off-site MySQL backups using cron and PHP. Enjoy the peace of mind.
March 12th, 2010 on 3:11 pm I know its a pedantic optimization, but shouldn't
March 13th, 2010 on 11:47 am That would certainly work. However, it's been my experience that a little bit of processing power (in this case, very little) is a good tradeoff for extended readability. This thing only runs once a day :)
March 17th, 2010 on 2:01 pm Okay, so before I continue this argument, I want to acknowledge that we're arguing pointless optimizations here that won't add to actual performance in any significant way... How about:
June 29th, 2011 on 7:15 pm What about the need to turn the site off before the backup taken? Or lock the tables then unlock them when the dump is done? mysql> FLUSH TABLES WITH READ LOCK; mysql> UNLOCK TABLES; I'm new to this so be easy on me.
July 26th, 2011 on 10:27 pm @Travis: That is probably a really good idea, especially once your database starts increasing in size and taking longer to dump. One of these days I'll get around to updating the script :)
August 12th, 2011 on 1:33 pm @admin: I've done some more digging and it looks like mysqldump already takes care of locking and unlocking tables. http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html Like I said, I'm new to this, so maybe I didn't read that right, but that's what I got out it..
August 18th, 2011 on 8:46 am @Travis -- thanks for looking into that!