What sounds like a fairly easy topic, actually isn't! Databasing is an increadibly complex topic and even backing them up is increadibly complex. I don't claim to be a DBA, I just run a few, small databases. I set myself up a backup system and I thought I'd share that in case anyone found it useful. Don't take it as gospel!
I have a few MySQL databases that I want to backup every night. I don't want to do dumps as I have a full copy of the database that I have to backup every night. Just like my filesystem, I want to do a full backup twice a month and do increamental backups inbetween. This saves backup space, saves backup time and saves server load.
So, twice a month, we do a full backup of the database. Every other night we simply backup and clear the tranaction logs. This can be done without interfearing with the database. For this, I wrote four scripts, that run before and after a full backup and before and after a transaction log backup. These prepare for the backups and clear up afterwards.
The cold backup: This starts with a job on my backup system which runs the pre-backup script that I wrote. This script connects to my MySQL server and starts by locking the database to writes. Yes, that means that the database cannot be written to at the present time, so excercise caution. Next, the script copies all the MySQL files (/var/lib/mysql) to a staging directory. Finally, the script tells MySQL to start a new transaction log and then releases the lock on the database. Assuming all this went well, the backup system receives a clean exit from the script and goes on to backup to staging area. Upon successful completion of the backup, the backup system executes the post-backup script. This deletes the now redundant transaction logs and also clears out the staging directory.
The transaction log backups: When the database make a write, it records it in the transaction logs. By backing up the transaction logs, we have a list of all the changes since the last cold backup, otherwise known as an incremental backup. Again, when my backup system starts a transaction log backup, it runs the pre-transaction log backup script. This connects to the MySQL server and tells it to start a new transaction log. It then copies all the old transaction logs to the staging area. If this script exits cleanly, the backup system backs up the staging area. Following a successful backup, the backup system will start the post-transaction log backup script, which deletes the now backed up transaction logs and clears out the staging area.
Can be downloaded from our subversion repository, using either svn or http.