Backing Up MySQL with PHP

Thursday, April 29, 2004 at 12:13 am | Comments off

This morning I woke up and found that my site was gone. I'm not just talking about the server being down here - no, the site (or better, my blog's contents) was gone. I was getting database errors left and right (perhaps that means I need to do some better error catching in xBlog, eh?), so I logged into phpMyAdmin. Or I tried anyway. I wasn't able to log in, so I logged into cPanel for this domain. I went to the MySQL database section, and much to my dismay, none of my databases showed up. At this point, I officially freaked out. I immediately went to the support center for my hosting, and saw that someone else was having the same problems. There was hope, at least it wasn't just my databases. One of the server administrators came on, and was simply able to restart the SQL server to fix the problem. Thankfully, all my data was still intact.

I did, however, learn a most important lesson (one that I should have already known). Backup you data. Since I didn't want to have to manually go into phpMyAdmin and backup the database for xBlog each day, I started looking at what I'd have to do to write a PHP script to do this. A bit of research showed me that the easiest way would be to use mysqldump, with a shell command.

So, I rolled up my sleeves and wrote the following PHP function to make automatically backing up MySQL databases an easy thing.

<?PHP
/* Function to backup a mysql database table
 * @param host
 *     The database host name (server)
 * @param user
 *     Database user
 * @param password
 *     Database password
 * @param table
 *     Database table name
 * @param backup_path
 *     The path to backup directory
 * @param backup_name
 *     The name of the backup file - no extension
 */
function mysql_backup($host, $user, $password, $table, $backup_path, $backup_name) {
  $day = date('w');
  # gzip (.gz)
  $backup = $backup_path.$backup_name.'_'.$day.'.gz';
  exec(sprintf('mysqldump --host=%s --user=%s --password=%s %s --quick --lock-tables --add-drop-table | gzip > %s', $host, $user, $password, $table, $backup));
  # sql (.sql)
  //$backup = $backup_path.$backup_name.'_'.$day.'.sql';
  //exec(sprintf('mysqldump --host=%s --user=%s --password=%s %s --quick --lock-tables --add-drop-table > %s', $host, $user, $password, $table, $backup));
}
mysql_backup('localhost', 'user', 'password', 'xblogpro', '/root/path/to/backups/', 'backup');
exec("/usr/bin/lynx http://xxx.xxx.xxx.xxx/backups/mysql_backup_grabber.php");
?>

The parameters that are passed are explained in the script, so I won't go into detail on what those are again. For more information on the various options I passed to the mysqldump command, take a look at the documentation. The exec function runs a system command that will tell the Lynx browser to run the PHP script that is on my local server, which is where I wanted to back the files up to. I chose to use this, as I really wouldn't have felt all that safe with my backups sitting on the same server as the MySQL server. If you aren't in need of such things, simply remove the exec function to only backup the data to the regular server.

You'll see that I have two different exec commands. The one that is uncommented is the one that I'm using. It will gzip the files right away. If you'd rather just export a .sql file, simply comment out the gzip lines, and uncomment the sql lines.

I chose to append the day of the week to each backup, so at any given point, I'll have the last 7 days backed up. I know the chances of something happening while the script is in the process of backing up the data is rather slim, but I'd rather not risk it.

The contents of mysql_content_grabber.php is very straight forward. I simply copy the file from the remote sever to my local server. Here it is (it must run on the local server, obviously):

<?PHP
$day = date('w', time()+4000);
copy("http://www.ryanbrill.com/backups/backup_$day.gz", $_SERVER['DOCUMENT_ROOT']."/backups/backup_$day.gz");
?>

Since I am located in CST, and the server is in EST, I added a little over an hour to the time on this script. You may need to adjust for the timezone difference between servers. Why a little over an hour? 'cause my local computer's clock and the remote server's clocks are probably not exactly synchronized. ;)

Finally, I set up a cron job to run the script every night at midnight. The cron looks something like this:

0 0 * * * php /root/path/to/backups/mysql_backup.php

There you have it. A quick and dirty way to automatically backup your MySQL databases with PHP. Hope it'll save a headache or two.

Edit: A small update was made to the script. The original used a header redirect to call the script that is running on my local host, however, that was not working with the cron job. I now use a system command to call the Lynx browser, which works much better.

Comments

Jebster
April 29th, 2004
12:51 AM | #

Very handy little script! Would require a static IP of your home computer though, that or you would have to update the script whenever you notice your IP has changed. Not to mention it would require your computer to be on at the time of the backup... Now you just have to make it turn on your computer if it's off, hehe

DarkBlue
April 29th, 2004
1:31 AM | #

You should also read Andy Budd's article on the subject and, dare I say it, my comments on the same post. There's a wealth of information there.

Nakijo
April 29th, 2004
3:06 AM | #

Or you could just use SQL Server and establish a regular maintenance plan...

I mean, call me shallow, but I prefer a straightforward GUI with some serious options for customisation, saving the option of commandline for if things need to get hardcore. My experiences with MySQL just confirmed for me that, commercially-speaking, SQL Server is by far the better option

Is the cost why you use MySQL, or is it one of those philosophical things? I've been meaning to ask this for some time, because I just don't get it. But then, I'm such a heathen that I don't even use PHP...

Piers
April 29th, 2004
3:57 AM | #

The hosting that Ryan uses (as do I) doesn't support SQL Server. TBH, I don't know many hosts that do.

Robert Wellock
April 29th, 2004
4:58 AM | #

I thought it was because of your Google advertising albeit now this page fails to be well-formed XML.

You must have been burning the candle too long... Had you stayed with 'application/xhtml+xml' you might have noticed the errors straight way as being around/prior to line 118.

Ryan
April 29th, 2004
8:07 AM | #

Yes, I had read Andy's article, along with a good number of articles that showed how to do this in Perl. I couldn't seem to find much on the subject in PHP, though. I will add, however, that your comments were very helpful. That's how the --quick option got in there. ;)

Nakijo, as Piers said, the host that I use only supports MySQL and PostgreSQL, which is fine, as MySQL is the database that I am most familiar with and would probably use most of the time anyway. PHP and MySQL are very closely integrated (as of version 4 releases, anyway), so it is a logical solution to use MySQL if you are using PHP.

Thanks for the note, Robert. Guess that's what happens when I post late at night. I would have liked to stay with application/xhtml+xml, but Google's ads did not even show up, due to the document.writes, I suppose.

Nakijo
April 29th, 2004
7:25 PM | #

Well, that explains it. Thanks. I can't see myself using PHP unless specifically requested by a client (and, to be honest, my clients are savvy enough to know or care what language I use), so I guess I won't have to worry about it

But I was curious, so thank you!

Richard@Home
April 30th, 2004
7:18 AM | #

Great article Ryan (as usual :-)

As I have the luxury of hosting my own server (@Home) backing up my MySQL databases is simply a question of copying the whole MySQL installation directory (which contains the database storage sub directories) every night to a safe place.

It takes about 2 minutes to restore all the databases, permissions etc simply by copying back the directory structure if something go wrong.

As for the fixed IP - take a look at www.no-ip.com . I've been using their free service for a couple of years now with NO complaints.

BTW - love the blue highlight in your comments form :D

Ryan
May 3rd, 2004
4:21 PM | #

I had to make a small change to the script. The original worked fine when run through a browser, but was not working with the cron job, due to the header redirect. So, the script now uses an system command (exec) to open the Lynx browser and run the script.

Bill Teeple
April 20th, 2005
12:48 AM | #

Excellent script - easy to implement and fairly elegant in its makeup... Is there a way to implement an e-mail that lets me know the script completed succesfuly - I know that CRONTAB does offer some basic e-mail functionality...

Bill

Comments are automatically closed after 45 days