List:General Discussion« Previous MessageNext Message »
From:Paul Choi Date:January 7 2009 12:56am
Subject:Re: Setup a replication slave without stopping master
View as plain text  
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
Thread
Setup a replication slave without stopping masterClaudio Nanni6 Jan
  • Re: Setup a replication slave without stopping masterJake Maul6 Jan
    • Re: Setup a replication slave without stopping masterBaron Schwartz6 Jan
    • Re: Setup a replication slave without stopping masterClaudio Nanni6 Jan
      • Re: Setup a replication slave without stopping masterJake Maul7 Jan
      • Re: Setup a replication slave without stopping masterBaron Schwartz7 Jan
  • Re: Setup a replication slave without stopping masterJed Reynolds6 Jan
    • Re: Setup a replication slave without stopping masterPaul Choi7 Jan
    • Re: Setup a replication slave without stopping masterBaron Schwartz7 Jan
      • Re: Setup a replication slave without stopping masterJed Reynolds8 Jan
  • RE: Setup a replication slave without stopping masterTheodore Petkantchin7 Jan