Revision 0.1.0, 8. January 2007
Table of contents
- 1. Introduction
- 2. Setup first server
- 3. Setup second server
- 4. Conclusion
This document describes how-to setup two-way MySQL replication on live hosts. However, you can just as well use the guide for non-production servers as well. This is not really as complicated as it sounds, it just needs some thought.
1.2. Revision history
|Revision v0.1.0||8. January 2007||By: Jani Reinikainen|
Use the information in this document at your own risk. I disavow any potential
liability for the contents of this document. Use of the concepts, examples,
and/or other content of this document is entirely at your own risk.
All copyrights are owned by their owners, unless specifically noted otherwise.
Use of a term in this document should not be regarded as affecting the
validity of any trademark or service mark.
Naming of particular products or brands should not be seen as endorsements.
You are strongly recommended to make a backup of your system before major
installation and should make backups at regular intervals.
2. Setup server number 1
Make sure the server has a unique server ID.
is not set.
Make sure binary logging is enabled.
Grant permission to the second server to pull data from this (first) server. Replace ‘secondserver.com’ with the hostname or IP address of the second server.
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'secondserver.com' IDENTIFIED BY 'slavepass';
Exit the MySQL shell.
2.3. Database dump
I prefer to use mysqldump for this even though it’s slower than just doing a binary copy. mysqldump automatically sets MASTER_LOG_FILE and MASTER_LOG_POS so you won’t have to. It also handles the locking of databases automatically.
Copy the dump over to the second server (I’m using SCP, but the transport method does not matter):
After that it’s safe to delete backup.sql from the first server.
3. Setup second server
Quick binary MySQL installation:
$ tar xfvz mysql-5.0.45-linux-x86_64-glibc23.tar.gz
# mv mysql-5.0.45-linux-x86_64-glibc23 /usr/local/mysql; cd /usr/local/mysql
$ scripts/mysql_install_db –user=mysql; chown -R root .; chown -R mysql data; chgrp -R mysql .
Make sure the server has a unique server ID (i.e. not the same as on server number 1).
is not set.
Make sure binary logging is enabled.
3.2. Load database dump
Make sure the MySQL server is running.
Grant permission to the first server to pull data from the second. Replace ‘firstserver.com’ with the hostname or IP address of the first server.
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'firstserver.com' IDENTIFIED BY 'slavepass'; mysql> FLUSH PRIVILEGES;
mysql> CHANGE MASTER TO MASTER_HOST='firstserver.com', MASTER_USER='repl', MASTER_PASSWORD='master_pass', \ MASTER_LOG_FILE='mysql-bin.000075', MASTER_LOG_POS=47514582;
mysql> SLAVE START;
You can check the status by issuing:
mysql> SHOW SLAVE STATUS\G
This should return something similar:
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: firstserver.com Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 47514905 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 558 Relay_Master_Log_File: mysql-bin.000075 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 47514905 Relay_Log_Space: 558 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec)
The important thing is that both Slave_IO_Running and Slave_SQL_Running return ‘Yes’. Great! Now issue a SHOW MASTER STATUS; and note the log file and position. The output should be similar to this:
+---------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------+----------+--------------+------------------+ | mysql-bin.003 | 73 | test | manual,mysql | +---------------+----------+--------------+------------------+
Log back into the first server and issue:
mysql> CHANGE MASTER TO MASTER_HOST='secondserver.com', MASTER_USER='repl', MASTER_PASSWORD='master_pass', \ MASTER_LOG_FILE='recorded_log_file_name', MASTER_LOG_POS=recorded_log_position;
mysql> START SLAVE;
Again, you can check the status by issuing:
mysql> SHOW SLAVE STATUS;
Again, if Slave_IO_Running and Slave_SQL_Running both return ‘Yes’, you’re all done!
Hope you found the guide useful. A donation wouldn’t hurt (even if it’s only a euro or two – everything helps), as this site is getting so many hits it’s getting a bit expensive. There’s a link to PayPal at the lower left corner of this site. Thanks!