devel.reinikainen.net

Revision 0.1.0, 8. January 2007

Table of contents

1. Introduction

1.1. Concepts

1.2. Revision history

1.3. Disclaimer

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

1.3. 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.

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.

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:

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.

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

• MySQL documentation

Tags: linux, mysql, replication

This entry was posted on Saturday, October 3rd, 2009 at 11:18 and is filed under SQL databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.