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.

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

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

UNLOCK TABLES; exit;
 * Get mysql going again. The rest we can do while it runs.

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

/usr/local/etc/rc.d/mysql-server.sh stop cp -p /var/db/mysql/my.cnf ~ rm -rf /var/db/mysql/* rsync -av root@mymaster:/mnt/ /var/db/mysql cp -p ~/my.cnf /var/db/mysql/ /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 to the slave and use a big hammer!
 * 1) save my my.cnf!
 * 1) 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.
 * 1) copy back our my.cnf
 * 1) go into /var/db/mysql and remove any mymaster.* files.
 * 2) Start the slave

umount /mnt mdconfig -d -u 4 rm /var/db/mysql/.snap/dbsnap
 * Go back to master and cleanup snapshot

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 -

Other notes
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;