«
»

How To Automatically Backup MySQL Databases to Your Computer

How often do your online databases get updated? How often do you back up? Are you dependent on your host for backups? Try this quick PHP script to backup your MySQL databases from your server.

The general rule of thumb for backups is backup often enough to where you wont mind losing the work since your last backup. So, if you don't want to lose more than an hour of work, you should backup every hour. If you don't want to lose any work at all you should have some sort of RAID set up. But RAID is beyond the scope of this article. Instead I am going to focus on scheduled backups using mysqldump.

This version of the script assumes you are on a Linux machine. A version for Windows would be very similar, but you would have to change how the paths are described and use pkzip or something similar instead of zip.

To be able to run this routine you will need:

  • A hosting account with MySQL that you can connect to from outside. In cPanel accounts this in done using the access hosts box under MySQL databases. Other hosting packages should have something similar.
  • PHP and MySQL installed on your local machine

The script
Below is the script itself. We're going to call it backup_database.php and assume you're saving it in the ~/scripts/ folder. You can name it and put it where ever you want, but when we schedule it in a few steps I'll be using those as examples.

PLAIN TEXT
PHP:
  1. $tempdate = Date("Ymd-Hi");
  2. $conn = mysql_connect("host", "username", "password") or die("Error:n".mysql_error($conn));
  3. $dblist = mysql_list_dbs($conn);
  4. $zipname = "/directory/".Date("Y")."/".Date("m")."-".Date("F")."/full-".$tempdate.".zip";
  5. /*  Make sure subdirectories exist  */
  6. if (!file_exists("/directory/".Date("Y")))
  7.     {
  8.     mkdir("/directory/".Date("Y"), 0777);
  9.     }
  10. if (!file_exists("/directory/".Date("Y")."/".Date("m")."-".Date("F")))
  11.     {
  12.     mkdir("/directory/".Date("Y")."/".Date("m")."-".Date("F"));
  13.     }
  14. while ($row = mysql_fetch_object($dblist))
  15.     {
  16.     echo $row->Database."n";
  17.     $temp = shell_exec("mysqldump -uUserName -pPassword -hHost ".$row->Database.">/directory/".$row->Database.".sql");
  18.     $temp = shell_exec("zip -D -m ".$zipname." /directory/*.sql");
  19.     }

Every time that the routine is run, all databases available to the username will be downloaded as a series of .SQL files and then zipped into /directory/year/monthnum-monthname/full-date.zip. If the year and month directories do not exist, they are created. The SQL files can be used to recreate the databases if needed.

Scheduling the backup with cron
If you're on a Linux server it's easy to setup a cron job to perform this backup however often you want. For this site I backup the database daily. For another site of mine it runs every 2 hours. You'll have to come to a balance of how much you're willing to lose in a crash versus how much space you are willing to use for your backup.

Edit your crontab so that it looks like the following and the backup will run at 1am every day.

PLAIN TEXT
CODE:
  1. 0 1 * * * ~/backup_database.sh

Question, Comments...

Do you have more questions. Please either leave a comment below or join us in our new forum.

Tags: , , , ,

This entry was posted on Friday, June 1st, 2007 at 10:07 am and is filed under Tips & Tricks. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply