mgr--mysql-故障恢复

故障场景1：节点因数据不一致执行语句报错，删除找不到对应记录

1.1 故障模拟

创建一个库后创建本表

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
use test;

CREATE TABLE `newtable` (
  `Column1` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Column1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

模拟数据不一致，mysql1节点关闭binlog执行语句

set SQL_LOG_BIN=0;
insert into newtable values(1);
set SQL_LOG_BIN=1;

delete from newtable;

查看mysql2和mysql3都是error,并且为只读模式

SELECT * FROM performance_schema.replication_group_members;

 cat /var/log/mysql.log

 1.2故障排查

查看错误节点最后执行的事务id

show master status;

mysql1

 mysql2和3error节点

 可以知道是error节点只执行到aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:38事务，执行39事务报错。

aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaa为同步binlog节点的uuid(正常为主节点)，1-38是指执行了1至38的事务。

在正常节点查询binlog,看39事务sql

#shell使用此工具查询
mysqlbinlog --no-defaults -vv --base64-output=decode-rows --include-gtids='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:39' /var/lib/mysql/binlog.000002
#grep aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:39方便定位具体sql
mysqlbinlog --no-defaults -vv --base64-output=decode-rows --include-gtids='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:39' /var/lib/mysql/binlog.000002 -vv |grep -B 1 -A 15 "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:39"

找到事务位置，看到最后的操作是删除此表第一列值=1的数据时出错了

 1.3 故障恢复-补缺失数据

知道这条数据是什么，补上这条数据继续同步。

error节点补数据一定要set SQL_LOG_BIN=0;否则执行的sql会写入binlog中，之后被同步给其他节点

set SQL_LOG_BIN=0;
set global super_read_only=0;
set global read_only=0;
use test;
insert into newtable values(1);
set global super_read_only=1;
set global read_only=1;
set SQL_LOG_BIN=1;

#再次进组
stop group_replication;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='Aa.1234567';

 查看集群状态和show master status;

 1.4 故障恢复-跳过报错事务继续同步

error节点创建一个空事务，跳过本次执行。

一次提交只占用一个事务id。

stop group_replication;
#设置当前会话的GTID_next变量。GTID_next变量用于指定下一个事务的GTID
SET @@SESSION.GTID_NEXT='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:39';
#开始一个新的事务
BEGIN; 
#提交当前的事务，在事务被提交后，GTID_next变量通常会自动递增，准备分配给下一个事务。
COMMIT;
#将GTID_next变量设置为自动模式
SET SESSION GTID_NEXT = AUTOMATIC;

START GROUP_REPLICATION USER='rpl_user', PASSWORD='Aa123456';

 查看集群状态和show master status;

3.克隆重做节点

在MySQL 8.0.17版本中引入了新的插件clone plugin。克隆插件允许克隆本地或远程MySQL服务器实例中的数据。克隆数据是存储在InnoDB中的数据的物理快照，包括模式、表、表空间和数据字典元数据。克隆的数据包括一个功能完整的数据目录，它允许使用克隆插件用于MySQL服务器配置。clone plugin的可应用场景：
1.数据物理热备：(本地 or 远程)
2.快速构建集群从节点的复制
3.MGR节点的快速扩充

 

3.1 接收端安装clone plugin克隆插件

如果mysqld服务尚未启动，则在my.cnf中配置初始化插件加载:

[mysqld]
plugin-load-add=mysql_clone.so

或者在mysql运行时加载插件

INSTALL PLUGIN clone SONAME 'mysql_clone.so';

3.2 本地克隆(clone data locally)

参考文档：https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-local.html

创建克隆用户并授予backup_admin权限

mysql> CREATE USER 'clone_user'@'%' IDENTIFIED BY 'Aa.1234567';
mysql> GRANT BACKUP_ADMIN ON *.* TO 'clone_user';

本地克隆语法：

mysql> CLONE LOCAL DATA DIRECTORY = '/path/to/clone_dir';

其中/path/to/clone_dir是数据克隆到的本地目录的完整路径。需要一个绝对路径，并且指定的目录(" clone_dir ")必须不存在，但是指定的路径必须是存在的路径。MySQL服务器必须具有创建目录所需的写访问权限

案例：

mysql > CLONE LOCAL DATA DIRECTORY = '/mysql/bak'

 3.3 远程克隆(clone remote data)

参考文档：https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-remote.html

远程克隆前置条件：

1. 克隆接收方和提供方均需要clone user，在提供方上，克隆用户需要BACKUP_ADMIN权限（show grants for 'user'@'localhost';不显示此权限）来访问和传输来自提供方的数据，以及在克隆操作期间阻止DDL。在接收方上，克隆用户需要CLONE_ADMIN特权来替换接收方数据、在克隆操作期间阻止DDL以及自动重新启动服务器。CLONE_ADMIN特权隐含地包括BACKUP_ADMIN和SHUTDOWN特权。
2. 克隆操作期间不允许使用 DDL，允许并发DML
3. 克隆接收方和提供方必须有相同的mysql server版本，且必须8.0.17+
4. 克隆接收方和提供方必须是相同的操作系统平台。
5. 接收方必须有足够的磁盘空间存放克隆的数据。默认情况下，在克隆提供数据之前会删除接收方数据，因此只需要足够的空间来存储克隆数据。如果使用DATA directory子句克隆到一个指定目录，则必须有足够的磁盘空间用于现有的接收方数据和克隆的数据。
6. InnoDB允许在数据目录之外创建一些表空间类型。如果提供的MySQL服务器实例有位于数据目录之外的表空间，克隆操作必须能够访问这些表空间
7. 克隆接收方和提供方必须具有相同的字符集和collation字符排序规则
8. 克隆接收方和提供方必须具有相同的innodb_page_size和innodb_data_file_path参数设置
9. 如果克隆加密或页面压缩的数据，则提供方和接收方必须具有相同的文件系统块大小
10. 如果要克隆加密的数据，则需要配置SSL加密安全连接
11. 克隆接收方的clone_valid_donor_list设置必须包含提供方MySQL服务器实例的主机地址
12. 同一时间只能有一个克隆操作
13. 克隆插件传输1MB包和元数据。因此，在提供方和接收方MySQL服务器实例上，所需的最小max_allowed_packet值为2MB
14. 克隆接收方也需要启动mysqld服务

3.3.1 源端授予提供用户BACKUP_ADMIN权限

GRANT BACKUP_ADMIN ON *.* TO 'source_user';

3.3.2 接收端创建克隆用户并授予clone admin权限

CREATE USER 'recipient_clone_user'@'%' IDENTIFIED BY 'Aa.1234567';
GRANT CLONE_ADMIN on *.* to 'recipient_clone_user'@'%';

3.3.3 添加克隆提供方的ip列表与端口至clone_valid_donor_list

SET GLOBAL clone_valid_donor_list = 'mysql1:3306';

3.3.4 设置网络宽带上限为5M,默认应该无上限

set global clone_max_network_bandwidth=5;
exit;

3.3.5 切换至克隆账号

mysql -urecipient_clone_user -pAa.1234567

3.3.6 克隆至指定位置（不影响本实例）

CLONE INSTANCE FROM 'source_user'@'host':port IDENTIFIED BY 'password' [DATA DIRECTORY [=] 'clone_dir'] [REQUIRE [NO] SSL];
#CLONE INSTANCE FROM 'user'@'host:3306' IDENTIFIED BY 'password' DATA DIRECTORY = '/path/to/clone_dir' REQUIRE SSL;
#DATA DIRECTORY指定存储克隆数据的目录，如果省略则使用mysql数据目录
#REQUIRE SSL:要求使用SSL连接进行克隆操作，以增强数据传输的安全性。如果环境中没有配置SSL，可以省略这个选项或者使用REQUIRE NO SSL

其中/path/to/clone_dir是数据克隆到的本地目录的完整路径。需要一个绝对路径，并且指定的目录(" clone_dir ")必须不存在，但是指定的路径必须是存在的路径。MySQL服务器必须具有创建目录所需的写访问权限

3.3.7 克隆覆盖自身（默认方式，提供方数据直接克隆到接收方，克隆后接收方实例自动重启）

CLONE INSTANCE FROM 'user'@'mysql1':3306 IDENTIFIED BY 'Aa.1234567';

3.3.8 查看克隆进度和状态

如果覆盖自身克隆已完成并重启，则无法查看进度状态

#查看克隆进度
 SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
#查看克隆状态
select
    state,
    cast(begin_time as datetime) as "start time",
    case
        when end_time is null then lpad(sys.format_time(power(10, 12) * (unix_timestamp(now()) - unix_timestamp(begin_time))), 10, ' ')
        else lpad(sys.format_time(power(10, 12) * (unix_timestamp(end_time) - unix_timestamp(begin_time))), 10, ' ')
    end as duration,
    source,
    destination,
    binlog_file,
    binlog_position,
    gtid_executed
from
    performance_schema.clone_status G;

案例：

3.3.9 重新加入集群

复制结束换账号登录可以直接重新加入集群

mysql -uroot -pAa.1234567
START GROUP_REPLICATION USER='rpl_user', PASSWORD='Aa.1234567';

 

 如何恢复损坏的innodb数据

检查表数据块是否损坏

$ mysqlcheck -c newmandela order -uroot -p   #检查特定表

$ mysqlcheck -c newmandela -uroot -p   #检查一个库中所有表

$mysqlcheck -c --all-databases -uroot -p   #检查所有库中所有表

$ mysqlcheck -c --databases newmandela radius -uroot -p  #检查几个库的表

使用 mysqlcheck 分析表，未实验

$ mysqlcheck -a radius payment_transactionrecord  -uroot -p
Enter password:
radius.payment_transactionrecord                   Table is already up to date

使用 mysqlcheck 优化表，未实验

# mysqlcheck -o radius payment_transactionrecord  -uroot -p
Enter password:
radius.payment_transactionrecord                   OK

使用 mysqlcheck 修复表，未实验

# mysqlcheck -r radius payment_transactionrecord  -uroot -p
Enter password:
radius.payment_transactionrecord                   OK

 

以下内容借鉴高性能mysql第3版，未经实验。

二级索引损坏

• 一般可以用optimize table来修复损坏的二级索引，会锁表。
• 也可以用select into outfile,删除和重建表，然后load data infile的方法

这些都是通过构建一个新表重建受影响的索引来修复损坏的索引数据。

聚簇索引损坏

如果聚簇索引损坏，只能用innodb_force_recovey选项来导出表，此参数通常为0正常启动，把值调高可能在强制恢复中造成数据缺失，个人认为不要改动此值，还是备份或重做节点恢复。

损坏系统结构

系统结构包括innodb事务日志，表空间的撤销日志区域和数据字典。这种损坏可能需要做整个数据库的导出和还原，因为innodb内部绝大部分的工作都可能受到影响。

 

如果innodb的数据已经损坏到根本不能启动mysql的程度，可以使用innodb recovery toolkit从表空间的数据文件中直接抽取数据