mysql8搭建mgr多主集群 mysql mgr搭建

转载

ctaxnews 2023-07-01 11:08:30

文章标签 mgr mysql 架构图 mysql bc linux 文章分类 MySQL 数据库

摘要

入职EB，学习搭建 MySQL Group Replication （MGR）组复制，以此记录。

下载 MySQL Community Server

下载入口：https://dev.mysql.com/downloads/mysql/

我这里选择的是 mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz

环境准备

这里是通过 vmware 起了 3 台虚拟机，采用系统是 Red Hat Enterprise Linux Server release 7.4 (Maipo)

主机名	IP
gsd-1	192.168.153.129
gsd-2	192.168.153.130
gsd-3	192.168.153.131

单机安装（3台都需要）

1、配置 hosts 文件

vi /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.153.129  gsd-1
192.168.153.130  gsd-2
192.168.153.131  gsd-3

2、创建 mysql 用户和组

groupadd mysql
useradd -g mysql mysql
su - mysql

以下操作均需在 mysql 用户下执行

3、解压并配置环境变量

# 上传 mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz 至 /home/mysql 下并解压
tar -jxvf mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz

#配置 mysql 环境变量,配置后重新登入使其生效，或者直接 source /home/mysql/.bash_profile

vim /home/mysql/.bash_profile

#.bash_profile
PATH=/home/mysql/mysql-8.0.17-linux-glibc2.12-x86_64/bin:$PATH:$HOME/bin
export PATH

4、配置 data 目录

mkdir /home/mysql/mysql-8.0.17-linux-glibc2.12-x86_64/data

5、初始化数据库

#这里实验环境下采用 --initialize-insecure 无需初始化密码
mysqld --initialize-insecure  --user=mysql --lower-case-table-names=1 --basedir=/home/mysql/mysql-8.0.17-linux-glibc2.12-x86_64  --datadir=/home/mysql/mysql-8.0.17-linux-glibc2.12-x86_64/data

6、配置 my.cnf 文件

vim /home/mysql/mysql-8.0.17-linux-glibc2.12-x86_64/my.cnf

# 建议实验下低配机器使用的配置
[mysqld]
basedir=/home/mysql/mysql-8.0.17-linux-glibc2.12-x86_64
datadir=/home/mysql/mysql-8.0.17-linux-glibc2.12-x86_64/data
lower_case_table_names=1
innodb_flush_log_at_trx_commit=2
log_bin_trust_function_creators=1
log_timestamps=SYSTEM
slow_query_log=on
long_query_time=0.02
slow_query_log_file=mysql-slow.log
relay_log=mysql-relay-bin
pid_file=mysql.pid
log_error=mysql.err
default_time_zone='+08:00'
user=mysql
general_log_file=mysql-general.log

7、启动数据库

# 后台运行 mysql 服务
mysqld_safe --defaults-file=/home/mysql/mysql-8.0.17-linux-glibc2.12-x86_64/my.cnf --user=mysql &
# 查看服务是否已启动
ps -ef |grep mysql
# 关闭 mysql 服务命令
mysqladmin -u root shutdown -p

8、修改 mysql 的 root 用户密码

mysql -uroot -p
alter user 'root'@'localhost' identified by 'aaaaaa';

至此，3 台虚拟机全部安装 mysql 服务完毕

MGR 组复制搭建（3 台都需要）

1、安装克隆插件

mysql -uroot -paaaaaa
# 安装克隆插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
# 确认插件生效
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'clone';

2、添加组复制配置 my.cnf

#### gsd-1 my.cnf
server-id=1
gtid-mode=on
enforce-gtid-consistency=on
binlog_checksum=NONE
slave-parallel-workers=32
slave-preserve-commit-order=1
slave-parallel-type=LOGICAL_CLOCK

#### gsd-2 my.cnf
server-id=2
gtid-mode=on
enforce-gtid-consistency=on
binlog_checksum=NONE
slave-parallel-workers=32
slave-preserve-commit-order=1
slave-parallel-type=LOGICAL_CLOCK

#### gsd-3 my.cnf
server-id=3
gtid-mode=on
enforce-gtid-consistency=on
binlog_checksum=NONE
slave-parallel-workers=32
slave-preserve-commit-order=1
slave-parallel-type=LOGICAL_CLOCK

3、重启数据库生效配置

mysqladmin -u root shutdown -paaaaaa
mysqld_safe --defaults-file=/home/mysql/mysql-8.0.17-linux-glibc2.12-x86_64/my.cnf --user=mysql &

4、创建组复制账户

mysql -uroot -paaaaaa
set sql_log_bin = 0;
create user 'rpl_user'@'%' identified by 'aaaaaa';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
flush privileges; 
set sql_log_bin =1;
#设定恢复凭据
change master to master_user='rpl_user',master_password='aaaaaa' for channel 'group_replication_recovery';

5、安装 group replication 插件并分别配置 my.cnf 的 group replication 配置

install plugin group_replication soname 'group_replication.so';

########  gsd-1 my.cnf
transaction_write_set_extraction=XXHASH64 
group_replication_group_name="3aa22029-8d88-11ec-b4c7-000c298f9283"
group_replication_start_on_boot=off
group_replication_local_address="gsd-1:33061"
group_replication_group_seeds="gsd-2:33061,gsd-3:33061"
group_replication_bootstrap_group=off
group_replication_recovery_use_ssl=on
group_replication_member_expel_timeout=60
group_replication_unreachable_majority_timeout=60
group_replication_ip_whitelist="gsd-1,gsd-2,gsd-3"
group_replication_autorejoin_tries=3
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

########  gsd-2 my.cnf
transaction_write_set_extraction=XXHASH64 
group_replication_group_name="3aa22029-8d88-11ec-b4c7-000c298f9283"
group_replication_start_on_boot=off
group_replication_local_address="gsd-2:33061"
group_replication_group_seeds="gsd-1:33061,gsd-3:33061"
group_replication_bootstrap_group=off
group_replication_recovery_use_ssl=on
group_replication_member_expel_timeout=60
group_replication_unreachable_majority_timeout=60
group_replication_ip_whitelist="gsd-1,gsd-2,gsd-3"
group_replication_autorejoin_tries=3
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

########  gsd-3 my.cnf
transaction_write_set_extraction=XXHASH64 
group_replication_group_name="3aa22029-8d88-11ec-b4c7-000c298f9283"
group_replication_start_on_boot=off
group_replication_local_address="gsd-3:33061"
group_replication_group_seeds="gsd-1:33061,gsd-2:33061"
group_replication_bootstrap_group=off
group_replication_recovery_use_ssl=on
group_replication_member_expel_timeout=60
group_replication_unreachable_majority_timeout=60
group_replication_ip_whitelist="gsd-1,gsd-2,gsd-3"
group_replication_autorejoin_tries=3
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

group_replication_group_name 配置三台需相同，这里参数UUID，可通过 mysql> select uuid();获取

5、重启数据库生效配置

6、启动组

　　gsd-1 作为主库先启动

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

#确认组是否已启动
SELECT * FROM performance_schema.replication_group_members;

　　gsd-2、gsd-3 分别加入组

START GROUP_REPLICATION;

#确认组状态
SELECT * FROM performance_schema.replication_group_members;

至此 mgr 搭建完成！

MGR 组复制测试

通过某一台创建测试库，测试表查看是否都有即可。

遇到的问题及处理

2022-02-14T17:13:42.951133-08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Timeout while waiting for the group communication engine to be ready!'
2022-02-14T17:13:42.951492-08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The group communication engine is not ready for the member to join. Local port: 33061'
2022-02-14T17:13:43.095506-08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2022-02-14T17:14:12.127510-08:00 8 [ERROR] [MY-011640] [Repl] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2022-02-14T17:14:12.128942-08:00 8 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'
解决办法：将 my.cnf 中的参数 group_replication_group_seeds 去掉本机 ip：port(gsd-1:33061) 地址项

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。