MySQL高可用之多源复制

ゝ一世哀愁。 2024-03-30 14:52 50阅读 0赞

**一、MySQL****高可用之多源复制**

摘要：在 MySQL 8.0 版本中，提供了多源复制，多源复制的出现对于[分库分表][Link 1]的业务提供了极大的便利,本文做了详细的说明

**⛳****️ 1.****多源复制简介**

1.  在 MySQL 8.0 版本中，提供了多源复制，多源复制的出现对于分库分表的业务提供了极大的便利，
2.  目前我们已经部署了多套多源复制供统计使用。
3.  MySQL 5.7 之前只能实现一主一从、一主多从或者多主多从的复制。
4.  如果想实现多主一从的复制，只能使用 MariaDB，但是 MariaDB 又与官方的MySQL 版本不兼容。
5.  MySQL 5.7 开始支持了多主一从的复制方式，也就是多源复制。
6.  MySQL 8.0 版本相比之前的版本，无论在功能还是性能、安全等方面都已经有不少的提升。
7.  拓展：MySQL之父Widenius先生离开了Sun之后，觉得依靠Sun/Oracle来发展MySQL，
8.  实在很不靠谱，于是决定另开分支，这个分支的名字叫做MariaDB。

多源复制的好处

1.  可以集中备份，在从库上备份，不会影响线上的数据正常运行
2.  节约购买从库服务器的成本，只需要一个从库服务器即可
3.  数据汇总在一起，方便后期做数据统计
4.  减轻DBA维护工作量。

**⛳****️ 2.****多源复制使用场景**

Backing up multiple servers to a single server.  
Merging table shards.  
Consolidating data from multiple servers to a single server.

![https://img-blog.csdnimg.cn/img\_convert/1a34feb937a8e990ee57b56bdbd1e1f6.png][https_img-blog.csdnimg.cn_img_convert_1a34feb937a8e990ee57b56bdbd1e1f6.png]

1.  如上图，多源复制采用多通道的模式，和普通的复制相比，就是使用 FOR CHANNEL进行了分离。
2.  CHANGE MASTERTO....FORCHANNEL'm1';
3.  CHANGE MASTERTO....FORCHANNEL'm2';
4.  要开启多源复制功能必须需要在从库上设置 master-info-repository 和 relay-log-info-repository 这两个参数。
5.  这两个参数是用来存储同步信息的，可以设置的值为 FILE 和 TABLE，5.7默认值是 FILE。
6.  比如 master-info 就保存在 master.info 文件中,
7.  relay-log-info 保存在 relay-log.info 文件中，
8.  如果服务器意外关闭，正确的 relay-log-info 没有来得及更新到 relay-log.info 文件，这样会造成数据丢失。
9.  为了数据更加安全，通常设为 TABLE。这些表都是 innodb 类型的，支持事务。
10. 相对文件存储安全得多。在 MySQL 库下可以看见这两个表信息，分别是 mysql.slave\_master\_info 和 mysql.slave\_relay\_log\_info。
11. 这两个参数也是可以动态调整的。
12. SET GLOBAL master\_info\_repository='TABLE';
13. SET GLOBAL relay\_log\_info\_repository='TABLE';

**⛳****️ 3.****多源复制部署**

搭建过程支持GTID复制模式和[binlog][]\+position方式复制。

![https://img-blog.csdnimg.cn/img\_convert/6f29b45f8d484bb7916da264aec68694.png][https_img-blog.csdnimg.cn_img_convert_6f29b45f8d484bb7916da264aec68694.png]

**✨** **3.1** **下载镜像**

docker pull mysql:8.0.27

\--docker network create --subnet=172.72.0.0/24 mysql-network docker rm -f mysql8027M33265 mysql8027M33266 mysql8027M33267 mysql8027M33268

**✨** **3.2** **创建映射目录**

mkdir -p /mysqlmultiple/master1/conf.d

mkdir -p /mysqlmultiple/master1/data mkdir -p /mysqlmultiple/master2/conf.d mkdir -p /mysqlmultiple/master2/data mkdir -p /mysqlmultiple/master3/conf.d mkdir -p /mysqlmultiple/master3/data mkdir -p /mysqlmultiple/slave/conf.d mkdir -p /mysqlmultiple/slave/data

**✨** **3.3** **创建容器，安装MySQL**

1.  docker run -d --name mysql8027M33265 \\
2.  \-h master1 -p 33265:3306 --net=mysql-network --ip 172.72.0.10 \\
3.  \-v /mysqlmultiple/master1/conf.d:/etc/mysql/conf.d -v /mysqlmultiple/master1/data:/var/lib/mysql/ \\
4.  \-e MYSQL\_ROOT\_PASSWORD=jem \\
5.  mysql:8.0.27
6.  
7.  docker run -d --name mysql8027M33266 \\
8.  \-h master2 -p 33266:3306 --net=mysql-network --ip 172.72.0.11 \\
9.  \-v /mysqlmultiple/master2/conf.d:/etc/mysql/conf.d -v /mysqlmultiple/master2/data:/var/lib/mysql/ \\
10. \-e MYSQL\_ROOT\_PASSWORD=jem \\
11. mysql:8.0.27
12. 
13. docker run -d --name mysql8027M33267 \\
14. \-h master3 -p 33267:3306 --net=mysql-network --ip 172.72.0.12 \\
15. \-v /mysqlmultiple/master3/conf.d:/etc/mysql/conf.d -v /mysqlmultiple/master3/data:/var/lib/mysql/ \\
16. \-e MYSQL\_ROOT\_PASSWORD=jem \\
17. mysql:8.0.27
18. 
19. docker run -d --name mysql8027S33268 \\
20. \-h slave1 -p 33268:3306 --net=mysql-network --ip 172.72.0.13 \\
21. \-v /mysqlmultiple/slave/conf.d:/etc/mysql/conf.d -v /mysqlmultiple/slave/data:/var/lib/mysql/ \\
22. \-e MYSQL\_ROOT\_PASSWORD=jem \\
23. mysql:8.0.27

**✨** **3.4** **参数文件配置**

1.  cat > /mysqlmultiple/master1/conf.d/my.cnf << "EOF"
2.  \[mysqld\]
3.  user=mysql
4.  port=3306
5.  character\_set\_server=utf8mb4
6.  secure\_file\_priv=
7.  server-id = 802733265
8.  log-bin =
9.  binlog\_format=row
10. expire\_logs\_days = 30
11. max\_binlog\_size = 100M
12. binlog-ignore-db = mysql
13. binlog-ignore-db = information\_schema
14. binlog-ignore-db = performance\_schema
15. binlog-ignore-db = sys
16. replicate\_ignore\_db=information\_schema
17. replicate\_ignore\_db=performance\_schema
18. replicate\_ignore\_db=mysql
19. replicate\_ignore\_db=sys
20. gtid-mode=ON
21. enforce-gtid-consistency=on
22. skip\_name\_resolve
23. report\_host=172.72.0.10
24. EOF
25. 
26. cat > /mysqlmultiple/master2/conf.d/my.cnf << "EOF"
27. \[mysqld\]
28. user=mysql
29. port=3306
30. character\_set\_server=utf8mb4
31. secure\_file\_priv=
32. server-id = 802733266
33. log-bin =
34. binlog\_format=row
35. expire\_logs\_days = 30
36. max\_binlog\_size = 100M
37. binlog-ignore-db = mysql
38. binlog-ignore-db = information\_schema
39. binlog-ignore-db = performance\_schema
40. binlog-ignore-db = sys
41. replicate\_ignore\_db=information\_schema
42. replicate\_ignore\_db=performance\_schema
43. replicate\_ignore\_db=mysql
44. replicate\_ignore\_db=sys
45. gtid-mode=ON
46. enforce-gtid-consistency=ON
47. skip\_name\_resolve
48. report\_host=172.72.0.11
49. EOF
50. 
51. cat > /mysqlmultiple/master3/conf.d/my.cnf << "EOF"
52. \[mysqld\]
53. user=mysql
54. port=3306
55. character\_set\_server=utf8mb4
56. secure\_file\_priv=
57. server-id = 802733267
58. log-bin =
59. binlog\_format=row
60. expire\_logs\_days = 30
61. max\_binlog\_size = 100M
62. binlog-ignore-db = mysql
63. binlog-ignore-db = information\_schema
64. binlog-ignore-db = performance\_schema
65. binlog-ignore-db = sys
66. replicate\_ignore\_db=information\_schema
67. replicate\_ignore\_db=performance\_schema
68. replicate\_ignore\_db=mysql
69. replicate\_ignore\_db=sys
70. gtid-mode=ON
71. enforce-gtid-consistency=ON
72. skip\_name\_resolve
73. report\_host=172.72.0.12
74. EOF
75. 
76. cat > /mysqlmultiple/slave/conf.d/my.cnf << "EOF"
77. \[mysqld\]
78. user=mysql
79. port=3306
80. character\_set\_server=utf8mb4
81. secure\_file\_priv=''
82. server-id = 802733268
83. log-bin =
84. binlog\_format=row
85. expire\_logs\_days = 30
86. max\_binlog\_size = 100M
87. binlog-ignore-db = mysql
88. binlog-ignore-db = information\_schema
89. binlog-ignore-db = performance\_schema
90. binlog-ignore-db = sys
91. replicate\_ignore\_db=information\_schema
92. replicate\_ignore\_db=performance\_schema
93. replicate\_ignore\_db=mysql
94. replicate\_ignore\_db=sys
95. gtid-mode=ON
96. enforce-gtid-consistency=ON
97. skip\_name\_resolve
98. report\_host=172.72.0.13
99. master-info-repository = table
100. relay-log-info-repository = table
101. EOF
102. 
103. docker restart mysql8027M33265
104. docker restart mysql8027M33266
105. docker restart mysql8027M33267
106. docker restart mysql8027S33268
107. docker ps

登陆容器，确认数据 docker ps --format "table \{ \{.ID\}\}\\t\{ \{.Names\}\}\\t\{ \{.Status\}\}\\t\{ \{.Ports\}\}"

![https://img-blog.csdnimg.cn/img\_convert/a5ec004f08eba66fc67a68c3976a0382.png][https_img-blog.csdnimg.cn_img_convert_a5ec004f08eba66fc67a68c3976a0382.png]

**✨** **3.5** **登陆MySQL**

1.  docker exec -it mysql8027M33265 mysql -uroot -pjem
2.  mysql -uroot -pjem -h192.168.1.54 -P33265 -e "select @@hostname,@@server\_id,@@server\_uuid"
3.  mysql -uroot -pjem -h192.168.1.54 -P33266 -e "select @@hostname,@@server\_id,@@server\_uuid"
4.  mysql -uroot -pjem -h192.168.1.54 -P33267 -e "select @@hostname,@@server\_id,@@server\_uuid"
5.  mysql -uroot -pjem -h192.168.1.54 -P33268 -e "select @@hostname,@@server\_id,@@server\_uuid"
6.  
7.  远程登陆需要修改密码
8.  docker exec -it mysql8027M33266 mysql -uroot -pjem
9.  mysql> alter user root@'%' identified with mysql\_native\_password by 'root'; --更改密码
10. mysql> flush privileges;
11. docker exec -it mysql8027S33268 mysql -uroot -pjem

**⛳****️ 4.****主从配置**

**✨** **4.1** **主库配置**

1.  \--在 3 台主库
2.  mysql -uroot -proot -h192.168.1.54 -P33265
3.  mysql -uroot -proot -h192.168.1.54 -P33266
4.  mysql -uroot -proot -h192.168.1.54 -P33267
5.  
6.  mysql> create user repl@'%' identified with mysql\_native\_password by 'root';
7.  mysql> grant all on \*.\* to repl@'%' with grant option;
8.  mysql> flush privileges;
9.  
10. select user,host,grant\_priv,password\_last\_changed,authentication\_string from mysql.user;
11. 
12. show master status \\G;
13. show slave hosts;
14. select @@hostname,@@server\_id,@@server\_uuid;

**✨** **4.2** **从库配置**

1.  mysql -uroot -proot -h192.168.1.54 -P33268
2.  
3.  change master to
4.  master\_host='172.72.0.10',
5.  master\_port=3306,master\_user='repl',
6.  master\_password='root',
7.  master\_auto\_position=1 FOR CHANNEL 'Master1';
8.  show slave status \\G;
9.  
10. \-- 启动所有 SLAVE
11. mysql> START SLAVE;
12. 
13. \--主库创建数据测试
14. mysql -uroot -proot -h192.168.1.54 -P33265
15. mysql> create database test;
16. 
17. \--主库2和3
18. change master to
19. master\_host='172.72.0.11',
20. master\_port=3306,
21. master\_user='repl',
22. master\_password='root',
23. master\_auto\_position=1 FOR CHANNEL 'Master2';
24. 
25. change master to
26. master\_host='172.72.0.12',
27. master\_port=3306,
28. master\_user='repl',
29. master\_password='root',
30. master\_auto\_position=1 FOR CHANNEL 'Master3';
31. 
32. \-- 也可以单独启动需要同步的通道
33. START SLAVE FOR CHANNEL 'master2';
34. START SLAVE FOR CHANNEL 'master3';
35. 
36. select a.master\_log\_pos,a.host,a.user\_name,a.user\_password,a.port,a.uuid,a.channel\_name
37. from mysql.slave\_master\_info a;

![https://img-blog.csdnimg.cn/img\_convert/4de8a867410e0029ec0055be9ab854ee.png][https_img-blog.csdnimg.cn_img_convert_4de8a867410e0029ec0055be9ab854ee.png]

![https://img-blog.csdnimg.cn/img\_convert/4ed6057a46f7688c4d8dfdf5103b6fae.png][https_img-blog.csdnimg.cn_img_convert_4ed6057a46f7688c4d8dfdf5103b6fae.png]

**⛳****️ 5.****测试多源复制**

1.  \-- mysql -uroot -proot -h192.168.1.54 -P33265
2.  create database master1;
3.  use master1;
4.  CREATE TABLE \`test1\` (\`id\` int(11) DEFAULT NULL,\`count\` int(11) DEFAULT NULL);
5.  insert into test1 values(1,1);
6.  
7.  \-- mysql -uroot -proot -h192.168.1.54 -P33266
8.  create database master2;
9.  use master2;
10. CREATE TABLE \`test2\` (\`id\` int(11) DEFAULT NULL,\`count\` int(11) DEFAULT NULL);
11. insert into test2 values(2,2);
12. 
13. \-- mysql -uroot -proot -h192.168.1.54 -P33267
14. create database master3;
15. use master3;
16. CREATE TABLE \`test3\` (\`id\` int(11) DEFAULT NULL,\`count\` int(11) DEFAULT NULL);
17. insert into test3 values(3,3);
18. 
19. 
20. \--从库查询
21. \-- mysql -uroot -proot -h192.168.1.54 -P33268
22. show databases;
23. SELECT \* FROM master1.test1;
24. SELECT \* FROM master2.test2;
25. SELECT \* FROM master3.test3;

**⛳****️ 6.** **注意的点**

1、初次配置耗时较长，需要将各个 master 的数据 dump 下来，再 source 到 slave 上。  
2、需要考虑各 master 数据增长频率，slave 的数据增长频率是这些数据的总和。如果太高，会导致大量的磁盘 IO，造成数据更新延迟，最严重的是会影响正常的查询。  
3、如果多个主数据库实例中存在同名的库，则同名库的表都会放到一个库中；  
4、如果同名库中的表名相同且结构相同，则数据会合并到一起；如果结构不同，则先建的有效。

[Link 1]: https://so.csdn.net/so/search?q=%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8&spm=1001.2101.3001.7020
[https_img-blog.csdnimg.cn_img_convert_1a34feb937a8e990ee57b56bdbd1e1f6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/fe3bc41bf03d4e3e943d19ffafa0b11e.png
[binlog]: https://so.csdn.net/so/search?q=binlog&spm=1001.2101.3001.7020
[https_img-blog.csdnimg.cn_img_convert_6f29b45f8d484bb7916da264aec68694.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/8b94e197854b4900aa269b84761fbb47.png
[https_img-blog.csdnimg.cn_img_convert_a5ec004f08eba66fc67a68c3976a0382.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/573683e23ac642e08994297c68fce9fd.png
[https_img-blog.csdnimg.cn_img_convert_4de8a867410e0029ec0055be9ab854ee.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/c4bbf5c9079f4f00909297091d450e10.png
[https_img-blog.csdnimg.cn_img_convert_4ed6057a46f7688c4d8dfdf5103b6fae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/9535b5ef494c4c879b86a706c79180a3.png