Backing Up MySQL dumps

On the heals of my un-deletion catastrophe, I thought It would be a good idea to automate a backup routine that would insure that I always have good MySQL dumps. Using logrotate, I crafted a config that will keep 30 days worth of compressed dump files. I made one for each MySQL schema (database) that I deemed critical.

I create two files in the /etc/logrotate.d, one fore each database, and it looked something like this

/var/backup/mysql/schema.sql {
rotate 30
daily
compress
create 644 mysql adm
dateext
maxage 30
prerotate
/usr/bin/mysqldump -u user -ppassword schema > /var/backup/mysql/schema.sql
endscript
}

It is interesting to note that since the DUMP files are just SQL Text, and repeatable data, they compress quite nicely (18 MB dump shrinks to 2.5MB) and since I have over 30 gigs on this 32 gig drive, I think I will be fine.

I use MySQLDump instead of backing up the actual files themselves because they are a bit more portable, and can always be manually checked, tweaked, or repaired if needed.

About these ads