A quick function to back up MySQL databases

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 the server.

The general rule of thumb for backups is to backup often enough to where you won’t mind losing the work since that last backup. So, if you don’t want to lose more that an hour of work, you should back up every hour. If you don’t want to lose any work you should have some sort of RAID set up. But, RAID is above and 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 need:

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

And, the PHP functions we’ll be using:

• date
• file_exists
• mkdir
• mysql_connect
• mysql_fetch_object
• mysql_list_dbs
• shell_exec

And, the code:

$tempdate = Date("Ymd-Hi");
$conn = mysql_connect("host", "username", "password") or die("Error:n".mysql_error($conn));
$dblist = mysql_list_dbs($conn);
$zipname = "/directory/".Date("Y")."/".Date("m")."-".Date("F")."/full-".$tempdate.".zip";
/*	Make sure subdirectories exist	*/
if (!file_exists("/directory/".Date("Y")))
	{
	mkdir("/directory/".Date("Y"), 0777);
	}
if (!file_exists("/directory/".Date("Y")."/".Date("m")."-".Date("F")))
	{
	mkdir("/directory/".Date("Y")."/".Date("m")."-".Date("F"));
	}
while ($row = mysql_fetch_object($dblist))
	{
	echo $row->Database."n";
	$temp = shell_exec("mysqldump -uUserName -pPassword -hHost ".$row->Database." >/directory/".$row->Database.".sql");
	$temp = shell_exec("zip -D -m ".$zipname." /directory/*.sql");
	}

Everytime 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.

Personally I have this running as a cron job. For this site, it runs daily. For other sites of mine it runs every two hours. It just depends how much I’m willing to lose. If I lost this site, I would only lose at most an article or two for the day. On other sites I could lose much more.