April 26, 2008, 09:03:23 AM
Since I've spent quite a few hours over the past week getting a hacked Wordpress site back up and running it seems like a good time to go and look over my backup routines. I've got a script that backs up the database for this forum, and every other database on the server, nightly. But, since the site that got hacked was on another server, using an account that was donated, I hadn't been backing up like I was supposed to. On the up side, I was able to fix the database problems from the hack in about 2 minutes.
To run the script you'll need PHP and MySQL on your computer, both of which can be installed on pretty much any operating system. If you try to run it on a Windows machine you will have to change the paths from /home/whatever to c:\whatever or where ever you want the files stored.
$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);
}
What it does is connect to the MySQL server using the account name listed in information in the line that starts $conn and gets a list of each database. Each of those databases is dumped to a separate text file and then those text files are moved to a zip file.
I've got my Linux file server setup to run this script every night at about 2:15am through a cron job. Windows has a task scheduler that should do something similar. I'm sure Mac has something that'll do the same, it's just been about 15 years since I've used a Mac.
Logged
This backup from this server is 14mb zipped. But you're right, a database from a single Wordpress site or something similar should be pretty small. It would be even smaller if you dumped a CSV instead of the full set of SQL commands to rebuild the databases but that's a much more complicated task. 
