Jed Reynolds wrote:
> If you are using LVM, you might consider snapshotting, however, doing
> a live snapshot without stopping mysql server would only work if you
> were copying only myisam tables. Mysql-hot-copy would probably be
> better, but either way, you need to flush your tables, which will
> briefly lock them, so they can get onto disk.
> In contrast, InnoDB actually needs to "shut down" to cleanly close its
> table structures before you can physically copy the filesystem.
If you can do an LVM snapshot on the dir(s) holding InnoDB files, then
you should actually be able to do a live backup. Once you restore from
the snapshot on a different host, mysql will behave as if it's
recovering from a crash. Then you can tell from the .err file where the
last binlog position was:
InnoDB: Last MySQL binlog file position 0 1574672, file name
Then you can use mysqlbinlog to apply binlogs until you are caught up.
The caveat is, again, that you have to do a snapshot on the entire
innodb_data_home_dir and innodb_log_group_home_dir. Hence both InnoDB
data file and log.
This approach is known to work with Solaris ZFS and should work the same
way with LVM.