MySQL5.7主从,单slave多master

超、凢脫俗 2021-12-23 02:39 151阅读 0赞

本次实验将使用MySQL 5.6.x作为多“主”。  
  
**〇 测试环境：**  
**OS：**[CentOS][] 6.5  
**master\_1:** 192.168.1.185(MySQL 5.6.30)  
**master\_2:** 192.168.1.186(MySQL 5.6.30)  
**slave:** 192.168.1.1.187(MySQL 5.7.15)

**〇 配置：**  
**master\_1相关配置：**

1.  \[mysqld\]
2.  server\_id = 185
3.  log-bin = master\_1
4.  log-bin-index = master\_1.index

**master\_2相关配置：**

1.  \[mysqld\]
2.  server\_id = 186
3.  log-bin = master\_2
4.  log-bin-index = master\_2.index

**slave相关配置：**

1.  \[mysqld\]
2.  server\_id = 187
3.  relay-log = slave
4.  relay-log-index = slave.index
5.  
6.  \# 多源复制结构中的slave，官方要求master-info和relay-log-info存放处必须为TABLE.
7.  \# 如果为FILE，则在添加多个master时，会失败：ER\_SLAVE\_NEW\_CHANNEL\_WRONG\_REPOSITORY.
8.  master-info-repository = TABLE
9.  relay-log-info-repository = TABLE

**〇 为master\_1 & master\_2上建立复制用户：**

1.  GRANT REPLICATION SLAVE ON \*.\* to repl@'192.168.1.187' IDENTIFIED BY 'repl';
2.  FLUSH PRIVILEGES;

**〇 测试数据准备：**  
**master\_1测试数据：**

1.  master\_1> FLUSH LOGS;
2.  Query OK, 0 rows affected (0.00 sec)
3.  
4.  master\_1> SHOW BINARY LOGS; -- 记住当前binlog的name和position
5.  \+-----------------+-----------+
6.  | Log\_name | File\_size |
7.  \+-----------------+-----------+
8.  | master\_1.000001 | 166 |
9.  | master\_1.000002 | 455 |
10. | master\_1.000003 | 120 |
11. \+-----------------+-----------+
12. 3 rows in set (0.00 sec)
13. 
14. master\_1> CREATE DATABASE master\_1;
15. Query OK, 1 row affected (0.03 sec)

**master\_2测试数据：**

1.  master\_2> FLUSH LOGS;
2.  Query OK, 0 rows affected (0.00 sec)
3.  
4.  master\_2> SHOW BINARY LOGS; -- 记住当前binlog的name和position
5.  \+-----------------+-----------+
6.  | Log\_name | File\_size |
7.  \+-----------------+-----------+
8.  | master\_2.000001 | 166 |
9.  | master\_2.000002 | 455 |
10. | master\_2.000003 | 120 |
11. \+-----------------+-----------+
12. 3 rows in set (0.00 sec)
13. 
14. master\_2> CREATE DATABASE master\_2;
15. Query OK, 1 row affected (0.02 sec)

**〇 在slave上执行：**

1.  salve> CHANGE MASTER TO
2.  \-> MASTER\_HOST='192.168.1.185',
3.  \-> MASTER\_USER='repl',
4.  \-> MASTER\_PORT=3306,
5.  \-> MASTER\_PASSWORD='repl',
6.  \-> MASTER\_LOG\_FILE='master\_1.000003',
7.  \-> MASTER\_LOG\_POS=120
8.  \-> FOR CHANNEL 'master\_1';
9.  Query OK, 0 rows affected, 2 warnings (0.02 sec) -- 此处产生的warnings是一些安全建议和警告，本实验无视。
10. 
11. salve> CHANGE MASTER TO
12. \-> MASTER\_HOST='192.168.1.186',
13. \-> MASTER\_USER='repl',
14. \-> MASTER\_PORT=3306,
15. \-> MASTER\_PASSWORD='repl',
16. \-> MASTER\_LOG\_FILE='master\_2.000003',
17. \-> MASTER\_LOG\_POS=120
18. \-> FOR CHANNEL 'master\_2';
19. Query OK, 0 rows affected, 2 warnings (0.02 sec)
20. 
21. slave> START SLAVE;
22. Query OK, 0 rows affected (0.01 sec)
23. 
24. salve> SHOW DATABASES; -- 此时在master\_1和master\_2上的binlog events已经被正常的apply了
25. \+--------------------+
26. | Database |
27. \+--------------------+
28. | information\_schema |
29. | master\_1 |
30. | master\_2 |
31. | mysql |
32. | performance\_schema |
33. | sys |
34. \+--------------------+
35. 6 rows in set (0.00 sec)

最后通过start slave status即可查到复制状态

1.  slave> SHOW SLAVE STATUS\\G
2.  \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
3.  Slave\_IO\_State: Waiting for master to send event
4.  Master\_Host: 192.168.1.185
5.  Master\_User: repl
6.  Master\_Port: 3306
7.  ……………………………………………………
8.  Slave\_IO\_Running: Yes
9.  Slave\_SQL\_Running: Yes
10. ……………………………………………………
11. Master\_Server\_Id: 185
12. Master\_UUID: ee1f8704-58c4-11e6-95b5-000c297f23b7
13. Master\_Info\_File: mysql.slave\_master\_info
14. SQL\_Delay: 0
15. SQL\_Remaining\_Delay: NULL
16. Slave\_SQL\_Running\_State: Slave has read all relay log; waiting for more updates
17. ……………………………………………………
18. Channel\_Name: master\_1
19. Master\_TLS\_Version:
20. \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
21. Slave\_IO\_State: Waiting for master to send event
22. Master\_Host: 192.168.1.186
23. Master\_User: repl
24. Master\_Port: 3306
25. Connect\_Retry: 60
26. ……………………………………………………
27. Slave\_IO\_Running: Yes
28. Slave\_SQL\_Running: Yes
29. ……………………………………………………
30. Master\_Server\_Id: 186
31. Master\_UUID: 53774f2d-7e14-11e6-8900-000c298e914c
32. Master\_Info\_File: mysql.slave\_master\_info
33. SQL\_Delay: 0
34. SQL\_Remaining\_Delay: NULL
35. Slave\_SQL\_Running\_State: Slave has read all relay log; waiting for more updates
36. ……………………………………………………
37. Channel\_Name: master\_2
38. Master\_TLS\_Version:
39. 2 rows in set (0.00 sec)

**〇 测试：**  
**master\_1上操作：**

1.  master\_1> CREATE TABLE master\_1.test\_table(id int);
2.  Query OK, 0 rows affected (0.05 sec)
3.  
4.  master\_1> INSERT INTO master\_1.test\_table SELECT 666666;
5.  Query OK, 1 row affected (0.01 sec)
6.  Records: 1 Duplicates: 0 Warnings: 0

**master\_2上操作：**

1.  master\_2> CREATE TABLE master\_2.test\_table(massage varchar(16));
2.  Query OK, 0 rows affected (0.02 sec)
3.  
4.  master\_2> INSERT INTO master\_2.test\_table SELECT '嘿嘿嘿';
5.  Query OK, 1 row affected (0.00 sec)
6.  Records: 1 Duplicates: 0 Warnings: 0
7.  
8.  master\_2> INSERT INTO master\_2.test\_table SELECT '三阳之炎';
9.  Query OK, 1 row affected (0.00 sec)
10. Records: 1 Duplicates: 0 Warnings: 0

**slave上操作：**

1.  salve> SELECT id FROM master\_1.test\_table;
2.  \+--------+
3.  | id |
4.  \+--------+
5.  | 666666 |
6.  \+--------+
7.  1 row in set (0.00 sec)
8.  
9.  
10. salve> SELECT massage FROM master\_2.test\_table;
11. \+--------------+
12. | massage |
13. \+--------------+
14. | 嘿嘿嘿 |
15. | 三阳之炎 |
16. \+--------------+
17. 2 rows in set (0.00 sec)

删除某个slave

先停掉，再reset即可

stop slave for channel 'master\_8';

reset slave all for channel 'master\_8';

**〇 其他相关语法：**

1.  START/STOP/RESET ALL/RESET SLAVE FOR CHANNEL 'XXX';
2.  
3.  SHOW SLAVE STATUS FOR CHANNEL 'XXX';

ps.  
与上述传统position方式类似，GTID方式配置起来也类似，开启GTID后，需要注意使用FOR CHANNEL 'xxx'关键字即可，比如：

1.  CHANGE MASTER TO
2.  MASTER\_HOST='',
3.  MASTER\_USER='repl',
4.  MASTER\_PORT=3306,
5.  MASTER\_PASSWORD='repl',
6.  MASTER\_AUTO\_POSITION = 1
7.  FOR CHANNEL 'master\_1';

多台主机的schema名字不可以一样，（比如master\_1为db\_00 ... db\_09共10库，master\_2为db\_10 ... db\_19，master\_3为db\_20 ... db\_29 ……）

转载于:https://www.cnblogs.com/xkcp008/p/9431787.html

[CentOS]: http://www.linuxidc.com/topicnews.aspx?tid=14