Mysql-MGR集群
[TOC]
0、概述
MySQL Group Replication（MGR：mysql组复制技术）是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案，以插件形式提供。
1
2
3
4
高一致性：基于原生复制以及分布式paxos协议实现组复制，并已插件的方式提供，保证数据一致性(paxos协议:解决多节点写入的问题)；
高容错性：自动检测机制，只要不是大多数节点都宕机就可以继续工作，有自动检测机制，当不同节点产生资源竞争时，不会出现错误，按 照先到先优先的原则进行处理，内置自动化防脑裂保护机制；
高扩展性：节点的增加与移除会自动更新组成员信息，新节点加入后，自动从其他节点同步增量数据，直到与其他节点数据一致，如果某个 节点被移除了，其他节点会自动更新组信息，自动维护新的组信息。
高灵活性：提供单主模式和多主模式，单主模式在主库宕机后能够自动选主，所有写入都在主节点进行，多主模式支持多节点写入。
使用限制
-
仅支持innodb存储引擎
1 2 3 4 5 6 7 8 9
MGR集群中，只支持innodb存储引擎，能够创建非innodb引擎的表，但是无法写入数据，向非innodb表写数据直接报错。因为事物支持在commit时对各节点进行冲突检查 要求： 节点必须开启binlog且为row模式 必须开启gtid 且主从状态信息存于表中 --master-info-repository=TABLE --realy-log-info-repository=TABLE --log-slave-updates打开，级联更新) 一致性检测设置 --transaction-write-extraction=XXHASH64 -
表必须有主见或者非NULL的唯一值
1
MGR集群中，只支持innodb引擎的表，并且该表必须有显式的主键，或者非Null的唯一键，否则即使能够创建表，也无法向表中写入数据。
-
网络限制
1
MGR 组通信引擎目前仅支持IPv4网络，并且对节点间的网络性能要求较高，低延迟、高带宽的网络是部署MGR集群的基础。
-
MGR忽略表锁和命名锁
1
在MGR中lock tables、unlock tables、get_lock、release_lock等这些表锁和命名锁将被忽略。
-
其他限制
1 2 3 4 5 6 7 8 9
1.RP和普通复制binlog校验不能共存，需设置--binlog-checksum=none; 2.不支持gap lock(间隙锁)，隔离级别需设置为read committed; 3.MGR多主模式中，默认不支持 SERIALIZABLE 隔离级别。 4.多主模式下，对同一个对象进行并发的有冲突的ddl和dml操作导致这种冲突在部分成员节点中无法检测到，最终可能导致数据不一致。 5.多主模式下，不支持级联约束的外键，可能造成有冲突的操作无法检测。 6.不支持超大事务。 7.多主模式下可能导致死锁，比如select ...for update在不同节点执行，由于多节点锁无法共享，很容易导致死锁。 8.不支持复制过滤，如果有节点设置了复制过滤，将影响节点间决议的达成。 9.MGR最多支持9个节点，大于9个节点，将拒绝新节点的加入。
1、集群规划
注意：本文档按照MGR单主多从模式进行搭建
节点规划
官方建议:节点数量为奇数个
本示例地址以下,部署时候按照实际情况进行修改
| hostname | IP | 节点规划 |
|---|---|---|
| zqt003 | 100.4.17.4 | Master（主节点） |
| zqt005 | 100.4.17.5 | Slaver1（从节点1） |
| zqt009 | 100.4.17.9 | Slaver2（从节点2） |
Mysql Server版本
本示例操作系统为：Centos7 x64
Mysql Server版本：8.0.21 （mysql-8.0.21-el7-x86_64.tar.gz）
下载地址：MySQL :: Download MySQL Community Server (Archived Versions)
2、Mysql部署（离线安装）
一）Server安装
注意：所有节点都需要部署
-
将mysql8_install.sh（安装脚本）、my_test.cnf（默认安装配置）、mysql-8.0.21-el7-x86_64.tar.gz（安装文件名）放在同一目录下，本示例放在/opt/mysql_install下
-
修改脚本里的实际的ip地址、端口、hosts对应的主机名
-
使用如下命令进行安装
1
/bin/bash mysql8_install.sh
-
按照提示输入
数据库服务名：例如 dataReport
数据库服务端口：默认3306，根据需要进行输入
MySQL serverId: 必须为纯数字，本示例采用ip地址后3位当成serverId，例如 100.4.17.4，那么本节点上serverId就取4174
innodb_buffer_pool_size大小：示例输入 2，2G大小，默认大小为128M
注：安装完成后my.cnf配置文件默认在/etc/目录下，文件名是以你的数据库名命名，例my_test.cnf，mysql.sock在/tmp目录下
-
更新root用户权限
1 2 3 4 5 6 7
#连接数据库,root密码默认123456 /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql_master.sock #切换database到mysql mysql>use mysql; mysql>update user set host='%' where user='root'; mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; #此步骤如果提示错误，再执行一遍 mysql>FLUSH PRIVILEGES;
-
使用navicat等客户端工具进行连接测试
二）配置主从复制
注意：只在从节点都需要部署
- 执行安装
1
/bin/bash mysql8_install.sh repl
三）搭建GMR
注意：先把3个节点MySQL实例启动后再开始搭建mgr，先在Primary节点上执行，再到Secondary节点上执行
-
执行安装
1
/bin/bash mysql8_install.sh mgr
-
查看安装错误日志
1 2 3
#错误日志目录：/data/mysql/test/log/err.log tailf -n 300 /data/mysql/test/log/err.log #关注错误信息
-
因为上一步部署过主从，需要重启一下主从
1 2 3 4 5 6 7
#连接数据库,root密码默认123456 /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql_master.sock #切换database到mysql mysql>use mysql; mysql>stop slave; mysql>reset master; mysql>reset slave;
-
节点加入白名单
1 2 3 4 5 6 7
#连接数据库,root密码默认123456 /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql_master.sock #切换database到mysql mysql>use mysql; mysql>STOP GROUP_REPLICATION; mysql>SET GLOBAL group_replication_ip_whitelist="100.4.17.5,100.4.17.4,100.4.17.9"; mysql>start GROUP_REPLICATION;
-
查看集群状态
1
mysql>select * from performance_schema.replication_group_members;
-
重点：一定要多看错误日志！！！！
附件：部署脚本
文件名: mysql8_install.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
#!/bin/bash
#文件名: mysql8_install.sh
echo "正在安装MySQL软件......."
useradd mysql
useradd nagios
useradd zabbix
sleep 2
######--配置参数--######
mysql8_version=mysql-8.0.21-el7-x86_64.tar.gz
mysql8_version_dir=mysql-8.0.21-el7-x86_64
######--数据库同步复制用户--######
repl_user=repl
repl_passwd=sysrepl
#######################
######--数据库root密码--######
root_passwd=123456
#######################
######--数据库DBA管理用户--######
dba_user=admin
dba_passwd=123456
#######################
######--数据库8.0克隆用户--######
clone_user=clone_user
clone_passwd=123456
#######################
######--修改hosts文件--######
cat << EOF >> /etc/hosts
100.4.17.4 zqt003
100.4.17.5 zqt005
100.4.17.9 zqt009
EOF
#######################
######--Mysql MGR配置--######
mysql_port=3306
primary_ip=100.4.17.4
secondary1_ip=100.4.17.5
secondary2_ip=100.4.17.9
primary_port=33061
secondary1_port=33061
secondary2_port=33061
local_ip=100.4.17.4
local_port=33061
#############################
######MySQL主从复制同步#######
if [ "$1" = "repl" ]
then
while true
do
read -t 30 -p "输入你的主库IP: " master_ip
read -t 30 -p "输入你的主库端口号: " master_port
if [[ -z $master_ip || -z $master_port ]]
then
continue
else
echo ""
echo "主库IP是： $master_ip"
echo "主库端口号是： $master_port"
break
fi
done
/usr/local/mysql/bin/mysql -h127.0.0.1 -u"$dba_user" -p"$dba_passwd" -P"$master_port" --connect-expired-password -e "CHANGE MASTER TO MASTER_HOST='$master_ip',MASTER_USER='$repl_user',MASTER_PASSWORD='$repl_passwd',MASTER_PORT=$master_port,MASTER_AUTO_POSITION = 1,MASTER_CONNECT_RETRY=10; START SLAVE;"
echo "MySQL主从复制同步已经初始化完毕。"
exit 0
fi
################################################
######MySQL MGR安装#######
if [ "$1" = "mgr" ]
then
while true
do
read -t 30 -p "是Primary吗？是请输入yes，否输入no: " is_primary
if [[ -z $is_primary ]]
then
continue
else
if [ $is_primary == "yes" ] || [ $is_primary == "no" ]
then
break
else
echo "你输入一个错误的字符$is_primary，请重新输入..."
continue
fi
fi
done
if [ $is_primary == "yes" ]
then
/usr/local/mysql/bin/mysql -h127.0.0.1 -u"$dba_user" -p"$dba_passwd" -P"$mysql_port" --connect-expired-password -e "INSTALL PLUGIN group_replication SONAME 'group_replication.so'; set persist group_replication_group_name = '3b12b5bd-f0c6-11e9-9778-000c2900afc6';set persist group_replication_local_address = '${local_ip}:${local_port}'; set persist group_replication_group_seeds = '${primary_ip}:${primary_port},${secondary1_ip}:${secondary1_port},${secondary2_ip}:${secondary2_port}';SET GLOBAL group_replication_bootstrap_group=ON; CHANGE MASTER TO MASTER_USER='$repl_user',MASTER_PASSWORD='$repl_passwd' FOR CHANNEL 'group_replication_recovery';START GROUP_REPLICATION;select sleep(5);select * from performance_schema.replication_group_members;SET GLOBAL group_replication_bootstrap_group=OFF;"
else
/usr/local/mysql/bin/mysql -h127.0.0.1 -u"$dba_user" -p"$dba_passwd" -P"$mysql_port" --connect-expired-password -e "INSTALL PLUGIN group_replication SONAME 'group_replication.so'; set persist group_replication_group_name = '3b12b5bd-f0c6-11e9-9778-000c2900afc6';set persist group_replication_local_address = '${local_ip}:${local_port}'; set persist group_replication_group_seeds = '${primary_ip}:${primary_port},${secondary1_ip}:${secondary1_port},${secondary2_ip}:${secondary2_port}'; SET GLOBAL group_replication_bootstrap_group=OFF; CHANGE MASTER TO MASTER_USER='$repl_user',MASTER_PASSWORD='$repl_passwd' FOR CHANNEL 'group_replication_recovery';START GROUP_REPLICATION;select sleep(5);select * from performance_schema.replication_group_members;"
fi
echo "MySQL Mgr组复制已经初始化完毕。"
exit 0
fi
################################################
ps aux | grep 'mysql' | grep -v 'grep' | grep -v 'bash'
if [ $? -eq 0 ]
then
echo "MySQL进程已经启动，无需二次安装。"
exit 0
fi
if [ ! -d /usr/local/${mysql8_version_dir} ]
then
#yum install xz -y
tar -zxvf ${mysql8_version} -C /usr/local/
ln -s /usr/local/${mysql8_version_dir} /usr/local/mysql
chown -R mysql.mysql /usr/local/mysql/
chown -R mysql.mysql /usr/local/mysql
else
ln -s /usr/local/${mysql8_version_dir} /usr/local/mysql
chown -R mysql.mysql /usr/local/mysql/
chown -R mysql.mysql /usr/local/mysql
fi
while true
do
read -t 30 -p "输入你的数据库服务名: " dbname
read -t 30 -p "输入你的数据库端口号: " dbport
read -t 30 -p "输入MySQL serverId: " serverId
read -t 30 -p "输入innodb_buffer_pool_size大小，单位G: " innodb_bp_size
if [[ -z $dbname || -z $dbport || -z $serverId || -z $innodb_bp_size ]]
then
continue
else
echo "数据库服务名字是： $dbname"
echo "数据库端口是： $dbport"
echo "MySQL serverId： $serverId"
echo "BP大小是： $innodb_bp_size GB"
break
fi
done
sed "s/test/$dbname/g;s/3306/$dbport/;s/413306/$serverId/;/innodb_buffer_pool_size/s/1/$innodb_bp_size/" my_test.cnf > /etc/my_$dbname.cnf
DATA_DIR=/data/mysql/$dbname
[ ! -d $DATA_DIR ] && mkdir -p $DATA_DIR/{data,binlog,relaylog,tmp,slowlog,log}; touch $DATA_DIR/log/error.log; chown -R mysql.mysql /data/mysql/
if [ `ls -A $DATA_DIR/data/ | wc -w` -eq 0 ]
then
cd /usr/local/mysql
echo ""
echo "初始化MySQL数据目录......"
echo ""
bin/mysqld --defaults-file=/etc/my_$dbname.cnf --initialize --lower-case-table-names=1 --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/$dbname/data
sleep 2
bin/mysqld_safe --defaults-file=/etc/my_$dbname.cnf --user=mysql &
fi
while true
do
netstat -ntlp | grep $dbport
if [ $? -eq 1 ]
then
echo "MySQL启动中，稍等......"
sleep 5
continue
else
break
fi
done
ps aux | grep 'mysql' | grep -v 'grep' | grep -v 'bash'
if [ $? -eq 0 ]
then
echo "MySQL安装完毕。"
else
echo "MySQL安装失败。"
fi
###更改root账号随机密码
random_passwd=`grep 'temporary password' $DATA_DIR/log/error.log | awk -F 'root@localhost: ' '{print $2}'`
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock -p"$random_passwd" --connect-expired-password -e "set sql_log_bin=0;alter user root@'localhost' identified by '$root_passwd';"
echo "root账号随机密码更改完毕。"
###创建同步账号和管理员账号
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock --connect-expired-password -p"$root_passwd" -e "set sql_log_bin=0;create user '$repl_user'@'%' IDENTIFIED BY '$repl_passwd'; GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO '$repl_user'@'%'; create user '$dba_user'@'%' IDENTIFIED BY '$dba_passwd'; GRANT ALL on *.* to '$dba_user'@'%' WITH GRANT OPTION;"
sed -i -r "s/(PATH=)/\1\/usr\/local\/mysql\/bin:/" /root/.bash_profile
source /root/.bash_profile
echo "MySQL账号初始化完毕。"
###安装clone插件
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock --connect-expired-password -p"$root_passwd" -e "set sql_log_bin=0;INSTALL PLUGIN CLONE SONAME 'mysql_clone.so'; CREATE USER '$clone_user'@'%' IDENTIFIED BY '$clone_passwd';GRANT BACKUP_ADMIN,CLONE_ADMIN ON *.* TO '$clone_user'@'%';"
echo ""
echo "clone克隆插件安装完毕。"
附件：默认配置文件
可根据具体需要进行更改
文件名: my_test.cnf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
[client]
port = 3306
socket = /tmp/mysql_test.sock
#default-character-set = utf8
prompt=(\\u@\\h) [\\d]>\\_
# The MySQL server
#########Basic##################
[mysqld]
##bind_address = 127.0.0.1
server-id = 413306
port = 3306
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/test/data
tmpdir = /data/mysql/test/tmp
socket = /tmp/mysql_test.sock
skip-external-locking
skip-name-resolve
##init_connect = 'SET global sql_mode = ""'
sql_mode = ''
default_authentication_plugin = mysql_native_password
activate_all_roles_on_login = 1
### Percona ###
#extra_port = 13306
#extra_max_connections = 100
###skip-networking
default-storage-engine = INNODB
character-set-server = utf8
wait_timeout = 3600
connect_timeout = 20
interactive_timeout = 3600
back_log = 500
#event_scheduler = ON
open_files_limit = 65535
#thread_handling = pool-of-threads
lower_case_table_names = 1
###### binlog ######
log-bin = /data/mysql/test/binlog/mysql-bin
binlog_format = ROW
binlog_checksum = NONE
binlog_transaction_dependency_tracking = WRITESET
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = on
log_slave_updates = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
max_binlog_size = 128M
binlog_cache_size = 1M
expire-logs-days = 7
#########replication#############
relay-log = /data/mysql/test/relaylog/relay-log
slave-net-timeout = 10
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_wait_no_slave = 1
#rpl_semi_sync_master_timeout = 1000
#rpl_semi_sync_slave_enabled = 1
#skip-slave-start
log_slave_updates = 1
relay_log_recovery = 1
#slave_skip_errors = 1062
read_only = 0
###### Mgr config ######
loose-transaction_write_set_extraction = XXHASH64
loose-group_replication_start_on_boot = ON ###是否随mysql启动Group Replication
loose-group_replication_bootstrap_group = OFF ###是否是Group Replication的引导节点，初次搭建集群的时候需要有一个节点设置为ON来启动Group Replication
###参数设置为ON，是为了标示以后加入集群的服务器都已这台服务器为基准。以后加入的就不需要进行设置
loose-group_replication_group_name = 850ce522-a85e-11e9-8ea3-48df3749e199
#loose-group_replication_local_address = 192.168.148.41:33061
#loose-group_replication_group_seeds = 192.168.148.41:33061,192.168.148.42:33072,192.168.148.39:33083
loose-group_replication_single_primary_mode = ON
loose-group_replication_exit_state_action = OFFLINE_MODE
loose-group_replication_transaction_size_limit = 150000000 ###默认143M事务大小，最大值2147483647（大约2G），当此系统变量设置为0时，该组接受的事务大小没有限制。
loose-group_replication_enforce_update_everywhere_checks = OFF ###在单主模式下设置为OFF，多主模式下设置为ON。
offline_mode = ON
###MHA ############
relay_log_purge = 1
###################
###parallel replication####
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 24
slave_preserve_commit_order = ON
##################################
#########slow log#############
slow_query_log = 1
slow_query_log_file = /data/mysql/test/log/mysql-slow.log
#log-slow-verbosity = query_plan,explain
long_query_time = 5
#########error log#############
log-error = /data/mysql/test/log/error.log
log_timestamps = system
#######per_thread_buffers############
max_connections=4100
max_user_connections=4000
max_connect_errors=100000000
max_allowed_packet = 256M
table_open_cache = 6144
table_definition_cache = 4096
sort_buffer_size = 128K
read_buffer_size = 128K
read_rnd_buffer_size = 128k
join_buffer_size = 128K
tmp_table_size = 64M
max_heap_table_size = 64M
bulk_insert_buffer_size = 32M
thread_cache_size = 64
#thread_concurrency = 32
thread_stack = 256K
##MyISAM##
key_buffer_size = 64M
myisam_sort_buffer_size = 8M
concurrent_insert=2
low_priority_updates=1
myisam_recover_options=BACKUP,FORCE
######### InnoDB #############
innodb_adaptive_hash_index = 0
innodb_autoinc_lock_mode = 2
innodb_numa_interleave = 1
innodb_sort_buffer_size = 32M
innodb_rollback_on_timeout = 1
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 16
innodb_lru_scan_depth = 2048
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 95
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
log_bin_trust_function_creators = 1
innodb_flush_method = O_DIRECT
#innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_file_per_table = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_use_native_aio = 1
innodb_io_capacity = 800
innodb_purge_threads = 16
innodb_purge_batch_size = 32
innodb_old_blocks_time = 1000
innodb_change_buffering = all
transaction_isolation = READ-COMMITTED
innodb_stats_on_metadata = 0
####### NEW ########
##innodb_kill_idle_transaction = 5
###### SSD #########
#innodb_flush_neighbors = 0
#innodb_log_block_size = 4096
####################
[mysqldump]
quick
max_allowed_packet = 128M
[mysql]
no-auto-rehash
#prompt=(\\u@\\h) [\\d]>\\_
prompt=(\\u@\\h) [\\d]>\\_
[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 256k
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 65535
#flush_caches = 1
#numa_interleave = 1
#malloc-lib = /usr/lib64/libjemalloc.so
3、常见问题汇总
-
hostname查看，修改
1 2 3 4
#查看主机名 hostname #修改主机名 vim /etc/hostname
-
查看错误日志
/data/mysql/test/数据库服务名称/log/err.log
-
数据库服务重启
1 2
#注意：my_slaver01.cnf以当前节点服务配置文件为准 /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_slaver01.cnf --user=mysql &
-
卸载MGR
1 2
#在mysql库中执行 mysql>uninstall plugin group_replication;
-
The server is currently in offline mode
更改配置文件，关闭OFFLINE模式
-
mysql 自启动服务
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
#1设置服务 vim /usr/lib/systemd/system/mysql.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=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my_master.cnf #2注册服务 systemctl enable mysql
-
集群启动不起来
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
select * from performance_schema.replication_group_members; select * from performance_schema.replication_group_member_stats \g; show variables like "%group_replication_ip%" show variables like "%group_replication_exit_state_action%" SET @@GLOBAL.offline_mode = OFF; show global variables like 'group_replication_ip_whitelist'; select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member'; SET GLOBAL group_replication_ip_whitelist="100.4.17.5,100.4.17.4,100.4.17.9"; SET GLOBAL group_replication_bootstrap_group="OFF"; STOP GROUP_REPLICATION; start GROUP_REPLICATION; show slave status\G SHOW GRANTS FOR repl'%'; STOP GROUP_REPLICATION; stop slave; start GROUP_REPLICATION; reset master; reset slave; SELECT * FROM performance_schema.replication_group_members show global variables like '%gtid%' set sql_log_bin=0; #重启主从 STOP SLAVE; CHANGE MASTER TO MASTER_HOST='100.4.17.4',MASTER_USER='repl', MASTER_PASSWORD='sysrepl',MASTER_PORT=3306,MASTER_AUTO_POSITION = 1, MASTER_CONNECT_RETRY=10; START SLAVE; SET GLOBAL group_replication_bootstrap_group="OFF"; STOP SLAVE; SET GLOBAL slave_parallel_workers=0; STOP GROUP_REPLICATION; start GROUP_REPLICATION; START SLAVE;
1 2 3 4
特别注意： STEP 1 = SET GLOBAL group_replication_bootstrap_group="ON" SETP 2 = stop slave; STEP 3 = start GROUP_REPLICATION;
-
需要关闭SELinux
-
必须用一个主节点引导
-
SET @@GLOBAL.offline_mode = OFF;
-
Plugin group_replication reported: ‘Can’t start slave SQL THREAD of channel ‘’ when group replication is running with single-primary mode on a secondary member.’ 因为同时部署了主从，所以可以停掉
4、操作记录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
#master节点
#集群status
select * from performance_schema.replication_group_members;
show global variables like '%gtid%';
show variables like "%group_replication_ip%"
show variables like "%group_replication_exit_state_action%"
SET @@GLOBAL.offline_mode = OFF;
show global variables like 'group_replication_ip_whitelist';
select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member';
SET GLOBAL group_replication_ip_whitelist="100.4.17.5,100.4.17.4,100.4.17.9";
SET GLOBAL group_replication_bootstrap_group="OFF";
STOP GROUP_REPLICATION;
start GROUP_REPLICATION;
show slave status\G
SHOW GRANTS FOR repl'%';
STOP GROUP_REPLICATION;
stop slave;
start GROUP_REPLICATION;
reset master;
reset slave;
SELECT * FROM performance_schema.replication_group_members
show global variables like '%gtid%'
set sql_log_bin=0;
show master status;
#重启主从
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='100.4.17.5',MASTER_USER='repl',
MASTER_PASSWORD='sysrepl',MASTER_PORT=3306,MASTER_AUTO_POSITION = 1,
MASTER_CONNECT_RETRY=10;
START SLAVE;
SET GLOBAL slave_parallel_workers=0;
SET GLOBAL group_replication_bootstrap_group="OFF";
STOP SLAVE;
STOP GROUP_REPLICATION;
start GROUP_REPLICATION;
START SLAVE;
show global variables like 'server_uuid';
show global variables like 'super%';
#查看SLAVE状态
show master status;
show slave status;
show slave hosts;
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='100.4.17.4',MASTER_USER='repl',
MASTER_PASSWORD='sysrepl',MASTER_PORT=3306,MASTER_AUTO_POSITION = 1,
MASTER_CONNECT_RETRY=10; START SLAVE;
start slave;
STOP GROUP_REPLICATION;
stop slave;
reset master;
start GROUP_REPLICATION;
start slave;
slaver01
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
#集群status
select * from performance_schema.replication_group_members;
show global variables like '%gtid%'
#查看SLAVE状态
show master status;
show slave status;
#查看当前GTID
SELECT @@GLOBAL.GTID_EXECUTED;
select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member';
show variables like "%group_replication_ip%"
SET @@GLOBAL.offline_mode = OFF;
STOP SLAVE;
STOP GROUP_REPLICATION;
start GROUP_REPLICATION;
start slave;
STOP GROUP_REPLICATION;
stop slave;
reset master;
start GROUP_REPLICATION;
start slave;
show global variables like '%gtid%'
set sql_log_bin=0;
STOP SLAVE;
SET GLOBAL slave_parallel_workers=0;
START SLAVE;
#1、重启主从
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='100.4.17.4',MASTER_USER='repl',
MASTER_PASSWORD='sysrepl',MASTER_PORT=3306,MASTER_AUTO_POSITION = 1,
MASTER_CONNECT_RETRY=10; START SLAVE;
start slave;
#2、重启GR
STOP SLAVE;
STOP GROUP_REPLICATION;
start GROUP_REPLICATION;
start slave;
show slave status;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1
slaver02
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
#集群status
select * from performance_schema.replication_group_members;
show global variables like '%gtid%'
show master status;
show slave status;
show slave hosts;
STOP SLAVE;
STOP GROUP_REPLICATION;
start GROUP_REPLICATION;
start slave;
SET @@GLOBAL.offline_mode = OFF;
STOP GROUP_REPLICATION;
stop slave;
reset master;
start GROUP_REPLICATION;
start slave;
set sql_log_bin=0;
#重启主从
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='100.4.17.4',MASTER_USER='repl',
MASTER_PASSWORD='sysrepl',MASTER_PORT=3306,MASTER_AUTO_POSITION = 1,
MASTER_CONNECT_RETRY=10; START SLAVE;
start slave;
STOP GROUP_REPLICATION;
stop slave;
reset master;
start GROUP_REPLICATION;
start slave;
STOP SLAVE;
SET GLOBAL slave_parallel_workers=0;
START SLAVE;
