From: Paul Choi (paulchoiplaxo.com)
Date: Tue Jan 06 2009 - 18:56:29 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]