devel.reinikainen.net
HomeDocumentsCodeBugtrackerSearchreinikainen.netJB Consulting

Implementing two-way MySQL replication on live servers

Jani Reinikainen, Address

Revision 0.1.0, 8. January 2007

Table of contents

1. Introduction
1.1. Concepts
1.2. Revision history
1.3. Copyright
1.4. Disclaimer
1.5. Acknowledgments and Thanks
2. Setup first server
2.1. my.cnf
2.2. Permissions
2.3. Database dump
3. Setup second server
3.1. my.cnf
3.2. Load database dump
3.3. Permissions
3.4. Replication
4. Conclusion
4.1. Further Documentation

1. Introduction

1.1. Concepts

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
Initial revision.


1.3. Copyright

Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. A copy of the license is included in the section entitled "GNU Free Documentation License".

1.4. Disclaimer

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.

1.5. Acknowledgments and Thanks

Thanks to everyone who gave comments as I was writing this, and especially all of you who have donated. We couldn't be here without your support.

2. Setup server number 1

2.1. my.cnf

Make sure the server has a unique server ID.

Make sure 

skip-networking
is not set.

Make sure binary logging is enabled.

2.2. Permissions

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.


$ mysqldump --master-data --all-databases -u username -p > backup.sql

Copy the dump over to the second server (I'm using SCP, but the transport method does not matter):


$ scp /tmp/backup.sql user@secondserver:/tmp

After that it's safe to delete backup.sql from the first server.

3. Setup second server

Quick binary MySQL installation:


# groupadd mysql; useradd -g mysql mysql
$ cd mysql; scripts/mysql_install_db --user=mysql; chown -R root .; chown -R mysql data; chgrp -R mysql .



3.1. my.cnf

Make sure the server has a unique server ID (i.e. not the same as on server number 1).

Make sure 

skip-networking
is not set.

Make sure binary logging is enabled.

3.2. Load database dump

Make sure the MySQL server is running.


$ mysql -u username -p < backup.sql



3.3. Permissions

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;


3.4. Replication

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!

4. Conclusion

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!

4.1. Further Documentation

Copyright © 2007 Jani Reinikainen. All rights reserved.
Permission granted to replicate information found on these pages, provided that all copyright headers/footers remain intact.