Which way is best to do backups?

[Chris Barnes]

There is a debate in my office on the best way to backup our MySql databases (plural - all of them).

(1) Create a script which use MysqlHotCopy, then tars & bz2 the directory, then scp it over to our backup machine. This is what we are currently doing.

(2) use RSnapshot (which we already use for other files). Rsnapshot uses mysqldump.



Issues we're having:
* Restoring the files created by hotcopy has proven to be "problematic" (admitedly, it could be because I don't know how to do it properly).

* mysqldump would have issues with file locking (we run our backups at 1:00am, so the likelyhood of database updates during the dump is very small). It *seems* that restoring from mysqldump is easier.