MySQL has become an important part of most Web servers, especially over the last few years. With all those databases, the risk of corruption, intrustion, data loss, hardware failure, and lots of other factors necessitate automated backups.
Luckily, backing up MySQL isn’t nearly as hard as it might sound. Actually, it’s only one command. But, not many people have only one MySQL DB, and dumping all of them to a single file can be hard to parse should you only need to import one. This is easily remedied by making a script run on cron to dump them all individually.
I set this script to run once a week, monday morning at 5am. It generates a logfile as well, info.txt.sl, which contains the date, a list of files, and their MD5Sum. Finally, it uses bzip2 to compress the files efficiently. MD5Sums are critical in determining the integrity of the archive and to ensure the database has not been corrupted or altered since the snapshot was taken.
Read the rest of this entry »