As a system administrator, it is inevitable that one day you will be required to recover data. How you go about preparing for this day will determine whether or not you will be a successful system administrator.
I’ve seen my fair share of data loss and recovered my fair share of data from backup media, be it dvd’s, disks or tape. This has instilled in me the following philosophy regarding data safety, “Prepare, Implement and Test.” Here are the basic rules:
- Do what you can to safeguard the primary data medium through redundency, but be damned sure you don’t rely on that alone.
- Prepare, implement and TEST your backup plan.
- Test the hardware by physically pulling a disk out of the RAID array.
- Destroy the data and follow your documented procedures to restore the system from tape.
Follow these simple rules and you will have a reasonably good chance of becoming a data saviour in the time of need. And that time will come.
So after a slight meander around, it’s time to proceed with the topic of this post, namely our mysql database backups.
Hardware safety aside, what do we do to look after our mysql databases? Well, we have two flavours of mysql database to safeguard, namely:
- Those under our provider’s direct control
- Those under our direct control
The databases under our provider’s control are currently not of great importance, so they are being manually exported as and when changes are made, which is quite infrequently.
The databases under our direct control are more important and thankfully these are still very small, so we have a script that runs on a daily basis to back them up. The script takes three options, namely database name, database user and email recipient.
And what does the script do, you might wonder? Well, it uses mysqldump to export the database given as database name by the user given as database user, encrypts it using gpg, stores a local copy on the host and emails the encrypted database to the email address given as email recipient.
This ensures that I have a compressed and encrypted local copy available for restore as well as a compressed and encrypted offsite copy, in case the box self destructs.
The script is run automatically by cron every day and can be run several tines a day should it be necessary. As soon as the compressed filesize becomes too large for email to manage efficiently, then we will begin to look at other alternatives such as slave servers and remote synchronisation etc.
Well, I hope this little tidbit helps to keep at least one person’s data safe and sound.