On Sun, Oct 05, 2003 at 11:55:03PM -0700, David L. Sifry wrote: > I've got a somewhat advanced mysql administration question for y'all. > Here's my situation: > > I've got a master database that is doing lots of inserts, deletes, and > updates. It has a number of slaves hanging off of it. One of those > slaves is completely unloaded - so it keeps very close track of the > master. Let's call that machine slave1. My problem is doing restores > from backups. The only way I've been able to get a reliable restore to > occur is to do a FLUSH TABLES WITH READ LOCK on the master, then copy > the /var/lib/mysql/* directories, do a SHOW MASTER STATUS on the master, > and then UNLOCK TABLES on the master. Then after copying the snapshot > to a new slave I do a CHANGE MASTER command on the new slave and > everything works. > > So far, so good.
Yes. I suspect that mysqlhotcopy might be useful too, but you've probably already automated the process. > The problem is that these databases are pretty large, and are regularly > updated. Stopping the master to do a snapshot is a pretty drastic > move. What I'd like to do is find a good recipe to use slave1 as the > machine to snapshot. I've tried the following, but things don't ever > seem to work: > > On slave1: > > mysql> SLAVE STOP; > mysql> FLUSH TABLES WITH READ LOCK; > Then I switch over to /var/lib/mysql/ and tarball the directory > cd /var/lib/mysql > tar zcvpPf /var/tmp/snapshot.tar.gz . > > Then I copy over the snapshot over to the new slave, and do the following: > /etc/init.d/mysql stop > cd /var/lib/mysql > tar zxvpPf /var/tmp/snapshot.tar.gz > > Then, when the tarball is finished unpacking, I run: > > /etc/init.d/mysql start > > I then log in as mysql superuser, and run the command: > mysql> slave start; While the tables are locked, be sure to also copy the slave's master.info file, relay log(s), and relay index file. Transfer all of that to the other slave and you should be good to go. Does that make sense? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 22 days, processed 836,548,967 queries (425/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
