MYSQL8.0 MGR搭建

安装

解压介质

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

清理mariadb

rpm -qa|grep mariadb

rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps

安装依赖包

yum install -y perl

yum install -y openssl-devel

安装server

特别说明，root用户完rpm后会有一个被封bash的mysql账户同步创建，可以不使用mysql用户管理

先移除test包

rpm -ivh *.rpm

warning: mysql-community-client-8.0.33-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY

error: Failed dependencies:

        perl(Data::Dumper) is needed by mysql-community-test-8.0.33-1.el7.x86_64

        perl(JSON) is needed by mysql-community-test-8.0.33-1.el7.x86_64

        perl(Test::More) is needed by mysql-community-test-8.0.33-1.el7.x86_64

[root@mysql80-2 ~]# rm mysql-community-test-8.0.33-1.el7.x86_64

再次安装

[root@mysql80-2 ~]# rpm -ivh *.rpm

warning: mysql-community-client-8.0.33-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-common-8.0.33-1.e################################# [  9%]

   2:mysql-community-client-plugins-8.################################# [ 18%]

   3:mysql-community-libs-8.0.33-1.el7################################# [ 27%]

   4:mysql-community-client-8.0.33-1.e################################# [ 36%]

   5:mysql-community-icu-data-files-8.################################# [ 45%]

   6:mysql-community-server-8.0.33-1.e################################# [ 55%]

   7:mysql-community-server-debug-8.0.################################# [ 64%]

   8:mysql-community-devel-8.0.33-1.el################################# [ 73%]

   9:mysql-community-libs-compat-8.0.3################################# [ 82%]

  10:mysql-community-embedded-compat-8################################# [ 91%]

  11:mysql-community-debuginfo-8.0.33-################################# [100%]

[root@mysql80-2 ~]#

初始化

启动并初始化sever

systemctl start mysqld

获取初始密码

grep 'temporary password' /var/log/mysqld.log

修改密码

特别说明，root用户完rpm后会有一个被封bash的mysql账户同步创建，可以不使用mysql用户管理

[root@mysql80-2 ~]#mysql -uroot -p

--2.2.2获得的初始密码

mysql>alter user root@localhost identified by 'Pas$w0rd';

MGR搭建流程

配置参数

vi /etc/my.cnf

server_id=1

gtid_mode=ON

enforce_gtid_consistency=ON

master_info_repository=TABLE

relay_log_info_repository=TABLE

binlog_checksum=NONE

log_slave_updates=ON

log_bin=binlog

binlog_format=ROW

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

plugin_load_add='group_replication.so'

transaction_write_set_extraction=XXHASH64

group_replication_group_name='d1414b10-e02d-11ed-a49e-fa163e46ca69'

group_replication_start_on_boot=off

group_replication_local_address="192.168.1.69:33061"        # 注意：各节点不同

group_replication_group_seeds="192.168.1.69:33061,192.168.1.70:33061"

group_replication_bootstrap_group=off

group_replication_ip_allowlist="192.168.1.69,192.168.1.70"

group_replication_recovery_get_public_key = 1

重点参数说明

以下参数各个server不同

server_id=1

group_replication_local_address="192.168.1.69:33061"

白名单设置，由于政治正确因素，没有兼容性

#8.0.22后版本

group_replication_ip_allowlist="192.168.1.69,192.168.1.70"

#8.0.22前版本

group_replication_ip_whitelist="192.168.1.69,192.168.1.70"

密钥自动获取，该设置于8.0的密码默认插件为caching_sha2_password，需要生成本地的rsa密钥，使用以下命令生成，建议所有节点均执行

[root@mysql80-2 ~]# mysql_ssl_rsa_setup

设置为自动获取公钥

group_replication_recovery_get_public_key = 1

使所有空实例参数生效

systemctl restart mysqld

初始化primary

创建用户

SET SQL_LOG_BIN=0;

 CREATE USER repluser@'%' IDENTIFIED BY 'Pas$w0rd';

 GRANT REPLICATION SLAVE ON *.* TO repluser@'%';

 FLUSH PRIVILEGES;

 SET SQL_LOG_BIN=1;

初始化

#设置MGR的用户，所有节点均相同

mysql>CHANGE MASTER TO MASTER_USER='repluser', MASTER_PASSWORD='Pas$w0rd' FOR CHANNEL 'group_replication_recovery';

#临时修改参数，允许初始化

mysql> SET GLOBAL group_replication_bootstrap_group=ON;

Query OK, 0 rows affected (0.00 sec)

#启动并完成初始化

mysql> START GROUP_REPLICATION;

Query OK, 0 rows affected (1.38 sec)

#还原参数

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

Query OK, 0 rows affected (0.00 sec)

其他节点加入

创建用户

SET SQL_LOG_BIN=0;

 CREATE USER repluser@'%' IDENTIFIED BY 'Pas$w0rd';

 GRANT REPLICATION SLAVE ON *.* TO repluser@'%';

 FLUSH PRIVILEGES;

 SET SQL_LOG_BIN=1;

加入集群

#设置MGR的用户，所有节点均相同

mysql> CHANGE MASTER TO MASTER_USER='repluser', MASTER_PASSWORD='Pas$w0rd' FOR CHANNEL 'group_replication_recovery';

 #启动并自动加入集群

mysql> START GROUP_REPLICATION;

查看状态

mysql> SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+

| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+

| group_replication_applier | f3ec0a02-e010-11ed-89e9-fa163e46ca69 | mysql80-1   |        3306 | ONLINE       | PRIMARY     | 8.0.33         | XCom                       |

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+

1 row in set (0.01 sec)

问题汇总

RPM安装出现cpio: read failed - No such file or directory

[root@mysql80-1 tmp]# rpm -ivh mysql-community-server-debug-8.0.33-1.el7.x86_64.rpm

warning: mysql-community-server-debug-8.0.33-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-server-debug-8.0.################################# [100%]

error: unpacking of archive failed on file /usr/sbin/mysqld-debug;64422ddc: cpio: read failed - No such file or directory

error: mysql-community-server-debug-8.0.33-1.el7.x86_64: install failed

先用lsattr排除目录的安全设置问题。

lsattr /usr

也有可能是包损坏，重新解压

RPM安装的mysqld日志在哪

默认位置为

/var/log/mysqld.log

MY-002061

2023-04-21T16:19:24.867202Z 37 [ERROR] [MY-010584] [Repl] Replica I/O for channel 'group_replication_recovery': Error connecting to source 'rpluser@mysql80-1:3306'. This was attempt 1/1, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

caching_sha2_password密码插件问题，该设置于8.0的密码默认插件为，需要生成本地的rsa密钥，使用以下命令生成，建议所有节点均执行

[root@mysql80-2 ~]# mysql_ssl_rsa_setup

设置为自动获取公钥

group_replication_recovery_get_public_key = 1

MY-001045

2023-04-21T16:18:24.787694Z 31 [ERROR] [MY-010584] [Repl] Replica I/O for channel 'group_replication_recovery': Error connecting to source 'rpluser@mysql80-1:3306'. This was attempt 1/1, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

确认replication用户的用户名与密码，确认以下两处：

CREATE USER repluser@'%' IDENTIFIED BY 'Pas$w0rd';

CHANGE MASTER TO MASTER_USER='repluser', MASTER_PASSWORD='Pas$w0rd' FOR CHANNEL 'group_replication_recovery';

学习知识，孵化思路。积累工具，下笔有道。