CentOS7部署MySQL80集群

趴着喝可乐

已于 2023-10-05 13:42:07 修改

阅读量243

点赞数 2

分类专栏： # CentOS # DataWarehouse 文章标签： MySQL MGR MySQL Cluster

于 2023-09-23 23:47:46 首次发布

本文链接：https://blog.csdn.net/u011424614/article/details/133222091

版权

CentOS 同时被 2 个专栏收录

26 篇文章 0 订阅

订阅专栏

DataWarehouse

19 篇文章 0 订阅

订阅专栏

CentOS7部署MySQL80集群

一、前言

Linux 发行版：CentOS-7-x86_64-DVD-1804.iso
MySQL 版本：mysql80-community-release-el7-9 （version：8.0.34）

Group Replication：https://dev.mysql.com/doc/refman/8.0/en/group-replication.html

Single-Primary Mode：https://dev.mysql.com/doc/refman/8.0/en/group-replication-single-primary-mode.html

Multi-Primary Mode：https://dev.mysql.com/doc/refman/8.0/en/group-replication-multi-primary-mode.html

Identifier Case Sensitivity：https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

MySQL Yum Repository：https://dev.mysql.com/downloads/repo/yum/

[ContOS] MySQL安装部署：https://blog.csdn.net/u011424614/article/details/94555816

[Windows] MySQL安装部署：https://blog.csdn.net/u011424614/article/details/102466819

CentOS7安装部署MySQL80：https://blog.csdn.net/u011424614/article/details/132418916

CentOS基础操作命令：https://blog.csdn.net/u011424614/article/details/94555916

二、正文

1.MySQL集群方案

MySQL Group Replication： （此文章部署方案）MySQL Group Replication是MySQL的官方多主复制方案，允许多个MySQL实例共同工作，提供高可用性和负载均衡。它采用了基于Paxos协议的组复制机制。
MySQL Cluster（NDB Cluster）： MySQL Cluster是一种高度可扩展的分布式数据库解决方案，适用于需要高可用性和实时性的应用程序。它具有自动分片、故障恢复和负载均衡等功能。
Percona XtraDB Cluster： Percona XtraDB Cluster是一个基于Percona Server和Galera Cluster的高可用性MySQL集群解决方案。它提供了自动数据同步和负载均衡。
Tungsten Cluster： Tungsten Cluster是一个开源的MySQL集群解决方案，提供了高可用性、负载均衡和故障切换功能。它还支持跨数据中心复制。
Vitess： Vitess是一个开源的数据库中间件，设计用于将MySQL扩展到大规模、高可用性的云原生应用程序。它支持水平分片、自动负载均衡和故障切换。

2.硬件配置

机器名	IP	端口
sys-cluster-01	192.168.249.131	3306、33061
sys-cluster-02	192.168.249.132	3306、33061
sys-cluster-03	192.168.249.133	3306、33061

3.安装部署MGR

1）基础环境

修改3台服务器的主机名

# 131 执行
hostnamectl set-hostname sys-cluster-01 --static
hostnamectl set-hostname sys-cluster-01 --transient

# 132 执行
hostnamectl set-hostname sys-cluster-02 --static
hostnamectl set-hostname sys-cluster-02 --transient

# 133 执行
hostnamectl set-hostname sys-cluster-03 --static
hostnamectl set-hostname sys-cluster-03 --transient

# 查询状态
hostnamectl status

修改3台服务器的hosts文件

cat > /etc/hosts <<EOF
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.249.131 sys-cluster-01
192.168.249.132 sys-cluster-02
192.168.249.133 sys-cluster-03
EOF

关闭3台服务器防火墙，或配置开放的端口，后者比较安全

#--关闭防火墙
systemctl stop firewalld.service

#--开机禁用防火墙
systemctl disable firewalld.service

#--防火墙状态
systemctl status firewalld.service

2）安装MySQL

在3 台服务器上安装MySQL

安装参考：《CentOS7安装部署MySQL80》
目录：

安装目录：/usr/local/mysql
数据库文件目录，例如表结构和数据：/var/lib/mysql
日志文件目录：/var/log/mysql
配置文件目录：/etc/mysql
用户名：root 密码：Root@#123456

3）修改集群配置

在3 台服务器上修改配置文件

根据服务器情况修改，不同点是 server_id 和 loose-group_replication_local_address

cat > /etc/my.cnf <<EOF
[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 禁用不支持MGR的存储引擎
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
# Server 实例级别的唯一标志
server_id=1
# 启用GTID
gtid_mode=ON
# 只允许执行GTID模式下被认为安全的语句
enforce_gtid_consistency=ON
# 启用二进制日志记录功能
log_bin=binlog
# 启用SQL线程回放之后将二进制写入自身的binlog中，在组复制中，依赖于每个成员持久化的binlog来实现一些数据自动平衡的特性
log_replica_updates=ON
# 启用ROW格式复制，增强数据一致性
binlog_format=ROW
# 启用双TABLE，使用InnoDB引擎表来保存IO和SQL线程的位置信息（复制元数据），以增强复制状态的安全性
master_info_repository=TABLE
relay_log_info_repository=TABLE
# MGR 配置
transaction_write_set_extraction=XXHASH64
plugin_load_add='group_replication.so'
loose-group_replication_group_name="fd9ad78a-be73-f9cb-1e3d-a10b2c77c74e"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "sys-cluster-01:33061"
loose-group_replication_group_seeds= "sys-cluster-01:33061,sys-cluster-02:33061,sys-cluster-03:33061"
loose-group_replication_bootstrap_group= off
group_replication_transaction_size_limit=600000000

# 控制 InnoDB 存储引擎的严格模式,OFF用于数据迁移或数据清理操作时
innodb_strict_mode=OFF
# 客户端和服务器之间可以传输的最大数据包大小
max_allowed_packet = 900M
# 指定 InnoDB 存储引擎的默认行格式
innodb-default-row-format = dynamic
# 指定事务等待锁的最长时间（以秒为单位）
innodb-lock-wait-timeout = 1200
# 定义 InnoDB 存储引擎用于缓冲事务日志的内存大小
innodb-log-buffer-size=512M
# 定义 InnoDB 存储引擎事务日志文件的大小
innodb-log-file-size = 1G

# 设置大小写不敏感
lower_case_table_names=1
# 数据库允许的最大并发连接数
max_connections = 1000
# 控制 MySQL 数据库的 SQL 执行行为和规则
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
EOF

修改配置后，重启服务

systemctl restart mysqld

可能会报错，可以先注释配置中的 lower_case_table_names=1 ，具体报错请查看 三、报错列表 - 报错1

4）创建复制用户

MySQL 8.0 创建用户时，默认使用 caching_sha2_password 插件，如果未指定 SSL 密钥，启动MGR时，会导致从节点将无法连接到主节点；这里有两个解决方案：

方案一：创建用户时，改用 mysql_native_password 插件

方案二：my.cnf 配置 group_replication_recovery_get_public_key=1，指定 MySQL 使用服务器公钥进行通信

方案一

3台服务器上，创建复制用户

mysql -uroot -p

# 查询插件，这里关注：group_replication
# （备用）INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED WITH mysql_native_password BY 'Root@#123456';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Root@#123456' FOR CHANNEL 'group_replication_recovery';

# 备用：DELETE FROM mysql.user WHERE User='rpl_user';
# 备用：use mysql;
select user,host,plugin from mysql.user;

方案二

3台服务器上，编辑 my.cnf

vim /etc/my.cnf

配置内容

# 指定 MySQL 使用服务器公钥进行通信
group_replication_recovery_get_public_key=1

3台服务器上，创建复制用户

mysql -uroot -p

# 查询插件，这里关注：group_replication
# （备用）INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'Root@#123456';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Root@#123456' FOR CHANNEL 'group_replication_recovery';

# 备用：DELETE FROM mysql.user WHERE User='rpl_user';
# 备用：use mysql;
select user,host,plugin from mysql.user;

5）单主模式

MySQL :: MySQL 8.0 Reference Manual :: 18.1.3.1 Single-Primary Mode

主服务器执行

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='Root@#123456';
SET GLOBAL group_replication_bootstrap_group=OFF;

从服务器执行

# 如果出现 报错3 时使用：reset master;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='Root@#123456';

查询复制组

SELECT * FROM performance_schema.replication_group_members;

6）多主模式

MySQL :: MySQL 8.0 Reference Manual :: 18.1.3.2 Multi-Primary Mode

如果启动了单主模式，需先停止 3 台服务器的复制组

stop group_replication;

3台服务器设置多主模式

set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;

随意一台服务器执行

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

其它服务器执行

START GROUP_REPLICATION;

查询复制组

SELECT * FROM performance_schema.replication_group_members;

7）多主转单主模式

启动多主模式后，执行以下 SQL 自动转单主

select group_replication_switch_to_single_primary_mode();

8）测试

主节点执行

创建数据库

create database demo;

use demo;

建表

CREATE TABLE `sys_log`  (
  `id` varchar(32) NOT NULL,
  `log_type` int(2) DEFAULT NULL COMMENT '日志类型（1登录日志，2操作日志）',
  `log_content` varchar(1000) DEFAULT NULL COMMENT '日志内容',
  `operate_type` int(2) NULL DEFAULT NULL COMMENT '操作类型',
  `userid` varchar(32) DEFAULT NULL COMMENT '操作用户账号',
  `username` varchar(100) DEFAULT NULL COMMENT '操作用户名称',
  `ip` varchar(100) DEFAULT NULL COMMENT 'IP',
  `method` varchar(500) DEFAULT NULL COMMENT '请求java方法',
  `request_url` varchar(255) DEFAULT NULL COMMENT '请求路径',
  `request_param` longtext COMMENT '请求参数',
  `request_type` varchar(10) DEFAULT NULL COMMENT '请求类型',
  `cost_time` bigint(20) DEFAULT NULL COMMENT '耗时',
  `create_by` varchar(32) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime(0) DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(32) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime(0) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_sl_userid`(`userid`) USING BTREE,
  INDEX `idx_sl_log_type`(`log_type`) USING BTREE,
  INDEX `idx_sl_operate_type`(`operate_type`) USING BTREE,
  INDEX `idx_sl_create_time`(`create_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '系统日志表' ROW_FORMAT = Dynamic;

3.写入测试数据

INSERT INTO `sys_log`(`id`, `log_type`, `log_content`, `operate_type`, `userid`, `username`, `ip`, `method`, `request_url`, `request_param`, `request_type`, `cost_time`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES ('1588063517844226055', 2, '日志-添加', 2, 'test', '测试', '192.168.249.111', 'org.test.controller.SystemController.add()', NULL, '[{\"createBy\":\"test\",\"createTime\":1667458822057,\"id\":1"}]', NULL, 45, NULL, '2023-09-23 15:00:22', NULL, NULL);

查询

# 查询全部数据
show databases;

# 查询数据
# 备用：use demo;
select * from demo.sys_log;

4.故障恢复

确定那个库是最新数据，作为主库启动
先关闭复制组

# 主服务器
STOP GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='root@#123';
SET GLOBAL group_replication_bootstrap_group=OFF;

# 从服务器
STOP GROUP_REPLICATION;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='root@#123';

# 查询复制组
SELECT * FROM performance_schema.replication_group_members;

5.重置MySQL

# 卸载
yum -y remove mysql mysql-server
# 删除数据目录
rm -rf /var/lib/mysql
# 安装
yum -y install mysql mysql-server

# 启动 MySQL
systemctl start mysqld

# 初始化 MySQL（配置大小写不敏感）
mysqld --initialize-insecure --lower_case_table_names=1 --user=mysql

# 不需要登陆密码
mysql -u root

# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root@#123456';

# 允许远程
use mysql;
update user set host='%' where user='root' and host='localhost';
flush privileges;

# (可选) 清除 MySQL 历史命令
TRUNCATE mysql.general_log;
TRUNCATE mysql.slow_log;

6.读写分离

读写分离通常是一种应对高负载的数据库访问需求的解决方案，其中一台主数据库用于写操作，而多个从数据库用于读操作，以提高性能和负载均衡。读写分离允许应用程序在读取大量数据时从多个从数据库中选择，从而减轻主数据库的负担。
读写分离方案

读写分离 :: ShardingSphere (apache.org)

ShardingSphere：ShardingSphere 是一款强大的数据库中间件，提供了多个数据库分库分表的解决方案，同时支持读写分离

三、报错列表

报错1

报错提示：

mysql 8.0.34 Different lower_case_table_names settings for server ('0') and data dictionary ('1').

解决方案：

MySQL :: MySQL 8.0 Reference Manual :: 9.2.3 Identifier Case Sensitivity

卸载重装，初始化时，设置 lower_case_table_names=1

具体操作查看本文章 5.MySQL重置

报错2

报错提示：

[ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] There is no local IP address matching the one configured for the local node (sys-cluster-01:33061).'

解决方案：

检查 my.cnf 配置文件的 loose-group_replication_local_address
检查 /etc/hosts 文件
检查主机名

报错3

报错提示：

[System] [MY-010597] [Repl] 'CHANGE REPLICATION SOURCE TO FOR CHANNEL 'group_replication_applier' executed'. Previous state source_host='', source_port= 3306, source_log_file='', source_log_pos= 4, source_bind=''. New state source_host='<NULL>', source_port= 0, source_log_file='', source_log_pos= 4, source_bind=''.

[ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 42581b52-4d3d-11ee-8b11-246e968cedf5:1-2 > Group transactions: 3d7fdcc5-4d3d-11ee-b1ae-246e968fdebd:1-2, fd9ad78a-be73-f9cb-1e3d-a10b2c77c74e:1-2'

[ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'

[System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to sys-cluster-01:3306, sys-cluster-02:3306 on view 16940693663611257:2.'

[System] [MY-011504] [Repl] Plugin group_replication reported: 'Group membership changed: This member has left the group.'

解决方案：

# 重置 bin-log 文件，会导致数据丢失，请谨慎使用
reset master;

报错4

报错提示：

[ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The group communication engine failed to test connectivity to the local group communication engine on sys-cluster-01:33061. This may be due to one or more invalid configuration settings. Double-check your group replication local address, firewall, SE Linux and TLS configurations and try restarting Group Replication on this server.'

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

[ERROR] [MY-011640] [Repl] Plugin group_replication reported: 'Timeout on wait for view after joining group'

[ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'

[System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'

解决方案：

#方案一：开放通讯端口（推荐）
yum install -y policycoreutils-python
semanage port -a -t mysqld_port_t -p tcp 33061

# 方案二：关闭SELinux（不推荐）
setenforce 0

报错5

报错提示：

[System] [MY-013587] [Repl] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'

[System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'

[System] [MY-010597] [Repl] 'CHANGE REPLICATION SOURCE TO FOR CHANNEL 'group_replication_applier' executed'. Previous state source_host='<NULL>', source_port= 0, source_log_file='', source_log_pos= 4, source_bind=''. New state source_host='<NULL>', source_port= 0, source_log_file='', source_log_pos= 4, source_bind=''.

[ERROR] [MY-011516] [Repl] Plugin group_replication reported: 'There is already a member with server_uuid d2e1ef31-5501-11ee-b408-000c29006d91. The member will now exit the group.'

[System] [MY-011504] [Repl] Plugin group_replication reported: 'Group membership changed: This member has left the group.'

[System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'

解决方案：

SQL 语句生成uuid

select uuid();

编辑 auto.cnf 文件

vim /var/lib/mysql/auto.cnf

修改内容：

[auto]
server-uuid=53756d89-567c-11ee-9764-000c292be40a

重启 mysql 服务

systemctl restart mysqld

报错6

报错提示：

[System] [MY-010597] [Repl] 'CHANGE REPLICATION SOURCE TO FOR CHANNEL 'group_replication_applier' executed'. Previous state source_host='<NULL>', source_port= 0, source_log_file='', source_log_pos= 5783, source_bind=''. New state source_host='<NULL>', source_port= 0, source_log_file='', source_log_pos= 4, source_bind=''.

[Warning] [MY-013737] [Repl] Plugin group_replication reported: 'This member joined a group on which all members do not support member actions, as such it did reset its member configuration to the default one.'

[Warning] [MY-013783] [Repl] This member joined a group on which all members do not support replication failover channels integration on Group Replication, as such it did reset its replication failover channels configuration to the default one.

[ERROR] [MY-011529] [Repl] Plugin group_replication reported: 'The member configuration is not compatible with the group configuration. Variables such as group_replication_single_primary_mode or group_replication_enforce_update_everywhere_checks must have the same value on every server in the group. (member configuration option: [group_replication_single_primary_mode], group configuration option: [group_replication_enforce_update_everywhere_checks]).'

[System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to sys-cluster-02:3306, sys-cluster-01:3306 on view 16951362302170083:2.'

[System] [MY-011504] [Repl] Plugin group_replication reported: 'Group membership changed: This member has left the group.'

解决方案

报错原因是，当前MySQL未开启多主模式

set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;

四、其它

1.常用命令

命令	功能
systemctl start mysqld	启动服务
systemctl enable mysqld	开机自启动
systemctl status mysqld	服务状态
systemctl restart mysqld	重启服务
systemctl stop mysqld	停止服务
mysql -uroot -p	客户端登录
stop group_replication;	停止复制组

2.密钥证书

使用 openssl 生成密钥证书

# 创建存储目录
mkdir /opt/openssl-mysql-key

# 创建证书
openssl req -x509 -nodes -days 3650 -newkey rsa:2048 -keyout /opt/openssl-mysql-key/server-key.pem -out /opt/openssl-mysql-key/server-cert.pem