List:General Discussion« Previous MessageNext Message »
From:Jed Reynolds Date:January 6 2009 6:11pm
Subject:Re: Setup a replication slave without stopping master
View as plain text  
Claudio Nanni wrote:
> All, Happy New Year, and let's hope somebody tries to stop the killing in
> gaza.
>
> I need to setup a replication slave off a master that is currently
> production and not stoppable or lockable(possibly).
>   

If you are using InnoDB, there is a --single-transaction method backup
( http://lists.mysql.com/replication/1235 ) however, you will need to 
try that in your staging environment under realistic load to see if you 
running that kind of transaction creates an unreasonable load spike or 
memory usage.

> Do you have any idea on how to setup the slave with minimum or no impact on
> the master?
>   

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.

I use a method where I flush the tables, firewall off the system, shut 
down mysql, do LVM snapshot, start mysql server, and then copy the 
snapshot before unfirewalling it.

Why do I leave it firewalled? Because once you start writing to an LVM 
volume that's been snapshotted, you start copying disk extents like mad, 
creating a high load condition that can force queries to reach 
connect_timeout.  I have my connect_timeout set pretty low in my 
environment.


> The database is about 80GB.
>   
Consider the transfer time with a dataset this large.

I would have a business level meeting with stakeholders telling them the 
possible risks and adjust their expectations for uptime or service 
availability.  Write some scripts to automate the transfer, however you 
do it, so that you don't fat-finger the process in production. Test and 
time your scripts in a staging environment. Use this data, adjust it as 
necessary for production load, to set stakeholder expectations.

Good luck!

Jed

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