What is the best way to do incremental backups in MySQL?

Question

We are using MySQL version 5.0 and most of the tables are InnoDB. We run replication to a slave server. We are thinking of backing up the MySQL log files on a daily basis.

Questions

• Is there any other way of doing an incremental backup without using the log files?
• What are the best practices when doing incremental backups?

Answer 1

AFAIK the only way of doing incremental backups is by using the binary-log. You have other options if you want to do full backups (InnoDB hotcopy), but incremental means that you need to log all transactions made.

You need to ask yourself why you're backing up data. Since you have a slave for replication, I assume the backup is primarly for reverting data in case of accidental deletion?

I would probably rotate the logs every 1 hour and take a backup of it. Meaning, restoring would leave the data at most 1 hour old, and you can restore to any point in time since the last full snapshot.