基于MGR方式搭建MySQL8.0一主多从集群

旭日_追逐的心 2023-04-24 发布于广东

展开全文

转自：https://www.cnblogs.com/cndarren/p/16161256.html

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、重启服务器，使配置生效

init 6

1、安装MySQL8.0

说明：

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

ii> MySQL安装版本为：8.0.23

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

su root

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

1.1、查找是否安装MySQL

rpm -qa|grep mysql

若安装有旧版本的MySQL，可参考如下方式进行卸载

1.1.1、rpm安装方式卸载

rpm -e --nodeps mysql-XXXXXXXXX

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

1.1.2、二进制包安装方式卸载

# 停掉mysql
systemctl stop mysqld.service
# 查看状态
systemctl status mysqld.service
# 查找mysql
find / -name mysql
# 删除查询出来的所有MySQL相关文件
rm -rf XXXXXX
# 查看和删除mysql用户
id mysql
userdel mysql
# 删除配置文件
rm -f /etc/my.cnf

1.2、查看并卸载mariadb

1> 查看 mariadb 的安装包

rpm -qa | grep mariadb

2> 卸载mariadb

rpm -e XXXXXXXX --nodeps

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

1.3、安装MySQL8.0

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

cd /usr/local
mkdir mysql
ls

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

wget https://dev./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
# 启动MySQL
systemctl start mysqld.service
# 设置MySQL自启动
systemctl enable mysqld.service

说明：

# MySQL相关命令
# 1、启动MySQL
systemctl start mysqld.service

# 2、停止MySQL
systemctl stop mysqld.service

# 3、重启MySQL
systemctl restart mysqld.service

# 4、查看MySQL状态
systemctl status mysqld.service

# 5、设置MySQL自启动
systemctl enable mysqld.sercice

# 6、查看MySQL是否自启动
systemctl list-unit-files|grep mysqld.service

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、配置防火墙

说明：关于防火墙配置，这里列出两种方式配置，根据系统防火墙要求选择

3.1、使用CentOS 7 默认防火墙firewalld

systemctl start firewalld
# 开放3306端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

说明：关于firewalld防火墙相关命令如下

# 1、启动防火墙服务, firewalld.service 可简写为 firewalld
systemctl start firewalld.service

# 2、查看防火墙服务运行状态
systemctl status firewalld.service

# 3、重启防火墙服务
systemctl restart firewalld.service

# 4、关闭防火墙
systemctl stop firewalld.service

# 5、开机启动防火墙
systemctl enable firewalld.service

# 6、禁止firewalld开机启动
systemctl disable firewalld.service

## firewall-cmd相关

# 1、添加端口, 例如: 添加 3306 端口, 说明: --permanent 参数表示永久生效，若无此参数则在执行 reload 后失效
firewall-cmd --zone=public --add-port=80/tcp --permanent

# 2、关闭端口, 例如：关闭3306端口
firewall-cmd --zone=public --remove-port=3306/tcp --permanent

# 3、更新防火墙规则
firewall-cmd --reload

# 4、查看开放端口, 例如: 查看开放的 3306 端口
firewall-cmd --zone= public --query-port=3306/tcp

# 5、删除开放端口, 例如: 删除 3306 端口
firewall-cmd --zone= public --remove-port=3306/tcp --permanent

# 6、查看防火墙所有开放的端口
firewall-cmd --zone=public --list-ports

# 7、查看防火墙状态(关闭后显示notrunning，开启后显示running)
firewall-cmd --state

# 8、查看防火墙版本信息
firewall-cmd --version

3.2、使用 iptables 防火墙

说明：

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

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

su root

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

3.2.1、关闭默认的firewall防火墙

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

3.2.2、安装 iptables 防火墙

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

yum -y install iptables-services

说明：

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

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

3.2.3、设置并启动防火墙

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

systemctl enable iptables;
systemctl start iptables;

3.2.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.2.5、重启防火墙使配置生效

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

systemctl restart iptables.service

3.2.6、设置防火墙开机启动

systemctl enable iptables.service

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

3.2.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

# 此参数是在server收集写集合的同时以便将其记录到二进制日志。写集合基于每行的主键，并且是行更改后的唯一标识此标识将用于检测冲突。
transaction_write_set_extraction=XXHASH64
# 组的名字可以随便起,但不能用主机的GTID! 所有节点的这个组名必须保持一致！
loose-group_replication_group_name="aadaaaaa-adda-adda-aaaa-aaaaaaddaaaa"
# 启动mysql时不自动启动组复制
loose-group_replication_start_on_boot=OFF
# 本机IP地址或者映射，33061用于接收来自其他组成员的传入连接
loose-group_replication_local_address= "master:33061"
# 当前主机成员需要加入组时，Server先访问这些种子成员中的一个，然后它请求重新配置以允许它加入组
# 需要注意的是，此参数不需要列出所有组成员，只需列出当前节点加入组需要访问的节点即可。
loose-group_replication_group_seeds= "master:33061,node1:33062,node2:33063"
# 是否自动引导组。此选项只能在一个server实例上使用，通常是首次引导组时(或在整组成员关闭的情况下)，如果多次引导，可能出现脑裂。
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"

三个节点添加完配置信息之后，分别重启MySQL服务，以使配置生效

systemctl restart mysqld.service

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;

可能遇到问题：

0> 若主库启动报如下错：

Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'

解决方案：执行如下命令

vi /etc/selinux/config
# 将 SELINUX=enforcing 改为 SELINUX=disabled

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> 从库报错如下信息

Slave SQL for channel 'group_replication_recovery': Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'CREATE DATABASE `test` /*!40100 COLLATE 'utf8mb4_general_ci' */', Error_code: MY-001007

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

mysql> STOP GROUP_REPLICATION;
mysql> set global super_read_only=0;
mysql> drop database test;
mysql> set global super_read_only=1;
mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;

若从库报错如下信息

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./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 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 Router并进行读写测试

# 启动router
mysqlrouter --config=/etc/mysqlrouter.conf &

# 只读：(轮询)
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"