Dumping MySQL databases on a replication slave

I spent some time looking at the mysqldump command in detail this afternoon with a view to backing up some in-house MySQL databases which were configured in a simple replicated environment (single master, single slave). I knew that executing something like:

# mysqldump -u user --password=xxxx dbname > dumpfile.sql

on the replication slave would do most of what I needed but I wanted to be sure that I was getting a consistent backup of the data nonetheless. At first, I thought I might have to stop MySQL before and after the dump but then I recalled of course that MySQL actually needs to be running to do a dump. My Subversion hat was duly removed for the afternoon!

Then I started to think about locking and flushing databases and tables, a simple database lock/unlock (read) before/after the dump should do the trick. Fortunately, this is taken care of for me by the mysqldump utility through the --opt parameter. This parameter is a shorthand way of enabling several other options, most notably the --lock-tables option, which locks the tables in the database being dumped for the duration of the dump itself.

Interestingly though, the replication process is also suspended by mysqldump and this actually lead me to falsely believe (for a short time) that all databases had been locked instead of just the one being dumped.

So, in essence, the simple command I started out with above actually does everything I need.

Configuring MySQL Database Replication using Solaris and ZFS

The following notes were taken during an exercise to configure MySQL database replication across two SunFire T2000 servers running Solaris 10, each of which also has a single ZFS file system mounted in /storage/xxxx (where the respective MySQL data files are located). The snapshot capabilities of ZFS were hugely beneficial in this scenario as they allowed mere seconds of database downtime (as opposed to potentially several minutes or even hours otherwise).

The process of replication setup is already well documented on the Internet, particularly here on the MySQL website. However, as usual, I have chosen to share my experiences and observations in the hope that may prove useful to others.


Each of the steps below is precluded by the system on which the commands are to be carried out, MASTER or SLAVE.


The following commands will affect the availability of the database so prepare a number of login sessions with the commands ready to be executed and execute them as quickly as possible in the correct order so as to minimize the down time of the database.

1. Lock the Database
master$ mysql -u root -p

2. Create a snapshot of the database
master# zfs snapshot tank/masterzfs@mysql-2007-07-18

3. Record the File and Position information from Master Status (to be used on slave later on)
Typical values include File: mysql-bin.000008 and Position: 420414560

4. Unlock the database again

5. Verify the successful creation of the ZFS snapshot
master# zfs list

6. Compress/pack the required contents of the ZFS snapshot
master# cd /storage/masterzfs/.zfs/snapshot/masterzfs-mysql-2007-07-18/mysql
master# tar -cf /storage/masterzfs/masterzfs-mysql-2007-07-13.tar mysql/

[NOTE] The .zfs directory above is a hidden directory and may not be visible (even via “ls -a”). However, it is there and you can access it!


7. Copy the database archive produced on the master to the slave (using SSH compression)
slave# mkdir /storage/slavezfs/tmp
slave# cd /storage/slavezfs/tmp
slave# scp -C etadev@db219:/storage/masterzfs/masterzfs-mysql-2007-07-13.tar .

[NOTE] This took approximately 75 minutes for a 4.7GB file over a 100Mb connection

8. Unpack the database archive at the slave
slave# cd /storage/slavezfs/tmp
slave# tar –xvf masterzfs-mysql-2007-07-13.tar


9. Grant database access to the slave
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'xx.xx.xx.xx' IDENTIFIED BY 'replpass';
Clearly, you will need to substitute your own username, slave IP Address and replication password here

10. Update the Master MySQL Configuration File(s) and restart MySQL (if required)
master# vi /etc/my.cnf
log-bin=mysql-bin (uncomment)
innodb_flush_log_at_trx_commit = 1 (uncomment if using InnoDB tables)


11. Move the unpacked files from the Master into place (ensure that MySQL and all services that use it are stopped)
slave# cd /storage/slavezfs
slave# mv mysql mysql.pre-repl
slave# mv tmp/mysql .

12. Update the Slave MySQL Configuration File(s) and restart MySQL (if required)
slave# vi /etc/my.cnf
log-bin=mysql-bin (uncomment)
innodb_flush_log_at_trx_commit = 1 (uncomment if using InnoDB tables)

13. Configure and Start Replication
mysql> CHANGE MASTER TO MASTER_HOST='xx.xx.xx.xx', MASTER_PORT=XXXX, MASTER_USER='repl', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=420414560;

Clearly, you will need to substitute your own username, master IP Address, Port and replication password here. Also MASTER_LOG_FILE and MASTER_LOG_POS are the values recorded from the SHOW MASTER STATUS in Step 3. Be careful when entering the CHANGE MASTER command above as the inclusion of white spaces in the log file setting can prevent replication from working correctly. Also, you should be aware that you are now accessing a different database than might have previously been on this system and so, the users/passwords may be different.

14. Verify Replication is Operating correctly
and examine the Seconds_Behind_Master field. When the slave is fully synchronised with the master, this will be 0.

Best of luck!