From: Claudio Nanni
Date: January 6 2009 8:09pm
Subject: Re: Setup a replication slave without stopping master
List-Archive: http://lists.mysql.com/mysql/215826
Message-Id: <4963BA72.7010804@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

First, Thank You all guys, I really appreciate your great answers.
Second in my experience this is one of the most challenging and frequent 
things with mysql on production servers,
once you have the slave practically you have online backups

I will try to answer one by one.

Jake Maul wrote:
> If you're making backups of the DB, it might be possible to use the
> backup data as a replication snapshot for kickstarting the slave. You
>   
If I only had a kickstart backup....! :))))
> You are making backups, right? :)
>   
ehm....the problems is exactly that. On production server you cannot 
stop or lock the server so I need
the replication slave mainly for backups (actually MySQL replication is 
simply great for this)
> Here's another possibility, although not one I would really recommend:
>   
I will give it a try, I am really interested in seeing what happens 
locking one table per time.

NOTE:
I have tried Innodb Hotbackup Tool today but it was locking the 
production server!
and the strange thing is that it was locking while doing a 'cp' of a 
.MYI file, pretty weird,
I would definitely not copy indexes but rather rebuild them offline, 
easily on the slave.
from the InnoDB Hot Backup site:

    * Online backup of InnoDB tables — the backup takes place entirely
      online, without preventing queries or updates.
    * Online backup of MyISAM tables — during the backup of InnoDB
      tables, read and write access is permitted to MyISAM tables. While
      the MyISAM tables are being copied, updates (but not reads) to the
      MyISAM tables are precluded.


> Jake
>   
Thank you man

===================================================================================

Baron Schwart

We get asked to do this a lot  :)   
A: (I know!!!)

There's a bunch of different cases.
What storage engines are you using?  
A: MyISAM and InnoDB

Do you have LVM with free space
on the volume group, or another way to take snapshots such as a SAN?
A: Let's dont take it as an option since I could have it on this one but not on other servers(I have about 60 servers in 10 replication clusters)

What I am looking for is a standard clean solution useful in any case and I also think important for the whole MySQL community.
Sorry if I repeat myself but once you have a slave running you have online backups(apart from load balancing), and the only difficulty
I see is when you have a production server with a pretty big database and you cannot stop or lockit.
Innodb Hotbackup today locked production and we had to kill it.


Great Baron, thanks.


========================================================================================
Jed:

If you are using InnoDB, there is a --single-transaction method backup
( http://lists.mysql.com/replication/1235 ) however,
A: I Will consider this

If you are using LVM, you might consider snapshotting,
A: Lets not consider this option

Mysql-hot-copy would probably be better,
A: Yes mysqlhotcopy locks the tables and work only for myisam and archive :(


In contrast, InnoDB actually needs to "shut down" to cleanly close its 
table structures before you can physically copy the filesystem.
A: This is the challenge!

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.
A: and you do this all on a production server? this is luxury!


I would have a business level meeting with stakeholders.....
A: DEFINITELY!

Good luck!

Jed


Nice from you too Jed!


Again, great to have 'collegues' as you!

Claudio