openEuler欧拉配置MySQL8的MGR单主双从.240108

​一、 系统优化(三个节点全部操作)

关闭防火墙

systemctl stop firewalld
systemctl disable firewalld

关闭selinux

echo "SELINUX=disabled" > /etc/selinux/config
echo "SELINUXTYPE=targeted" >> /etc/selinux/config
cat /etc/selinux/config
setenforce 0

设置主机名

hostnamectl set-hostname PRD-MS-Mysql01

更改host

vim /etc/hosts

XXX.XX.XX.105  PRD-MS-Mysql01
XXX.XX.XX.106  PRD-MS-Mysql02
XXX.XX.XX.107  PRD-MS-Mysql03

安装Mysql

dnf install -y mysql-server
systemctl start mysqld && systemctl enable mysqld

二、MySQL配置 主节点 XXX.XX.XX.105

vim /etc/my.cnf

server_id=105
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
transaction_write_set_extraction=XXHASH64
plugin_load_add='group_replication.so'
loose-group_replication_group_name = '57b971b0-8383-11ee-b2f2-2a4e7333b5c9'
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = 'PRD-MS-Mysql01:33061'
loose-group_replication_group_seeds = 'PRD-MS-Mysql01:33061,PRD-MS-Mysql02:33062,PRD-MS-Mysql03:33063'
loose-group_replication_bootstrap_group = off
loose-group_replication_recovery_get_public_key= ON
loose-group_replication_single_primary_mode=on
loose-group_replication_enforce_update_everywhere_checks=off
  loose-group_replication_ip_whitelist='XXX.XX.XX.105,XXX.XX.XX.106,XXX.XX.XX.107'

systemctl restart mysqld

直接输入mysql，改root密码 （Mysql8.0.30 默认root密码为空）

ALTER USER 'root'@'localhost' IDENTIFIED BY '********';

创建MGR组

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'Password105';
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 REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='Password105' FOR CHANNEL 'group_replication_recovery';

启动集群 主节点

SET GLOBAL group_replication_bootstrap_group=ON;   
START GROUP_REPLICATION USER='rpl_user', PASSWORD='Password105';
SET GLOBAL group_replication_bootstrap_group=OFF;

查看状态

select * from performance_schema.replication_group_members;
select * from performance_schema.replication_connection_status\G;

报错日志查看：
tail -f /var/log/mysql/mysqld.log

三、MySQL配置 从节点 XXX.XX.XX.106、107

my.cnf,其他和主节点一样，这两个地方分别改：

server-id=106
 
loose-group_replication_local_address = 'PRD-MS-Mysql02:33062'
loose-group_replication_group_seeds = 'PRD-MS-Mysql01:33061,PRD-MS-Mysql02:33062,PRD-MS-Mysql03:33063'

改root密码、创建MGR组、查日志等，和主库一样，区别在于，从节点只要一句就能启动：

启动集群 从节点

START GROUP_REPLICATION USER='rpl_user', PASSWORD='Password105';

全部OK后，主节点看看：

 
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 | 1d16d8fb-8474-11ee-89c4-2ac8e8fb3ed8 | PRD-MS-Mysql03 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
| group_replication_applier | 480c6ae8-8535-11ee-a8ef-1e0edcf341b3 | PRD-MS-Mysql01 |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
| group_replication_applier | e6848114-84e8-11ee-ad92-463fdcfb1e78 | PRD-MS-Mysql02 |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

四、后续优化

更新root密码，设置远程访问

select host,user from user;
update user set host='%' where user='root' and host='localhost';
ALTER USER 'root'@'%' IDENTIFIED BY 'c新密码*';
FLUSH PRIVILEGES;

my.cnf ，打开开机自启，主节点：

loose-group_replication_start_on_boot = on
loose-group_replication_bootstrap_group = on

从节点：
loose-group_replication_start_on_boot = on

​That's All.