MySQL 8.0.27 集群搭建——基于MGR方式的一主两从架构

原创

Liujun_Deng 2023-08-13 18:51:14 博主文章分类：Mysql ©著作权

文章标签 MySQL MySQL 8.0.27 集群搭建 MGR集群一主两从架构 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者Liujun_Deng的原创作品，请联系作者获取转载授权，否则将追究法律责任

文档课题：MySQL 8.0.27 集群搭建——基于MGR方式的一主两从架构
数据库：MySQL 8.0.27
安装包：mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar
系统：rhel 7.3 64位
详细信息：

MySQL 8.0.27 集群搭建——基于MGR方式的一主两从架构_一主两从架构

1、准备工作
说明：1.1至1.3均需在三台服务器执行.
1.1、修改hosts文件
--在hosts中设置hostname与IP映射绑定关系.
[root@MySQL-827MGR-Master ~]# cat <<EOF>>/etc/hosts
192.168.133.118 MySQL-827MGR-Master
192.168.133.119 MySQL-827MGR-Slave01
192.168.133.120 MySQL-827MGR-Slave02
EOF

1.2、关闭防火墙
[root@MySQL-827MGR-Master ~]# systemctl stop firewalld
[root@MySQL-827MGR-Master ~]# systemctl disable firewalld
[root@MySQL-827MGR-Master ~]# systemctl status firewalld

1.3、禁用selinux
[root@MySQL-827MGR-Master ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

说明：修改selinux后需重启生效.
2、安装MySQL
说明：2.1至2.3在三台服务器均执行.
2.1、确认MySQL安装情况
--查看并卸载mysql
[root@MySQL-827MGR-Master lib64]# rpm -qa|grep mysql
mysql-community-common-8.0.27-1.el7.x86_64

说明：若有安装旧版MySQL，参考如下卸载.
--卸载mysql.
[root@MySQL-827MGR-Master ~]# rpm -e mysql-community-common-8.0.27-1.el7.x86_64 --nodeps

--查看并卸载mariadb
[root@MySQL-827MGR-Master ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.52-1.el7.x86_64
[root@MySQL-827MGR-Master ~]# rpm -e mariadb-libs-5.5.52-1.el7.x86_64 --nodeps
[root@MySQL-827MGR-Master ~]# rpm -qa|grep mariadb

2.2、安装MySQL
--创建目录、上传并解压安装包
[root@MySQL-827MGR-Master ~]# mkdir -p /usr/local/mysql
sftp> cd /usr/local/mysql
sftp> lcd F:\installmedium\mysql\MySQL-8.0.27
sftp> put mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar
[root@MySQL-827MGR-Master ~]# cd /usr/local/mysql
[root@MySQL-827MGR-Master mysql]# tar -xvf mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar

--安装mysql 8.0.27 common、libs、client、server
[root@MySQL-827MGR-Master mysql]# rpm -ivh mysql-community-common-8.0.27-1.el7.x86_64.rpm --nodeps --force
[root@MySQL-827MGR-Master mysql]# rpm -ivh mysql-community-libs-8.0.27-1.el7.x86_64.rpm --nodeps --force
[root@MySQL-827MGR-Master mysql]# rpm -ivh mysql-community-client-8.0.27-1.el7.x86_64.rpm --nodeps --force
[root@MySQL-827MGR-Master mysql]# rpm -ivh mysql-community-server-8.0.27-1.el7.x86_64.rpm --nodeps --force

--检查安装情况
[root@MySQL-827MGR-Master mysql]# rpm -qa | grep mysql
mysql-community-common-8.0.27-1.el7.x86_64
mysql-community-server-8.0.27-1.el7.x86_64
mysql-community-client-8.0.27-1.el7.x86_64
mysql-community-libs-8.0.27-1.el7.x86_64

2.3、启动MySQL
--初始化和配置MySQL
[root@MySQL-827MGR-Master mysql]# mysqld --initialize
[root@MySQL-827MGR-Master mysql]# chown -R mysql:mysql /var/lib/mysql

--启动MySQL
[root@MySQL-827MGR-Master mysql]# systemctl start mysqld.service
--设置自启动
[root@MySQL-827MGR-Master mysql]# systemctl enable mysqld.service

3、相关配置
说明：该步骤三台服务器均执行.
--修改mysql初始密码
[root@MySQL-827MGR-Master mysql]# cat /var/log/mysqld.log | grep password
2023-08-12T01:46:22.747404Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Fis/C0Rcqw4&

[root@MySQL-827MGR-Master mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'mysql_4U';
Query OK, 0 rows affected (0.01 sec)

--创建远程访问用户
mysql> create user 'root'@'%' identified with mysql_native_password by 'mysql_4U';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

--相关目录
mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

mysql> select @@basedir;
+-----------+
| @@basedir |
+-----------+
| /usr/     |
+-----------+
1 row in set (0.00 sec)

4、MySQL相关命令
启动MySQL
systemctl start mysqld.service
停止MySQL
systemctl stop mysqld.service
重启MySQL
systemctl restart mysqld.service
查看MySQL状态
systemctl status mysqld.service
设置MySQL自启动
systemctl enable mysqld.sercice
查看MySQL是否自启动
systemctl list-unit-files|grep mysqld.service

5、创建MGR集群
说明：以下搭建基于MGR方式的MySQL集群.
5.1、修改配置文件
5.1.1、主节点
[root@MySQL-827MGR-Master ~]# cp /etc/my.cnf /etc/my.cnf.bak`date +%Y%m%d%H%M%S`
[root@MySQL-827MGR-Master ~]# vi /etc/my.cnf
按如下修改：
[mysqld]
basedir=/usr
datadir=/var/lib/mysql
server_id=1    
socket=/var/lib/mysql/mysql.sock
log-error=/var/lib/mysql/mysqld.log
pid-file=/var/lib/mysql/mysqld.pid

binlog_checksum=NONE
gtid_mode=ON
enforce_gtid_consistency=ON
default_authentication_plugin=mysql_native_password
loose-group_replication_recovery_get_public_key=on
loose-group_replication_recovery_use_ssl=on
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="192.168.133.118:33061"
loose-group_replication_group_seeds="192.168.133.118:33061,192.168.133.119:33061,192.168.133.120:33061"
loose-group_replication_bootstrap_group=OFF

[mysql]
prompt= "Master[\\d]> "

--重启主节点
[root@MySQL-827MGR-Master ~]# systemctl restart mysqld.service

5.1.2、从节点1
[root@MySQL-827MGR-Slave01 ~]# cp /etc/my.cnf /etc/my.cnf.bak`date +%Y%m%d%H%M%S`
[root@MySQL-827MGR-Slave01 ~]# vi /etc/my.cnf
按如下修改：
[mysqld]
basedir=/usr
datadir=/var/lib/mysql  
server_id=2    
socket=/var/lib/mysql/mysql.sock
log-error=/var/lib/mysql/mysqld.log
pid-file=/var/lib/mysql/mysqld.pid

binlog_checksum=NONE
gtid_mode=ON
enforce_gtid_consistency=ON
default_authentication_plugin=mysql_native_password
loose-group_replication_recovery_get_public_key=on
loose-group_replication_recovery_use_ssl=on
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="192.168.133.119:33061"
loose-group_replication_group_seeds="192.168.133.118:33061,192.168.133.119:33061,192.168.133.120:33061"
loose-group_replication_bootstrap_group=OFF

[mysql]
prompt="Slave01[\\d]> "

--重启从节点1
[root@MySQL-827MGR-Slave01 ~]# systemctl restart mysqld.service

5.1.3、从节点2
[root@MySQL-827MGR-Slave02 ~]# cp /etc/my.cnf /etc/my.cnf.bak`date +%Y%m%d%H%M%S`
[root@MySQL-827MGR-Slave02 ~]# vi /etc/my.cnf
按如下修改：
[mysqld]
basedir=/usr
datadir=/var/lib/mysql 
server_id=3 
socket=/var/lib/mysql/mysql.sock
log-error=/var/lib/mysql/mysqld.log
pid-file=/var/lib/mysql/mysqld.pid

binlog_checksum=NONE
gtid_mode=ON
enforce_gtid_consistency=ON
default_authentication_plugin=mysql_native_password
#loose-group_replication_recovery_get_public_key=on
loose-group_replication_recovery_use_ssl=on
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="192.168.133.120:33061"
loose-group_replication_group_seeds="192.168.133.118:33061,192.168.133.119:33061,192.168.133.120:33061"
loose-group_replication_bootstrap_group=OFF
transaction_write_set_extraction=XXHASH64

[mysql]
prompt="Slave02[\\d]> "

--重启从节点2
[root@MySQL-827MGR-Slave02 ~]# systemctl restart mysqld.service

5.1.4、参数说明
#定义用户事务期间哈希写入提取的算法，组复制模式下必须设置为XXHASH64
transaction_write_set_extraction=XXHASH64
#确定组复制恢复时是否应该应用SSL，通常设置为"开"，但默认设置为"关"
loose-group_replication_recovery_use_ssl=ON
#该服务器的实例所在复制组的名称，必须是有效UUID，所有节点必须相同
loose-group_replication_group_name="28a779f2-a8f0-4621-8d7b-f8b54b03fc5d"
#确定服务器是否应该在服务器启动期间启动组复制
loose-group_replication_start_on_boot=OFF
#为复制组中其它成员提供网络地址，此处端口号避免使用3306，否则会冲突
loose-group_replication_local_address= "192.168.133.118:33061"
#用于建立新成员到组的连接组成员列表，该列表指定为由分隔号间隔的组成员网络地址列表
loose-group_replication_group_seeds= "192.168.133.118:33061,192.168.133.119:33061,192.168.133.120:33061"
#配置此服务器为引导组，该选项必须仅在一台服务器上设置，且仅当第一次启动组或重新启动整个组时，成功引导组启动后，将此选项设置为关闭
loose-group_replication_bootstrap_group=OFF
#使用mysql_native_password密码策略，防止navicat连不上mysql8
default_authentication_plugin=mysql_native_password
#设置mysql插件所在目录，因为MGR基于插件，所以必须设置插件路径
plugin_dir=/usr/lib64/mysql/plugin
#此参数决定primary节点到secondary节点的请求是否基于RSA密匙对的密码交换所需的公匙
loose-group_replication_recovery_get_public_key=on

5.2、创建复制账号
说明：三个节点均执行.
[root@MySQL-827MGR-Master ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Master[(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

Master[(none)]> create user 'repl'@'%' identified by 'repl@12345';
Query OK, 0 rows affected (0.01 sec)

Master[(none)]> grant replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)

Master[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Master[(none)]> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

说明：复制账号repl可自定义，需保证各节点复制账号一致.

5.3、安装mgr插件
说明：5.3.1至5.3.2三个节点均执行.
5.3.1、升级openssl
--将openssl-1.0.1升级到openssl-1.0.2
sftp> lcd F:\package
sftp> cd /root
sftp> put openssl-libs-1.0.2k-19.el7.x86_64.rpm
[root@MySQL-827MGR-Master ~]# rpm -ivh openssl-libs-1.0.2k-19.el7.x86_64.rpm --force
5.3.2、安装插件
[root@MySQL-827MGR-Master ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Master[(none)]> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (12.84 sec)

说明：此处第一次install插件会报如下异常.exit后重新登录数据库install会成功.

ERROR 1126 (HY000): Can't open shared library '/usr/lib64/mysql/plugin/group_replication.so' (errno: 2 /usr/lib64/mysql/plugin/group_replication.so: symbol X509_check_ip_asc, version libcrypto.so.10 not defined in file libcrypto.so)

5.4、主节点开启组复制
Master[(none)]> set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.00 sec)

Master[(none)]> start group_replication;
Query OK, 0 rows affected (1.15 sec)

Master[(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST         | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 9259a41b-3985-11ee-a5e4-000c299ea627 | MySQL-827MGR-Master |        3306 | ONLINE       | PRIMARY     | 8.0.27         | XCom                       |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)

Master[(none)]> set global group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.00 sec)

5.5、从节点开启组复制
说明：以下在两个从节点执行.
Slave01[(none)]> reset master;
Query OK, 0 rows affected (0.02 sec)

Slave01[(none)]> change master to master_user="repl",master_password="repl@12345" for channel 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.00 sec)

Slave01[(none)]> start group_replication;
Query OK, 0 rows affected (4.78 sec)

6、集群状态确认
Master[(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST          | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 5d4170a4-3987-11ee-af25-0050563ccee2 | MySQL-827MGR-Slave02 |        3306 | ONLINE       | SECONDARY   | 8.0.27         | XCom                       |
| group_replication_applier | 9259a41b-3985-11ee-a5e4-000c299ea627 | MySQL-827MGR-Master  |        3306 | ONLINE       | PRIMARY     | 8.0.27         | XCom                       |
| group_replication_applier | 9e1ab21c-3986-11ee-ad9a-0050562d243d | MySQL-827MGR-Slave01 |        3306 | ONLINE       | SECONDARY   | 8.0.27         | XCom                       |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

说明：如上所示，MGR集群搭建成功.

参考文档：https://www.cnblogs.com/nothingonyou/p/12145348.html
https://www.bilibili.com/video/BV1VN4y1A7Ta/?spm_id_from=333.337.search-card.all.click