Backing up MySQL?

You can use binary logs to perform something like an incremental backup. When enabled, binary logs contain records of every insert/update/delete performed on the database. So you can replay the logs to restore the database to a certain point in time. (If you replay the logs in a different server, you've got master-slave replication.)

Just make sure to flush the logs appropriately, and also make full backups at a certain interval.

If you can tolerate downtime, here is a quick-and-dirty possibility:

1. Run 'mysqladmin refresh' to flush the tables and logs to disk.

2. rsync the MySQL directory (usually something like /var/lib/mysql).

3. Stop MySQL.

4. rsync the MySQL directory again. Since you are only copying the changes since the refresh, this should complete relatively quickly. You know all the files are in a quiescent state as the server is stopped.

5. Start MySQL.

This isn't for heavy-duty sites where uptime is critical and transactions are fast and furious, but is a simple solution for simple sites.

I use mysqlhotcopy in conjunction with rsync.

A couple of other thoughts:

1. If the database is on an LVM partition and you use InnoDB, you can perform hot snapshot backups using lvm and let InnoDB handle recovery from its logs (if a restore is ever needed).

Setting up InnoDB to have one file per table might ease backups too.

2. You could have a MySQL slave and make your mysqldump backups from that without any write-lock issues.

http://mike.kruckenberg.com/archives/20 … ups_u.html">http://mike.kruckenberg.com/archives/2006/05/mysqlbackupsu.html

http://www.scribd.com/doc/3920492/Read- … QL-Edition">http://www.scribd.com/doc/3920492/Read-World-Web-Performance-and-Scalability-MySQL-Edition

This second link is simply an awesome resource and mentions both concepts.