Liunx 搭建MySQL集群（三）

本文讲解的是主备互备的过程。

上面已经讲过主备的搭建：
Liunx 搭建MySQL集群（一）
Liunx 搭建MySQL集群（二）

配置参数：

#主备都需要新增
log-slave-updates = true     #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
#主自增长ID
auto_increment_offset = 1
auto_increment_increment = 2  

#备自增加ID
auto_increment_offset = 2
auto_increment_increment = 2

（1）搭建主主

现在环境已经准备好了，当前部署状态为，备 向 主 同步，现在开始搭建双主架构：

记录 主备 节点二进制位置：

#主
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000004 |      157 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
#备
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| relay-bin.000002 |      444 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

（2）配置同步信息：

#主
mysql> change master to master_host='172.16.185.162',master_port=3306,master_user='repl',master_password='123456',master_log_file='relay-bin.000002',master_log_pos=444; 
Query OK, 0 rows affected, 9 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)

#备
mysql> change master to master_host='172.16.185.161',master_port=3306,master_user='repl',master_password='123456',master_log_file='master-bin.000004',master_log_pos=157;
Query OK, 0 rows affected, 9 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)

（3）查看主状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.16.185.162
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: relay-bin.000002
          Read_Master_Log_Pos: 444
               Relay_Log_File: k8s-master-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: relay-bin.000002
             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: 444
              Relay_Log_Space: 541
              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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: ccb4ad5f-9c58-11ec-be0d-000c296b4fa0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

ERROR: 
No query specified

查看备状态：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.16.185.161
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000004
          Read_Master_Log_Pos: 157
               Relay_Log_File: k8s-node-1-relay-bin.000002
                Relay_Log_Pos: 327
        Relay_Master_Log_File: master-bin.000004
             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: 157
              Relay_Log_Space: 542
              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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: ca2799be-9c58-11ec-bdf6-000c2962a603
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

可以看到状态都正常：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

（4）测试主备复制：

#旧数据【主/备】
mysql> select * from user;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
| 10 | name001 |    0 |
| 11 | name002 |    0 |
| 12 | name003 |    0 |
| 13 | name003 |    0 |
| 14 | name005 |    0 |
| 15 | name005 |    0 |
| 16 | name006 |    0 |
| 17 | name006 |    0 |
| 18 | name007 |    0 |
+----+---------+------+
9 rows in set (0.00 sec)
#主新增一条数据
mysql> insert into user(name) values('name008');
Query OK, 1 row affected (0.01 sec)

#主备都新增了一条数据name:name008
mysql> select * from user;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
| 10 | name001 |    0 |
| 11 | name002 |    0 |
| 12 | name003 |    0 |
| 13 | name003 |    0 |
| 14 | name005 |    0 |
| 15 | name005 |    0 |
| 16 | name006 |    0 |
| 17 | name006 |    0 |
| 18 | name007 |    0 |
| 19 | name008 |    0 |
+----+---------+------+
10 rows in set (0.00 sec)

#备新增一条数据
mysql> insert into user(name) values('name009');
Query OK, 1 row affected (0.00 sec)

#主备都新增了一条数据name:name009
mysql> select * from user;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
| 10 | name001 |    0 |
| 11 | name002 |    0 |
| 12 | name003 |    0 |
| 13 | name003 |    0 |
| 14 | name005 |    0 |
| 15 | name005 |    0 |
| 16 | name006 |    0 |
| 17 | name006 |    0 |
| 18 | name007 |    0 |
| 19 | name008 |    0 |
| 20 | name009 |    0 |
+----+---------+------+
11 rows in set (0.00 sec)

至此我们的主主搭建成功了。
在这里说明一下，主从和主备搭建流程一样，我这里就不说明了，可以看我前面的一篇文章有搭建过程。