List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:January 6 2009 8:09pm
Subject:Re: Setup a replication slave without stopping master
View as plain text  
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

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