1.开始准备(all 节点)
[root@mpb91 ~]# systemctl stop mysqld.service
[root@mpb91 ~]# cd /usr/local/mysql
mv data/ data_bak/
mv redo/ redo_bak/
mv binlog/ binlog_bak/
mkdir data
mkdir redo
mkdir binlog
or
rm -rf /usr/local/mysql/binlog/*
rm -rf /usr/local/mysql/data/*
rm -rf /usr/local/mysql/redo/*
[root@mpb91 mysql]#
ls /u05/bak/MYSQL192.168.102.211/all_20210728014349
2.准备(prepare)一个完全备份 (all 节点)
[root@mpb91 mysql]#
innobackupex --apply-log /u05/bak/MYSQL192.168.102.211/all_20210728014349/
3.临时注解配置 (all 节点)
vim /usr/local/mysql/etc/my.cnf
#innodb_undo_directory=/usr/local/mysql/undo
#innodb_undo_tablespaces=4
4.#执行恢复操作 (all 节点)
[root@mpb91 pxb]#
innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --copy-back --rsync /u05/bak/MYSQL192.168.102.211/all_20210728014349/
5.更改 data/ 等 目录权限 (all 节点)
cp /usr/local/mysql/data_bak/auto.cnf /usr/local/mysql/data/
chown -Rf mysql:mysql /usr/local/mysql
- 去掉步骤3中的 临时注解 (all 节点)
7.1安装MGR前结构整理 (仅仅 91 主节点)
systemctl restart mysqld
mysql.login
#创建主键（所有表都必须有主键）
#将表的引擎改成 InnerDB（所有表都要）
--查詢是否存在無主鍵的表（如返回數據則需要修正）
select
table_schema,
table_name ,table_type
from information_schema.tables
where (table_schema,table_name) not in
(
select distinct table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI'
)
and table_schema not in
(
'sys','mysql','information_schema','performance_schema'
) and table_type!='VIEW';
--查詢是否存在非InnoDB表（如返回數據則需要修正）
select table_schema,table_name,engine from information_schema.tables where engine!='innodb' and table_schema not in('mysql','information_schema','performance_schema');
##必須執行
reset master;
7.2 安装MGR (all 节点)
配置完成后, 要一次启动数据库，安装MGR插件，设置复制账号
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SET SQL_LOG_BIN=0;
# CREATE USER repl@'%' IDENTIFIED BY 'Reppw_123'; (已存在无需新建）
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Reppw_123' FOR CHANNEL 'group_replication_recovery';
8.启动MGR单主模式
8.1 主库(91)节点加入MGR集群
1) 启动MGR，在主库(211)节点上上执行
root@mpb91:(none) [:30: ] 1 SQL->
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;
8.2 其他节点加入MGR集群，在从库（92.93）上执行
root@mpb92:(none) [:36: ] 1 SQL->
START GROUP_REPLICATION;
#報錯則如下
set global group_replication_allow_local_disjoint_gtids_join=ON;
START GROUP_REPLICATION;
再次查看MGR组信息 (在三个MGR节点上都可以查看)
root@mpb92:(none) [:37: ] 4 SQL->
SELECT * FROM performance_schema.replication_group_members;
9.查看master
方法1
root@mpb92:(none) [:50: ] 13 SQL->
SELECT ta.* ,tb.MEMBER_HOST,tb.MEMBER_PORT,tb.MEMBER_STATE FROM
performance_schema.global_status ta,performance_schema.replication_group_members tb
WHERE ta.VARIABLE_NAME='group_replication_primary_member' and ta.VARIABLE_VALUE=tb.MEMBER_ID;
+----------------------------------+--------------------------------------+-------------+-------------+--------------+
| VARIABLE_NAME | VARIABLE_VALUE | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+----------------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_primary_member | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211 | 3306 | ONLINE |
+----------------------------------+--------------------------------------+-------------+-------------+--------------+
方法2
root@mpb92:(none) [:52: ] 18 SQL->
SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME='group_replication_primary_member';
+--------------------------------------+
| VARIABLE_VALUE |
+--------------------------------------+
| 2116c7e9-63c9-11eb-a1ba-000c299e2211 |
+--------------------------------------+
方法3
root@mpb92:(none) [:53: ] 19 SQL->SHOW STATUS LIKE 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 2116c7e9-63c9-11eb-a1ba-000c299e2211 |
+----------------------------------+--------------------------------------+
[root@mpb94 ~]# mysqlsh --uri root@mpb91:3306
var cluster = dba.getCluster('myCluster')
報錯則如下
var cluster = dba.createCluster('myCluster', {adoptFromGR: true});
cluster.status()
[root@mpb94 local]# pwd
/usr/local
[root@mpb94 ~]# systemctl stopmysqlrouter
/usr/local/mysql-route/bin/mysqlrouter --bootstrap root@mpb91:3306 -d myrouter --user=root
[root@mpb94 ~]# systemctl start mysqlrouter
[root@mpb94 ~]# systemctl status mysqlrouter
[root@mpb94 ~]# ps -ef|grep myroute
root 9244 1 0 13:19 ? 00:00:00 /usr/local/mysql-route/bin/mysqlrouter -c /usr/local/myrouter/mysqlrouter.conf
root 9283 9199 0 13:21 pts/1 00:00:00 grep --color=auto myroute
[root@mpb94 ~]# netstat -tunlp | grep 9244
tcp 0 0 0.0.0.0:64460 0.0.0.0:* LISTEN 9244/mysqlrouter
tcp 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 9244/mysqlrouter
tcp 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 9244/mysqlrouter
tcp 0 0 0.0.0.0:64470 0.0.0.0:* LISTEN 9244/mysqlrouter
測試
[root@mpb91 ~]# mysql -u root -h mpb94 -P 6446 -p
