4.2 Setting Up Replication On server3

(This chapter is for server3 only!)

On server3, we can now import the SQL dump snapshot.sql like this:

server3:

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql

Afterwards, we must find out about the master status of server3 as well and write it down:

mysql -u root -p

USE exampledb;
FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 |     1067 | exampledb    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

Then unlock the tables:

UNLOCK TABLES;

and run the following command to make server3 a slave of server2 (it is important that you replace the values in the following command with the values you got from the SHOW MASTER STATUS; command that we ran on server2!):

CHANGE MASTER TO MASTER_HOST='192.168.0.101', MASTER_USER='slaveuser_for_s3', MASTER_PASSWORD='slave_user_for_server3_password', MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=1067;

Finally start the slave:

START SLAVE;

Then check the slave status:

SHOW SLAVE STATUS \G

It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should check your setup again and take a look at /var/log/syslog to find out about any errors):

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.101
                Master_User: slaveuser_for_s3
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000010
        Read_Master_Log_Pos: 1067
             Relay_Log_File: slave-relay.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000010
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: exampledb
        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: 1067
            Relay_Log_Space: 235
            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)

mysql>

Afterwards, you can leave the MySQL shell on server3:

quit

Now the replication from server2 to server3 is set up. Next we must configure replication from server3 to server4.

4.3 Setting Up Replication On server4

(This chapter is for server4 only!)

On server4, we can now import the SQL dump snapshot.sql like this:

server4:

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql

Afterwards, we must find out about the master status of server4 as well and write it down:

mysql -u root -p

USE exampledb;
FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 |     1067 | exampledb    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

Then unlock the tables:

UNLOCK TABLES;

and run the following command to make server4 a slave of server3 (it is important that you replace the values in the following command with the values you got from the SHOW MASTER STATUS; command that we ran on server3!):

CHANGE MASTER TO MASTER_HOST='192.168.0.102', MASTER_USER='slaveuser_for_s4', MASTER_PASSWORD='slave_user_for_server4_password', MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=1067;

Finally start the slave:

START SLAVE;

Then check the slave status:

SHOW SLAVE STATUS \G

It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should check your setup again and take a look at /var/log/syslog to find out about any errors):

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.102
                Master_User: slaveuser_for_s4
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000010
        Read_Master_Log_Pos: 1067
             Relay_Log_File: slave-relay.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000010
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: exampledb
        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: 1067
            Relay_Log_Space: 235
            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)

mysql>

Afterwards, you can leave the MySQL shell on server3:

quit

Now the replication from server3 to server4 is set up. Finally we must configure replication from server4 to server1 to close the replication circle.

4.4 Setting Up Replication On server1

(This chapter is for server1 only!)

To do this, we stop the slave on server1 and make it a slave of server4:

server1:

mysql -u root -p

STOP SLAVE;

Make sure that you use the values of the SHOW MASTER STATUS; command that you ran on server4 in the following command:

CHANGE MASTER TO MASTER_HOST='192.168.0.103', MASTER_USER='slaveuser_for_s1', MASTER_PASSWORD='slave_user_for_server1_password', MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=1067;

Then start the slave on server1:

START SLAVE;

Then check the slave status:

SHOW SLAVE STATUS \G

It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should check your setup again and take a look at /var/log/syslog to find out about any errors):

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.103
                Master_User: slaveuser_for_s1
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000010
        Read_Master_Log_Pos: 1067
             Relay_Log_File: slave-relay.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000010
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: exampledb
        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: 1067
            Relay_Log_Space: 235
            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)

mysql>

Afterwards you can leave the MySQL shell:

quit

If nothing went wrong, MySQL master-master replication should now be working. If it isn't, please check /var/log/syslog for MySQL errors.

5 Links

• MySQL: http://www.mysql.com
• Debian: http://www.debian.org