Tuesday, October 20, 2009

Backing up MySQL databases

Oxford have installed a simple script to backup the DPM mysql db once a day at 6am.
The script was loosely based on Glasgow's example here .

In order to restrict the file names produced to just 7, I've opted to use the current day rather than date.

[root@t2se01 ~]# cat /root/mysql-dump-pdg.pl
# Loosely based on the Glasgow script but simplified.
# Select the current day only as we want to have just seven unique file names which will be overwritten
# thus reducing the total backup size.

@weekDays = qw(Sunday Monday Tuesday Wednesday Thursday Friday Saturday);
($second, $minute, $hour, $dayOfMonth, $month, $yearOffset, $dayOfWeek, $dayOfYear, $daylightSavings) = localtime();
$theTime = "$weekDays[$dayOfWeek]";
#print $theTime;


# Read mysql password
open(PW, $mysql_pw_file) || die "Failed to open password file $mysql_pw_file: $!\n";
chomp $mysql_pw;
close PW;

# Dump the db now
chdir $backup_dir || die "Failed to change to backup directory $backup_dir: $!\n";

system "/usr/bin/mysqldump --user=$mysql_user --password=$mysql_pw --opt --all-databases | gzip -c > mysql-dump-$theTime.sql.gz";
die "Mysql failed died with exit code $?\n" if $? != 0;

This is run by /etc/cron.d/mysql-dump
0 6 * * * root /root/mysql-dump-pdg.pl

So far it seems to work in testing!

No comments: