MySQL8集群搭建——基于MGR方式

说明：此集群搭建为单主模式，即一主多从

0、前置工作

0.1、主、从服务器信息

主机名	操作系统	HostName映射
192.168.91.131	CentOS7	master
192.168.91.132	CentOS7	node1
192.168.91.133	CentOS7	node2

0.2、设置HostName与IP映射信息

1> 按照上面列表信息，修改IP地址对应的HostName，登录每一个服务器，执行如下命令修改为对应的映射

vi /etc/hostname

2> 在每一个服务器下，执行如下命令，在hosts中添加IP与HostName映射的绑定关系

vi /etc/hosts

添加配置信息如下：

192.168.91.131   master
192.168.91.132   node1
192.168.91.133   node2

0.3、禁用SELINUX

执行如下命令，将SELINUX=enforcing改为SELINUX=disabled

vi /etc/selinux/config

0.4、重启服务器，使配置生效

1、安装MySQL8.0

说明：

i> 以下安装步骤在主、从服务器上均需要执行，也就是本例中的192.168.91.131、192.168.91.132、192.168.91.133服务器

ii> 切换为 root 用户进行如下操作，切换root用户命令如下

su root

输入root对应的密码，即可完成切换

1.1、查看并卸载mariadb

1> 查看 mariadb 的安装包

rpm -qa | grep mariadb

2> 卸载mariadb

rpm -e XXXXXXXX --nodeps

说明：此处 XXXXXXXX 值为mariadb相关的安装包，请注意删除所有mariadb相关的安装包

1.2、安装MySQL8.0

1> 在/usr/local目录下创建mysql文件夹，通过ls查看目录结构

cd /usr/local
mkdir mysql
ls

2> 进入到创建的mysql文件夹中，下载mysql安装包

wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.23-1.el7.x86_64.rpm-bundle.tar

说明：此处MySQL安装版本为：8.0.23

3> 在mysql文件夹中，解压下载的MySQL安装包

tar -xvf mysql-8.0.23-1.el7.x86_64.rpm-bundle.tar

4> 在当前文件夹中，安装 common，libs，client，server

rpm -ivh mysql-community-common-8.0.23-1.el7.x86_64.rpm  --nodeps --force
rpm -ivh mysql-community-libs-8.0.23-1.el7.x86_64.rpm  --nodeps --force
rpm -ivh mysql-community-client-8.0.23-1.el7.x86_64.rpm  --nodeps --force
rpm -ivh mysql-community-server-8.0.23-1.el7.x86_64.rpm  --nodeps --force

 5> 在当前mysql文件夹下，执行如下命令，检查mysql安装情况

rpm -qa | grep mysql

2、配置MySQL8.0

说明：

i> 以下安装步骤在主、从服务器上均需要执行，也就是本例中的192.168.91.131、192.168.91.132、192.168.91.133服务器

ii> 切换为 root 用户进行如下操作，切换root用户命令如下

su root

输入root对应的密码，即可完成切换

2.1、初始化和配置MySQL

在当前mysql文件夹下，执行如下命令

mysqld --initialize
chown mysql:mysql /var/lib/mysql -R
systemctl start mysqld.service
systemctl enable mysqld

2.2、查看并复制MySQL初始密码

在当前mysql文件夹下，执行如下命令

cat /var/log/mysqld.log | grep password

2.3、 登录MySQL

mysql -uroot -p

说明：此处MySQL的密码为上一步的初始密码

2.4、修改MySQL密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

说明：MySQL密码自行设置，这里以 123456 为例

2.5、远程访问的授权

mysql> create user 'root'@'%' identified with mysql_native_password by '123456';
mysql> grant all privileges on *.* to 'root'@'%' with grant option;
mysql> flush privileges;

2.6、 修改加密规则并退出

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER; 
mysql> flush privileges;
mysql> exit;

3、配置iptables防火墙并进行连接测试

说明：

i> 以下安装步骤在主、从服务器上均需要执行，也就是本例中的192.168.91.131、192.168.91.132、192.168.91.133服务器

ii> 切换为 root 用户进行如下操作，切换root用户命令如下

su root

输入root对应的密码，即可完成切换

3.1、关闭默认的firewall防火墙

systemctl stop firewalld.service;
systemctl disable firewalld.service;
systemctl mask firewalld.service;

3.2、安装 iptables 防火墙

在根目录文件夹下，执行如下命令，安装iptables防火墙

yum -y install iptables-services

说明：

从CentOS7(RHEL7)开始，官方的标准防火墙设置软件从iptables变更为firewalld。如果需要使用iptables，则需禁用自带的firewalld服务，同时安装iptables服务。

同时由于在使用Docker时，启用centos7默认的firewall，启动端口映射时，防火墙规则不生效。docker默认使用了iptables防火墙机制。所以需要关闭firewall使用iptables解决。

3.3、设置并启动防火墙

在根目录文件夹下，执行如下命令

systemctl enable iptables;
systemctl start iptables;

3.4、编辑防火墙文件，添加端口

在根目录文件夹下，执行如下命令

vi /etc/sysconfig/iptables

添加如下端口配置

-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 443 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 8080 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 8090 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 33061 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 33062 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 33063 -j ACCEPT

3.5、重启防火墙使配置生效

在根目录文件夹下，执行如下命令

systemctl restart iptables.service

3.6、设置防火墙开机启动

systemctl enable iptables.service

3.7、使用 ifconfig 命令查看 IP地址

3.8、使用数据库连接工具连接MySQL数据库进行本地测试

4、基于MGR方式搭建MySQL集群

重新打开一个终端窗口，在新打开的窗口中，切换为 root 用户进行如下操作，切换root用户命令如下

su root

输入root对应的密码，即可完成切换

4.1、修改mysql配置文件信息（三个节点均需配置）

在根目录下，修改mysql配置文件，执行如下命令

vi /etc/my.cnf

具体配置信息内容如下(以第一个节点为例)：

datadir=/var/lib/mysql       
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aadaaaaa-adda-adda-aaaa-aaaaaaddaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "master:33061"
loose-group_replication_group_seeds= "master:33061,node1:33062,node2:33063"
loose-group_replication_bootstrap_group=OFF

第二个节点修改内容如下：

server_id=2
loose-group_replication_local_address= "node1:33062"

第三个节点修改内容如下：

server_id=3
loose-group_replication_local_address= "node2:33063"

4.2、创建复制账号（三个节点均需配置）

登录mysql，各节点执行如下命令

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER mgruser@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO mgruser@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='mgruser', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

说明：上述命令中的复制账号mgruser，可以根据自己喜好设置，但是需要保证各节点复制账号保持一致

4.3、安装MGR插件

mysql> install PLUGIN group_replication SONAME 'group_replication.so';
-- 查看group replication组件
mysql> show plugins;

4.4、启动MGR单主模式

4.4.1、主节点操作

1> 在主节点（当前主节点为：192.168.91.131）启动MGR，执行如下命令

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

2> 查看MGR组信息

mysql> SELECT * FROM performance_schema.replication_group_members;

4.4.2、从节点操作

1> 从节点加入MGR，在从库（192.168.91.132，192.168.91.133）上执行如下命令

mysql> START GROUP_REPLICATION;

2> 在从节点上查看MGR组信息

mysql> SELECT * FROM performance_schema.replication_group_members;

 可能遇到问题：

1> 若从节点state值一直为RECOVERING，同时查看日志报如下错：

Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

注：查看日志命令如下：

cat /var/log/mysqld.log

报错原因：由于mysql8.0之后加密规则变成 caching_sha2_password，所以使用MGR方式复制时，需要打开公钥访问。

解决方案：在每个从节点上执行下面命令

mysql> STOP GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_recovery_get_public_key=ON;
mysql> START GROUP_REPLICATION;

再次查看MGR组信息

mysql> SELECT * FROM performance_schema.replication_group_members;

2> 从库报错如下信息

Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 6ef62e0a-1614-11ec-84c1-000c29e49c19:1-3, aadaaaaa-adda-adda-aaaa-aaaaaaddaaaa:1-7 > Group transactions: aadaaaaa-adda-adda-aaaa-aaaaaaddaaaa:1-2'
Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'

解决方案：在从节点执行如下命令

mysql> STOP GROUP_REPLICATION;
mysql> RESET MASTER;
mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;

5、数据同步测试

1> 使用数据库连接工具连接数据库集群，进行读写操作，查看数据是否同步；

# 1、在主库节点master数据库上执行
mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE user (`id` INT(11) PRIMARY KEY, `name` varchar(256) NOT NULL);
mysql> INSERT INTO user VALUES (1, '星空流年');
# 2、在3个节点查看, 可以看到均有相同的表和数据
mysql> select * from test.user;
# 3、在从节点测试写入，验证不支持写入操作

2> 主、从库服务停掉，查看从库服务器状态变化

# 1、将从库节点node1上从mgr组中去除
mysql> stop group_replication;
# 2、在主库节点master或从库节点node2查看，发现仅剩2个节点
mysql> SELECT * FROM performance_schema.replication_group_members;
# 3、在主库节点master插入数据, 进行写操作，此时查看从库节点node1数据库并没有进行数据同步
# 4、将从库节点node1加入mgr组，之后再次查看从库节点node1数据库，发现数据库信息已同步
mysql> start group_replication;
# 5、主库节点master移除mgr组之后，会在从库节点node1、node2中按照配置选择对应的从库节点作为主库节点

6、MGR整合MySQL Router实现读写分离

6.1、安装MySQL Router

说明：读写分离这部分，选择从库节点进行安装部署（这里以 192.168.91.132 从库节点举例说明）

重新打开一个终端窗口，在新打开的窗口中，切换为 root 用户进行如下操作，切换root用户命令如下

su root

输入root对应的密码，即可完成切换

1> 进入到/usr/local/mysql文件夹下，下载MySQL Router安装包，并解压重命名

wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-8.0.23-el7-x86_64.tar.gz
tar -xvf mysql-router-8.0.23-el7-x86_64.tar.gz
mv mysql-router-8.0.23-el7-x86_64.tar.gz mysql-router-8.0.23

2> 进入到mysql-router-8.0.23文件夹下，执行如下命令，添加系统变量

pwd
echo "export PATH=$PATH:/usr/local/mysql/mysql-router-8.0.23/bin/" >> /etc/profile
source /etc/profile

3> 在当前文件夹下，查看MySQL Router是否安装成功

mysqlrouter --help
mysqlrouter -V

6.2、配置MySQL Router

 1> 进入 /usr/local/mysql/mysql-router-8.0.23 文件夹下创建日志、数据文件夹

mkdir log
mkdir data
ls

 2> 在当前文件夹下，进行用户授权

chown -R root:123456 /usr/local/mysql/mysql-router-8.0.23
ll

3> 进到 /usr/local/mysql/mysql-router-8.0.23/share/doc/mysqlrouter 文件夹下，复制sample_mysqlrouter.conf文件

cd /usr/local/mysql/mysql-router-8.0.23/share/doc/mysqlrouter
cp sample_mysqlrouter.conf /etc/mysqlrouter.conf

4> 进到 /etc 文件夹下，编辑 mysqlrouter.conf 配置文件，添加如下信息

[DEFAULT]
logging_folder=/usr/local/mysql/mysql-router-8.0.23/log
runtime_folder=/usr/local/mysql/mysql-router-8.0.23/run
data_folder=/usr/local/mysql/mysql-router-8.0.23/data
connect_timeout=30
read_timeout=30

[logger]
level = INFO

[routing:primary]
bind_address = 0.0.0.0
# 端口7001
bind_port = 7001  
max_connections = 1024
# 可用的支持写操作的主库，或者主库共用的IP
destinations = 192.168.91.131:3306,192.168.91.132:3306,192.168.91.133:3306 
routing_strategy = first-available

[routing:secondary] 
bind_address = 0.0.0.0 
# 端口7002
bind_port = 7002 
max_connections = 1024 
# 参与读负载均衡的从库
destinations = 192.168.91.132:3306,192.168.91.133:3306
routing_strategy = round-robin

5> 给 mysqlrouter.conf 配置文件授权

chown root:123456 /etc/mysqlrouter.conf
ll

6> 修改iptables防火墙配置放开 7001/7002 端口

vi /etc/sysconfig/iptables

添加如下配置

-A INPUT -m state --state NEW -m tcp -p tcp --dport 7001 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 7002 -j ACCEPT

重启iptables，使配置生效

systemctl restart iptables.service

7> 在当前文件夹下，执行如下命令进行读写测试

只读：(轮询)
mysql -h 192.168.91.132 -u root -p -P 7002 -e "select @@hostname"
读写：
mysql -h 192.168.91.132 -u root -p -P 7001 -e "select @@hostname"