I use rsync to do backups, and I'm not quite sure how to handle a live db. What do other people do?

It's easy to dump a db with mysqldump, but because it's not incremental, using rsync over the net becomes hard if you have a lot of data.

Is there a good incremental dump tool for 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://www.scribd.com/doc/3920492/Read- ... QL-Edition

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