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
/blah/blah/mysql_binlog/binlog.091206
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.
-Paul Choi
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org