HOWTO setup mysql replication

From Chaos

This article is part of the HOWTO series.

Contents 1 Summary 2 Details 2.1 Setup the Master 2.2 Setup the Slave 3 References

Summary

First, make sure you are using the same 4.0.x or 4.1.x version on both master and slave (I did this with 2 x 4.0.18 servers). If you hose your MySQL installation we take no responsiblity! Make a backup before continuing!

Please note that these steps are also available in the MySQL Handbook.

Details

Setup the Master

1. Login to the master, and run the following commands. All we are doing is logging in to the MySQL server, stopping the tables from being written to, and gathering various information. Take note of the 'File:' and 'Position:' fields. You will need them later!:

root@master:~# mysql -u root -p

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS\G

mysql> EXIT;

2. Shut down the master MySQL Server

root@master:~# mysqladmin -u root -p shutdown

3. Go to /var/ (or whever the DB location is), and tar up the entire directory.

root@master:~# cd /var

root@master:/var# tar -czvf mysql.tar.gz mysql

4. Once the tar is done, you can re-start mysql, and unlock the tables. This will re-enable writes to all the tables which is ok since you already have the previous filename and position from above. Then, SCP the file to the slave MySQL server.

root@master:/var# mysqld_safe &

root@master:/var# mysql -u root -p

mysql> UNLOCK TABLES;

mysql> EXIT;

root@master:/var# scp mysql.tar.gz administrator@slave:~/.

Setup the Slave

1. Stop mysql on the slave, move mysql to mysql.old and untar the master's mysql DB to /var/mysql (don't forget to chown it as per the appropriate mysql user/group!).

root@slave:~# mysqladmin -u root -p shutdown

root@slave:~# cd /var

root@slave:/var# rm -rf mysql.old

root@slave:/var# mv mysql mysql.old

root@slave:/var# tar -zxvf /Users/administrator/mysql.tar.gz -C .

root@slave:/var# chown -R mysql:admin mysql

2. We need to remove relay-log.info from the old mysql instance (otherwise things will break). Then, start mysql on the slave.

root@slave:/var# rm mysql/relay-log.info

root@slave:/var# mysqld_safe &

3. Log in to the slave MySQL server, and run the following commands. This will enable replication, and if all goes well, there won't be any errors in /var/mysql/`hostname`.err. Make sure to use the filename and position from above! Don't forget to fill in your settings.

root@slave:/var# mysql -u root -p

mysql> SLAVE STOP;

mysql> CHANGE MASTER TO MASTER_HOST='MY_MASTER_HOST', MASTER_CONNECT_RETRY=30, MASTER_USER='MY_REPLICATION_USER', MASTER_PASSWORD='MY_REPLICATION_PASS', MASTER_LOG_FILE='MY_LOG_FILE', MASTER_LOG_POS=MY_LOG_POSITION;

mysql> SLAVE START;

mysql> EXIT;

root@slave:/var# cat /var/mysql/`hostname`.err

References

• http://dev.mysql.com/doc/refman/4.1/en/index.html