Describes how replication works in a MySQL cluster.

7. Cluster Replication

7.1. Brief Description

Since MySQL 5.1, cluster + replication is supported, which is undoubtedly a surprise for users who want to build a high-availability solution. In this mode, both real-time master-slave backup and cluster-based load balancing are available. The disadvantage is that the performance of this solution is not too high according to my test results, improvements are yet to be made.

The configuration of cluster + replication is actually very simple, that is, after configuring two independent clusters, you can use one of the SQL nodes as the slave of the other cluster SQL node. You can even use the following architectures:

Three clusters and six SQL nodes form a three-point circular replication.

Three clusters and six SQL nodes form a 6-Point Circular replication, and use another SQL node.

7.2. Start Configuration

7.2.1. configuration on master

Because the replication in the cluster is based on row-based replication, you must set the logbin-format to ROW or MIXED.
Compared with the general mysqld server configuration, you only need to add the following two lines:

 
 

  
  
server-id = 1  
  
  
binlog_format = "ROW" #or MIXED 
 
 

7.2.2. slave Configuration

The new version of MySQL no longer uses my. cnf to specify information about the master, but is managed by changing the MASTER command. Therefore, you only need to add lines similar to the following on the slave:

 
 

  
  
server-id = 2 
  
  
relay-log-purge=1 
  
  
skip-slave-start  
  
  
replicate-ignore-db=mysql 
 
 

7.3. Start slave

Start mysqld on the master and slave in the normal way, and run show processlist. You can see that there are only two mysqld processes:

 
 

  
  
mysql> SHOW PROCESSLIST;  
  
  
+----+-------------+-----------+------+---------+------+-----------------------------------+------------------+  
  
  
| Id | User        | Host      | db   | Command | Time | State                             | Info             |  
  
  
+----+-------------+-----------+------+---------+------+-----------------------------------+------------------+  
  
  
|  1 | system user |           |      | Daemon  |    0 | Waiting for event from ndbcluster | NULL             |  
  
  
|  2 | root        | localhost | NULL | Query   |    0 | NULL                              | show processlist |  
  
  
+----+-------------+-----------+------+---------+------+-----------------------------------+------------------+  
  
  
2 rows in set (0.00 sec) 
 
 

Execute SHOW slave STATUS on SLAVE and check again:

 
 

  
  
mysql> show slave status\G  
  
  
Empty set (0.00 sec) 
 
 

As you can see, there are no replication-related configurations. Therefore, we need to add an account on the master for replication:

 
 

  
  
mysql> GRANT REPLICATION SLAVE, GRANT REPLICATION CLIENT ON *.* TO rep@’192.168.1.2’ IDENTIFIED BY ‘rep’;  
  
  
Query OK, 0 rows affected (0.00 sec) 
 
 

Then, we use the change master command to specify the master-related parameters:

 
 

  
  
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.2', MASTER_USER='rep', MASTER_PASSWORD='rep';   
  
  
Query OK, 0 rows affected (0.01 sec)  
  
  
 
  
  
mysql> SHOW SLAVE STATUS\G  
  
  
*************************** 1. row ***************************  
  
  
               Slave_IO_State:   
  
  
                  Master_Host: 192.168.0.2  
  
  
                  Master_User: rep  
  
  
                  Master_Port: 3306  
  
  
                Connect_Retry: 60  
  
  
              Master_Log_File:   
  
  
          Read_Master_Log_Pos: 4  
  
  
               Relay_Log_File: slave-relay-bin.000001  
  
  
                Relay_Log_Pos: 4  
  
  
        Relay_Master_Log_File:   
  
  
             Slave_IO_Running: No  
  
  
            Slave_SQL_Running: No  
  
  
              Replicate_Do_DB:   
  
  
          Replicate_Ignore_DB: mysql  
  
  
           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: 0  
  
  
              Relay_Log_Space: 106  
  
  
              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: NULL  
  
  
Master_SSL_Verify_Server_Cert: No  
  
  
                Last_IO_Errno: 0  
  
  
                Last_IO_Error:   
  
  
               Last_SQL_Errno: 0  
  
  
               Last_SQL_Error:   
  
  
1 row in set (0.00 sec) 
 
 

Run start slave to START it.

 
 

  
  
mysql> start slave;  
  
  
Query OK, 0 rows affected (0.00 sec) 
 
 

Let's take a look:

 
 

  
  
mysql> SHOW SLAVE STATUS\G  
  
  
*************************** 1. row ***************************  
  
  
               Slave_IO_State: Waiting for master to send event  
  
  
                  Master_Host: 192.168.0.2  
  
  
                  Master_User: rep  
  
  
                  Master_Port: 3306  
  
  
                Connect_Retry: 60  
  
  
              Master_Log_File: binlog.000001  
  
  
          Read_Master_Log_Pos: 256  
  
  
               Relay_Log_File: slave-relay-bin.000002  
  
  
                Relay_Log_Pos: 398  
  
  
        Relay_Master_Log_File: binlog.000001  
  
  
             Slave_IO_Running: Yes  
  
  
            Slave_SQL_Running: Yes  
  
  
              Replicate_Do_DB:   
  
  
          Replicate_Ignore_DB: mysql  
  
  
           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: 256  
  
  
              Relay_Log_Space: 557  
  
  
              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  
  
  
Master_SSL_Verify_Server_Cert: No  
  
  
                Last_IO_Errno: 0  
  
  
                Last_IO_Error:   
  
  
               Last_SQL_Errno: 0  
  
  
               Last_SQL_Error:   
  
  
1 row in set (0.00 sec) 
 
 

7.4. Simple Test

This is left for the reader to complete according to the regular test.