Backing up a mysql server
In an increasingly data dependant world, backing up has never been more important. This especially applies to databases on servers. It is shocking to know how many people fail to backup their data out of sheer ignorance or a failure to understand the purpose of backing up until its too late (I used to be one of those people, but then I was young and didn’t have much worthwhile data on my computer anyway).
With the advent of the LAMP server architecture, mysql databases are increasingly in use on servers (this is now even more true now with Ruby On Rails setups). In order to protect all my database data, I wrote a quick and simple script using existing mysql tools found in most linux setups to perform a hot backup. This backup can be for a single database or the entire DBMS.
The script is called mysql-backup and to describe simply it performs the following operations:
- Setup the filename for the backup with the current date and time
- Set the backup folder (/var/lib/mysql/backup - a standard folder in Ubuntu setups)
- Do a backup of all databases (the –all-databases line can be customised if you wish to only backup a single database on your server)
- Purge any older backups prior to the last 10 (if 10 already exist - this line can be commented out if you wish to keep all backups, but note that this might take up a lot of space in the long run)
The best way to run the script is through a cron job. I usually perform my database backups at least once a day on low demand machines, and twice daily on high use http servers.
You can find a copy of the script on the Linux Scripts page of this site.
