集群信息
一主两从
| 端口 | 角色 |
| 3307 | 主 |
| 3309 | 备 |
| 3303 | 备 |
从库故障
关掉 3303 从库
删除所有数据，模拟故障
从库恢复还原(物理备份恢复)
备份另一台 处于组关中的 从库的数据，端口为 3309
物理备份
xtrabackup --defaults-file=/etc/my3309.cnf --user=backup --password=123456 --parallel=4 --target-dir=/backup --backup --socket=/tmp/mysql3309.sock
备份到 /backup 目录下
3303从库数据还原
xtrabackup --defaults-file=/etc/my3303.cnf --target-dir=/backup --prepare ---准备 xtrabackup --defaults-file=/etc/my3303.cnf --target-dir=/backup --copy-back ---还原
修改目录权限
chown -R mysql.mysql /data/mysql3303
3303从库还原组关系
启动
root@LAPTOP-FPIQJ438:/mysql-5.7-3# ./bin/mysqld_safe --defaults-file=/etc/my3303.cnf & [3] 1392 root@LAPTOP-FPIQJ438:/mysql-5.7-3# Logging to '/data/mysql3303/LAPTOP-FPIQJ438.err'. 2024-02-22T03:23:18.518541Z mysqld_safe Starting mysqld daemon with databases from /data/mysql3303 root@LAPTOP-FPIQJ438:/mysql-5.7-3#
查看 3309 从库 物理备份信息中的 GTID值
root@LAPTOP-FPIQJ438:/mysql-5.7-3# more /backup/xtrabackup_binlog_info binlog.000006 1162 aadaaaaa-adda-adda-aaaa-aaaaaaddaaaa:1-1129
保存 aadaaaaa-adda-adda-aaaa-aaaaaaddaaaa:1-1129
从库还原组关系
mysql> reset master; Query OK, 0 rows affected (0.03 sec) mysql> mysql> mysql> mysql> CHANGE MASTER TO MASTER_USER='root', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 1 warning (0.02 sec) 由于没有保留auto.cnf 文件，需要重新指定组关系 mysql> mysql> set global gtid_purged='aadaaaaa-adda-adda-aaaa-aaaaaaddaaaa:1-1129'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (3.42 sec) mysql> mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | group_replication_applier | 6e001967-cfc4-11ee-992e-00155d92cd92 | LAPTOP-FPIQJ438 | 3309 | ONLINE | | group_replication_applier | a645bf19-cfc8-11ee-bf7e-00155d92cd92 | LAPTOP-FPIQJ438 | 3307 | ONLINE | | group_replication_applier | b95d4598-d131-11ee-8e8f-00155d92cb72 | LAPTOP-FPIQJ438 | 3303 | ONLINE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+ 3 rows in set (0.00 sec) mysql>
注意：由于模拟故障，将 3303从库 的所有信息全部删除，包括 auto.cnf 文件。该文件里保存着 从库的master信息。还原的时候 3303从库 需要 重新 change master to 加入组关系用户。建议保留 从库auto.cnf 文件
还原成功
从库恢复还原(逻辑备份恢复)
重新初始化 3303 从库(重新安装，模拟故障)
重新安装的库需要 下载 MGR 插件
root@LAPTOP-FPIQJ438:/mysql-5.7-3/bin# mysql -uroot -p -h127.0.0.1 -P3303 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.38-log MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> mysql> mysql> mysql> install PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.01 sec) mysql> show plugins; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 45 rows in set (0.00 sec) mysql>
逻辑备份
逻辑备份 另一台 从库 3309 的数据
mysqldump -u backup -p123456 --skip-add-drop-table --set-gtid-purged=on --routines --single_transaction --master-data=2 --all-databases --column-statistics=0 -S /tmp/mysql3309.sock > /backup/all.sql
注意：--set-gtid-purged 参数需要设置为on ,以便在 备份文件中可以看到 GTID
3303从库还原数据
root@LAPTOP-FPIQJ438:/mysql-5.7-3/bin# mysql -uroot -p123456 -h127.0.0.1 -P3303 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.38-log MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> source /backup/all.sql
3303从库还原组关系
查看 逻辑备份文件中的 GTID
root@LAPTOP-FPIQJ438:/mysql-5.7-3/bin# more /backup/all.sql -- MySQL dump 10.13 Distrib 8.0.32, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 5.7.38-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */; /*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @@SESSION.SQL_LOG_BIN= 0; -- -- GTID state at the beginning of the backup -- SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'aadaaaaa-adda-adda-aaaa-aaaaaaddaaaa:1-1130'; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000006', MASTER_LOG_POS=1486; -- -- Current Database: `mysql` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 */; USE `mysql`;
GTID为 aadaaaaa-adda-adda-aaaa-aaaaaaddaaaa:1-1130，将该值保存
从库还原组关系
mysql> mysql> reset master; Query OK, 0 rows affected (0.02 sec) mysql> mysql> mysql> mysql> CHANGE MASTER TO MASTER_USER='root', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 1 warning (0.04 sec) mysql> mysql> mysql> mysql> set global gtid_purged='aadaaaaa-adda-adda-aaaa-aaaaaaddaaaa:1-1130'; Query OK, 0 rows affected (0.01 sec) mysql> mysql> mysql> mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (3.04 sec) mysql> mysql> mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | group_replication_applier | 6e001967-cfc4-11ee-992e-00155d92cd92 | LAPTOP-FPIQJ438 | 3309 | ONLINE | | group_replication_applier | 80a3a411-d133-11ee-9ea9-00155d92cb72 | LAPTOP-FPIQJ438 | 3303 | ONLINE | | group_replication_applier | a645bf19-cfc8-11ee-bf7e-00155d92cd92 | LAPTOP-FPIQJ438 | 3307 | ONLINE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+ 3 rows in set (0.00 sec) mysql> mysql>
还原组关系成功
总结
1.这两种还原方法同样适用于 MGR中加入新节点。
2.从节点组关系恢复 或者 组关系中新加入节点，从节点数据恢复同步是一方面，更重要的是gtid_purged值，组关系靠GTID值来同步事务。在各节点数据一致的情况下，gtid_purged值不同也会导致还原失败。如果从库gtid_purged值不是组关系成员中的gtid_purged值。 加入组关系时 从库 会从初始的 gtid_purged值同步。导致 重复执行 其它组成员已经执行过的 gtid 事务，日志中会报 数据已存在，同步停止 的错误。
3.还原故障节点组关系 或者 新加入成员到组关系。还原时 目标成员的 gtid_purged值 的设置 应该以备份集中的 gtid_purged值 为准 。物理备份 查看 备份集中的 xtrabackup_binlog_info 文件。逻辑备份 备份时需要将--set-gtid-purged 参数 需要设置为on，之后查看备份集。
