8.0MGR单主模式搭建_克隆(clone)插件方式

为了应对事务一致性要求很高的系统对高可用数据库系统的要求，并且增强高可用集群的自管理能力，避免节点故障后的failover需要人工干预或其它辅助工具干预，MySQL5.7新引入了Group Replication，用于搭建更高事务一致性的高可用数据库集群系统。MGR是基于Paxos协议的Group Replication搭建的系统，不仅可以自动进行failover，而且同时保证系统中多个节点之间的事务一致性，避免因节点故障或网络问题而导致的节点间事务不一致。此外还提供了节点管理的能力，真正将整个集群做为一个整体对外提供服务。

MGR是基于原生复制及paxos协议的组复制技术，并以插件的方式提供，可以采取多主模式和单主模式。

在单主模式下，会自动选主，只有一个节点可以对外提供写/读事务的服务，而其它所有节点只能提供只读事务的服务，是官方推荐的Group Replication复制模式。（目前最多支持9个节点）

在多主模式下，每个节点都可以对外提供读写事务的服务。但在多主模式下，多个节点间的事务可能有比较大的冲突，从而影响性能，并且对查询语句也有更多的限制。

MySQL 8.0.17的克隆clone简介

MySQL 8.0.17的克隆插件允许在本地或从远程 MySQL 实例在线克隆数据，从此搭建从库可以不再需要备份工具（PXB或mysqldump）来实现了。克隆数据是存储在 InnoDB 其中的数据的物理快照，其中包括库、表、表空间和数据字典元数据。克隆的数据包含一个功能齐全的数据目录，允许使用克隆插件进行 MySQL 服务器配置。

克隆插件支持两种克隆方式

本地克隆

本地克隆：本地克隆操作将启动克隆操作的 MySQL 服务器实例中的数据克隆到同服务器或同节点上的一个目录里。

远程克隆

远程克隆：默认情况下，远程克隆操作会删除接受者(recipient)数据目录中的数据，并将其替换为捐赠者(donor)的克隆数据。（可选）您也可以将数据克隆到接受者的其他目录，以避免删除现有数据。

远程克隆操作和本地克隆操作克隆的数据没有区别，数据是相同的。克隆插件支持复制。除克隆数据外，克隆操作还从捐赠者中提取并传输复制位置信息，并将其应用于接受者，从而可以使用克隆插件来配置组复制或主从复制。使用克隆插件进行配置比复制大量事务要快得多，效率更高。

MySQL 8.0 clone插件提供从一个实例克隆数据的功能，克隆功能提供了更有效的方式来快速创建MySQL实例，搭建主从复制和组复制。本文介绍使用 MySQL 8.0 clone 插件快速添加组复制（MGR）节点的方法。

限制条件

• 版本大于等于8.0.17且不支持跨版本。要求相同版本号，您无法MySQL 5.7和MySQL 8.0之间进行克隆，在8.0.19和8.0.20之间也不可以，而且要求版本>=8.0.17。
• 克隆操作期间不允许使用 DDL，允许并发DML。
• 两台机器具有相同的操作系统OS。同一平台同一架构，例如linux to windows、x64 to x32 是不支持。
• 两台MySQL实例具体相同的 innodb_page_size 和 innodb_data_file_path(ibdata文件名)
• 同一时刻仅仅允许有一个克隆任务存在
• recipient 需要设置变量clone_valid_donor_list
• max_allowed_packet 大于2M
• doner的undo表空间文件名称不能重复
• 不会克隆my.cnf文件
• 不会克隆binlog二进制日志。
• 仅仅支持innodb引擎。不克隆其他存储引擎数据。MyISAM并且 CSV存储在包括sys模式的任何模式中的表都被克隆为空表。
• 捐赠者和接受者都需要安装克隆插件
• 捐赠者和接受者分别需要有至少BACKUP_ADMIN/CLONE_ADMIN权限的账号
• 不支持通过MySQL router连接到捐赠者实例。
• 默认情况下，克隆数据后会自动重新启动接受者 MySQL 实例。要自动重新启动，必须在接收方上提供监视进程以检测服务器是否已关闭。否则，在克隆数据后，克隆操作将停止并出现以下错误，并且关闭接受者 MySQL 服务器实例。此错误不表示克隆失败。这意味着必须在克隆数据后手动重新启动接受者的 MySQL 实例。

ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).

官网地址：https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html
 

服务器规划

ip	主机名	Server ID	实例端口	MGR通讯端口	备注
192.168.40.152	MGR01	1521	3306 3306 3306	33061	MGR主
192.168.40.153	MGR02	1531			MGR备
192.168.40.154	MGR03	1541			MGR备
MySQL版本：mysql-8.0.36-linux-glibc2.17-x86_64.tar.xz 操作系统：CentOS Linux release 7.9.2009

安装MySQL

3台主机分别使用脚本一键式安装，注意更改脚本中的package和主机名等相关参数。

cat mysql8_install.sh
#!/bin/bash
#路径端口防火墙策略视情况更改
#2.内核参数视情况修改
#15.back_db备份库视情况修改

dir=$(pwd)
package=mysql-8.0.36-linux-glibc2.17-x86_64.tar.xz


echo  "1.system parameter configure"

echo  "1.0.configure hostname"
hostnamectl set-hostname MGR02

echo  "1.1.adjust system parameter"
optimizeSystemConf(){
conf_exist=$(cat /etc/sysctl.conf|grep mysql|wc -l)
if [ $conf_exist -eq 0 ]; then
    echo "optimize system core conf"
	cat >> /etc/sysctl.conf <<EOF
#add by mysql
#/proc/sys/kernel/优化
# 10000 connect remain:
kernel.sem = 250 162500 250 650	 

#notice: shall shmmax is base on 16GB, you may adjust it for your MEM
#TODO: open blow two paramenter may make error like this: can not fork xxxx, just reboot your computer ~

for 2GB Mem:
kernel.shmall = 419430								
kernel.shmmax = 171796918     

#for 4GB Mem:
#kernel.shmall = 838860								
#kernel.shmmax = 3435973836  

#for 8GB Mem:
#kernel.shmall = 1677721								
#kernel.shmmax = 6871947673  

#for 16GB Mem:
#kernel.shmall = 3774873								
#kernel.shmmax = 8589934592 

#for 32GB Mem:
#kernel.shmall = 7549747
#kernel.shmmax = 17179869184
#for 64GB Mem:
#kernel.shmall = 15099494
#kernel.shmmax = 34359738368
#for 128GB Mem:
#kernel.shmall = 30198988
#kernel.shmmax = 68719476736
#for 256GB Mem:
#kernel.shmall = 60397977
#kernel.shmmax = 137438953472
#for 512GB Mem:
#kernel.shmall = 120795955
#kernel.shmmax = 274877906944

kernel.shmmni = 4096		

vm.dirty_background_ratio=2 
vm.dirty_ratio = 40			

vm.overcommit_memory = 2	
vm.overcommit_ratio = 90 	

vm.swappiness = 0 				

fs.aio-max-nr = 1048576		
fs.file-max = 6815744		
fs.nr_open = 20480000       

# TCP端口使用范围
net.ipv4.ip_local_port_range = 10000 65000
net.ipv4.tcp_keepalive_time = 1200
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_max_syn_backlog = 8192
net.ipv4.tcp_max_tw_buckets = 6000
# 记录的那些尚未收到客户端确认信息的连接请求的最大值
net.ipv4.tcp_max_syn_backlog = 65536
# 每个网络接口接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目
net.core.somaxconn=1024
net.core.netdev_max_backlog = 32768
net.core.wmem_default = 8388608
net.core.wmem_max = 1048576
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_syn_retries = 2
net.ipv4.route.gc_timeout = 100
net.ipv4.tcp_wmem = 8192 436600 873200
net.ipv4.tcp_rmem  = 32768 436600 873200
net.ipv4.tcp_mem = 94500000 91500000 92700000
net.ipv4.tcp_max_orphans = 3276800
EOF
else
   echo "system configuration is already optimized, so we do nothing"
fi
}
optimizeSystemConf

echo  "1.2.Optimize Limit"
optimizeLimitConf(){
conf_exist=$(cat /etc/security/limits.conf|grep mysql|wc -l)
if [ $conf_exist -eq 0 ]; then
	echo "optimize limit configuration"
	cat >> /etc/security/limits.conf << "EOF"
#add by mysql
* soft  nproc   65536
* hard  nproc   65536
* soft  nofile  65536
* hard  nofile  65536
* soft  stack   10240
* hard  stack   32768
* soft core unlimited
* hard core unlimited
EOF
else
	echo "limit is already optimized, so we do nothing"
fi
}
optimizeLimitConf

echo  "1.3.firewall config"
function conf_firewall() {
##################gt>0
if [ $(systemctl status firewalld.service | grep -c running) -gt 0 ]; then  
     #systemctl stop firewalld.service
     #systemctl disable firewalld.service 
     firewall-cmd --zone=public --add-port=3306/tcp --permanent
     firewall-cmd --zone=public --add-port=22/tcp --permanent
     firewall-cmd --reload
     #禁用防火墙区域偏移
     sed -i 's/^AllowZoneDrifting=yes/AllowZoneDrifting=no/' /etc/firewalld/firewalld.conf 
   else
   echo "firewall not open"
fi
}
conf_firewall

echo  "1.4.adjust optimize selinux"
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
setenforce 0 

echo  "1.5关闭 numa和禁用透明大页"
sed -i "s/quiet/quiet numa=off transparent_hugepage=never/g"  /etc/default/grub 
grub2-mkconfig -o /etc/grub2.cfg


#echo  "1.6.os iso mount"
#mount $dir/*.iso /mnt/
#cat << EOF >> /etc/fstab
#/dev/sr0    /mnt        iso9660 loop            0 0
#EOF
#
#mkdir -p /etc/yum.repos.d/bak
#mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/bak
#cat >> /etc/yum.repos.d/os.repo <<"EOF"
#[OS1]
#name=OS
#baseurl=file:///mnt
#enabled=1
#gpgcheck=0
#EOF


echo  "2. variable list"
FILE_CONF=/topsoft/mysqldb/my.cnf
DATADIR=/topsoft/mysqldb/data
BASEDIR=/topsoft/mysqldb/mysql
SCRIPTS_DIR=/topsoft/mysqldb/scripts
LOGPATH=/topsoft/mysqldb/data/log

echo  "3. mysql exits"
id $mysql >& /dev/null
if [ $? -ne 0 ]
then
        echo "mysql already exits"
else 
        echo "mysql not exits，please create"
        groupadd mysql
        useradd -r -g mysql -s /bin/false mysql
fi

echo  "4.create directory"
if [ ! -d /topsoft/mysqldb ]
then
        cd /topsoft
	    mkdir -p mysqldb/{data,tmp,log,binlog,relaylog,mysql-files,backup,scripts}
else 	
        echo "/topsoft/mysqldb directory exits，please exit"	
        exit 1
fi

echo "5. unzip"
cd $dir
tar -xvf $package -C /topsoft/mysqldb
echo "mysql upzip success"
echo "directory rights"
mv /topsoft/mysqldb/mysql*x86_64 /topsoft/mysqldb/mysql
chown -R mysql:mysql /topsoft/mysqldb

#-------------------------------install mysql------------------------------------
echo "7. install dependency package"
#强制关掉yum进程
rm -f /var/run/yum.pid 
#yum install -y vim ncurses-devel libaio-devel gcc gcc-c++ cmake autoconf net-tools perl lib

echo "9. editor my.cnf"
cat > /topsoft/mysqldb/my.cnf  << "EOF"
[client]
port = 3306
socket = /topsoft/mysqldb/data/mysql.sock
default-character-set = utf8mb4

[mysql]
# 设置mysql客户端默认字符集
default-character-set = utf8mb4
socket = /topsoft/mysqldb/data/mysql.sock
prompt="\\u@\\h :\\d \\R:\\m:\\s>" #设置命令行提示符

[mysqld]
#操作用户#
user=mysql

#目录#
basedir=/topsoft/mysqldb/mysql  #mysql安装根目录
datadir=/topsoft/mysqldb/data #mysql数据文件所在目录
socket = /topsoft/mysqldb/data/mysql.sock

#字符集#
character-set-server = utf8mb4 #数据库默认字符集,注意不要再用utf8了
collation-server = utf8mb4_general_ci #数据库字符集对应一些排序规则,要属于character-set-server对应值的集合内
init_connect='SET NAMES utf8mb4' #设置client连接mysql时的字符集,防止乱码

#运行实例相关#
server_id = 103 #Mysql服务实例的唯一编号 每个mysql服务实例Id需唯一 可设置成ip最后一位
port = 3306 #服务端口号 默认3306
pid_file=/topsoft/mysqldb/data/mysqld.pid #pid文件的路径
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

#二进制日志#
log_bin = /topsoft/mysqldb/binlog/mybinlog  #二进制日志文件
binlog_format = ROW
binlog_expire_logs_seconds=604800 #mysql binlog日志文件保存的过期时间7天，过期后自动删除;默认值是0，不限制,这样会占用空间太多 单位秒
max_binlog_size = 1G #限制单个文件大小,默认大小:1,073,741,824,即1G,太大了

#慢查询日志#
log_queries_not_using_indexes = 1 #把未使用到索引的sql记录到慢查询日志
slow_query_log = 1 #是否打开慢查询sql日志
slow_query_log_file = /topsoft/mysqldb/log/mysql-slow.log #慢查询sql日志的文件地址
long_query_time = 1 #慢查询执行的秒数，超过这个值则会被记录到慢查询日志

#导出文件到指定目录
secure_file_priv=/topsoft/mysqldb/mysql-files

#增加临时表空间大小限制参数
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:1G

#时区#
default_time_zone="+8:00" #设置默认服务器时区
log_timestamps = system #解决日志中时间和本地差8小时

#认证策略解决登录ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded#
default_authentication_plugin = mysql_native_password 

#错误日志#
log_error=/topsoft/mysqldb/log/mysqld_error.log 

#性能参数#
open_files_limit = 65535
back_log=600 #连接数达到max_connections时，新来的请求将会被存在堆栈中。数量超过back_log，将不被授予连接资源
max_connections = 1000 #最大并发连接数，过小会影响连接的数量，报Too many connections错误，过大会导致服务资源用完无响应，最大值不能超过100000
max_user_connections=1000 #指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。
table_open_cache = 1024 #能同时打开表的个数
table_definition_cache = 1024
thread_stack = 512K
thread_cache_size = 1500
sort_buffer_size = 12M   #只是在需要的时候才分配，并且在那些操作做完之后就释放了
join_buffer_size = 12M
read_buffer_size = 24M  #读入缓冲区大小,对表进行顺序扫描时将分配1个读入缓冲区。对表的顺序扫描请求非常频繁，并且频繁扫描进行得太慢可增加。只是在需要的时候才分配，并且在操作结束后就释放
read_rnd_buffer_size = 8M  #随机读缓冲区大小,当按任意顺序读取行时(如:排序)，将分配一个随机读缓存区。只是在需要的时候才分配，并且在那些操作做完之后就释放
bulk_insert_buffer_size = 4M
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 48M  #heap(堆积)表缓冲大小,提高联接查询速度。只是在需要的时候才分配，并且在那些操作做完之后就释放了
max_heap_table_size = 32M
binlog_cache_size = 12M
max_binlog_cache_size = 50M
key_buffer_size=256M  #索引缓冲区大小。内存在4GB左右的服务器该参数可设置为256M或384M

#库表名不区分大小写#
lower_case_table_names = 1

#数据安全#
innodb_flush_log_at_trx_commit = 2 #每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，但并不会立即刷写到磁盘
innodb_log_buffer_size=64M  #将日志写入日志磁盘文件前的缓冲大小
innodb_log_file_size = 256M #InnoDB redo log大小

#最大允许的包#
max_allowed_packet = 48M

#超时#
interactive_timeout = 1800 #MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭  MySQL默认的wait_timeout 值为8个小时,
wait_timeout = 1800  #interactive_timeout参数需要同时配置才能生效

#禁用域名的解析#
skip_name_resolve = 1 #dns慢的情况下会影响性能，禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间

#禁用符号链接以防止各种安全风险
skip_symbolic_links=yes

#innodb是否为每个表使用独立的表空间文件#
innodb_file_per_table = 1 #开启该参数的时候，Innodb将每个新创建的表的数据及索引存储在一个独立的.ibd文件里，而不是系统的表空间。

#innodb缓冲池的大小设置#
#说明缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数，否则mysql会自动调整为相应的倍数#
innodb_buffer_pool_chunk_size= 256M   定义了buffer中每个chunk的大小  chunk 块
innodb_buffer_pool_size = 16G  #物理服务器内存的50%~75%  缓存 InnoDB存储引擎的表中的数据和索引数据，提高查询访问速度

#抑制即不显示 [Warning] [MY-013712] [Server] No suitable 'keyring_component_metadata_query' service implementation found to fulfill the request.
log_error_suppression_list='MY-013712'
EOF
mv /etc/my.cnf /tmp/my.cnf
ln -s /topsoft/mysqldb/my.cnf /etc/my.cnf

echo "10. add path to profile --> PASS"
echo 'LANG=en_US.UTF-8' >> /etc/profile
echo 'export PATH=$PATH:/topsoft/mysqldb/mysql/bin' >> /etc/profile
echo 'export MYSQL_DATA=/topsoft/mysqldb/data' >> /etc/profile
echo 'export MYSQL_HOME=/topsoft/mysqldb/mysql' >> /etc/profile
source /etc/profile


echo "11. directory privileges"
chown -R mysql:mysql /topsoft/mysqldb
chmod -R 755 /topsoft/mysqldb

echo  "12. start initialize mysql..."
#--basedir 安装目录
#--datadir 数据目录
/topsoft/mysqldb/mysql/bin/mysqld --initialize  --user=mysql  --basedir=/topsoft/mysqldb/mysql  --datadir=/topsoft/mysqldb/data --console

echo "13. auto system start --> PASS"
cat > /usr/lib/systemd/system/mysqld.service << "EOF"
[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=mysql
Group=mysql
ExecStart=/topsoft/mysqldb/mysql/bin/mysqld --defaults-file=/topsoft/mysqldb/my.cnf
LimitNOFILE=65536  
LimitNPROC=65536
EOF

chmod +x /usr/lib/systemd/system/mysqld.service

systemctl daemon-reload 
systemctl enable mysqld 
systemctl start mysqld 
systemctl status mysqld 
if [ $? -eq 0 ];then
    echo "start success"
else
    echo "start fail"
fi
sleep 10
if [ -f /topsoft/mysqldb/data/mysql.sock ] 
then 
     echo "mysql.sock exist";
	 ln -s /topsoft/mysqldb/data/mysql.sock /tmp/mysql.sock
else
     echo "The file doesn't exist"
fi	 

echo "14. change mysql root password and root remote visit--> PASS"
passwd=$(grep password /topsoft/mysqldb/log/mysqld_error.log | head -1 | awk 'END {print $NF}')
echo "mysql" > /topsoft/mysqldb/.pass

cat > /topsoft/mysqldb/change.sql  << "EOF"
use mysql;
FLUSH PRIVILEGES;
alter user 'root'@'localhost' identified with mysql_native_password by 'mysql';
alter user 'root'@'localhost'  password expire never;
FLUSH PRIVILEGES;
create user 'root'@'%' identified with mysql_native_password by 'mysql';
alter user 'root'@'%'  password expire never;
FLUSH PRIVILEGES;
grant all privileges on *.*  to   'root'@'%' with grant option;
FLUSH PRIVILEGES;
--create user 'root'@'192.168.16.%' identified with mysql_native_password by 'mysql';
--grant all privileges on *.* to 'root'@'192.168.16.%'   with grant option;
--alter user 'root'@'192.168.16.%'  password expire never;
--FLUSH PRIVILEGES;
EOF

mysql -uroot -p"$passwd"  -e "source /topsoft/mysqldb/change.sql;"
#创建业务库 智能分析平台
#mysql -uroot -p"${mysql_passwd}" -e "create database operational_platform default charset utf8mb4  collate utf8mb4_general_ci;" 
#mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"
#创建普通用户并授权远程访问
#mysql -uroot -p"${mysql_passwd}" -e "create user 'znfxpt'@'%' identified by 'mysql';"
#mysql -uroot -p"${mysql_passwd}" -e "grant all privileges on znfxpt_test.* to 'znfxpt'@'%' with grant option;"
#mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"
#修改密码
#mysql -uroot -p"${mysql_passwd}" -e "alter user 'znfxpt'@'%' identified with mysql_native_password by 'mysql';"
#mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"
#创建只读用户
#mysql -uroot -p"${mysql_passwd}" -e "create user 'query_user'@'%' identified by 'mysql';"
#mysql -uroot -p"${mysql_passwd}" -e "grant select on 'znfxpt'.* to query_user@'%';
#mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"

echo "18.数据库信息"
echo "数据库信息:mysql;密码：mysql;port:3306"

修改MySQL参数

group_name的值不能设置为每个节点的uuid,无论有多少个节点（目前mgr最多支持9个），uuid都必须一致。

增加到[mysqld]模块下面：
-- 节点1
cp /etc/my.cnf /etc/my.cnf_bak_`date +%F`
cat >> /etc/my.cnf << "EOF"
#add  Replication configuration parameters
server_id = 1521
binlog_checksum=NONE
log_slave_updates = 1
gtid_mode=ON
enforce_gtid_consistency=on
log_slave_updates=ON

master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=mgr152-relay-bin


transaction_write_set_extraction=XXHASH64
plugin_load_add='group_replication.so'
group_replication_group_name="34975c79-405c-11eb-9f4c-5254044caef1"
group_replication_start_on_boot=OFF
group_replication_local_address= "192.168.40.152:33061"
group_replication_group_seeds= "192.168.40.152:33061,192.168.40.153:33061,192.168.40.154:33061"
group_replication_bootstrap_group=OFF
loose-group_replication_recovery_retry_count=31536000
loose-group_replication_single_primary_mode=on
loose-group_replication_enforce_update_everywhere_checks=off
loose-group_replication_ip_whitelist="192.168.40.152,192.168.40.153,192.168.40.154"
report_host=192.168.40.152
report_port=3306
EOF

-- 节点2
cp /etc/my.cnf /etc/my.cnf_bak_`date +%F`
cat >> /etc/my.cnf << "EOF"
#add  Replication configuration parameters
server_id = 1531
binlog_checksum=NONE
log_slave_updates = on
gtid_mode=ON
enforce_gtid_consistency=on

master_info_repository=TABLE
relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64
plugin_load_add='group_replication.so'
group_replication_group_name="34975c79-405c-11eb-9f4c-5254044caef1"
group_replication_start_on_boot=OFF
group_replication_local_address= "192.168.40.153:33061"
group_replication_group_seeds= "192.168.40.152:33061,192.168.40.153:33061,192.168.40.154:33061"
group_replication_bootstrap_group=OFF
loose-group_replication_recovery_retry_count=31536000
loose-group_replication_single_primary_mode=on
loose-group_replication_enforce_update_everywhere_checks=off
loose-group_replication_ip_whitelist="192.168.40.152,192.168.40.153,192.168.40.154"

report_host=192.168.40.153
report_port=3306
EOF

-- 节点3
cp /etc/my.cnf /etc/my.cnf_bak_`date +%F`
cat >> /etc/my.cnf << "EOF"
#add  Replication configuration parameters
server_id = 1541
binlog_checksum=NONE
log_slave_updates = on
gtid_mode=ON
enforce_gtid_consistency=on

master_info_repository=TABLE
relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64

plugin_load_add='group_replication.so'
group_replication_group_name="34975c79-405c-11eb-9f4c-5254044caef1"
group_replication_start_on_boot=OFF
group_replication_local_address= "192.168.40.154:33061"
group_replication_group_seeds= "192.168.40.152:33061,192.168.40.153:33061,192.168.40.154:33061"
group_replication_bootstrap_group=OFF
loose-group_replication_recovery_retry_count=31536000
loose-group_replication_single_primary_mode=on
loose-group_replication_enforce_update_everywhere_checks=off
loose-group_replication_ip_whitelist="192.168.40.152,192.168.40.153,192.168.40.154"

report_host=192.168.40.154
report_port=3306
EOF

重启MySQL

3台主机分别重启MySQL服务

-- 重启MySQL
systemctl restart mysqld
systemctl status mysqld

-- 进入MySQL
mysql -uroot -pmysql

-- 远程连接MySQL
mysql -uroot -pmysql -h192.168.40.152 -P3306 
mysql -uroot -pmysql -h192.168.40.153 -P3306 
mysql -uroot -pmysql -h192.168.40.154 -P3306

-- 查看MySQL日志
tail -100f /topsoft/mysqldb/log/mysqld_error.log
tail -100f /topsoft/mysqldb/log/mysqld_error.log
tail -100f /topsoft/mysqldb/log/mysqld_error.log

-- 查看MySQL的主机名、server_id和server_uuid
mysql -uroot -pmysql -h192.168.40.152 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -pmysql -h192.168.40.153 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -pmysql -h192.168.40.154 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"

输出结果如下：

--节点1
[root@mysqldb01 log]# mysql -uroot -pmysql -h192.168.40.152 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| mgr01      |        1521 | 7fb72760-011c-11ef-b7aa-000c29d414b6 |
+------------+-------------+--------------------------------------+

--节点2
[root@localhost opt]# mysql -uroot -pmysql -h192.168.40.153 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| mgr02      |        1531 | 109d5359-0121-11ef-8acf-000c2922d68f |
+------------+-------------+--------------------------------------+

--节点3
[root@localhost opt]# mysql -uroot -pmysql -h192.168.40.154 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| mgr03      |        1541 | 25c048b0-0121-11ef-9874-000c294fdfdd |
+------------+-------------+--------------------------------------+

安装MGR插件

所有节点分别安装MGR插件。

mysql -uroot -pmysql -h192.168.40.152 -P3306

--安装组复制插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- 如果MySQL版本大于8.0.17，那么建议再安装clone插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

--查看插件是否安装成功
show plugins;

--输出结果如下：
+----------------------------------+----------+--------------------+----------------------+---------+
| Name                             | Status   | Type               | Library              | License |
+----------------------------------+----------+--------------------+----------------------+---------+
| group_replication                | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
| clone                            | ACTIVE   | CLONE              | mysql_clone.so       | GPL     |

设置复制账号

-- 在主库(192.168.40.152)上执行
CREATE USER repl@'%' IDENTIFIED WITH mysql_native_password BY 'repl';
GRANT REPLICATION SLAVE,BACKUP_ADMIN ON *.* TO repl@'%';
FLUSH PRIVILEGES;

--扩展
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl';
ALTER USER 'repl'@'localhost' IDENTIFIED WITH mysql_native_password BY 'repl';
ALTER USER 'repl'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'repl';

-- 所有节点执行
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

启动MGR(主库192.168.40.152上执行)

主库192.168.40.152上执行

-- 在主库(192.168.40.152)上执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

-- 查看MGR组信息 
SELECT * FROM performance_schema.replication_group_members;

结果输出如下：

root@localhost :(none) 13:33:32>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 | 7fb72760-011c-11ef-b7aa-000c29d414b6 | 192.168.40.152 |        3306 | ONLINE       | PRIMARY     | 8.0.36         | XCom                       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+

其他节点加入MGR

在从库（192.168.40.153，192.168.40.154）上执行

--从节点加入MGR
reset master;

--加入组复制
START GROUP_REPLICATION;

-- 查看MGR集群组状态
SELECT * FROM performance_schema.replication_group_members;

--查看复制进度
select
stage,
state,
cast(begin_time as DATETIME) as "START TIME",
cast(end_time as DATETIME) as "FINISH TIME",
lpad(sys.format_time(power(10,12) * (unix_timestamp(end_time) - unix_timestamp(begin_time))), 10, ' ') as DURATION,
lpad(concat(format(round(estimate/1024/1024,0), 0), "MB"), 16, ' ') as "Estimate",
case when begin_time is NULL then LPAD('%0', 7, ' ')
when estimate > 0 then
lpad(concat(round(data*100/estimate, 0), "%"), 7, ' ')
when end_time is NULL then lpad('0%', 7, ' ')
else lpad('100%', 7, ' ')
end as "Done(%)"
from performance_schema.clone_progress;

结果输入如下：

root@localhost :(none) 13:46:54>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 | 109d5359-0121-11ef-8acf-000c2922d68f | 192.168.40.153 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
| group_replication_applier | 25c048b0-0121-11ef-9874-000c294fdfdd | 192.168.40.154 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
| group_replication_applier | 7fb72760-011c-11ef-b7aa-000c29d414b6 | 192.168.40.152 |        3306 | ONLINE       | PRIMARY     | 8.0.36         | XCom                       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.01 sec)

测试数据同步

在主节点上执行以下命令，然后在其它节点查询：

--主节点上构建测试数据
create database dxj;
CREATE TABLE dxj.`tb1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `hostname` varchar(100) DEFAULT NULL,
 `server_id` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


insert into dxj.tb1(hostname,server_id) select @@hostname,@@server_id;
select * from dxj.tb1;

--主节点上查询测试数据
root@localhost :(none) 13:49:47>select * from dxj.tb1;
+----+----------+-----------+
| id | hostname | server_id |
+----+----------+-----------+
|  1 | mgr01    | 1521      |
+----+----------+-----------+
1 row in set (0.00 sec)

--从节点查询
root@localhost :(none) 13:49:47>select * from dxj.tb1;
+----+----------+-----------+
| id | hostname | server_id |
+----+----------+-----------+
|  1 | mgr01    | 1521      |
+----+----------+-----------+
1 row in set (0.00 sec)

主备自动切换测试

关闭主库

root@localhost :(none) 13:49:48>shutdown ;
Query OK, 0 rows affected (0.01 sec)

登陆原从库1查询

结果：原从库1成为新主库

root@localhost :mysql 13:50:00>select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 0e67c440-0136-11ef-b0cd-000c2922d68f |
+--------------------------------------+
1 row in set (0.00 sec)

--查看MGR集群组状态
root@localhost :mysql 13:55:15>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 | 109d5359-0121-11ef-8acf-000c2922d68f | 192.168.40.153 |        3306 | ONLINE       | PRIMARY     | 8.0.36         | XCom                       |
| group_replication_applier | 25c048b0-0121-11ef-9874-000c294fdfdd | 192.168.40.154 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+

登陆原从库2查询

结果：原从库2自动升级为新主库的从库

root@localhost :(none) 13:50:06>select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 554d671c-0136-11ef-a8d8-000c294fdfdd |
+--------------------------------------+
1 row in set (0.03 sec)

--查看MGR集群组状态
root@localhost :(none) 13:57:14>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 | 109d5359-0121-11ef-8acf-000c2922d68f | 192.168.40.153 |        3306 | ONLINE       | PRIMARY     | 8.0.36         | XCom                       |
| group_replication_applier | 25c048b0-0121-11ef-9874-000c294fdfdd | 192.168.40.154 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)

原主库修复后拉起，并加入group replication组

--原主库启库
[root@mysqldb01 log]# systemctl start mysqld

--加入group replication组
root@not_connected :(none) 14:00:19>CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
No connection. Trying to reconnect...
Connection id:    10
Current database: *** NONE ***

Query OK, 0 rows affected, 5 warnings (0.01 sec)

--开始同步
root@localhost :(none) 14:00:41>START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.91 sec)

--查看MGR集群组状态
root@localhost :(none) 14:01:00>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 | 109d5359-0121-11ef-8acf-000c2922d68f | 192.168.40.153 |        3306 | ONLINE       | PRIMARY     | 8.0.36         | XCom                       |
| group_replication_applier | 25c048b0-0121-11ef-9874-000c294fdfdd | 192.168.40.154 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
| group_replication_applier | 7fb72760-011c-11ef-b7aa-000c29d414b6 | 192.168.40.152 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.02 sec)

root@localhost :(none) 14:01:09>select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| e8855fef-0136-11ef-8632-000c29d414b6 |
+--------------------------------------+
1 row in set (0.02 sec)

在新主节点上插入新数据

--在新主节点上插入新数据
insert into dxj.tb1(hostname,server_id) select @@hostname,@@server_id;

--在旧主即新从节点上查看
root@localhost :(none) 14:04:52>select * from dxj.tb1;
+----+----------+-----------+
| id | hostname | server_id |
+----+----------+-----------+
|  1 | mgr01    | 1521      |
|  2 | mgr02    | 1531      |
+----+----------+-----------+
2 rows in set (0.01 sec)

结果：数据与主库一致，MGR故障恢复测试成功。

在现有的3节点中再加入1从节点

新从节点信息：192.168.40.155

安装MySQL

使用脚本一键式安装，注意更改脚本中的package和主机名等相关参数。

cat mysql8_install.sh
#!/bin/bash
#路径端口防火墙策略视情况更改
#2.内核参数视情况修改
#15.back_db备份库视情况修改

dir=$(pwd)
package=mysql-8.0.36-linux-glibc2.17-x86_64.tar.xz


echo  "1.system parameter configure"

echo  "1.0.configure hostname"
hostnamectl set-hostname MGR02

echo  "1.1.adjust system parameter"
optimizeSystemConf(){
conf_exist=$(cat /etc/sysctl.conf|grep mysql|wc -l)
if [ $conf_exist -eq 0 ]; then
    echo "optimize system core conf"
	cat >> /etc/sysctl.conf <<EOF
#add by mysql
#/proc/sys/kernel/优化
# 10000 connect remain:
kernel.sem = 250 162500 250 650	 

#notice: shall shmmax is base on 16GB, you may adjust it for your MEM
#TODO: open blow two paramenter may make error like this: can not fork xxxx, just reboot your computer ~

for 2GB Mem:
kernel.shmall = 419430								
kernel.shmmax = 171796918     

#for 4GB Mem:
#kernel.shmall = 838860								
#kernel.shmmax = 3435973836  

#for 8GB Mem:
#kernel.shmall = 1677721								
#kernel.shmmax = 6871947673  

#for 16GB Mem:
#kernel.shmall = 3774873								
#kernel.shmmax = 8589934592 

#for 32GB Mem:
#kernel.shmall = 7549747
#kernel.shmmax = 17179869184
#for 64GB Mem:
#kernel.shmall = 15099494
#kernel.shmmax = 34359738368
#for 128GB Mem:
#kernel.shmall = 30198988
#kernel.shmmax = 68719476736
#for 256GB Mem:
#kernel.shmall = 60397977
#kernel.shmmax = 137438953472
#for 512GB Mem:
#kernel.shmall = 120795955
#kernel.shmmax = 274877906944

kernel.shmmni = 4096		

vm.dirty_background_ratio=2 
vm.dirty_ratio = 40			

vm.overcommit_memory = 2	
vm.overcommit_ratio = 90 	

vm.swappiness = 0 				

fs.aio-max-nr = 1048576		
fs.file-max = 6815744		
fs.nr_open = 20480000       

# TCP端口使用范围
net.ipv4.ip_local_port_range = 10000 65000
net.ipv4.tcp_keepalive_time = 1200
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_max_syn_backlog = 8192
net.ipv4.tcp_max_tw_buckets = 6000
# 记录的那些尚未收到客户端确认信息的连接请求的最大值
net.ipv4.tcp_max_syn_backlog = 65536
# 每个网络接口接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目
net.core.somaxconn=1024
net.core.netdev_max_backlog = 32768
net.core.wmem_default = 8388608
net.core.wmem_max = 1048576
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_syn_retries = 2
net.ipv4.route.gc_timeout = 100
net.ipv4.tcp_wmem = 8192 436600 873200
net.ipv4.tcp_rmem  = 32768 436600 873200
net.ipv4.tcp_mem = 94500000 91500000 92700000
net.ipv4.tcp_max_orphans = 3276800
EOF
else
   echo "system configuration is already optimized, so we do nothing"
fi
}
optimizeSystemConf

echo  "1.2.Optimize Limit"
optimizeLimitConf(){
conf_exist=$(cat /etc/security/limits.conf|grep mysql|wc -l)
if [ $conf_exist -eq 0 ]; then
	echo "optimize limit configuration"
	cat >> /etc/security/limits.conf << "EOF"
#add by mysql
* soft  nproc   65536
* hard  nproc   65536
* soft  nofile  65536
* hard  nofile  65536
* soft  stack   10240
* hard  stack   32768
* soft core unlimited
* hard core unlimited
EOF
else
	echo "limit is already optimized, so we do nothing"
fi
}
optimizeLimitConf

echo  "1.3.firewall config"
function conf_firewall() {
##################gt>0
if [ $(systemctl status firewalld.service | grep -c running) -gt 0 ]; then  
     #systemctl stop firewalld.service
     #systemctl disable firewalld.service 
     firewall-cmd --zone=public --add-port=3306/tcp --permanent
     firewall-cmd --zone=public --add-port=22/tcp --permanent
     firewall-cmd --reload
     #禁用防火墙区域偏移
     sed -i 's/^AllowZoneDrifting=yes/AllowZoneDrifting=no/' /etc/firewalld/firewalld.conf 
   else
   echo "firewall not open"
fi
}
conf_firewall

echo  "1.4.adjust optimize selinux"
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
setenforce 0 

echo  "1.5关闭 numa和禁用透明大页"
sed -i "s/quiet/quiet numa=off transparent_hugepage=never/g"  /etc/default/grub 
grub2-mkconfig -o /etc/grub2.cfg


#echo  "1.6.os iso mount"
#mount $dir/*.iso /mnt/
#cat << EOF >> /etc/fstab
#/dev/sr0    /mnt        iso9660 loop            0 0
#EOF
#
#mkdir -p /etc/yum.repos.d/bak
#mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/bak
#cat >> /etc/yum.repos.d/os.repo <<"EOF"
#[OS1]
#name=OS
#baseurl=file:///mnt
#enabled=1
#gpgcheck=0
#EOF


echo  "2. variable list"
FILE_CONF=/topsoft/mysqldb/my.cnf
DATADIR=/topsoft/mysqldb/data
BASEDIR=/topsoft/mysqldb/mysql
SCRIPTS_DIR=/topsoft/mysqldb/scripts
LOGPATH=/topsoft/mysqldb/data/log

echo  "3. mysql exits"
id $mysql >& /dev/null
if [ $? -ne 0 ]
then
        echo "mysql already exits"
else 
        echo "mysql not exits，please create"
        groupadd mysql
        useradd -r -g mysql -s /bin/false mysql
fi

echo  "4.create directory"
if [ ! -d /topsoft/mysqldb ]
then
        cd /topsoft
	    mkdir -p mysqldb/{data,tmp,log,binlog,relaylog,mysql-files,backup,scripts}
else 	
        echo "/topsoft/mysqldb directory exits，please exit"	
        exit 1
fi

echo "5. unzip"
cd $dir
tar -xvf $package -C /topsoft/mysqldb
echo "mysql upzip success"
echo "directory rights"
mv /topsoft/mysqldb/mysql*x86_64 /topsoft/mysqldb/mysql
chown -R mysql:mysql /topsoft/mysqldb

#-------------------------------install mysql------------------------------------
echo "7. install dependency package"
#强制关掉yum进程
rm -f /var/run/yum.pid 
#yum install -y vim ncurses-devel libaio-devel gcc gcc-c++ cmake autoconf net-tools perl lib

echo "9. editor my.cnf"
cat > /topsoft/mysqldb/my.cnf  << "EOF"
[client]
port = 3306
socket = /topsoft/mysqldb/data/mysql.sock
default-character-set = utf8mb4

[mysql]
# 设置mysql客户端默认字符集
default-character-set = utf8mb4
socket = /topsoft/mysqldb/data/mysql.sock
prompt="\\u@\\h :\\d \\R:\\m:\\s>" #设置命令行提示符

[mysqld]
#操作用户#
user=mysql

#目录#
basedir=/topsoft/mysqldb/mysql  #mysql安装根目录
datadir=/topsoft/mysqldb/data #mysql数据文件所在目录
socket = /topsoft/mysqldb/data/mysql.sock

#字符集#
character-set-server = utf8mb4 #数据库默认字符集,注意不要再用utf8了
collation-server = utf8mb4_general_ci #数据库字符集对应一些排序规则,要属于character-set-server对应值的集合内
init_connect='SET NAMES utf8mb4' #设置client连接mysql时的字符集,防止乱码

#运行实例相关#
server_id = 103 #Mysql服务实例的唯一编号 每个mysql服务实例Id需唯一 可设置成ip最后一位
port = 3306 #服务端口号 默认3306
pid_file=/topsoft/mysqldb/data/mysqld.pid #pid文件的路径
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

#二进制日志#
log_bin = /topsoft/mysqldb/binlog/mybinlog  #二进制日志文件
binlog_format = ROW
binlog_expire_logs_seconds=604800 #mysql binlog日志文件保存的过期时间7天，过期后自动删除;默认值是0，不限制,这样会占用空间太多 单位秒
max_binlog_size = 1G #限制单个文件大小,默认大小:1,073,741,824,即1G,太大了

#慢查询日志#
log_queries_not_using_indexes = 1 #把未使用到索引的sql记录到慢查询日志
slow_query_log = 1 #是否打开慢查询sql日志
slow_query_log_file = /topsoft/mysqldb/log/mysql-slow.log #慢查询sql日志的文件地址
long_query_time = 1 #慢查询执行的秒数，超过这个值则会被记录到慢查询日志

#导出文件到指定目录
secure_file_priv=/topsoft/mysqldb/mysql-files

#增加临时表空间大小限制参数
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:1G

#时区#
default_time_zone="+8:00" #设置默认服务器时区
log_timestamps = system #解决日志中时间和本地差8小时

#认证策略解决登录ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded#
default_authentication_plugin = mysql_native_password 

#错误日志#
log_error=/topsoft/mysqldb/log/mysqld_error.log 

#性能参数#
open_files_limit = 65535
back_log=600 #连接数达到max_connections时，新来的请求将会被存在堆栈中。数量超过back_log，将不被授予连接资源
max_connections = 1000 #最大并发连接数，过小会影响连接的数量，报Too many connections错误，过大会导致服务资源用完无响应，最大值不能超过100000
max_user_connections=1000 #指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。
table_open_cache = 1024 #能同时打开表的个数
table_definition_cache = 1024
thread_stack = 512K
thread_cache_size = 1500
sort_buffer_size = 12M   #只是在需要的时候才分配，并且在那些操作做完之后就释放了
join_buffer_size = 12M
read_buffer_size = 24M  #读入缓冲区大小,对表进行顺序扫描时将分配1个读入缓冲区。对表的顺序扫描请求非常频繁，并且频繁扫描进行得太慢可增加。只是在需要的时候才分配，并且在操作结束后就释放
read_rnd_buffer_size = 8M  #随机读缓冲区大小,当按任意顺序读取行时(如:排序)，将分配一个随机读缓存区。只是在需要的时候才分配，并且在那些操作做完之后就释放
bulk_insert_buffer_size = 4M
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 48M  #heap(堆积)表缓冲大小,提高联接查询速度。只是在需要的时候才分配，并且在那些操作做完之后就释放了
max_heap_table_size = 32M
binlog_cache_size = 12M
max_binlog_cache_size = 50M
key_buffer_size=256M  #索引缓冲区大小。内存在4GB左右的服务器该参数可设置为256M或384M

#库表名不区分大小写#
lower_case_table_names = 1

#数据安全#
innodb_flush_log_at_trx_commit = 2 #每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，但并不会立即刷写到磁盘
innodb_log_buffer_size=64M  #将日志写入日志磁盘文件前的缓冲大小
innodb_log_file_size = 256M #InnoDB redo log大小

#最大允许的包#
max_allowed_packet = 48M

#超时#
interactive_timeout = 1800 #MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭  MySQL默认的wait_timeout 值为8个小时,
wait_timeout = 1800  #interactive_timeout参数需要同时配置才能生效

#禁用域名的解析#
skip_name_resolve = 1 #dns慢的情况下会影响性能，禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间

#禁用符号链接以防止各种安全风险
skip_symbolic_links=yes

#innodb是否为每个表使用独立的表空间文件#
innodb_file_per_table = 1 #开启该参数的时候，Innodb将每个新创建的表的数据及索引存储在一个独立的.ibd文件里，而不是系统的表空间。

#innodb缓冲池的大小设置#
#说明缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数，否则mysql会自动调整为相应的倍数#
innodb_buffer_pool_chunk_size= 256M   定义了buffer中每个chunk的大小  chunk 块
innodb_buffer_pool_size = 16G  #物理服务器内存的50%~75%  缓存 InnoDB存储引擎的表中的数据和索引数据，提高查询访问速度

#抑制即不显示 [Warning] [MY-013712] [Server] No suitable 'keyring_component_metadata_query' service implementation found to fulfill the request.
log_error_suppression_list='MY-013712'
EOF
mv /etc/my.cnf /tmp/my.cnf
ln -s /topsoft/mysqldb/my.cnf /etc/my.cnf

echo "10. add path to profile --> PASS"
echo 'LANG=en_US.UTF-8' >> /etc/profile
echo 'export PATH=$PATH:/topsoft/mysqldb/mysql/bin' >> /etc/profile
echo 'export MYSQL_DATA=/topsoft/mysqldb/data' >> /etc/profile
echo 'export MYSQL_HOME=/topsoft/mysqldb/mysql' >> /etc/profile
source /etc/profile


echo "11. directory privileges"
chown -R mysql:mysql /topsoft/mysqldb
chmod -R 755 /topsoft/mysqldb

echo  "12. start initialize mysql..."
#--basedir 安装目录
#--datadir 数据目录
/topsoft/mysqldb/mysql/bin/mysqld --initialize  --user=mysql  --basedir=/topsoft/mysqldb/mysql  --datadir=/topsoft/mysqldb/data --console

echo "13. auto system start --> PASS"
cat > /usr/lib/systemd/system/mysqld.service << "EOF"
[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=mysql
Group=mysql
ExecStart=/topsoft/mysqldb/mysql/bin/mysqld --defaults-file=/topsoft/mysqldb/my.cnf
LimitNOFILE=65536  
LimitNPROC=65536
EOF

chmod +x /usr/lib/systemd/system/mysqld.service

systemctl daemon-reload 
systemctl enable mysqld 
systemctl start mysqld 
systemctl status mysqld 
if [ $? -eq 0 ];then
    echo "start success"
else
    echo "start fail"
fi
sleep 10
if [ -f /topsoft/mysqldb/data/mysql.sock ] 
then 
     echo "mysql.sock exist";
	 ln -s /topsoft/mysqldb/data/mysql.sock /tmp/mysql.sock
else
     echo "The file doesn't exist"
fi	 

echo "14. change mysql root password and root remote visit--> PASS"
passwd=$(grep password /topsoft/mysqldb/log/mysqld_error.log | head -1 | awk 'END {print $NF}')
echo "mysql" > /topsoft/mysqldb/.pass

cat > /topsoft/mysqldb/change.sql  << "EOF"
use mysql;
FLUSH PRIVILEGES;
alter user 'root'@'localhost' identified with mysql_native_password by 'mysql';
alter user 'root'@'localhost'  password expire never;
FLUSH PRIVILEGES;
create user 'root'@'%' identified with mysql_native_password by 'mysql';
alter user 'root'@'%'  password expire never;
FLUSH PRIVILEGES;
grant all privileges on *.*  to   'root'@'%' with grant option;
FLUSH PRIVILEGES;
--create user 'root'@'192.168.16.%' identified with mysql_native_password by 'mysql';
--grant all privileges on *.* to 'root'@'192.168.16.%'   with grant option;
--alter user 'root'@'192.168.16.%'  password expire never;
--FLUSH PRIVILEGES;
EOF

mysql -uroot -p"$passwd"  -e "source /topsoft/mysqldb/change.sql;"
#创建业务库 智能分析平台
#mysql -uroot -p"${mysql_passwd}" -e "create database operational_platform default charset utf8mb4  collate utf8mb4_general_ci;" 
#mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"
#创建普通用户并授权远程访问
#mysql -uroot -p"${mysql_passwd}" -e "create user 'znfxpt'@'%' identified by 'mysql';"
#mysql -uroot -p"${mysql_passwd}" -e "grant all privileges on znfxpt_test.* to 'znfxpt'@'%' with grant option;"
#mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"
#修改密码
#mysql -uroot -p"${mysql_passwd}" -e "alter user 'znfxpt'@'%' identified with mysql_native_password by 'mysql';"
#mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"
#创建只读用户
#mysql -uroot -p"${mysql_passwd}" -e "create user 'query_user'@'%' identified by 'mysql';"
#mysql -uroot -p"${mysql_passwd}" -e "grant select on 'znfxpt'.* to query_user@'%';
#mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"

echo "18.数据库信息"
echo "数据库信息:mysql;密码：mysql;port:3306"

修改MySQL参数

group_name的值不能设置为每个节点的uuid,无论有多少个节点（目前mgr最多支持9个），uuid都必须一致。

增加到[mysqld]模块下面：
-- 节点4
cp /etc/my.cnf /etc/my.cnf_bak_`date +%F`
cat >> /etc/my.cnf << "EOF"
#add  Replication configuration parameters
server_id = 1551
binlog_checksum=NONE
log_slave_updates = on
gtid_mode=ON
enforce_gtid_consistency=on

master_info_repository=TABLE
relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64

plugin_load_add='group_replication.so'
group_replication_group_name="34975c79-405c-11eb-9f4c-5254044caef1"
group_replication_start_on_boot=OFF
group_replication_local_address= "192.168.40.155:33061"
group_replication_group_seeds= "192.168.40.152:33061,192.168.40.153:33061,192.168.40.154:33061,192.168.40.155:33061"
group_replication_bootstrap_group=OFF
loose-group_replication_recovery_retry_count=31536000
loose-group_replication_single_primary_mode=on
loose-group_replication_enforce_update_everywhere_checks=off
loose-group_replication_ip_whitelist="192.168.40.152,192.168.40.153,192.168.40.154,192.168.40.155"

report_host=192.168.40.155
report_port=3306
EOF

重启MySQL

重启MySQL服务

-- 重启MySQL
systemctl restart mysqld
systemctl status mysqld

-- 进入MySQL
mysql -uroot -pmysql

-- 远程连接MySQL
mysql -uroot -pmysql -h192.168.40.155 -P3306

-- 查看MySQL日志
tail -100f /topsoft/mysqldb/log/mysqld_error.log

-- 查看MySQL的主机名、server_id和server_uuid
mysql -uroot -pmysql -h192.168.40.155 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"

输出结果如下：

--节点4
[root@localhost opt]# mysql -uroot -pmysql -h192.168.40.154 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| mgr04      |        1551 | 648169f4-0141-11ef-9cd0-000c29667289 |
+------------+-------------+--------------------------------------+

安装MGR插件

安装MGR插件。

mysql -uroot -pmysql -h192.168.40.155 -P3306

--安装组复制插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- 如果MySQL版本大于8.0.17，那么建议再安装clone插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

--查看插件是否安装成功
show plugins;

--输出结果如下：
+----------------------------------+----------+--------------------+----------------------+---------+
| Name                             | Status   | Type               | Library              | License |
+----------------------------------+----------+--------------------+----------------------+---------+
| group_replication                | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
| clone                            | ACTIVE   | CLONE              | mysql_clone.so       | GPL     |

新节点执行克隆任务

查看MGR集群组状态

现有mgr中 查看MGR集群组状态

--现有mgr中 查看MGR集群组状态
root@localhost :(none) 15:09:10>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 | 109d5359-0121-11ef-8acf-000c2922d68f | 192.168.40.153 |        3306 | ONLINE       | PRIMARY     | 8.0.36         | XCom                       |
| group_replication_applier | 25c048b0-0121-11ef-9874-000c294fdfdd | 192.168.40.154 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
| group_replication_applier | 7fb72760-011c-11ef-b7aa-000c29d414b6 | 192.168.40.152 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.03 sec)

在原3节点执行修改参数

查看参数

在原3节点执行修改参数

--查看参数
root@localhost :(none) 15:30:34>show variables like '%group_replication_group_seeds%';
+-------------------------------+----------------------------------------------------------------+
| Variable_name                 | Value                                                          |
+-------------------------------+----------------------------------------------------------------+
| group_replication_group_seeds | 192.168.40.152:33061,192.168.40.153:33061,192.168.40.154:33061 |
+-------------------------------+----------------------------------------------------------------+
1 row in set (0.05 sec)


root@localhost :(none) 15:38:37>show variables like '%group_replication_ip_whitelist%';
+--------------------------------+----------------------------------------------+
| Variable_name                  | Value                                        |
+--------------------------------+----------------------------------------------+
| group_replication_ip_whitelist | 192.168.40.152,192.168.40.153,192.168.40.154 |
+--------------------------------+----------------------------------------------+
1 row in set (0.01 sec)

更改参数

在原3节点执行修改参数

set global group_replication_group_seeds='192.168.40.152:33061,192.168.40.153:33061,192.168.40.154:33061,192.168.40.155:33061';
stop group_replication;
set global group_replication_ip_whitelist="192.168.40.152,192.168.40.153,192.168.40.154,192.168.40.155";
start group_replication;

配置主节点的复制账号

-- 所有节点执行
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

新节点执行克隆任务

--开始同步
start group_replication;

-- 查看组复制成员及状态
SELECT * FROM performance_schema.replication_group_members;


-- 查看克隆进度和状态
SELECT * FROM performance_schema.clone_status \G


MySQL [(none)]> select
stage,
state,
cast(begin_time as DATETIME) as "START TIME",
cast(end_time as DATETIME) as "FINISH TIME",
lpad(sys.format_time(power(10,12) * (unix_timestamp(end_time) - unix_timestamp(begin_time))), 10, ' ') as DURATION,
lpad(concat(format(round(estimate/1024/1024,0), 0), "MB"), 16, ' ') as "Estimate",
case when begin_time is NULL then LPAD('%0', 7, ' ')
when estimate > 0 then
lpad(concat(round(data*100/estimate, 0), "%"), 7, ' ')
when end_time is NULL then lpad('0%', 7, ' ')
else lpad('100%', 7, ' ')
end as "Done(%)"
from performance_schema.clone_progress;

查看数据是否同步

--从节点查询
root@localhost :(none) 13:49:47>select * from dxj.tb1;
+----+----------+-----------+
| id | hostname | server_id |
+----+----------+-----------+
|  1 | mgr01    | 1521      |
+----+----------+-----------+
1 row in set (0.00 sec)

至此，通过clone插件的方式添加MGR节点已成功，非常简单也非常快速。

问题处理

插入数据报错

解决方法：

--给表添加主键
mysql> alter table test3 add primary key(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

--再次对表执行插入数据操作，成功
mysql> insert into test3 values(1,'aaa');
Query OK, 1 row affected (0.01 sec)

主库一旦执行事务，备库就退出group

主库全备传到备库，备库恢复后，发现未给复制组用户授权，在主、备库上分别执行授权后发现 一旦执行事务，备库就退出group

原因：主备库单独执行了事务，写入了自己的binlog,导致不一致

解决方法：

发现不一致时执行关闭写入binlog命令，授权完毕后再开启。

SET SQL_LOG_BIN=0;
GRANT BACKUP_ADMIN ON *.* TO repl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

若已经不一致了，需在备库reset master再执行加入group的命令。

主库一写数据，secondary库就离线

主库一写数据，secondary库就离线。在日志中查询为如下错误：

select * from performance_schema.replication_connection_status \G;

原因：mysql为8.0.22默认使用 caching_sha2_password 身份验证机制——从原来的 mysql_native_password 更改为 caching_sha2_password。 从 5.7 升级 8.0 版本的不会改变现有用户的身份验证方法，但新用户会默认使用新的 caching_sha2_password

解决方法：

ALTER USER 'rpl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'rpl_user';
ALTER USER 'rpl_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rpl_user';
ALTER USER 'rpl_user'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'rpl_user';

Primary库与secondary库间数据传递正常，问题解决。

Primary库与secondary库间数据不一致，复制数据时发生致命错误，备库离开replication 组

解决方法：

1. 清空从节点的数据

2. 从主库备份数据传至备库进行恢复

3. 在备库执行reset master

4. 重新执行 CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_user' FOR CHANNEL 'group_replication_recovery'

5. START GROUP_REPLICATION

参考链接：

https://www.cnblogs.com/lhrbest/p/14590968.html

手把手教你搭建Mysql8.0.22 MGR高可用集群 - 知乎

【DB宝35】使用MySQL 8.0 克隆(clone)插件快速添加MGR节点