MySQL异常恢复-drop表恢复
MySQL异常恢复-drop表恢复
恢复思路:
1、通过延迟从库进行恢复
2、通过备份+时间点恢复
3、通过undrop_for_innodb进行恢复
4、通过磁盘恢复工具找回相应的文件,然后想办法恢复
概述:
此主要介绍undrop_for_innodb的恢复方法
环境:mysql 5.7.26
OS:CentOS Linux release 7.6.1810
mysql参数:innodb_file_per_table=ON场景
注意:一旦有误操作,请停止数据库写入,避免磁盘数据页被覆盖
另:数据库一定要备份、备份、备份
一、安装undrop-for-innodb工具
yum install -y make gcc flex bison
git clone https://github.com/twindb/undrop-for-innodb.git
cd undrop-for-innodb
make
make sys_parser
该工具实际也可找回被delete的数据
二、恢复drop表
innodb_file_per_table=ON场景下,drop表后,.ibd文件也被删除了,需要扫描磁盘获取相应的数据页进行恢复。而如果是innodb_file_per_table=OFF,则数据存放在共享表空间ibdata1里的,只要扫描该ibdata1文件即可。
此以innodb_file_per_table=ON场景下恢复为例。
1、stream_parser解析系统表空间
./stream_parser -f /dev/mapper/centos-root -s 1G -t 28G
运行完成后,会在当前目录下生成pages-centos-root, 目录下按照每个页为一个文件,分为索引页和数据较大的 BLOB 页,我们访问系统表的话,是存在索引页中的
#-s:表示使用多少内存来扫描磁盘
#-t:扫描磁盘的大小,此以整个磁盘分区大小为界限(如果是共享表空间则不需要改参数)
#-f:mysql数据datadir的所在的文件系统
2 手工查找table id
./c_parser:
Error: Usage: ./c_parser [-4|-5|-6] [-dDV] -f <InnoDB page or dir> -t table.sql [-T N:M] [-b <external pages directory>]
Where
-f <InnoDB page(s)> -- InnoDB page or directory with pages(all pages should have same index_id)
-t <table.sql> -- CREATE statement of a table
-o <file> -- Save dump in this file. Otherwise print to stdout
-l <file> -- Save SQL statements in this file. Otherwise print to stderr
-h -- Print this help
-d -- Process only those pages which potentially could have deleted records (default = NO)
-D -- Recover deleted rows only (default = NO)
-U -- Recover UNdeleted rows only (default = YES)
-V -- Verbose mode (lots of debug information)
-4 -- innodb_datafile is in REDUNDANT format
-5 -- innodb_datafile is in COMPACT format
-6 -- innodb_datafile is in MySQL 5.6 format
c_parser can detect REDUNDANT or COMPACT, so -4 and -5 are optional. If you use MySQL 5.6+ however, -6 is necessary
-T -- retrieves only pages with index id = NM (N - high word, M - low word of id)
-b <dir> -- Directory where external pages can be found. Usually it is pages-XXX/FIL_PAGE_TYPE_BLOB/
-i <file> -- Read external pages at their offsets from <file>.
-p prefix -- Use prefix for a directory name in LOAD DATA INFILE command
-x -- Print text values in hexadecimal format.
./c_parser 参数根据实际调整
mkdir dumps/default
./c_parser -6f pages-centos-root/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql 2> dumps/default/SYS_TABLES.sql | grep "testdb/t\\\_wangzx" > dumps/default/SYS_TABLES
./c_parser -6Df pages-centos-root/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql 2> dumps/default/SYS_TABLES.sql | grep "testdb/t\\\_wangzx" >> dumps/default/SYS_TABLES
注意:实际表名为t_wangzx,由于转义问题此t\\\_wangzx
此确认table id为40,后续操作需要筛选该ID
3 通过table id查看page相关信息
此步骤实际可获取到相应的字段信息、索引信息、数据页的信息
# 获取列相关信息
pages=pages-centos-root/FIL_PAGE_INDEX/0000000000000002.page
t=SYS_COLUMNS
./c_parser -6f $pages -t dictionary/$t.sql 2> dumps/default/$t.sql | awk '($4 == 40 || $4 == 70){print}' > dumps/default/$t
./c_parser -6Df $pages -t dictionary/$t.sql 2> dumps/default/$t.sql | awk '($4 == 40 || $4 == 70){print}' > dumps/default/$t
# 获取索引相关信息(此可获取到聚集索引所在数据页41)
pages=pages-centos-root/FIL_PAGE_INDEX/0000000000000003.page
t=SYS_INDEXES
./c_parser -6f $pages -t dictionary/$t.sql 2> dumps/default/$t.sql | awk '($4 == 40 || $4 == 77){print}' > dumps/default/$t
./c_parser -6Df $pages -t dictionary/$t.sql 2> dumps/default/$t.sql | awk '($4 == 40 || $4 == 77){print}' > dumps/default/$t
# 获取字段相关信息(41为聚集数据页)
pages=pages-centos-root/FIL_PAGE_INDEX/0000000000000004.page
t=SYS_FIELDS
./c_parser -6f $pages -t dictionary/$t.sql 2> dumps/default/$t.sql | awk '($4 == 41){print}' > dumps/default/$t
./c_parser -6Df $pages -t dictionary/$t.sql 2> dumps/default/$t.sql | awk '($4 == 41){print}' > dumps/default/$t
4、获取表结构
如果已知建表语句,可忽略此步骤
实际可根据上述信息来获取表结构,也可以用recover_dictionary.sh脚本和sys_parse来获取建表语句
此通过手工执行脚本来恢复表结构
mysql -e "create database test;"
for t in SYS_TABLES SYS_COLUMNS SYS_INDEXES SYS_FIELDS
do
echo -n "$t ... "
mysql test < dictionary/$t.sql
done
for t in SYS_TABLES SYS_COLUMNS SYS_INDEXES SYS_FIELDS
do
echo -n "$t ... "
mysql test < dumps/default/$t.sql
if [ $? -ne 0 ];then
echo "ERROR: $t"
break
fi
nr=`less dumps/default/$t | grep -v -- '--'| wc -l`
echo -n "$nr recs "
echo "OK"
done
./sys_parser -h127.0.0.1 -uroot -p123456 -d test testdb/t_wangzx > dumps/default/create_t_wangzx.sql
source dumps/default/create_t_wangzx.sql
使用recover_dictionary.sh脚本报错,此跳过不使用
更改43、50、58行连接mysql的方式
或者配置/etc/my.cnf,使得mysql可以自动登录相应的数据库
vim /etc/my.cnf
[mysql]
user=root
password=123456
socket=/data/mysql57/3306/sock/mysql3306.sock
执行recover_dictionary.sh脚本
该脚本默认会创建test数据库
执行时报错:
SYS_TABLES ... ERROR 1300 (HY000) at line 2: Invalid utf8 character string: '"
由于dumps/default/SYS_COLUMNS等文件内容存在特殊的符号无法解析引起
如:dumps/default/SYS_TABLES,手工删除此类行,drop database test;然后再次执行,也可以手工去mysql test < dumps/default/SYS_TABLES.sql来导入数据字典信息
5、解析数据页(根据聚集索引页41)
./c_parser -6f pages-centos-root/FIL_PAGE_INDEX/0000000000000041.page -t dumps/t_wangzx.sql > dumps/default/t_wangzx 2> dumps/default/t_wangzx.sql
实际查看该文件,存在无效的数据,如下。这些数据需要手工删除
通过如下脚本过滤所需数据:
mv dumps/default/t_wangzx dumps/default/t_wangzx.bak
cat dumps/default/t_wangzx.bak | awk '($4 > 0){print}' > dumps/default/t_wangzx
6、导入数据
mysql testdb < dumps/default/t_wangzx.sql
此时大多数数据已经找回,如果磁盘被覆盖了,可能存在数据缺失情况