MySQ-8.42 MGR 组复制部署及详解

目录

1 MGR要求

2 操作系统信息和软件版本

3 集群架构图

4 MySQL MGR 主库部署步骤

---

1 MGR要求

InnoDB 存储引擎
表上必须存在主键或唯一非空索引
MGR可允许的最大节点9个

2 操作系统信息和软件版本

root@u24-mysql-mgr-42:~# cat /etc/issue
Ubuntu 24.04.2 LTS \n \l

mysql-8.0.42-linux-glibc2.28-x86_64.tar.xz

3 集群架构图

192.168.254.40 u24-mysql-mgr-40   #主库
192.168.254.41 u24-mysql-mgr-40   #从库
192.168.254.42 u24-mysql-mgr-40      #从库

4 MySQL MGR 主库部署步骤

4.1 安装MySQL-8.0.42，采用二进制包方式安装
apt-get install libaio1 -y
tar -xf mysql-8.0.42-linux-glibc2.28-x86_64.tar.xz -C /usr/local/
cd /usr/local/
mv mysql-8.0.42-linux-glibc2.28-x86_64/ mysql-8.0.42

groupadd mysql
useradd -g mysql -s /sbin/nologin mysql

#my.cnf  配置文件
root@u24-mysql-mgr-40:~# cat /etc/my.cnf 
[client]
socket = /mysql/data/mysql.sock

[mysqld]
basedir = /usr/local/mysql-8.0.42
datadir = /mysql/data
user = mysql
port = 3306
socket = /mysql/data/mysql.sock
log_error = /mysql/data/mysqld.err
log_timestamps = system

log-bin = mysql-bin
server-id = 1

#systemd 配置文件
root@u24-mysql-mgr-40:~# cat /lib/systemd/system/mysql.service 
[Unit]
Description=MySQL server
Documentation=man:mysql(8) 
After=network.target

[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/mysql/data/mysqld.pid
TimeoutSec=0
ExecStart=/usr/local/mysql-8.0.42/bin/mysqld --defaults-file=/etc/my.cnf --pid-file=/mysql/data/mysqld.pid --daemonize $MYSQLD_OTPS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILE=65535
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=False

[Install]
WantedBy=multi-user.target
Alias=mysqld.service

#将MySQL已经初始化数据文件拷贝到/mysql/data 目录下
rsync -r /mysql 192.168.254.40:/
rsync -r /mysql 192.168.254.41:/
rsync -r /mysql 192.168.254.42:/

授权
chown -R mysql:mysql /mysql/

#修改uuid,保证每台服务器唯一值
root@u24-mysql-mgr-40:/mysql/data# vi auto.cnf 
[auto]
server-uuid=4ab24c26-3a42-11f0-95cf-000c29228481

#启动MySQL服务
systemctl start mysql
systemctl enable mysql

4.2 安装MGR 主库步骤
#修改my.cnf
root@u24-mysql-mgr-40:~# vi /etc/my.cnf 
[client]
socket = /mysql/data/mysql.sock

[mysqld]
basedir = /usr/local/mysql-8.0.42
datadir = /mysql/data
user = mysql
port = 3306
socket = /mysql/data/mysql.sock
log_error = /mysql/data/mysqld.err
log_timestamps = system

#MGR
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
log-bin = mysql-bin
server-id = 1

plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.254.40:33061"
group_replication_group_seeds= "192.168.254.40:33061,192.168.254.41:33061,192.168.254.42:33061"
group_replication_bootstrap_group=off

#创建用户和授权
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;

mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

#检查该组是否已创建
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 | 4ab24c26-3a42-11f0-95cf-000c29228481 | u24-mysql-mgr-40 |        3306 | ONLINE       | PRIMARY     | 8.0.42         | XCom                       |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.01 sec)

4.3 安装MGR 从库库步骤：
#修改my.cnf
root@u24-mysql-mgr-41:~# cat /etc/my.cnf 
[client]
socket = /mysql/data/mysql.sock

[mysqld]
basedir = /usr/local/mysql-8.0.42
datadir = /mysql/data
user = mysql
port = 3306
socket = /mysql/data/mysql.sock
log_error = /mysql/data/mysqld.err
log_timestamps = system

#MGR
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON

log-bin = mysql-bin
server-id = 2

plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.254.41:33061"
group_replication_group_seeds= "192.168.254.40:33061,192.168.254.41:33061,192.168.254.42:33061"
group_replication_bootstrap_group=off

#重启
root@u24-mysql-mgr-41:~# systemctl daemon-reload
root@u24-mysql-mgr-41:~# systemctl restart mysql

#创建用户和授权
mysql -uroot -p
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
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='password' FOR CHANNEL 'group_replication_recovery';

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 | 4ab24c26-3a42-11f0-95cf-000c29228481 | u24-mysql-mgr-40 |        3306 | ONLINE       | PRIMARY     | 8.0.42          | XCom                       |
| group_replication_applier | ac39f1e6-6dfa-11e6-a69d-00212844f856 |  u24-mysql-mgr-41|        3306 | ONLINE       | SECONDARY   | 8.0.42          | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+