mysql mgr 搭建记录
摘要
入职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) 地址项
