MySQL Slaving featuring FreeBSD Snapshots
For any number of reasons, mysql replication 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: Replication has already been set up, and we just want to clear out the old binary logs and start fresh. For information on setting up replication 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
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 -
FLUSH LOGS; -- reopens all logs PURGE BINARY LOGS TO 'mysql-bin.009590' TRUNCATE mysql.general_log; TRUNCATE mysql.slow_log; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; RESET MASTER; RESET SLAVE; CHANGE MASTER TO master_host = '192.168.168.xxx', master_user = 'replicator', master_password = 'xxx', master_connect_retry = 3, master_log_file = 'log-bin.xxx', master_log_pos = xxx; SET sql_log_bin = 0; SET sql_slave_skip_counter = 1;