mysql5.7 MGR集群搭建部署

谁借莪１个温暖的怀抱￠ 2022-06-13 02:00 272阅读 0赞

最近看了一下mysql5.7的MGR集群挺不错的，有单主和多主模式，于是乎搭建测试了一下效果还不错，我指的不错是搭建和维护方面都比较简单。网上绝大多数都是单主模式，当然我这里也是，为了加深印象，特意记录一下搭建过程，等以后再去尝试多主模式，相信大家现在数据库的瓶颈基本都是在写，读写分离虽然是一种可行的解决方案，但是如果数据量很大，写一样会有问题，虽然有些解决方案能部署多个主节点，能同时进行读写，但是脑裂又是一个严重的问题，所以这里MGR集群内置了自动化脑裂防护机制又得到了很多人的青睐，这里MGR简称MySQL Group Replication是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。注意本文这里不再阐述原理性的东西。  
注意：我这里采用编译安装的方式，如果想简单直接yum安装mysql5.7也行，mysql编译安装需要的磁盘空间还是比较大的，一般在7G左右，所以要提前规划好，用三个节点比较接近生产环境，而且更直接清晰。  
详细部署信息如下：

<table style="padding:0px; font-size:12px; color:rgb(63,63,63); height:127px"> 
 <tbody style="padding:0px"> 
  <tr style="padding:0px; border:1px solid rgb(232,232,232)"> 
   <td style="padding:6px 0px">主机名</td> 
   <td style="padding:6px 0px">IP地址</td> 
   <td style="padding:6px 0px">安装软件</td> 
   <td style="padding:6px 0px">用途</td> 
  </tr> 
  <tr style="padding:0px; border:1px solid rgb(232,232,232)"> 
   <td style="padding:6px 0px">apache</td> 
   <td style="padding:6px 0px">192.168.2.25</td> 
   <td style="padding:6px 0px">cmake、boost、mysql</td> 
   <td style="padding:6px 0px">节点</td> 
  </tr> 
  <tr style="padding:0px; border:1px solid rgb(232,232,232)"> 
   <td style="padding:6px 0px">nginx</td> 
   <td style="padding:6px 0px">192.168.2.26</td> 
   <td style="padding:6px 0px">cmake、boost、mysql</td> 
   <td style="padding:6px 0px">节点</td> 
  </tr> 
  <tr style="padding:0px; border:1px solid rgb(232,232,232)"> 
   <td style="padding:6px 0px">kibana</td> 
   <td style="padding:6px 0px">192.168.2.30</td> 
   <td style="padding:6px 0px">cmake、boost、mysql</td> 
   <td style="padding:6px 0px">节点</td> 
  </tr> 
 </tbody> 
</table>

1、三台机器准备工作

1.  rpm -qa mysql mariadb

如果有则卸载即可！  
写入hosts文件映射关系，集群用得到  
192.168.2.25    apache  
192.168.2.26    nginx  
192.168.2.30    kibana  
2、安装依赖包

1.  yum install gcc gcc-c++ ncurses-devel -y

3、安装cmake，下载地址：https://cmake.org/download/

1.  tar zxvf cmake-3.7.2.tar.gz
2.  cd make-3.7.2
3.  ./configure
4.  gmake && gmake install

4、安装boost，因为mysql5.7需要，注意这里下载版本是1\_59\_0和mysql版本是对应的，如果你的MySQL版本和我的不一样，不添加-DWITH\_BOOST这个参数时它会报错告诉你需要下载boost的哪个版本。

1.  tar zxvf boost\_1\_59\_0.tar.gz
2.  cp -r boost\_1\_59\_0 /usr/local/boost

5、安装mysql5.7.17及初始化操作

1.  groupadd mysql
2.  useradd -M -s /sbin/nologin mysql -g mysql
3.  tar zxvf mysql-5.7.17.tar.gz
4.  cd mysql-5.7.17
5.  cmake -DCMAKE\_INSTALL\_PREFIX=/data/mysql -DSYSCONFDIR=/etc -DDEFAULT\_CHARSET=utf8 -DDEFAULT\_COLLATION=utf8\_general\_ci -DWITH\_EXTRA\_CHARSETS=all -DWITH\_BOOST=/usr/local/boost
6.  make
7.  make install
8.  chown -R mysql.mysql /data/mysql
9.  mv /etc/my.cnf /etc/my.cnf.default
10. cp /data/mysql/support-files/my-default.cnf /etc/my.cnf
11. /data/mysql/bin/mysqld –initialize –user=mysql –basedir=/data/mysql –datadir=/data/mysql/data                 //注意初始化会生成一个随机的密码，请牢记
12. echo “PATH=$PATH:/data/mysql/bin” >> /etc/profile
13. source /etc/profile
14. cp /data/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
15. chmod +x /etc/rc.d/init.d/mysqld

以上步骤在三台机器上都需要执行

6、开始搭建MGR集群环境，修改第一个节点的my.cnf文件，内容如下：

1.  \# For advice on how to change settings please see
2.  \# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
3.  \# \*\*\* DO NOT EDIT THIS FILE. It’s a template which will be copied to the
4.  \# \*\*\* default location during install, and will be replaced if you
5.  \# \*\*\* upgrade to a newer version of MySQL.
6.    
    
7.  \[mysqld\]
8.    
    
9.  \# Remove leading \# and set to the amount of RAM for the most important data
10. \# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
11. \# innodb\_buffer\_pool\_size = 128M
12.   
    
13. \# Remove leading \# to turn on a very important data integrity option: logging
14. \# changes to the binary log between backups.
15. \# log\_bin
16.   
    
17. \# These are commonly set, remove the \# and set as required.
18. basedir = /data/mysql
19. datadir = /data/mysql/data
20. port = 3306
21. socket = /data/mysql/data/mysql.sock
22. log-error = /data/mysql/data/mysqld.log
23. pid-file = /data/mysql/data/mysqld.pid
24.   
    
25. \# Remove leading \# to set options mainly useful for reporting servers.
26. \# The server defaults are faster for transactions and fast SELECTs.
27. \# Adjust sizes as needed, experiment to find the optimal values.
28. \# join\_buffer\_size = 128M
29. \# sort\_buffer\_size = 2M
30. \# read\_rnd\_buffer\_size = 2M
31.   
    
32. sql\_mode=NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES
33.   
    
34. \# Group Replication
35. server\_id = 1
36. gtid\_mode = ON
37. enforce\_gtid\_consistency = ON
38. master\_info\_repository = TABLE
39. relay\_log\_info\_repository = TABLE
40. binlog\_checksum = NONE
41. log\_slave\_updates = ON
42. log\_bin = binlog
43. binlog\_format= ROW
44.   
    
45. transaction\_write\_set\_extraction = XXHASH64
46. loose-group\_replication\_group\_name = ‘ce9be252-2b71-11e6-b8f4-00212844f856’
47. loose-group\_replication\_start\_on\_boot = off
48. loose-group\_replication\_local\_address = ‘192.168.2.25:33061’
49. loose-group\_replication\_group\_seeds =’192.168.2.25:33061,192.168.2.26:33061,192.168.2.30:33061′
50. loose-group\_replication\_bootstrap\_group = off
51.   
    
52. \[client\]
53. socket = /data/mysql/data/mysql.sock

启动mysql服务  
/etc/init.d/mysqld start

1.  set sql\_log\_bin=0;
2.  create user rpl\_user@’%’;
3.  grant replication slave on \*.\* to rpl\_user@’%’ identified by ‘rpl\_pass’;
4.  flush privileges;
5.  set sql\_log\_bin=1;
6.  change master to master\_user=’rpl\_user’,master\_password=’rpl\_pass’ for channel ‘group\_replication\_recovery’;
7.  install PLUGIN group\_replication SONAME ‘group\_replication.so’;
8.  set global group\_replication\_bootstrap\_group=ON;
9.  start group\_replication;
10. set global group\_replication\_bootstrap\_group=OFF;
11. select \* from performance\_schema.replication\_group\_members;

显示结果如下：

![技术分享][23916356_14967352001r96.png]

如果出现ONLINE，说明正常，这就是主节点，再搭建两个从节点。

7、第二个节点加入集群，复制刚刚的第一个节点的主配置文件my.cnf，只需要修改两个地方就行，已经用红色标注

1.  \# For advice on how to change settings please see
2.  \# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
3.  \# \*\*\* DO NOT EDIT THIS FILE. It’s a template which will be copied to the
4.  \# \*\*\* default location during install, and will be replaced if you
5.  \# \*\*\* upgrade to a newer version of MySQL.
6.    
    
7.  \[mysqld\]
8.    
    
9.  \# Remove leading \# and set to the amount of RAM for the most important data
10. \# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
11. \# innodb\_buffer\_pool\_size = 128M
12.   
    
13. \# Remove leading \# to turn on a very important data integrity option: logging
14. \# changes to the binary log between backups.
15. \# log\_bin
16.   
    
17. \# These are commonly set, remove the \# and set as required.
18. basedir = /data/mysql
19. datadir = /data/mysql/data
20. port = 3306
21. socket = /data/mysql/data/mysql.sock
22. log-error = /data/mysql/data/mysqld.log
23. pid-file = /data/mysql/data/mysqld.pid
24.   
    
25. \# Remove leading \# to set options mainly useful for reporting servers.
26. \# The server defaults are faster for transactions and fast SELECTs.
27. \# Adjust sizes as needed, experiment to find the optimal values.
28. \# join\_buffer\_size = 128M
29. \# sort\_buffer\_size = 2M
30. \# read\_rnd\_buffer\_size = 2M
31.   
    
32. sql\_mode=NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES
33.   
    
34. \# Group Replication
35. server\_id = 2
36. gtid\_mode = ON
37. enforce\_gtid\_consistency = ON
38. master\_info\_repository = TABLE
39. relay\_log\_info\_repository = TABLE
40. binlog\_checksum = NONE
41. log\_slave\_updates = ON
42. log\_bin = binlog
43. binlog\_format= ROW
44.   
    
45. transaction\_write\_set\_extraction = XXHASH64
46. loose-group\_replication\_group\_name = ‘ce9be252-2b71-11e6-b8f4-00212844f856’
47. loose-group\_replication\_start\_on\_boot = off
48. loose-group\_replication\_local\_address = ‘192.168.2.26:33061’
49. loose-group\_replication\_group\_seeds =’192.168.2.25:33061,192.168.2.26:33061,192.168.2.30:33061′
50. loose-group\_replication\_bootstrap\_group = off
51.   
    
52. \[client\]
53. socket = /data/mysql/data/mysql.sock

第二个节点执行如下命令：

1.  set sql\_log\_bin=0;
2.  create user rpl\_user@’%’;
3.  grant replication slave on \*.\* to rpl\_user@’%’ identified by ‘rpl\_pass’;
4.  set sql\_log\_bin=1;
5.  change master to master\_user=’rpl\_user’,master\_password=’rpl\_pass’ for channel ‘group\_replication\_recovery’;
6.  install plugin group\_replication SONAME ‘group\_replication.so’;
7.  set global group\_replication\_allow\_local\_disjoint\_gtids\_join=ON;
8.  start group\_replication;

显示结果如下：

![技术分享][23916356_1496738002D6j5.png]

同理第三个节点加入操作方法也和第二个节点一样。  
截图如下：

![技术分享][23916356_1496738093Rr66.png]

查询哪个是主节点：

![技术分享][23916356_14967381613K9p.png]

从上图来看很明显apache主机是主节点。

测试步骤：  
1、在主库上创建一个库，然后创建表，在两个从库上查询数据是否同步？  
2、两个从库只能执行查询操作？  
2、手动关闭主库，确认两个从库其中一个是否会变成主库？而且是MEMBER\_ID第一个字母按优先级排列的接管主库？

日常维护步骤：  
1、如果从库某一节点关闭

1.  start group\_replication;

2、如果所有的库都关闭后，第一个库作为主库首先执行

1.  set global group\_replication\_bootstrap\_group=ON;
2.  start group\_replication;

剩下的库直接执行即可！

1.  start group\_replication;

3、如果主库故障，会自动从两个从库选出一个主库，主库启动后再次执行如下命令后会变成从库

1.  start group\_replication;

[23916356_14967352001r96.png]: /images/20220613/f603dfe76bc44f5b828e03e9240dd274.png
[23916356_1496738002D6j5.png]: /images/20220613/09915a1e378a41acbae63b16a4ac2ceb.png
[23916356_1496738093Rr66.png]: /images/20220613/b8f7c4d220674184ac43ccc18c0f94db.png
[23916356_14967381613K9p.png]: /images/20220613/078b48719f1b4c5b9545fecac48c401c.png