Is there a faster way to backup and restore for mysql?

Question

Sql Server and Oracle have commercial products like Idera (Sql Safe) and Quest (LiteSpeed) that dramatically speed up backup and restores.

Does mysql have any commercial software like that?

I'm working with large amounts of data in mysql and if we have to restore it takes 8-10 hours. So I'm hoping to move to a commercial product that can do it faster.

Any thoughts?

Answer 1

MySQL Backup & Restore Databases Software 7.0 is one, but Restoring a backup is mostly a long series of INSERT statements so you check here for tips on improving performance Speed of INSERT Statements

Answer 2

You could check out xtrabackup. It is still alpha, but according to the last release announcement it "shows perfect stable results in our tests".

I have not tried it to know the performance, but it appears to be more low-level than the sql output that mysqldump generates.

Answer 3

Online backups are not exactly what you are looking for. The tools out there that exist for MySQL don't provide a lot of functionality that other DBMS vendors have. If you enable MySQL binary logs you can have "point in time" recovery. However, the initial load will still take forever.

http://dev.mysql.com/doc/refman/5.1/en/binary-log.html

http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html

Answer 4

If a commercial option works for you, check out InnoDB Hot Backup. They say that backup time is comparable to file copy time and recovery is faster.