MySQL8.0.29搭建MGR分为上下两部分，分别是:MGR多主搭建、MGR多主和单主的在线切换
MySQL组复制具有了高一致性、高容错性、高扩展性、高灵活性的特点，提供了高可用且可靠稳定的MySQL服务，增强了MySQL原有的高可用集群架构。在忽略网络延迟的情况，可以轻松的实现多活和异地容灾近端写库，组复制是MySQL未来发展的趋势，相信在未来的生产环境中会越来越多，MySQL也会越来越稳定。
MGR多主搭建
一、环境规划
二、操作系统配置(所有主机同操作)
1、修改IP、主机名
vi /etc/hosts
10.123.1.21 cwdb1
10.123.1.22 cwdb2
10.123.1.23 cwdb3
10.123.1.24 cwdb4
2、修改资源限制参数
echo "session required pam_limits.so" >> /etc/pam.d/login
cat /etc/pam.d/login
echo "* soft nproc 16384" >> /etc/security/limits.conf
echo "* hard nproc 16384" >> /etc/security/limits.conf
echo "* soft nofile 16384" >> /etc/security/limits.conf
echo "* hard nofile 65536" >> /etc/security/limits.conf
echo "* soft stack 16384" >> /etc/security/limits.conf
echo "* hard stack 32768" >> /etc/security/limits.conf
cat /etc/security/limits.conf
echo "* - nproc 16384" > /etc/security/limits.d/20-nproc.conf
cat /etc/security/limits.d/20-nproc.conf
echo "* soft memlock 3072000" >> /etc/security/limits.conf
echo "* hard memlock 3072000" >> /etc/security/limits.conf
echo "vm.nr_hugepages=300" >> /etc/sysctl.conf
echo "vm.swappiness=10" >> /etc/sysctl.conf
echo "vm.min_free_kbytes=51200" >> /etc/sysctl.conf
grep "net.ipv4.tcp_keepalive_time = 30" /etc/sysctl.conf
if [ $? != 0 ]
then
cat <<EOF>> /etc/sysctl.conf
net.ipv4.tcp_max_tw_buckets = 6000
net.ipv4.ip_local_port_range = 1024 65000
net.ipv4.tcp_tw_recycle = 0
net.ipv4.tcp_tw_reuse = 1
net.core.somaxconn = 65000
net.core.netdev_max_backlog = 262144
net.ipv4.tcp_max_orphans = 262144
net.ipv4.tcp_max_syn_backlog = 262144
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syn_retries = 1
net.ipv4.tcp_fin_timeout = 1
net.ipv4.tcp_keepalive_time = 30
net.ipv4.tcp_keepalive_probes = 6
net.ipv4.tcp_keepalive_intvl = 5
net.ipv4.tcp_timestamps = 0
EOF
sed -i 's/net.bridge.bridge-nf-call-ip6tables =0/#net.bridge.bridge-nf-call-ip6tables = 0/g' /etc/sysctl.conf
sed -i 's/net.bridge.bridge-nf-call-iptables =0/#net.bridge.bridge-nf-call-iptables = 0/g' /etc/sysctl.conf
sed -i 's/net.bridge.bridge-nf-call-arptables =0/#net.bridge.bridge-nf-call-arptables = 0/g' /etc/sysctl.conf
fi
sysctl -p
3、设置系统安全策略
① SELinux
echo "SELINUX=disabled" > /etc/selinux/config
echo "SELINUXTYPE=targeted" >> /etc/selinux/config
cat /etc/selinux/config
setenforce 0
②关闭防火墙
systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service
③关闭透明页
echo "if test -f /sys/kernel/mm/transparent_hugepage/enabled; then">> /etc/rc.d/rc.local
echo "echo never > /sys/kernel/mm/transparent_hugepage/enabled">> /etc/rc.d/rc.local
echo "fi">> /etc/rc.d/rc.local
echo "if test -f /sys/kernel/mm/transparent_hugepage/defrag; then">> /etc/rc.d/rc.local
echo "echo never > /sys/kernel/mm/transparent_hugepage/defrag">> /etc/rc.d/rc.local
echo "fi">> /etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local
④关闭NUMA功能
vi /etc/default/grub
GRUB_CMDLINE_LINUX="crashkernel=auto rhgb quiet numa=off"
然后执行：
grub2-mkconfig -o /etc/grub2.cfg
shutdown -r now
4、配置yum源 ------内网二进制安装可以不按照这样做
挂载光驱
mount /dev/cdrom /mnt
配置yum源
cd /etc/yum.repos.d
mkdir bk
mv CentOS* bk/
把后缀.repo的文件都放到bk里，新建00.repo
echo "[EL7]" >> 00.repo
echo "name = linux 7.9 dvd" >> 00.repo
echo "baseurl=file:///mnt" >> 00.repo
echo "gpgcheck=0" >> 00.repo
echo "enabled=1" >> 00.repo
三、开始安装MySQL8.0.29
1--环境检查（查看系统是否自带mariadb，并卸载）
rpm -qa |grep mariadb
rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
2--创建用户、组、创建目录
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
mkdir -p /data01/mysql/app
mkdir -p /data01/mysql/data/3306/data
mkdir -p /data01/mysql/log/3306/binlog
mkdir -p /data01/mysql/log/3306/relaylog
mkdir -p /data01/mysql/backup/backup-db
mkdir -p /data01/mysql/backup/backup-tmp
mkdir -p /data01/mysql/backup/backup-binlog
chown -R mysql:mysql /data01/*
3--解压mysql安装包
cd /data01/mysql/app/
tar xvf mysql-8.0.29-linux-glibc2.12-x86_64.tar.xz
ln -s mysql-8.0.29-linux-glibc2.12-x86_64 mysql
--添加到环境变量
vi ~/.bash_profile
PATH=$PATH:/data01/mysql/app/mysql/bin:$HOME/bin
source ~/.bash_profile
4、my.cnf配置文件
10.123.1.21
vi /data01/mysql/data/3306/my.cnf
[client]
port=3306
socket = /data01/mysql/data/3306/mysql.sock
[mysql]
no-beep
prompt="\u@mysqldb\R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8
[mysqld]
########basic settings########
server-id=213306
port=3306
user = mysql
bind_address= 0.0.0.0
basedir=/data01/mysql/app/mysql
datadir=/data01/mysql/data/3306/data
socket = /data01/mysql/data/3306/mysql.sock
pid-file=/data01/mysql/data/3306/mysql.pid
character-set-server=utf8mb4
skip-character-set-client-handshake=1
autocommit = 1
#skip_name_resolve = 1
max_connections = 8000
max_connect_errors = 100
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 1024M
max_allowed_packet = 16M
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M
#event_scheduler =1
#query_cache_type = 1
#query_cache_size=1M
table_open_cache=4096
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M
back_log=2048
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000
########log settings########
log-output=FILE
general_log = 0
general_log_file=/data01/mysql/log/3306/mysql-general.err
slow_query_log = ON
slow_query_log_file=/data01/mysql/log/3306/mysql-query.err
long_query_time=3
min_examined_row_limit = 10
log-error=/data01/mysql/log/3306/mysql-error.err
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_replica_statements = 1
log_throttle_queries_not_using_indexes = 10
binlog_expire_logs_seconds=604800
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 30
#innodb_flush_method = O_DIRECT
innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
#innodb_undo_logs = 128
#innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_flush_neighbors = 1
innodb_purge_threads = 4
#innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
#show_compatibility_56=on
-----------------------------------------------------------------------
10.123.1.22
vi /data01/mysql/data/3306/my.cnf
[client]
port=3306
socket = /data01/mysql/data/3306/mysql.sock
[mysql]
no-beep
prompt="\u@mysqldb\R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8
[mysqld]
########basic settings########
server-id=223306
port=3306
user = mysql
bind_address= 0.0.0.0
basedir=/data01/mysql/app/mysql
datadir=/data01/mysql/data/3306/data
socket = /data01/mysql/data/3306/mysql.sock
pid-file=/data01/mysql/data/3306/mysql.pid
character-set-server=utf8mb4
skip-character-set-client-handshake=1
autocommit = 1
#skip_name_resolve = 1
max_connections = 8000
max_connect_errors = 100
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 1024M
max_allowed_packet = 16M
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M
#event_scheduler =1
#query_cache_type = 1
#query_cache_size=1M
table_open_cache=4096
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M
back_log=2048
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000
########log settings########
log-output=FILE
general_log = 0
general_log_file=/data01/mysql/log/3306/mysql-general.err
slow_query_log = ON
slow_query_log_file=/data01/mysql/log/3306/mysql-query.err
long_query_time=3
min_examined_row_limit = 10
log-error=/data01/mysql/log/3306/mysql-error.err
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_replica_statements = 1
log_throttle_queries_not_using_indexes = 10
binlog_expire_logs_seconds=604800
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 30
#innodb_flush_method = O_DIRECT
innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
#innodb_undo_logs = 128
#innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_flush_neighbors = 1
innodb_purge_threads = 4
#innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
#show_compatibility_56=on
--------------------------------------------------------------------------------
10.123.1.23
vi /data01/mysql/data/3306/my.cnf
[client]
port=3306
socket = /data01/mysql/data/3306/mysql.sock
[mysql]
no-beep
prompt="\u@mysqldb\R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8
[mysqld]
########basic settings########
server-id=233306
port=3306
user = mysql
bind_address= 0.0.0.0
basedir=/data01/mysql/app/mysql
datadir=/data01/mysql/data/3306/data
socket = /data01/mysql/data/3306/mysql.sock
pid-file=/data01/mysql/data/3306/mysql.pid
character-set-server=utf8mb4
skip-character-set-client-handshake=1
autocommit = 1
#skip_name_resolve = 1
max_connections = 8000
max_connect_errors = 100
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 1024M
max_allowed_packet = 16M
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M
#event_scheduler =1
#query_cache_type = 1
#query_cache_size=1M
table_open_cache=4096
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M
back_log=2048
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000
########log settings########
log-output=FILE
general_log = 0
general_log_file=/data01/mysql/log/3306/mysql-general.err
slow_query_log = ON
slow_query_log_file=/data01/mysql/log/3306/mysql-query.err
long_query_time=3
min_examined_row_limit = 10
log-error=/data01/mysql/log/3306/mysql-error.err
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_replica_statements = 1
log_throttle_queries_not_using_indexes = 10
binlog_expire_logs_seconds=604800
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 30
#innodb_flush_method = O_DIRECT
innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
#innodb_undo_logs = 128
#innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_flush_neighbors = 1
innodb_purge_threads = 4
#innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
#show_compatibility_56=on
-------------------------------------------------------------------------
10.123.1.24
vi /data01/mysql/data/3306/my.cnf
[client]
port=3306
socket = /data01/mysql/data/3306/mysql.sock
[mysql]
no-beep
prompt="\u@mysqldb\R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8
[mysqld]
########basic settings########
server-id=243306
port=3306
user = mysql
bind_address= 0.0.0.0
basedir=/data01/mysql/app/mysql
datadir=/data01/mysql/data/3306/data
socket = /data01/mysql/data/3306/mysql.sock
pid-file=/data01/mysql/data/3306/mysql.pid
character-set-server=utf8mb4
skip-character-set-client-handshake=1
autocommit = 1
#skip_name_resolve = 1
max_connections = 8000
max_connect_errors = 100
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 1024M
max_allowed_packet = 16M
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M
#event_scheduler =1
#query_cache_type = 1
#query_cache_size=1M
table_open_cache=4096
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M
back_log=2048
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000
########log settings########
log-output=FILE
general_log = 0
general_log_file=/data01/mysql/log/3306/mysql-general.err
slow_query_log = ON
slow_query_log_file=/data01/mysql/log/3306/mysql-query.err
long_query_time=3
min_examined_row_limit = 10
log-error=/data01/mysql/log/3306/mysql-error.err
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_replica_statements = 1
log_throttle_queries_not_using_indexes = 10
binlog_expire_logs_seconds=604800
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 30
#innodb_flush_method = O_DIRECT
innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
#innodb_undo_logs = 128
#innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_flush_neighbors = 1
innodb_purge_threads = 4
#innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
#show_compatibility_56=on
5、初始化my.cnf配置文件
/data01/mysql/app/mysql/bin/mysqld --defaults-file=/data01/mysql/data/3306/my.cnf --initialize --user=mysql --basedir=/data01/mysql/app/mysql --datadir=/data01/mysql/data/3306/data
# remove default my.cnf
rm -f /etc/my.cnf
rm -rf /etc/my.cnf.d
ln -s /data01/mysql/data/3306/my.cnf /etc/my.cnf
启动MySQL服务
/data01/mysql/app/mysql/bin/mysqld_safe --defaults-file=/data01/mysql/data/3306/my.cnf &
修改密码：
原始密码在/data01/mysql/log/3306/mysql-error.err里面找
ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootroot';
flush privileges;
四、配置数据库启动脚本
vi /data01/mysql/data/3306/3306mysql.sh
#init
port=3306
mysql_user="root"
mysql_pwd="rootroot"
CmdPath="/data01/mysql/app/mysql/bin"
mysql_sock="/data01/mysql/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL 3306...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data01/mysql/data/${port}/my.cnf 2>&1 > /dev/null &
else
printf "MySQL 3306 is running...\n"
exit
fi
}
#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL 3306 is stopped...\n"
exit
else
printf "Stoping MySQL 3306...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data01/mysql/data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql()
{
printf "Restarting MySQL 3306...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data01/mysql/data/${port}/3306mysql.sh {start|stop|restart}\n"
esac
五、MySQL group repliedcation多主模式实施操作
1、配置my.cnf文件参数（所有节点都配置）
vi /data01/mysql/data/3306/my.cnf
#bind_address= 0.0.0.0 把原配置文件中这个参数注释掉
log_bin=/data01/mysql/log/3306/binlog/mysql-binlog
log_bin_index=/data01/mysql/log/3306/binlog/mysql-binlog.index
binlog_format=row
binlog_rows_query_log_events=on
binlog_checksum=none
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
slave_preserve_commit_order=1
gtid_mode = on
enforce_gtid_consistency = 1
log-slave-updates = 1
binlog_gtid_simple_recovery=1
relay_log = /data01/mysql/log/3306/relaylog/mysql-relay.log
relay-log-index = /data01/mysql/log/3306/relaylog/mysql-relay.index
master_info_repository=table
relay_log_info_repository=table
plugin_load="group_replication=group_replication.so"
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "10.123.1.21:33006"
loose-group_replication_group_seeds= "10.123.1.21:33006,10.123.1.22:33006,10.123.1.23:33006,10.123.1.24:33006"
loose-group_replication_bootstrap_group= off
group_replication_single_primary_mode=off
group_replication_enforce_update_everywhere_checks=on
配置完成后，重启MySQL服务
sh /data01/mysql/data/3306/3306mysql.sh restart
2、配置第一个节点：
mysql -uroot -prootroot
2.1、#创建用户复制的用户
set sql_log_bin=0;
create user repmgr@'%' identified by 'repmgr';
grant replication slave,replication client on *.* to repmgr@'%';
create user repmgr@'127.0.0.1' identified by 'repmgr';
grant replication slave,replication client on *.* to repmgr@'127.0.0.1';
create user repmgr@'localhost' identified by 'repmgr';
grant replication slave,replication client on *.* to repmgr@'localhost';
flush privileges;
set sql_log_bin=1;
set global group_replication_recovery_get_public_key=on;
2.2、#配置复制时所使用的用户
change master to
master_user='repmgr',
master_password='repmgr'
for channel 'group_replication_recovery';
2.3、#查看插件是否激活
show plugins;
2.4、#建个群，谁做这一步谁就相当于是这组的组长，其他组内成员之内加入组，不能进行此步创建
set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;
#查看组内成员状态
select * from performance_schema.replication_group_members;
3、配置第二个节点：
mysql -uroot -prootroot
3.1、#创建用户复制的用户
set sql_log_bin=0;
create user repmgr@'%' identified by 'repmgr';
grant replication slave,replication client on *.* to repmgr@'%';
create user repmgr@'127.0.0.1' identified by 'repmgr';
grant replication slave,replication client on *.* to repmgr@'127.0.0.1';
create user repmgr@'localhost' identified by 'repmgr';
grant replication slave,replication client on *.* to repmgr@'localhost';
flush privileges;
set sql_log_bin=1;
set global group_replication_recovery_get_public_key=on;
3.2、#配置复制时所使用的用户
change master to
master_user='repmgr',
master_password='repmgr'
for channel 'group_replication_recovery';
3.3、#查看插件是否激活
show plugins;
3.4、#加入前面创建好的赋值组
start group_replication;
#查看组内成员状态
select * from performance_schema.replication_group_members;
4、配置其他节点：
mysql -uroot -prootroot
4.1、#创建用户复制的用户
set sql_log_bin=0;
create user repmgr@'%' identified by 'repmgr';
grant replication slave,replication client on *.* to repmgr@'%';
create user repmgr@'127.0.0.1' identified by 'repmgr';
grant replication slave,replication client on *.* to repmgr@'127.0.0.1';
create user repmgr@'localhost' identified by 'repmgr';
grant replication slave,replication client on *.* to repmgr@'localhost';
flush privileges;
set sql_log_bin=1;
set global group_replication_recovery_get_public_key=on;
4.2、#配置复制时所使用的用户
change master to
master_user='repmgr',
master_password='repmgr'
for channel 'group_replication_recovery';
4.3、#查看插件是否激活
show plugins;
4.4、#加入前面创建好的复制组
start group_replication;
#查看组内成员状态
select * from performance_schema.replication_group_members;
六、测试检查
一节点执行：
create database ceshidb;
use ceshidb;
create table ceshidb.ceshi1 (id int primary key ,name varchar(40));
insert into ceshidb.ceshi1 values (1,'cs111'),(2,'cs112'),(3,'cs113'),(4,'cs114'),(5,'cs115');
commit;
其他节点查询：
select * from ceshidb.ceshi1;
-----------------------------------------
二节点执行：
insert into ceshidb.ceshi1 values (21,'cs111'),(22,'cs112'),(23,'cs113'),(24,'cs114'),(25,'cs115');
commit;
其他节点查询：
select * from ceshidb.ceshi1;
---------------------------------------------
三节点执行：
create database ceshidb1;
create table ceshidb1.ceshi1 (id int primary key ,name varchar(40));
insert into ceshidb1.ceshi1 values (31,'cs211'),(32,'cs212'),(33,'cs213'),(34,'cs214'),(35,'cs215');
commit;
其他节点查询：
show databases;
select * from ceshidb1.ceshi1;
MGR多主和单主的在线切换
MGR多主和单主的在线切换，其实很简单修改参数就可以
多主设置： group_replication_single_primary_mode=off global group_replication_enforce_update_everywhere_checks=on，
单主设置： group_replication_single_primary_mode=on global group_replication_enforce_update_everywhere_checks=off
启停MySQL数据库，修改配置文件用上面的方法，不停MySQL服务在线修改用 false、ture
一、MGR多主切单主模式
1、停止组复制（所有节点）
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=false;
set global group_replication_single_primary_mode=true;
2、一节点执行
set global group_replication_bootstrap_group=on;
start GROUP_REPLICATION;
set global group_replication_bootstrap_group=off;
3、其他节点执行
start GROUP_REPLICATION;
4、查看MGR组内成员状态信息
select * from performance_schema.replication_group_members;
二、切回MGR多主模式
1、停止组复制（所有节点）
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=true;
set global group_replication_single_primary_mode=false;
2、一节点执行
set global group_replication_bootstrap_group=on;
start GROUP_REPLICATION;
set global group_replication_bootstrap_group=off;
3、其他节点执行
start GROUP_REPLICATION;
4、查看MGR组内成员状态信息
select * from performance_schema.replication_group_members;
MGR常用相关状态查看命令
查看MGR组内成员状态信息
select * from performance_schema.replication_group_members;
查看主节点状态信息：
select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member';
查看当前节点详细日志应用信息：：
select * from performance_schema.replication_group_member_stats \G
查看主库信息：
show master status；
查看当前复制渠道连接信息：
select * from performance_schema.replication_connection_status\G
MGR宕机恢复及在线增加、删除节点后续会以实验的形式发出来，欢迎大家提出合理的意见及建议，因个人能力有限，文章中难免有遗漏的地方，望见谅。此文章思路及方案可实施到线上环境，但请先在测试环境进行验证后，再到线上实施。祝大家年薪百万！
