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? :) >> > > 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 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=jakem...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
