Backing Up MySQL dumpsOctober 23, 2007
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
create 644 mysql adm
/usr/bin/mysqldump -u user -ppassword schema > /var/backup/mysql/schema.sql
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.