Re: Setup a replication slave without stopping master
Baron Schwartz wrote: In contrast, InnoDB actually needs to shut down to cleanly close its table structures before you can physically copy the filesystem. Actually, not true -- an LVM snapshot (or other snapshot) is a great way to take a backup of InnoDB. You just need a truly atomic snapshot, and then you can let InnoDB run its recovery routine on the snapshot to get back to a consistent state. Fascinating. From reading the mysql docs, I would never have assumed that I was doing the right thing by taking a snapshot of a live innodb instance with a flush tables. I will certainly keep this in mind for future. My current snapshotting procedure takes between 45s and 90s depending on which instance I snapshot, and that's about 20G of data that I start copying over. That's not counting time copying anything from the snapshotted volume. 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. That will depend a lot on the workload. Yes, very pertinent point. I should have qualified how I have a write-intensive environment. I used to just drop the firewall after I restarted mysql, but when my application reliability was criticized during my snapshots, I had to leave the firewall up until the snapshotted copy was copied off before pooling it back in. Luckily I have four servers and there's only rare conditions when I need to switch masters. I'm grateful that I have a maintenance window for the site, too. Thank you, Baron! Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Setup a replication slave without stopping master
Hi Jed, 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. Actually, not true -- an LVM snapshot (or other snapshot) is a great way to take a backup of InnoDB. You just need a truly atomic snapshot, and then you can let InnoDB run its recovery routine on the snapshot to get back to a consistent state. 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. That will depend a lot on the workload. Here are some good links for further reading: http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/ http://www.mysqlperformanceblog.com/2008/06/09/estimating-undo-space-needed-for-lvm-snapshot/ -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Setup a replication slave without stopping master
Claudio, ehmthe 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) Just don't rely on the slave to BE the backup. You can use it to make it easier to take backups, but don't fall into the slave==backup trap. An accidental DROP TABLE can show you why slave!=backup. NOTE: I have tried Innodb Hotbackup Tool today but it was locking the production server! Right, it does that :) So it sounds like you have a mixture of InnoDB and MyISAM tables, which is what I was trying to ask you in my previous message. In this case you have to do some dirty tricks. It depends on your situation. For example, you can get the InnoDB data out with --single-transaction; you can get the MyISAM data with flush-and-rsync, then repair and replace missing rows, etc. There is no good solution. But with a little work, and some knowledge of your workload, you can get most of the data out without too much trouble, leave the troublesome ones for last, roll up your sleeves for those, and then fix the differences with mk-table-checksum and/or other approaches. Have fun. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Setup a replication slave without stopping master
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). Do you have any idea on how to setup the slave with minimum or no impact on the master? The database is about 80GB. Thanks C.
Re: Setup a replication slave without stopping master
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 would need to be recording the master log file and position at the time the backups are made as well, however (usually easy to hack in if you're not already recording this). You are making backups, right? :) Here's another possibility, although not one I would really recommend: 1) Import all schema's into the slave 2) Set up replication (don't bother starting slave since it'll probably fail right away, unless you only do INSERT, never UPDATE/DELETE) 3) Get maatkit 4) Use mk-table-checksum --replicate on the master... this will lock your tables one at a time. 5) Use mk-table-sync --synctomaster on the slave to fix all the problems (that is, everything). The time to do this might not be much shorter than the 'correct' method of mysqldump --master-data or tar/mysqlhotcopy, but at least your tables would be only locked one at a time and not all at once (I think). Other than that, there's not a lot you can do. If you don't stop the db entirely, you'll at least still be able to read from it while you do your mysqldump / mysqlhotcopy / mk-table-checksum. Jake On Tue, Jan 6, 2009 at 4:04 AM, Claudio Nanni claudio.na...@gmail.com 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). Do you have any idea on how to setup the slave with minimum or no impact on the master? The database is about 80GB. Thanks C. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Setup a replication slave without stopping master
We get asked to do this a lot :) There's a bunch of different cases. What storage engines are you using? Do you have LVM with free space on the volume group, or another way to take snapshots such as a SAN? Sometimes there are clever ways to get around various limitations, but I'll wait till you reply because there are too many different permutations to just write about all of them. On Tue, Jan 6, 2009 at 4:04 AM, Claudio Nanni claudio.na...@gmail.com wrote: I need to setup a replication slave off a master that is currently production and not stoppable or lockable(possibly). Do you have any idea on how to setup the slave with minimum or no impact on the master? The database is about 80GB. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Setup a replication slave without stopping master
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Setup a replication slave without stopping master
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? :) ehmthe 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Setup a replication slave without stopping master
Claudio, please provide details: Which engine (InnoDB or MyISAM) are you running? Which MySQL version are you running? What is the OS? What is the files system? Regards Theo -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Tuesday, 6 January 2009 10:05 PM To: mysql@lists.mysql.com Subject: Setup a replication slave without stopping master 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). Do you have any idea on how to setup the slave with minimum or no impact on the master? The database is about 80GB. Thanks C. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Setup a replication slave without stopping master
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 To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Setup a replication slave without stopping master
Sounds like you already know the score. Yeah, we use slaves as 'backup-capable' servers too... sometimes to the point of having a separate slave who's sole purpose in life is to be taken down and backed up. :) As it happens, I worked with mk-table-checksum and mk-table-sync some today. On a ~11GB database (size of the mysql dump files, that is), it took approximately 15 minutes to checksum everything, locking the tables one-by-one. HOWEVER, attempting to fix the errors I had on the slaves seemed to cause a GLOBAL lock on the master, and was not very fast at all... had to kill it after ~10 minutes with comparatively minimal progress (it wasn't giving very much output even with --verbose). Seems like the kind of thing that'd be useful on small databases, but if I were you I'd test it somehow before doing it for real. Maybe you could dump the schema somewhere else and fill in some random data, then see how fast mk-table-sync was able to sync it all to the slave. The --single-transaction option is not bad, but know that it is (obviously) ineffective with MyISAM tables... they just ignore it and dump normally. Don't know what happens if you dump some of each in the same dump (which you would need to do for replication). When I find myself in this type of situation, I generally try and give the stakeholders a few very simple choices. In my (agreeably, somewhat limited) experience, if you lay out the pros/cons, they feel like they can make an informed decision on the best way to proceed and won't haggle nearly as much over the nigh-impossible. Basically, I lay out the consequences for each path and let them choose which to walk down. In your case, I think I would go for 1) standard mysqldump (downtime, reliable, reusable elsewhere, lowest incident of human error) 2) LVM snapshotting (little or no downtime, we've never done this before, not usable everywhere else, presumably harder and thus more operator-error-prone) 3) Do nothing (no downtime at all, but also still no backups... but maybe you have RAID-5 and good ACLs) Who knows, they may decide that the downtime of a normal dump is worth the tradeoff once the alternatives are actually spelled out. Or they might decide the uptime is still king and it's not worth the hassle at all. Of course, go ahead and mention what you'd *like* to do, as you're supposedly the expert on the situation Jake On Tue, Jan 6, 2009 at 1:09 PM, Claudio Nanni claudio.na...@gmail.com wrote: 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? :) ehmthe 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
