MySQL Slaving featuring FreeBSD Snapshots

MySQL Slaving featuring FreeBSD Snapshots
From Devpit
Jump to: navigation, search

For any number of reasons, slaving mysql could get out of sync. If this happens, the following procedure can be used to clean up and resynchronize the slave(s). This procedure is designed to minimize DB downtime by taking advantage of FreeBSD snapshots.

Assumptions: Slaving has already been set up, and we're just want to clear out the old binary logs and start fresh. For information on setting up slaving for the first time, refer to http://dev.mysql.com/doc/refman/4.1/en/replication-howto.html and http://dev.mysql.com/doc/refman/4.1/en/replication-faq.html.

* Go to the MySQL master and open a mysql session as root

# Delete all old logs and start fresh
RESET MASTER;
# Lock the tables and note the log position
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS; # Make a note of the file and position.
# IMPORTANT: keep this mysql session open, or it will unlock your tables.

* Take a snapshot

# Refer to /usr/src/sys/ufs/ffs/README.snapshot or mount(8)
mount -u -o snapshot /var/db/mysql/.snap/dbsnap /var/db/mysql

* Get mysql going again. The rest we can do while it runs.

UNLOCK TABLES;
exit;

* Mount the snapshot

mdconfig -a -t vnode -f /var/db/mysql/.snap/dbsnap -u 4
mount -r /dev/md4 /mnt

* Go to the slave and use a big hammer!

/usr/local/etc/rc.d/mysql-server.sh stop
# save my my.cnf!
cp -p /var/db/mysql/my.cnf ~
rm -rf /var/db/mysql/*
# If you don't have root ssh setup (it's not a good idea) - or if you db is not huge, you can just make a tar and copy it over instead.
rsync -av root@mymaster:/mnt/ /var/db/mysql
# copy back our my.cnf
cp -p ~/my.cnf /var/db/mysql/
# go into /var/db/mysql and remove any mymaster.* files.
# Start the slave
/usr/local/etc/rc.d/mysql-server.sh start
mysql> CHANGE MASTER TO
-> MASTER_HOST='mymaster.mecasa.com',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='xxxxxxxx',
-> MASTER_LOG_FILE='mysql-bin.000001', # you recorded this earlier
-> MASTER_LOG_POS=79; # you recorded this earlier
mysql> start slave;

* Go back to master and cleanup snapshot

umount /mnt
mdconfig -d -u 4
rm /var/db/mysql/.snap/dbsnap

Alternate approach

Instead of mounting the snapshot, you can use the dump and restore utilities to rebuild its contents on the target machine. For example, from the target machine, execute:

target$ cd /var/db/mysql # An empty filesystem
target$ ssh source 'dump -0 -C 32 -f - /var/db/mysql/.snap/dbsnap' | restore -rf -