So, automatically at 3:30pm on Wednesdays and Fridays, Joe's MySQL database is dumped to /var/backups/mydb-backup.

One Wednesday morning, Joe's database crashes. Joe calmly restores last Friday's backup and pumps his fist triumphantly when his database is backed up within minutes. Over the next few moments, Joe's inbox is flooded with angry e-mails. At first, Joe doesn't understand why everyone is so mad at him. He conquered the database crash and saved the day, but an e-mail from Susie Sales lets Joe know where he went wrong:

Joe:

I made a ton of sales this weekend, and keyed them into the system. When I told my manager about it, he was unable to pull any of them up on his computer. I am also no longer able to see them. What did you do to all my sales?

-Susie

Where did Joe go wrong? The obvious place to point the finger is at the duration between Joe's backups, but even if Joe made backups day to day, this problem would still exist for sales that happen between the last backup and a database crash. Information in databases are often so time sensitive that the immediate second after a backup is made, is a second of information too valuable to lose. Therefore, it is often important for database admins to utilize incremental backups.

Incremental backups work by storing every data modifying query made to a data, i.e. INSERT, UPDATE, DELETE, etc. Utilizing these backups allow Joe Sysop to take his database from the time of the last full backup to the time of the database crash, minimizing data loss. In terms of overhead, MySQL tests yielded only a 1% decrease in performance when enabling the binary log (the key component in incremental backups).

In order to utilize incremental backups in MySQL, you need to remember a few things:

1. Logging 101.Binary logging is where the magic of incremental backups take place. Without the binary log, you have no record of all the transactions and modifications to your database, and thus nothing to recover from. Be sure to add --bin-logto mysqld.

2. Flush your logs.As disgusting and routine as this may sound, this actually is in reference to your MySQL log files. Running the command FLUSH LOGSwhen you do your full backups will stop the current binary log file and start a new one. This allows you to decifer what stored transactions are between backups A and B. A good practice is to store these binary logs with your backups, giving you a clear picture of what happened between backup to backup.

3. No substitutions please. Incrementalbackups are NOTa substitution for full database backups. They are simply a tool to further minimize data loss, and rely on full database backups to work efficiently.

The actual process of restoring your incremental backup is similar to your restoration of a regular backup. If you have been flushing your logs regularly, and we hope you do, your binary log files should be separated from full backup to backup. Utilizing the command line tool mysqlbinlogwill parse the events logged into executable SQL. You can pipe this output directly into MySQL, but it is probably safer to dump this output into a text file to review. As SQL administrators, we never make SQL mistakes on a production database, but in the event we executed DROP `important_table`; on accident, we wouldn't want to re-issue the command via our incremental backup. Then we can review the code to make sure it was not the source of our data loss, and execute the code safely back into MySQL with all the information restored from our last full backup.