Month: July 2007

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.

Introduction

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

MASTER

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
mysql> FLUSH TABLES WITH READ LOCK;

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)
mysql> SHOW MASTER STATUS;
Typical values include File: mysql-bin.000008 and Position: 420414560

4. Unlock the database again
mysql> UNLOCK TABLES;

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!

SLAVE

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

MASTER

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
server-id=1
log-bin=mysql-bin (uncomment)
innodb_flush_log_at_trx_commit = 1 (uncomment if using InnoDB tables)

SLAVE

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
server-id=2
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;
mysql> START SLAVE;
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
mysql> SHOW SLAVE STATUS;
and examine the Seconds_Behind_Master field. When the slave is fully synchronised with the master, this will be 0.

Best of luck!