跳到主要内容

Mysql 8.0.35 MGR集群实施配置

提示

本文为站长原创文章，版权所有，未经允许，禁止转载！

信息

数据库环境信息：
操作系统：CentOS Server 7.9 64bit
Mysql数据库版本：mysql 8.0.35 Community Server 64bit

IP地址对应关系:
192.168.99.131 mysql01
192.168.99.132 mysql02
192.168.99.133 mysql03

一、MGR介绍​

MySQL MGR（MySQL Group Replication）是MySQL官方推出的一种高可用性和可扩展性解决方案。它基于组复制（Group Replication）技术，为MySQL数据库提供了内置的分布式数据库集群解决方案。
MySQL Group Replication允许多个MySQL实例组成一个复制集群，其中每个实例都可以处理读写请求。集群中的所有实例都能够接收客户端的读写操作，并通过复制协议将这些操作同步到其他实例上，以确保数据的一致性。
MySQL Group Replication的特点包括：
1. 自动容错：当集群中的节点出现故障时，集群能够自动进行故障转移，从而确保系统的高可用性。新的节点可以自动加入集群，并且集群能够自动调整以适应变化。
2. 一致性：集群中的所有节点都保持数据的一致性，因此客户端可以向任何节点发送读写请求，而不必担心数据不一致的问题。
3. 扩展性：MySQL Group Replication支持动态扩展，可以根据负载的变化来扩展集群的规模，从而提高系统的处理能力。

二、配置​

1、系统配置​

cat /etc/security/limits.conf 
* hard nofile 65535
* soft nofile 65535

# 生效配置
ulimit -a
cat /etc/sysctl.d/99-sysctl.conf 
# shared memory
#kernel.shmmax = 34359738368
#kernel.shmall = 8388609
#kernel.shmmni = 4096

# semaphore
kernel.sem = 250 32000 100 128

# network
net.core.somaxconn = 4096
net.unix.max_dgram_qlen = 256 
net.core.netdev_max_backlog=8192
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 30

# file
fs.file-max = 65535

# 生效配置
sysctl -p
cat /usr/lib/systemd/system/mysql80.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql80
Group=mysql80
LimitNOFILE=65535
LimitNPROC=65535
Type=forking

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
 
# Execute pre and post scripts as root
PermissionsStartOnly=true

PIDFile=/opt/mysql80/run/mysqld.pid

# Start main service
ExecStart=/opt/mysql80/8.0.35/bin/mysqld --defaults-file=/home/mysql80/my.cnf --daemonize $MYSQLD_OPTS

Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false

# 设置为自动启动
systemctl enable mysql80
systemctl start mysql80
systemctl status mysql80

# 修改配置后
systemctl daemon-reload

2、数据库配置​

# repl 用户创建用于复制
mysql> create user repl@'192.168.99.%' identified with mysql_native_password by  'aaronqlm'; 

mysql> grant replication slave on *.* to repl@'192.168.99.%'; 

# 创建一个普通数据库和赋予权限
create database aaronqlm
create user aaronqlm IDENTIFIED with mysql_native_password BY by 'aaronqlm';   # 在后续版本中不再可用
create user aaronqlm IDENTIFIED with caching_sha2_password BY by 'aaronqlm';   # default_authentication_plugin
grant all privileges on aaronqlm.* to aaronqlm;

# 创建表t1
CREATE TABLE t1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10),
    mark VARCHAR(100)
);

# 插入100w条数据
drop PROCEDURE insert_t1_million_records;
DELIMITER //
CREATE PROCEDURE insert_t1_million_records()
BEGIN
    DECLARE counter INT DEFAULT 0;
    START TRANSACTION;
    WHILE counter < 1000000 DO
        INSERT INTO t1 (name, mark) VALUES (CONCAT('name', counter), CONCAT('mark', counter));
        SET counter = counter + 1;
    END WHILE;
    COMMIT;
END//
DELIMITER ;

call insert_t1_million_records()

3、MGR配置​

# 初始化
mysqld --initialize --user=mysql80 --basedir /opt/mysql80/8.0.35 --datadir /opt/mysql80/data

# my.cnf
# node1
[mysql80@mysql01 ~]$ cat my.cnf 
[mysqld]
basedir = '/opt/mysql80/8.0.35'
datadir = '/opt/mysql80/data'
port = 3308
socket = '/opt/mysql80/run/mysql.sock'
default-time_zone = '+8:00'
pid-file = '/opt/mysql80/run/mysqld.pid'
log_error_verbosity = 3
log-error = '/opt/mysql80/log/mysqld-err.log'
secure-file-priv = '/opt/mysql80/secure-file'
table_open_cache = 8192
skip_name_resolve = ON
slave_parallel_workers=4

server_id = 1
mysqlx = OFF
gtid-mode = on
enforce-gtid-consistency = true

report_host = '192.168.99.131'
sql_require_primary_key = 1
loose-plugin_load_add = 'mysql_clone.so'
loose-plugin_load_add = 'group_replication.so'
loose-group_replication_group_name = '59831f0c-ffd6-4e06-ab6d-e2fdb0e47b25' 
loose-group_replication_start_on_boot = 0 
loose-group_replication_local_address = '192.168.99.131:13308'
loose-group_replication_group_seeds = '192.168.99.131:13308,192.168.99.132:13308,192.168.99.133:13308'
loose-group_replication_bootstrap_group = OFF
loose-group_replication_flow_control_mode = 'DISABLED'
loose-group_replication_single_primary_mode = ON
loose-group_replication_ip_whitelist = '192.168.99.0/24'
transaction_isolation = READ-COMMITTED

#node 2
[mysql80@mysql02 ~]$ cat my.cnf 
[mysqld]
basedir = '/opt/mysql80/8.0.35'
datadir = '/opt/mysql80/data'
port = 3308
socket = '/opt/mysql80/run/mysql.sock'
default-time_zone = '+8:00'
pid-file = '/opt/mysql80/run/mysqld.pid'
log_error_verbosity = 3
log-error = '/opt/mysql80/log/mysqld-err.log'
secure-file-priv = '/opt/mysql80/secure-file'
table_open_cache = 8192
skip_name_resolve = ON
slave_parallel_workers=4

server_id = 2
mysqlx = OFF
gtid-mode = on
enforce-gtid-consistency = true

report_host = '192.168.99.132'
sql_require_primary_key = 1
loose-plugin_load_add = 'mysql_clone.so'
loose-plugin_load_add = 'group_replication.so'
loose-group_replication_group_name = '59831f0c-ffd6-4e06-ab6d-e2fdb0e47b25' 
loose-group_replication_start_on_boot = 0 
loose-group_replication_local_address = '192.168.99.132:13308'
loose-group_replication_group_seeds = '192.168.99.131:13308,192.168.99.132:13308,192.168.99.133:13308'
loose-group_replication_bootstrap_group = OFF
loose-group_replication_flow_control_mode = 'DISABLED'
loose-group_replication_single_primary_mode = ON
loose-group_replication_ip_whitelist = '192.168.99.0/24'
transaction_isolation = READ-COMMITTED

# node3
[mysql80@mysql03 ~]$ cat my.cnf 
[mysqld]
basedir = '/opt/mysql80/8.0.35'
datadir = '/opt/mysql80/data'
port = 3308
socket = '/opt/mysql80/run/mysql.sock'
default-time_zone = '+8:00'
pid-file = '/opt/mysql80/run/mysqld.pid'
log_error_verbosity = 3
log-error = '/opt/mysql80/log/mysqld-err.log'
secure-file-priv = '/opt/mysql80/secure-file'
table_open_cache = 8192
skip_name_resolve = ON
slave_parallel_workers=4

server_id = 3
mysqlx = OFF
gtid-mode = on
enforce-gtid-consistency = true

report_host = '192.168.99.133'
sql_require_primary_key = 1
loose-plugin_load_add = 'mysql_clone.so'
loose-plugin_load_add = 'group_replication.so'
loose-group_replication_group_name = '59831f0c-ffd6-4e06-ab6d-e2fdb0e47b25' 
loose-group_replication_start_on_boot = 0 
loose-group_replication_local_address = '192.168.99.133:13308'
loose-group_replication_group_seeds = '192.168.99.131:13308,192.168.99.132:13308,192.168.99.133:13308'
loose-group_replication_bootstrap_group = OFF
loose-group_replication_flow_control_mode = 'DISABLED'
loose-group_replication_single_primary_mode = ON
loose-group_replication_ip_whitelist = '192.168.99.0/24'
transaction_isolation = READ-COMMITTED


# 安装复制插件（所有节点）配置文件中有了
# mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

# 设置复制账号 ( 所有节点 )  
$ mysql -S /opt/mysql80/run/mysql.sock -uroot -pmysql
SET SQL_LOG_BIN=0;  

drop user repl;
drop user repl@'localhost';
drop user repl@'127.0.0.1';

CREATE USER repl@'%' IDENTIFIED with mysql_native_password BY 'mysql';
CREATE USER repl@'localhost' IDENTIFIED with mysql_native_password BY 'mysql';
CREATE USER repl@'127.0.0.1' IDENTIFIED with mysql_native_password BY 'mysql';

GRANT REPLICATION SLAVE,replication client ON *.* TO repl@'%';
GRANT REPLICATION SLAVE,replication client on *.* to repl@'localhost';
GRANT REPLICATION SLAVE,replication client on *.* to repl@'127.0.0.1';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

# 启动 MGR ，在主库 (99.131) 上执行  
stop slave;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery';
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;

## 等所有节点加入进来后再设置为OFF
SET GLOBAL group_replication_bootstrap_group=OFF;

# 其他节点加入 MGR ，在从库上执行  
# 默认启动了slave
stop slave;

reset master;
CHANGE MASTER TO MASTER_USER='repl' , MASTER_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery';

# CHANGE MASTER TO master_host='192.168.99.131' , MASTER_PORT=3308 , MASTER_USER='repl', MASTER_PASSWORD='mysql';

START GROUP_REPLICATION;

三、参数说明​

1. `skip_name_resolve`: 这个参数在连接授权时禁用对主机名的反向解析，可以加快客户端连接的速度。
2. `master_info_repository=TABLE`: 表示用表存储主服务器复制信息。
3. `relay_log_info_repository=TABLE`: 表示用表存储中继日志信息。
4. `report_host=10.0.0.51`: 指定MySQL实例向复制集报告的主机名或IP地址。
5. `report_port=13306`: 指定MySQL实例向复制集报告的端口号。
6. `socket=/tmp/mysql13306.sock`: 指定MySQL实例的Unix套接字文件路径。
7. `default_authentication_plugin=mysql_native_password`: 指定默认的身份验证插件为`mysql_native_password`。
8. `binlog_checksum=NONE`: 指定binlog的校验和类型为NONE，表示不进行校验和。
9. `mysqlx=off`: 禁用MySQL X协议。


## group_replication 变量使用的 loose- 前缀是指示 Server 启用时尚未加载复制插件也将继续启动  
## 指示 Server 必须为每个事务收集写集合，并使用 XXHASH64 哈希算法将其编码为散列  [8.0.35默认值]
transaction_write_set_extraction = XXHASH64  
## 表示将加入或者创建的复制组命名为 01e5fb97-be64-41f7-bafd-3afc7a6ab555  
## 可自定义 ( 通过 cat /proc/sys/kernel/random/uuid)  
loose-group_replication_group_name='01e5fb97-be64-41f7-bafd-3afc7a6ab555'  
## 设置为 Server 启动时不自动启动组复制  
loose-group_replication_start_on_boot=off  
## 绑定本地的 192.168.29.128 及 33061 端口接受其他组成员的连接， IP 地址必须为其他组成员可正常访问  
loose-group_replication_local_address='192.168.29.128:33061'  
## 本行为告诉服务器当服务器加入组时，应当连接到  
## 这些种子服务器进行配置。本设置可以不是全部的组成员服务地址。  
loose-group_replication_group_seeds='192.168.29.128:33061,192.168.29.128:33062,192.168.29.128:33063'
## 配置是否自动引导组  
# 设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准，以后加入的就不需要设置 (只在其中一个节点上设置）
loose-group_replication_bootstrap_group = off  

## 配置白名单，默认情况下只允许 192.168.29.128 连接到复制组，如果是其他 IP 则需要配置。  
loose-group_replication_ip_whitelist=”10.30.0.0/16,10.31.0..0/16,10.27.0.0/16″

## binlog_transaction_dependency_tracking = WRITESET  
#  提高并发度， WRITESET 要求所有的表都要有主键。  - 8.0 的新技术 。

# 建表/alter表时要求必须表必须有主键
sql_require_primary_key=1

#当退出MGR后，把该实例设置为read_only，避免误操作写入数据
loose-group_replication_exit_state_action=READ_ONLY

#一般没什么必要开启流控机制
loose-group_replication_flow_control_mode = 'DISABLED'

#【强烈】建议只用单主模式，如果是实验目的，可以尝试玩玩多主模式
loose-group_replication_single_primary_mode=ON

四、自动化建库脚本​

1、删库脚本​

[mysql80@mysql01 ~]$ cat rm_mysqldb.sh 
#!/bin/bash

echo "Do you want to delete all the DB? (y/n)"
read response

echo "mysql" |sudo -S /bin/systemctl stop mysql80

if [ "$response" = "y" ]; then
    echo "Deleting the file..."
    rm -rf /opt/mysql80/data/*
    rm -rf /opt/mysql80/log/*
    rm -rf /opt/mysql80/run/*
    echo "mysql" |sudo -S rm -rf /opt/mysql80/secure-file/*
    echo "All File deleted."
else
    echo "File not deleted."
fi

2、建库初始化脚本​

初始主节点 mysql01​

[mysql80@mysql01 ~]$ cat  createdb.sh 
#!/bin/bash

echo "Do you want to delete all the DB? (y/n)"
read response

if [ "$response" = "y" ]; then
    echo "mysql" |sudo -S /bin/systemctl stop mysql80
    sleep 2
    
    echo "Deleting the file..."
    rm -rf /opt/mysql80/data/*
    rm -rf /opt/mysql80/log/*
    rm -rf /opt/mysql80/run/*
    echo "mysql" |sudo -S rm -rf /opt/mysql80/secure-file/*
    echo "All File deleted."

    mysqld --initialize --user=mysql80 --basedir /opt/mysql80/8.0.35 --datadir /opt/mysql80/data &> createdb.tmp
    sleep 1
    rootpwd=`tail -1 createdb.tmp |sed 's/.*root@localhost: //'`
    rootpwd="'$rootpwd'"
    echo "mysql" |sudo -S /bin/systemctl start mysql80
    sleep 2
    
    mysql1="mysql -S /opt/mysql80/run/mysql.sock -uroot -p${rootpwd}"
    mysqlsh="$mysql1 --connect-expired-password -e \"alter user root@'localhost' identified by 'mysql';\""
    eval $mysqlsh &> /dev/null
    
    echo "正在创建数据库"
    mysql -S /opt/mysql80/run/mysql.sock -uroot -pmysql --connect-expired-password < mgr.sql 
    echo "mysql" |sudo -S /bin/systemctl stop mysql80
    echo "mysql" |sudo -S /bin/systemctl start mysql80
    sleep 2
    mysql -S /opt/mysql80/run/mysql.sock -uroot -pmysql --connect-expired-password < mgr2.sql
else
    echo "File not deleted."
fi
[mysql80@mysql01 ~]$ cat mgr.sql 
CREATE DATABASE IF NOT EXISTS aaronqlm;
create user aaronqlm identified by 'aaronqlm';
grant all privileges on aaronqlm.* to aaronqlm;
use aaronqlm;

CREATE TABLE t1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10),
    mark VARCHAR(100)
);

commit;

DELIMITER //
CREATE PROCEDURE insert_t1_million_records()
BEGIN
    DECLARE counter INT DEFAULT 0;
    START TRANSACTION;
    WHILE counter < 1000000 DO
        INSERT INTO t1 (name, mark) VALUES (CONCAT('name', counter), CONCAT('mark', counter));
        SET counter = counter + 1;
    END WHILE;
    COMMIT;
END //
DELIMITER ;

commit;

call insert_t1_million_records();

commit;
[mysql80@mysql01 ~]$ cat mgr2.sql 
set SQL_LOG_BIN=0;

CREATE USER root  IDENTIFIED with mysql_native_password  BY 'mysql';
GRANT all privileges on *.* to root;

CREATE USER repl@'%'  IDENTIFIED with mysql_native_password  BY 'mysql';
CREATE USER repl@'localhost'  IDENTIFIED with mysql_native_password  BY 'mysql';
CREATE USER repl@'127.0.0.1'  IDENTIFIED with mysql_native_password  BY 'mysql';

GRANT REPLICATION SLAVE,replication client ON *.* TO repl@'%';
GRANT REPLICATION SLAVE,replication client on *.* to repl@'localhost';
GRANT REPLICATION SLAVE,replication client on *.* to repl@'127.0.0.1';
FLUSH PRIVILEGES;

commit;
SET SQL_LOG_BIN=1;

commit;

stop slave;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery';
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;
commit;

第二节点​

[mysql80@mysql02 ~]$ cat createdb.sh
#!/bin/bash

echo "Do you want to delete all the DB? (y/n)"
read response

if [ "$response" = "y" ]; then
    echo "mysql" |sudo -S /bin/systemctl stop mysql80
    sleep 2
    
    echo "Deleting the file..."
    rm -rf /opt/mysql80/data/*
    rm -rf /opt/mysql80/log/*
    rm -rf /opt/mysql80/run/*
    echo "mysql" |sudo -S rm -rf /opt/mysql80/secure-file/*
    echo "All File deleted."

    mysqld --initialize --user=mysql80 --basedir /opt/mysql80/8.0.35 --datadir /opt/mysql80/data &> createdb.tmp
    sleep 1
    rootpwd=`tail -1 createdb.tmp |sed 's/.*root@localhost: //'`
    rootpwd="'$rootpwd'"
    echo "mysql" |sudo -S /bin/systemctl start mysql80
    sleep 2
    
    mysql1="mysql -S /opt/mysql80/run/mysql.sock -uroot -p${rootpwd}"
    mysqlsh="$mysql1 --connect-expired-password -e \"alter user root@'localhost' identified by 'mysql';\""
    eval $mysqlsh &> /dev/null
    
    sleep 2
    mysql -S /opt/mysql80/run/mysql.sock -uroot -pmysql --connect-expired-password < mgr.sql
else
    echo "File not deleted."
fi
[mysql80@mysql02 ~]$ cat mgr.sql 
set SQL_LOG_BIN=0;
CREATE USER root  IDENTIFIED with mysql_native_password  BY 'mysql';
GRANT all privileges on *.* to root;

CREATE USER repl@'%'  IDENTIFIED with mysql_native_password  BY 'mysql';
CREATE USER repl@'localhost'  IDENTIFIED with mysql_native_password  BY 'mysql';
CREATE USER repl@'127.0.0.1'  IDENTIFIED with mysql_native_password  BY 'mysql';

GRANT REPLICATION SLAVE,replication client ON *.* TO repl@'%';
GRANT REPLICATION SLAVE,replication client on *.* to repl@'localhost';
GRANT REPLICATION SLAVE,replication client on *.* to repl@'127.0.0.1';
FLUSH PRIVILEGES;

commit;
SET SQL_LOG_BIN=1;

commit;

stop slave;
reset master;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;
commit;

第三节点​

[mysql80@mysql03 ~]$ cat createdb.sh
#!/bin/bash

echo "Do you want to delete all the DB? (y/n)"
read response

if [ "$response" = "y" ]; then
    echo "mysql" |sudo -S /bin/systemctl stop mysql80
    sleep 2
    
    echo "Deleting the file..."
    rm -rf /opt/mysql80/data/*
    rm -rf /opt/mysql80/log/*
    rm -rf /opt/mysql80/run/*
    echo "mysql" |sudo -S rm -rf /opt/mysql80/secure-file/*
    echo "All File deleted."

    mysqld --initialize --user=mysql80 --basedir /opt/mysql80/8.0.35 --datadir /opt/mysql80/data &> createdb.tmp
    sleep 1
    rootpwd=`tail -1 createdb.tmp |sed 's/.*root@localhost: //'`
    rootpwd="'$rootpwd'"
    echo "mysql" |sudo -S /bin/systemctl start mysql80
    sleep 2
    
    mysql1="mysql -S /opt/mysql80/run/mysql.sock -uroot -p${rootpwd}"
    mysqlsh="$mysql1 --connect-expired-password -e \"alter user root@'localhost' identified by 'mysql';\""
    eval $mysqlsh &> /dev/null
    
    sleep 2
    mysql -S /opt/mysql80/run/mysql.sock -uroot -pmysql --connect-expired-password < mgr.sql
else
    echo "File not deleted."
fi
[mysql80@mysql03 ~]$ cat mgr.sql 
set SQL_LOG_BIN=0;
CREATE USER root  IDENTIFIED with mysql_native_password  BY 'mysql';
GRANT all privileges on *.* to root;

CREATE USER repl@'%'  IDENTIFIED with mysql_native_password  BY 'mysql';
CREATE USER repl@'localhost'  IDENTIFIED with mysql_native_password  BY 'mysql';
CREATE USER repl@'127.0.0.1'  IDENTIFIED with mysql_native_password  BY 'mysql';

GRANT REPLICATION SLAVE,replication client ON *.* TO repl@'%';
GRANT REPLICATION SLAVE,replication client on *.* to repl@'localhost';
GRANT REPLICATION SLAVE,replication client on *.* to repl@'127.0.0.1';
FLUSH PRIVILEGES;

commit;
SET SQL_LOG_BIN=1;

commit;

stop slave;
reset master;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;
commit;

五、关/开机流程​

stop group_replication;
start group_replication;

六、参考语句​

SELECT * FROM performance_schema.replication_group_members;

select * from performance_schema.replication_connection_status;