0%

MySQL数据备份恢复

数据库备份恢复是运维工作的重点,当发生数据丢失或异常,需要通过备份等手段恢复数据,保护数据安全。

备份类型分为三种:冷备、热备和温备,冷备即将数据库服务停止后对文件进行拷贝;热备即在线备份,备份过程中不影响业务正常运行;温备即对数据库全局加锁,禁止数据更新以达到备份一致性。

备份方式通常采用逻辑备份或物理备份,逻辑备份速度较慢,备份灵活,可针对实例、数据库、表、对象等进行备份,且不受版本影响;物理备份速度较快,备份不够灵活,跨版本之前可能存在兼容性问题。

制定备份策略应该综合考虑备份空间大小和恢复速度,全备周期短,空间使用就大;全备周期长,恢复起来就慢。

逻辑备份

mysqldump

常用参数

参数 描述
-u,–user 指定连接的用户名
-p,–password 指定连接的用户密码
-S,–socket 指定sock文件连接
-h,–host 指定连接的服务器,IP或者主机名
-P,–port 指定连接服务器端口
-tables 导出指定的表对象,默认会覆盖-B,–databases参数
–single-transaction 在备份前开启一个事务,来获得备份一致性,仅针对innodb,但是无法隔离DDL操作。启用该参数时将禁用–lock-tables参数
-A,–all-databases 备份所有数据库
-B,–databases 备份指定数据库
–default-character-set 设定字符集
-l,–lock-tables 以只读方式依次锁定每个库的所有表
-x,–lock-all-tables 备份过程锁定所有库所有表
–add-drop-database 在创建任何数据库语句前附加drop database语句
–add-drop-table 在任何建表语句前附加drop table语句
-add-locks 在生成的insert语句前附加lock语句,默认启用。可以使用–skip-add-locks禁用
-master-data 该参数只有1和2两个值,用于在主库备份时添加change master语句,值为2时该语句会被注释
-dump-slave 该参数只有1和2两个值,用于在从库备份时添加change master语句,值为2时该语句会被注释
-t,–no-create-info 只导出数据,不导出表结构
-d,–no-data 只导出表结构,不导出数据
-c,–complete-insert 使用完整的insert语句,其中包含字段信息,可以提高插入效率
-q,–quick 表示导出时不会将数据加载至buffer,默认启用
-w,–where 指定导出条件
–set-gtid-purged 忽略当前数据库GTID

备份原理

mysqldump是mysql自带的逻辑导出备份工具,支持任意数据库引擎,工具会将数据导出为SQL脚本。备份恢复速度较慢,但比较灵活,支持多粒度的导出选择。

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
| 2020-06-16 05:49:14.220995 |    101394 |    139163 | Query        | FLUSH /*!40101 LOCAL */ TABLES                                                                  
| 2020-06-16 05:49:14.242835 | 101394 | 139163 | Query | FLUSH TABLES WITH READ LOCK
| 2020-06-16 05:49:14.244013 | 101394 | 139163 | Query | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
| 2020-06-16 05:49:14.244823 | 101394 | 139163 | Query | START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
| 2020-06-16 05:49:14.245000 | 101394 | 139163 | Query | SHOW MASTER STATUS
| 2020-06-16 05:49:14.245188 | 101394 | 139163 | Query | UNLOCK TABLES
| 2020-06-16 05:49:14.245448 | 101394 | 139163 | Query | SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME |
| 2020-06-16 05:49:14.248891 | 101394 | 139163 | Query | SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
| 2020-06-16 05:49:14.249597 | 101394 | 139163 | Query | SHOW DATABASES
| 2020-06-16 05:49:14.263663 | 101394 | 139163 | Query | SHOW VARIABLES LIKE 'ndbinfo\_version'
| 2020-06-16 05:49:14.270093 | 101394 | 139163 | Init DB | mysql
| 2020-06-16 05:49:14.270251 | 101394 | 139163 | Query | SHOW CREATE DATABASE IF NOT EXISTS `mysql`
| 2020-06-16 05:49:14.270388 | 101394 | 139163 | Query | SAVEPOINT sp
| 2020-06-16 05:49:14.270514 | 101394 | 139163 | Query | show tables
| 2020-06-16 05:49:14.272480 | 101394 | 139163 | Query | show table status like 'columns\_priv'
| 2020-06-16 05:49:14.282748 | 101394 | 139163 | Query | SET SQL_QUOTE_SHOW_CREATE=1
| 2020-06-16 05:49:14.282901 | 101394 | 139163 | Query | SET SESSION character_set_results = 'binary'
| 2020-06-16 05:49:14.283031 | 101394 | 139163 | Query | show create table `columns_priv`
| 2020-06-16 05:49:14.283220 | 101394 | 139163 | Query | SET SESSION character_set_results = 'utf8'
| 2020-06-16 05:49:14.283357 | 101394 | 139163 | Query | show fields from `columns_priv`
| 2020-06-16 05:49:14.284576 | 101394 | 139163 | Query | show fields from `columns_priv`
| 2020-06-16 05:49:14.285229 | 101394 | 139163 | Query | SELECT /*!40001 SQL_NO_CACHE */ * FROM `columns_priv`
| 2020-06-16 05:49:14.285492 | 101394 | 139163 | Query | SET SESSION character_set_results = 'binary'
| 2020-06-16 05:49:14.285612 | 101394 | 139163 | Query | use `mysql`
| 2020-06-16 05:49:14.285757 | 101394 | 139163 | Query | select @@collation_database
| 2020-06-16 05:49:14.285915 | 101394 | 139163 | Query | SHOW TRIGGERS LIKE 'columns\_priv'
| 2020-06-16 05:49:14.286655 | 101394 | 139163 | Query | SET SESSION character_set_results = 'utf8'
| 2020-06-16 05:49:14.286817 | 101394 | 139163 | Query | ROLLBACK TO SAVEPOINT sp

上述是一个完整备份产生的general log,来根据其中的信息分析一个备份执行过程:

  1. 导出选项设置了master_data,它会通过flush table with read lock来获取全局读锁
  2. 导出选项设置了single-transaction,它会将事务隔离级别设置为REPEATABLE READ(仅针对当前会话),并开启一个一致性快照读事务来保证备份数据一致性
  3. show master status获取binlog_file和binlog_pos
  4. 释放全局读锁
  5. 创建一个savepoint保存点
  6. 查询表的结构及数据并导出
  7. 数据表导出后,回滚到之前的save point,这样就不会阻塞已备份的表的DDL操作,需要注意的是这并不会回滚之前快照读事务
  8. 循环执行5-7,直到导出完成退出

示例

备份所有数据库

1
$ mysqldump -uroot -p -A > alldb-`date +%Y%m%d`.sql

备份指定数据库

1
$ mysqldump -uroot -p -B test > testdb-`date +%Y%m%d`.sql

备份指定表

1
$ mysqldump -uroot -p test tab1 > tab-`date +%Y%m%d`.sql

备份tab1中ID大于101的数据

1
$ mysqldump -uroot -p test tab1 --where='id>1' > tabwhere-`date +%Y%m%d`.sql

备份存储过程和函数

1
$ mysqldump -uroot -p -R -ndt test > proc-`date +%Y%m%d`.sql

压缩备份

1
$ mysqldump -uroot -p –B test | gzip >db-`date +%Y%m%d`.sql

数据恢复

1
$ mysql -uroot -p < testdb-20191129.sql

解压备份并恢复

1
$ gunzip < tab-20190814.sql.gz | mysql -uroot -p

Tips:也可以在mysql命令行中通过source命令执行sql脚本

mysqlpump

mysqlpump是MySQL5.7新增的一个逻辑备份工具,它的使用方法和选项基本都与mysqldump非常类似。mysqlpump最大的升级便是支持基于库和表的并行导出,mysqlpump的并行架构为:队列+线程,其允许通过参数–parallel-schemas指定数据库队列数,通过参数–default-parallelism设置并行线程数。

pump

相对mysqldump另一个优化提升就是在于mysqlpump是会延迟创建索引,也就是会把创建索引的步骤放在脚本的最尾端,当数据导入完成之后才会开始创建索引,加快数据导入。

利用3个队列,5个并行线程来完成数据导出

1
mysqlpump -uroot -p --single-transaction --default-character-set=utf8  --parallel-schemas=3 --default-parallelism=5 -A > /backup/alldb.sql

Tips:更多详情请参考mysqlpump

mydumper

mydumper是由Facebook开发的另一套逻辑备份工具,主要特性如下:

  • 导出和导入都支持并行,比mysqldump要快
  • 具有事务性和非事务性表一致的快照
  • 可快速进行文件压缩
  • 支持导出binlog
  • 可采用守护进程的方式定时扫描和输出连续的二进制日志
  • 支持按照指定文件大小切割备份文件

工作流程

  1. FLUSH TABLES WITH READ LOCK,施加全局只读锁
  2. 读取当前时间点的二进制日志文件名和postion记录在metadata中,
  3. START TRANSACTION WITH CONSISTENT SNAPSHOT,开启读一致性事务
  4. 启动N个线程导出数据,默认4个
  5. dump non-InnoDB tables,备份非事务型数据表
  6. UNLOCK TABLES,释放全局只读锁
  7. dump InnoDB tables,基于事务导出innodb表

mydumper

安装使用Download

安装依赖

1
$ yum install glib2-devel mysql-devel zlib-devel pcre-devel cmake gcc-c++ -y

解压安装包

1
$ tar –xvf mydumper-0.9.1.tar.gz

编译安装

1
2
3
$ cd mydumper-0.9.1
$ cmake .
$ make && make install -j 4

常用参数

mydumper备份参数

参数 描述
-B,–database 指定备份的数据库
-T,–tables-list 指定备份表,多个表用逗号分隔
-O,–omit-from-file 包含要跳过的database.table条目的文件,每行一个,在正则表达式之前应用
-o,–outputdir 输出文件的目录
-s,–statement-size 生成insert语句的字节数,默认1000000
-r,rows 将表按行分块时,指定的块行数,覆盖–chunk-filesize
-F,–chunk-filesize 将表按大小分块时指定的块大小,单位MB
-c,–compress 压缩输出文件
-e,–build-empty-files 即使没有表数据依旧生成一个空文件
-x,–regex 支持正则表达式
-i,–ignore-engines 忽略的存储引擎,逗号分隔
-N,–insert-ignore 使用insert ignore备份数据
-m,–no-schemas 不导出表结构
-d,–no-data 不导出表数据
-G,–triggers 导出触发器
-R,–routines 导出存储过程与函数
-W,-no-views 不导出视图
-k,–no-locks 不执行临时共享读锁定
–less-locking 最大限度减少innodb表锁定时间
-l,–long-query-guard 设置长查询时间,默认60s
-K,–kill-long-queries Kill长时间执行的查询
-D,–daemon 启用守护进程模式
-I,–snapshot-interval 在daemon模式下每个转储快照之间的时间间隔,默认60s
-L,–logfile 日志文件名
–use-savepoints 使用savepoint减少数据锁定问题,需要super权限
–lock-all-tables 使用LOCK TABLE替代FTWRL(FLUSH TABLE WITH READ LOCK)
-U,–updated-since 根据update time转储过去时间中更新的表
-t,–threads 使用的线程数,默认为4
-h,–host 连接的服务器,ip或主机名
-u,–user 连接的用户名
-p,–password 连接的用户密码
-P,–port 连接的服务器端口
-S,–socket 连接使用的sock文件

myloader恢复参数

参数 描述
-d, –directory 备份文件的文件夹
-q, –queries-per-transaction 每次事物执行的查询数量,默认是1000
-o, –overwrite-tables 如果要恢复的表存在,则先drop掉该表,使用该参数,需要备份时候要备份表结构
-B, –database 需要还原的数据库
-e, –enable-binlog 启用还原数据的二进制日志
-h, –host 主机
-u, –user 还原的用户
-p, –password 密码
-P, –port 端口
-S, –socket socket文件
-t, –threads 还原所使用的线程数,默认是4
-C, –compress-protocol 压缩协议
-V, –version 显示版本
-v, –verbose 输出模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2

示例

备份所有数据库

1
$ mydumper -uroot -p -t 10 -F 100 -o /service/backup

备份test数据库

1
$ mydumper --user=root --password='Abcd123#' -B test -o /service/backup

备份test下的指定表

1
$ mydumper --user=root --password='Abcd123#' -B test -T tab1,tab2 -o /service/backup

备份test下tab开头的表

1
$ mydumper --user=root --password='Abcd123#' -B test --regex=tab* -o /service/backup

还原test数据库

1
$ myloader --user=root --password='Abcd123#' -B test -d /service/backup

还原test下的tab1表(需要删除其它表的备份文件,否则整个数据库恢复)

1
$ myloader --user=root --password='Abcd123#' -B test -o tab1 -d /service/backup

利用守护进程模式,每30分钟创建一个快照并输出文件

1
mydumper --user=root --password='Abcd123#' --daemon --snapshot-interval=30 --logfile=dump.log

在导出的文件中metadata包含了gtid和binlog信息,db.table.sql包含表数据(insert),db.table-schema.sql包含表结构语句,db-schema.sql包含建库语句。更多mydumper信息内容请参考mydumper官网

物理备份

xtrabackup

xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具,支持MySQL、Percona server和MariaDB,开源免费,是目前较为受欢迎的主流备份工具。

安装xtrabackup

下载RPM包

DownLoad RPM Package

安装依赖

1
$ yum install libev-* rsync perl-Digest-MD5 perl-DBD-MySQL -y

安装xtrabackup

1
$ rpm -ivh percona-xtrabackup-24-2.4.15-1.el7.x86_64.rpm

创建备份用户

1
sql> grant reload,lock tables,replication client on *.* to 'dbbackup'@'localhost' identified by 'Abcd123#' ;

innobackupex

innobackupex是一个封装了xtrabackup的Perl脚本,支持同时备份innodb和myisam,但在对myisam备份时需要加一个全局的读锁,myisam不支持增量备份。innobackupex支持stream流备份,提升备份性能。

备份原理

  • 复制redo log,并启动一个xtrabackup_log后台检测的进程,实时检测mysql redo的变化,一旦发现redo有新的日志写入,立刻将日志写入到日志文件xtrabackup_log中
  • 复制innodb的数据文件和系统表空间文件ibdata1到备份目录
  • 复制结束后,执行flush table with read lock全局锁操作
  • 复制非事务引擎数据文件及其它文件
  • 获取binary log 的位置
  • 停止redo log复制
  • unlock tables解锁
  • 复制buffer pool dump
  • 备份结束

xtrabackup

示例

全备份

1
$ innobackupex --defaults-file=/etc/my.cnf --user=dbbackup --password='xxxx' /service/backup

增量备份

1
$ innobackupex --defaults-file=/etc/my.cnf --incremental --user=dbbackup --password=’xxxx’ /service/backup/inc --incremental-basedir=/backup/full/2019-08-15_10-51-01

增量恢复数据库

1
2
3
4
5
$ innobackupex --apply-log --redo-only /service/backup/2019-08-15_10-51-01/
$ innobackupex --apply-log --redo-only /service/backup/2019-08-15_10-51-01 --incremental-dir=/service/backup/inc/2019-08-16_10-51-01
$ innobackupex --apply-log /service/backup/2019-08-15_10-51-01 --incremental-dir=/service/backup/inc/2019-08-17_10-51-01
$ innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /service/backup/2019-08-15_10-51-01/
$ chown -R mysql.mysql /service/mysql/

Tips:恢复最后一个增量时不应该加redo-only,否则rollback将在启动时完成

备份数据库并压缩

1
$ innobackupex --user=dbbackup --password='xxx' --stream=tar /tmp | gzip - > /backup/dbbak-`date +%Y%M%d`.tar.gz

xbstream流式备份(支持并行)

1
innobackupex --user=dbbackup --password='xxx' --parallel=10 --stream=xbstream /tmp > /mysqlbackup/test-20200616.xbstream

备份到远程服务器(SSH互信)

1
$ innobackupex --user=dbbackup --password='xxx' --stream=xbstream /tmp | ssh root@10.0.139.162 \ "cat - > /service/backup.xbstream"

备份到远程服务器并解压(SSH互信)

1
$ innobackupex --user=dbbackup --password='xxx' --stream=xbstream  /tmp | ssh root@10.0.139.162 "xbstream -x -C /service/mysql/data"

恢复数据库(xbstream)

1
2
3
4
5
6
--解压备份
$ xbstream -x < /service/backup/backup.xbstream -C /service/mysql/data
--应用日志
$ innobackupex --apply-log /service/mysql/data
--修改目录权限
$ chown -R mysql.mysql /service/mysql

闪回(flashback)

当数据库发生误删除或数据错误时,需要对数据进行恢复,前面都是通过备份以及删除错误操作的binlog来完成恢复,此方法法费时费力,甚至需要停机维护,并不适合快速回滚。这时可以通过binlog2sql对mysql进行闪回,能快速恢复且不用停机

binlog2sql

闪回原理

MySQL binlog以event的形式,记录了MySQL server从启用binlog以来所有的变更信息,能够帮助重现这之间的所有变化。MySQL引入binlog主要有两个目的:一是为了主从复制;二是某些备份还原操作后需要重新应用binlog。

有三种可选的binlog格式,各有优缺点:

  • statement:基于SQL语句的模式,binlog数据量小,但是某些语句和函数在复制过程可能导致数据不一致甚至出错;
  • row:基于行的模式,记录的是行的完整变化。很安全,但是binlog会比其他两种模式大很多;
  • mixed:混合模式,根据语句来选用是statement还是row模式;

利用binlog闪回,需要将binlog格式设置为row。row模式下,一条使用innodb的insert会产生如下格式的binlog:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# at 1129
#161225 23:15:38 server id 3773306082 end_log_pos 1197 Query thread_id=1903021 exec_time=0 error_code=0
SET TIMESTAMP=1482678938/*!*/;
BEGIN
/*!*/;
# at 1197
#161225 23:15:38 server id 3773306082 end_log_pos 1245 Table_map: `test`.`user` mapped to number 290
# at 1245
#161225 23:15:38 server id 3773306082 end_log_pos 1352 Write_rows: table id 290 flags: STMT_END_F

BINLOG '
muJfWBPiFOjgMAAAAN0EAAAAACIBAAAAAAEABHRlc3QABHVzZXIAAwMPEQMeAAAC
muJfWB7iFOjgawAAAEgFAAAAACIBAAAAAAEAAgAD//gBAAAABuWwj+i1tVhK1hH4AgAAAAblsI/p
krFYStYg+AMAAAAG5bCP5a2ZWE/onPgEAAAABuWwj+adjlhNeAD4BQAAAAJ0dFhRYJM=
'/*!*/;
# at 1352
#161225 23:15:38 server id 3773306082 end_log_pos 1379 Xid = 5327954
COMMIT/*!*/;

既然binlog以event形式记录了所有的变更信息,那么我们把需要回滚的event,从后往前回滚回去即可。

对于单个event的回滚,我们以表test.user来演示原理

1
2
3
4
5
6
7
8
mysql> show create table test.user\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

对于delete操作,我们从binlog提取出delete信息,生成的回滚语句是insert。

1
2
原始:DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='小赵';
回滚:INSERT INTO `test`.`user`(`id`, `name`) VALUES (1, '小赵');

对于insert操作,回滚SQL是delete。

1
2
原始:INSERT INTO `test`.`user`(`id`, `name`) VALUES (2, '小钱');
回滚:DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='小钱';

对于update操作,回滚sql应该交换SET和WHERE的值。

1
2
原始:UPDATE `test`.`user` SET `id`=3, `name`='小李' WHERE `id`=3 AND `name`='小孙';
回滚:UPDATE `test`.`user` SET `id`=3, `name`='小孙' WHERE `id`=3 AND `name`='小李';

安装binlog2sql

下载binlog2sql

DownLoad binlog2sql

安装setuptools

1
2
3
$ tar –xvf setuptools-0.6c11.tar.gz
$ cd setuptools-0.6c11
$ python setup.py install

安装pip

1
2
3
$ tar –xvf pip-9.0.1.tar.gz
$ cd pip-9.0.1
$ python setup.py install

安装下列python依赖

  • PyMySQL-0.9.3-py2.py3-none-any.whl
  • traceback2-1.4.0-py2.py3-none-any.whl
  • wheel-0.33.4-py2.py3-none-any.whl
  • argparse-1.4.0-py2.py3-none-any.whl
  • linecache2-1.0.0-py2.py3-none-any.whl
  • six-1.12.0-py2.py3-none-any.whl
  • unittest2-1.1.0-py2.py3-none-any.whl
  • mysql-replication-0.19.tar.gz
1
pip install *.whl mysql-replication-0.19.tar.gz

解压binlog2sql

1
$ unzip binlog2sql-master.zip

基本用法

参数选项

参数 描述
-h 连接服务器
-u 连接用户
-p 连接用户密码
-P 连接服务器端口
–stop-never 持续解析binlog至最新binlog位置,默认为false
-K,–no-primary-key 对insert语句去除主键,默认为false
-B,–flashback 生成UNDO SQL,可以解析大文件,不受内存限制,默认为false,不可与stop-never和no-primary-key共用
–back-interval -B模式下,每打印1000行sleep几秒,默认为1
–start-file 解析起始文件
–start-position/–start-pos 解析起始位置
–stop-file 解析终止文件
–stop-position/–end-pos 解析终止位置
–start-datetime 解析起始时间,格式为%Y-%m-%d %H-%M-%S
–stop-datetime 解析终止时间,格式为%Y-%m-%d %H-%M-%S
-d,–databases 指定分析目标数据库,多个库用空格分隔
-t,–tables 指定分析目标表,多个表用空格分隔
–only-dml 只解析DML,忽略DDL,默认为false
–sql-type 只解析指定类型,支持DELETE,UPDATE,INSERT,多个类型用空格分隔,默认全解析

解析标准SQL

1
$ python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -d test -t test3 test4 --start-file='mysql-bin.000002'

解析UNDO SQL

1
python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -d test -t test3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147

恢复示例

业务于11:44误删除了test库tab2表部分数据,需要紧急回滚

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
root@test 16:50:  select * from tab2;
+------+------------+
| id | name |
+------+------------+
| 2 | luhengxing |
| 3 | luhengxing |
| 4 | luhengxing |
| 1 | luhengxing |
| 5 | luhengxing |
+------+------------+
5 rows in set (0.00 sec)

#11:44时,user表大批数据被误删除。与此同时,正常业务数据是在继续写入的
root@test 16:50: delete from tab2 where id>2;
Query OK, 3 rows affected (0.02 sec)

root@test 17:51: select * from tab2;
+------+------------+
| id | name |
+------+------------+
| 2 | luhengxing |
| 1 | luhengxing |
+------+------------+

查看binlog信息

1
2
3
4
5
6
root@test 17:51:  show master logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 478 |
+---------------+-----------+

尝试解析最新的binlog关于test库user表的SQL

1
2
3
4
5
6
$ python binlog2sql.py -h127.0.0.1 -P33006 -uroot -p'Abcd123#' -d test -t tab2 --start-file='binlog.000001' --start-datetime='2019-12-04 17:50:00' > /tmp/raw.sql

$ cat /tmp/raw.sql
DELETE FROM `test`.`tab2` WHERE `id`=3 AND `name`='luhengxing' LIMIT 1; #start 211 end 451 time 2019-12-04 17:51:15
DELETE FROM `test`.`tab2` WHERE `id`=4 AND `name`='luhengxing' LIMIT 1; #start 211 end 451 time 2019-12-04 17:51:15
DELETE FROM `test`.`tab2` WHERE `id`=5 AND `name`='luhengxing' LIMIT 1; #start 211 end 451 time 2019-12-04 17:51:15

根据上述信息,可以判断DELETE误操作的位置位于211-451之间,利用位置信息生成UNDO SQL

1
2
3
4
5
6
$ python binlog2sql.py -h127.0.0.1 -P33006 -uroot -p'Abcd123#' -d test -t tab2 --start-file='binlog.000001' --start-position=211 --stop-position=451 -B >/tmp/rollback.sql

$ cat /tmp/rollback.sql
INSERT INTO `test`.`tab2`(`id`, `name`) VALUES (5, 'luhengxing'); #start 211 end 451 time 2019-12-04 17:51:15
INSERT INTO `test`.`tab2`(`id`, `name`) VALUES (4, 'luhengxing'); #start 211 end 451 time 2019-12-04 17:51:15
INSERT INTO `test`.`tab2`(`id`, `name`) VALUES (3, 'luhengxing'); #start 211 end 451 time 2019-12-04 17:51:15

应用rollback.sql恢复数据

1
2
3
4
5
6
7
8
$ mysql -h127.0.0.1 -P33006 -uroot -p'Abcd123#' < /tmp/rollback.sql

mysql> select count(*) from tab2;
+----------+
| count(*) |
+----------+
| 5 |
+----------+

恢复流程概述

  1. 先根据库、表、时间做一次筛选,再根据位置信息做更准确的过滤
  2. 由于数据一直写入,要确保undo sql不包含其它数据,可根据是否是同一事务、误操作行数、字段值等条件来判断
  3. 如果只回滚某张表,并且该表与其它表存在关联,关联表并不会被回滚,需要与业务确认清楚
  4. 无法闪回DDL操作,因为对于DDL操作并不会记录每行的数据变化

更多关于binlog2sql的内存请参考:
MySQL闪回原理与实战
MySQL下实现闪回的设计思路[彭立勋]

MyFlash

MyFlash是由美团点评开源的flashback工具,通过解析binlog日志完成回滚操作,相对于binlog2sql来说增加了更多过滤选项,让回滚更简单。项目地址:MyFlash

使用限制

  • binlog格式必须为row,且binlog_row_image=full
  • 仅支持5.6与5.7
  • 只能回滚DML(增、删、改)

解压安装目录

1
2
[root@t-luhx03-v-szzb mysql]# unzip MyFlash-master.zip
[root@t-luhx03-v-szzb mysql]# mv MyFlash-master /usr/local/myflash

参数说明

选项 描述
databaseNames 指定需要回滚的数据库名,多个数据库可以用”,”隔开,不指定则为所有库
tableNames 指定需要回滚的表名。多个表可以用”,”隔开,不指定则为所有表
start-position 指定回滚开始的位置。不指定就从文件的开始位置回滚
stop-position 指定回滚结束的位置。不指定就回滚到文件结尾
start-datetime 指定回滚的开始时间。时间格式为%Y-%m-%d %H:%M:%S
stop-datetime 指定回滚的结束时间。时间格式为%Y-%m-%d %H:%M:%S
sqlTypes 指定需要回滚的sql类型。支持INSERT/UPDATE/DELETE,多个类型可用”,”分隔
maxSplitSize 对文件进行固定大小的分隔,单位为M,过滤条件有效,但不进行回滚操作。主要用于将大的binlog文件切割,防止binlog过大,对线上造成影响
binlogFileNames 指定要回滚的binlog文件,仅支持单个文件
outBinlogFileNameBase 指定输出的binlog文件前缀,默认为binlog_output_base.flashback
logLevel 仅供开发者使用,默认级别为error级别
include-gtids 指定需要回滚的gtid,支持gtid的单个和范围两种形式
exclude-gtids 指定不需要回滚的gtid,用法同include-gtids

回滚整个文件

1
2
/usr/local/myflash/binary/flashback --binlogFileNames=binlog.000015
mysqlbinlog --skip-gtids binlog_output_base.flashback | mysql -h<host> -u<user> -p

回滚所有INSERT操作

1
2
/usr/local/myflash/binary/flashback --sqlTypes='INSERT' --binlogFileNames=binlog.000015
mysqlbinlog --skip-gtids binlog_output_base.flashback | mysql -h<host> -u<user> -p

回滚大文件

1
2
3
4
/usr/local/myflash/binary/flashback --binlogFileNames=binlog.000015
/usr/local/myflash/binary/flashback --maxSplitSize=1 --binlogFileNames=binlog_output_base.flashback
mysqlbinlog --skip-gtids binlog_output_base.flashback.000001 | mysql -h<host> -u<user> -p
mysqlbinlog --skip-gtids binlog_output_base.flashback.000002 | mysql -h<host> -u<user> -p

回滚指定时间范围内的操作

1
2
/usr/local/myflash/binary/flashback --start-datetime='2020-06-03 00:00:00' --stop-datetime='2020-06-04 00:00:00' --binlogFileNames=binlog.000015
mysqlbinlog binlog_output_base.flashback | mysql -h<host> -u<user> -p

关于MyFlash的闪回原理请参考:MyFlash-美团点评的开源MySQL闪回工具

无备份恢复

当发生数据异常时,如果没有有效备份并且没有开启binlog时,需要通过一些特殊的方式来找回数据,但这种方式还是会存在丢失数据的可能,仅用在极端情况下的数据恢复,还是强烈建议定期做数据库备份并进行恢复演练验证。

undrop-for-innodb

undrop-for-innodb是针对MySQL Innodb的数据恢复工具,其会通过扫描文件或磁盘设备,解析innodb数据页来恢复丢失的数据,对于DDL误删除表或磁盘损坏等情况很有帮助。项目地址:undrop-for-innodb

解压安装包

1
2
3
4
[root@t-luhx03-v-szzb mysql]# unzip undrop-for-innodb-develop.zip 
[root@t-luhx03-v-szzb mysql]# mv undrop-for-innodb-develop /usr/local/undrop
[root@t-luhx03-v-szzb mysql]# cd /usr/local/undrop
[root@t-luhx03-v-szzb undrop]# make
  • dictionary:存放数据字典sql脚本
  • sakila:测试用的schema
  • stream_parser:可执行文件,用于扫描磁盘或磁盘设备,找出innodb数据页,按照index_id进行组织
  • c_parser:可执行文件,用于解析innodb数据页,找出行记录
  • sys_parser:可执行文件,通过字典表恢复表结构

创建测试表

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
root@test 13:57:  desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| a | int(11) | YES | MUL | NULL | |
| b | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)

root@test 14:11: checksum table t1;
+---------+------------+
| Table | Checksum |
+---------+------------+
| test.t1 | 3888021100 |
+---------+------------+
1 row in set (0.00 sec)

root@test 14:11: select count(*) from t1;
+----------+
| count(*) |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec)

DROP测试表

1
2
root@test 14:12:  drop table t1;
Query OK, 0 rows affected (0.05 sec)

恢复表结构

分析tablespace

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[root@t-luhx03-v-szzb undrop]# ./stream_parser -f /service/mysql/data/ibdata1 
Opening file: /service/mysql/data/ibdata1
File information:

ID of device containing file: 64770
inode number: 3407887
protection: 100640 (regular file)
number of hard links: 1
user ID of owner: 1012
group ID of owner: 1007
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 2097160
time of last access: 1592374456 Wed Jun 17 14:14:16 2020
time of last modification: 1592374457 Wed Jun 17 14:14:17 2020
time of last status change: 1592374457 Wed Jun 17 14:14:17 2020
total size, in bytes: 1073741824 (1.000 GiB)

Size to process: 1073741824 (1.000 GiB)
Worker(0): 5.47% done. 2020-06-17 14:21:15 ETA(in 00:00:20). Processing speed: 48.000 MiB/sec
Worker(0): 30.47% done. 2020-06-17 14:20:58 ETA(in 00:00:02). Processing speed: 256.000 MiB/sec
Worker(0): 54.69% done. 2020-06-17 14:20:58 ETA(in 00:00:01). Processing speed: 248.000 MiB/sec
Worker(0): 78.12% done. 2020-06-17 14:20:58 ETA(in 00:00:00). Processing speed: 240.000 MiB/sec
All workers finished in 4 sec

执行完成会生成一个pages-ibdata1目录,子目录分为索引页和blob页,我们需要的内容在索引页中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@t-luhx03-v-szzb FIL_PAGE_INDEX]# ls -lrt
total 288
-rw-r----- 1 root root 16384 Jun 17 14:20 18446744069414584320.page
-rw-r----- 1 root root 16384 Jun 17 14:20 0000000000000004.page
-rw-r----- 1 root root 16384 Jun 17 14:20 0000000000000003.page
-rw-r----- 1 root root 16384 Jun 17 14:20 0000000000000017.page
-rw-r----- 1 root root 16384 Jun 17 14:20 0000000000000014.page
-rw-r----- 1 root root 16384 Jun 17 14:20 0000000000000013.page
-rw-r----- 1 root root 16384 Jun 17 14:20 0000000000000012.page
-rw-r----- 1 root root 16384 Jun 17 14:20 0000000000000011.page
-rw-r----- 1 root root 32768 Jun 17 14:20 0000000000000002.page
-rw-r----- 1 root root 32768 Jun 17 14:20 0000000000000001.page
-rw-r----- 1 root root 32768 Jun 17 14:20 0000000000000016.page
-rw-r----- 1 root root 32768 Jun 17 14:20 0000000000000015.page
-rw-r----- 1 root root 32768 Jun 17 14:20 0000000000000005.page

所有字典表的index_id是硬编码的,01对应SYS_TABLES,02对应SYS_COLUMNS,03对应SYS_INDEXES,04对应SYS_FILEDS

我们通过c_parser来解析对应页的内容

1
2
3
4
5
[root@t-luhx03-v-szzb undrop]# mkdir dumps/default
[root@t-luhx03-v-szzb undrop]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql >./dumps/default/SYS_TABLES 2>./dumps/default/SYS_TABLES.sql
[root@t-luhx03-v-szzb undrop]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql >./dumps/default/SYS_INDEXES 2>./dumps/default/SYS_INDEXES.sql
[root@t-luhx03-v-szzb undrop]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t dictionary/SYS_COLUMNS.sql >./dumps/default/SYS_COLUMNS 2>./dumps/default/SYS_COLUMNS.sql
[root@t-luhx03-v-szzb undrop]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page -t dictionary/SYS_FIELDS.sql >./dumps/default/SYS_FIELDS 2>./dumps/default/SYS_FIELDS.sql

参数中的4DF中的4表示文件格式是REDUNDANT,系统表的默认格式;D表示只恢复删除的记录;f后面跟对应的page

我们将dictionary和dumps目录拷贝到异机再去恢复

1
2
[root@t-luhx03-v-szzb undrop]# scp -r dumps @10.0.139.161:/usr/local/undrop/
[root@t-luhx03-v-szzb undrop]# scp -r dictionary/ @10.0.139.161:/usr/local/undrop/

恢复数据字典表(需要开启LOAD DATA)

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
root@(none) 14:55:  create database recover;
Query OK, 1 row affected (0.01 sec)

root@(none) 14:56: use recover
Database changed

root@recover 14:57: source /usr/local/undrop/dictionary/SYS_TABLES.sql

root@recover 14:57: source /usr/local/undrop/dictionary/SYS_INDEXES.sql

root@recover 14:57: source /usr/local/undrop/dictionary/SYS_FIELDS.sql

root@recover 14:57: source /usr/local/undrop/dictionary/SYS_COLUMNS.sql

root@recover 15:08: source /usr/local/undrop/dumps/default/SYS_TABLES.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 1

root@recover 15:08: source /usr/local/undrop/dumps/default/SYS_INDEXES.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 1

root@recover 15:09: source /usr/local/undrop/dumps/default/SYS_COLUMNS.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected, 1 warning (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 1

root@recover 15:09: source /usr/local/undrop/dumps/default/SYS_FIELDS.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 1

编译sys_parser生成DDL语句

1
2
3
4
5
6
7
8
[root@t-luhx01-v-szzb undrop]# make sys_parser
[root@t-luhx01-v-szzb undrop]# ./sys_parser -h127.0.0.1 -uroot -p'Abcd123#' -d recover test/t1
CREATE TABLE `t1`(
`id` INT NOT NULL,
`a` INT,
`b` INT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

数据恢复

如果开启了innodb_file_per_table,表对应的ibd表空间文件会被删除,此时需要扫描整个磁盘,需确保磁盘没有数据写入防止数据页被覆盖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[root@t-luhx03-v-szzb undrop]# ./stream_parser -f /dev/mapper/dbvg-dblv -t 2G -t 100G
Opening file: /dev/mapper/dbvg-dblv
File information:

ID of device containing file: 5
inode number: 15546
protection: 60660 (block device)
number of hard links: 1
user ID of owner: 0
group ID of owner: 6
device ID (if special file): 64770
blocksize for filesystem I/O: 4096
number of blocks allocated: 0
time of last access: 1592352002 Wed Jun 17 08:00:02 2020
time of last modification: 1573539555 Tue Nov 12 14:19:15 2019
time of last status change: 1573539555 Tue Nov 12 14:19:15 2019
total size, in bytes: 0 (0.000 exp(+0))

Size to process: 107374182400 (100.000 GiB)
Worker(0): 1.02% done. 2020-06-17 15:51:27 ETA(in 00:06:32). Processing speed: 257.984 MiB/sec
Worker(0): 2.02% done. 2020-06-17 15:53:06 ETA(in 00:08:06). Processing speed: 206.394 MiB/sec
Worker(0): 3.03% done. 2020-06-17 15:54:44 ETA(in 00:09:38). Processing speed: 171.717 MiB/sec

执行完成后会在pages-dbvg-dblv/FIL_PAGE_INDEX下生成page文件,查找表对应的INDEX_ID

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
root@recover 15:55:  select * from sys_indexes;
+----------+----+---------+----------+------+-------+------------+
| TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+----+---------+----------+------+-------+------------+
| 44 | 49 | PRIMARY | 1 | 3 | 32 | 4294967295 |
| 44 | 50 | a | 1 | 0 | 32 | 4294967295 |
+----------+----+---------+----------+------+-------+------------+
2 rows in set (0.00 sec)

root@recover 15:55: select * from sys_tables;
+---------+----+--------+------+--------+---------+--------------+-------+
| NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+---------+----+--------+------+--------+---------+--------------+-------+
| test/t1 | 44 | 3 | 33 | 0 | 80 | | 32 |
+---------+----+--------+------+--------+---------+--------------+-------+

上面可以看到我需要的就是INDEX_ID为49的page,将表结构创建语句写入/tmp/t1.sql,通过c_parser来导出数据

1
2

[root@t-luhx03-v-szzb undrop]# ./c_parser -6f pages-dbvg-dblv/FIL_PAGE_INDEX/0000000000000049.page -t /tmp/t1.sql > ./dumps/default/t1 2> ./dumps/default/t1_load.sql

恢复数据

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
root@recover 16:21:  source /tmp/t1.sql
Query OK, 0 rows affected (0.18 sec)

root@recover 16:22: set global local_infile=1;
Query OK, 0 rows affected (0.00 sec)

root@recover 16:23: source /usr/local/undrop/dumps/default/t1.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 730 rows affected, 1 warning (0.02 sec)
Records: 420 Deleted: 310 Skipped: 0 Warnings: 1

root@recover 16:30: select count(*) from t1;
+----------+
| count(*) |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec)

root@recover 16:30: checksum table t1;
+------------+------------+
| Table | Checksum |
+------------+------------+
| recover.t1 | 3888021100 |
+------------+------------+

percona-data-recovery-tool-for-innodb

Percona Data Recovery Tool for InnoDB是Percona提供针对innodb或者xtradb引擎进行数据恢复的工具。

工具特点

  • 支持innodb和xtradb,不支持myisam
  • 利用数据文件进行恢复,数据库可处于关闭状态
  • 不能保证数据一定可恢复,如果数据块被覆盖则不可恢复

工具原理
Innodb数据是以索引的方式组织的,所有数据都是存在16K的数据块中。恢复将所有数据文件分成单个16K的页面,根据每个页面标记的起始点开始匹配,如果与表定义的size合适,则认为匹配成功输出记录。

安装工具

下载工具
DownLoad Percona data recovery tool

安装依赖

1
$ yum install ncurses-devel glibc-static perl-DBD-MySQL -y

编译配置工具

1
2
$ ./mysql-source/configure
$ make

恢复示例

模拟删除数据

1
2
3
4
5
6
7
8
9
10
11
12
root@test 11:19:  select * from tab1;
+----+------------+
| id | name |
+----+------------+
| 1 | lu |
| 2 | heng |
| 3 | xing |
| 4 | luhengxing |
+----+------------+

root@test 11:19: truncate table tab1;
Query OK, 0 rows affected (0.10 sec)

切分页

InnoDB页的默认大小是16K,每个页属于一个特定表中的一个特定的index。page_parser工具通过读取数据文件,根据页头中的index ID,拷贝每个页到一个单独的文件中

参数-5表示row format为COMPACT格式,适用于MySQL 5.0之后;-f则是要解析的数据文件

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
$ ./page_parser -5 -f /service/mysql/data/ibdata1
Opening file: /service/mysql/data_bak/ibdata1:
64770 ID of device containing file
3407879 inode number
33184 protection
1 number of hard links
0 user ID of owner
0 group ID of owner
0 device ID (if special file)
1073741824 total size, in bytes
4096 blocksize for filesystem I/O
2097160 number of blocks allocated
1575516411 time of last access
1575516414 time of last modification
1575516414 time of last status change
1073741824 Size to process in bytes
104857600 Disk cache size in bytes
1.00% done. 2019-12-05 11:32:15 ETA(in 00:01 hours). Processing speed: 10737419 B/sec
3.05% done. 2019-12-05 11:31:25 ETA(in 00:00 hours). Processing speed: 21959752 B/sec
5.18% done. 2019-12-05 11:31:23 ETA(in 00:00 hours). Processing speed: 22869596 B/sec
7.30% done. 2019-12-05 11:31:23 ETA(in 00:00 hours). Processing speed: 22856188 B/sec
9.43% done. 2019-12-05 11:31:23 ETA(in 00:00 hours). Processing speed: 22878566 B/sec
11.27% done. 2019-12-05 11:31:30 ETA(in 00:00 hours). Processing speed: 19698493 B/sec
13.40% done. 2019-12-05 11:31:23 ETA(in 00:00 hours). Processing speed: 22891353 B/sec
15.47% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22182604 B/sec
17.60% done. 2019-12-05 11:31:23 ETA(in 00:00 hours). Processing speed: 22946649 B/sec
19.53% done. 2019-12-05 11:31:27 ETA(in 00:00 hours). Processing speed: 20661813 B/sec
21.51% done. 2019-12-05 11:31:26 ETA(in 00:00 hours). Processing speed: 21251019 B/sec
23.64% done. 2019-12-05 11:31:23 ETA(in 00:00 hours). Processing speed: 22846196 B/sec
25.77% done. 2019-12-05 11:31:23 ETA(in 00:00 hours). Processing speed: 22879397 B/sec
27.90% done. 2019-12-05 11:31:23 ETA(in 00:00 hours). Processing speed: 22878220 B/sec
29.77% done. 2019-12-05 11:31:28 ETA(in 00:00 hours). Processing speed: 20081073 B/sec
31.88% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22741681 B/sec
34.01% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22834734 B/sec
36.14% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22853565 B/sec
38.26% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22812801 B/sec
40.11% done. 2019-12-05 11:31:28 ETA(in 00:00 hours). Processing speed: 19807987 B/sec
42.24% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22903891 B/sec
44.37% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22847973 B/sec
46.50% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22874206 B/sec
48.63% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22855972 B/sec
50.35% done. 2019-12-05 11:31:29 ETA(in 00:00 hours). Processing speed: 18515758 B/sec
52.44% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22411303 B/sec
54.54% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22540107 B/sec
56.67% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22834205 B/sec
58.58% done. 2019-12-05 11:31:26 ETA(in 00:00 hours). Processing speed: 20594776 B/sec
60.56% done. 2019-12-05 11:31:25 ETA(in 00:00 hours). Processing speed: 21194692 B/sec
62.68% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22828968 B/sec
64.81% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22824780 B/sec
66.93% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22790007 B/sec
68.91% done. 2019-12-05 11:31:25 ETA(in 00:00 hours). Processing speed: 21208919 B/sec
71.04% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22853378 B/sec
73.16% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22784025 B/sec
75.29% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22845096 B/sec
77.42% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22869912 B/sec
79.47% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22074113 B/sec
81.60% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22866712 B/sec
83.72% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22787570 B/sec
85.85% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22844781 B/sec
87.88% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 21750695 B/sec
89.99% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22689534 B/sec
92.11% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22809100 B/sec
94.22% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22602229 B/sec
96.35% done. 2019-12-05 11:31:24 ETA(in 00:00 hours). Processing speed: 22877629 B/sec

$ ls -l pages-1575516636/
total 8
drwxr-x--- 17 root root 4096 Dec 5 11:30 FIL_PAGE_INDEX
drwxr-x--- 2 root root 4096 Dec 5 11:30 FIL_PAGE_TYPE_BLOB

选择INDEX ID

一般建议根据主键进行恢复

如果数据库仍处于运行状态,并且表没有被drop掉,MySQL 5.7之前可以通过Innodb Tablespace Monitor来获取表上的index_id,如果是5.7及之后的版本可以通过下列语句查询

1
2
3
4
5
6
mysql> select t.name as table_name,i.name as index_name,index_id from information_schema.INNODB_SYS_TABLES t,information_schema.INNODB_SYS_INDEXES i where t.table_id=i.table_id and t.name='test/tab1';
+------------+------------+----------+
| table_name | index_name | index_id |
+------------+------------+----------+
| test/tab1 | PRIMARY | 50 |
+------------+------------+----------+

Tips:该表主键INDEX ID为50,因此需要恢复的INNODB页在0-50子目录下

生成表定义

1
./create_defs.pl --user=root --password='Abcd123#' --port=33006 --db=test --table=tab2 >include/table_defs.h

重新编译constraints_parser工具

1
$ make

合并page

1
$ find pages-1575526210/FIL_PAGE_INDEX/0-50/ -type f -name '*.page' | sort -n | xargs cat > pages-1575526210/FIL_PAGE_INDEX/0-50/customer_pages

恢复删除的数据

1
2
3
4
5
6
7
8
9
$ ./constraints_parser -5 -f pages-1575526210/FIL_PAGE_INDEX/0-50/customer_pages > recovery.sql
LOAD DATA INFILE '/media/mysql/percona-data-recovery-tool-for-innodb-0.5/dumps/default/tab1' REPLACE INTO TABLE `tab1` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'tab1\t' (id, na
me);

$ more recovery.sql
tab1 1 "lu"
tab1 2 "heng"
tab1 3 "xing"
tab1 4 "luhengxing"

加载恢复数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ chown -R mysql.mysql recovery.sql

root@test 14:43: LOAD DATA INFILE '/media/mysql/percona-data-recovery-tool-for-innodb-0.5/recovery.sql' REPLACE INTO TABLE `tab1` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'tab
1\t' (id, name);

root@test 14:43: select * from tab1;
+----+------------+
| id | name |
+----+------------+
| 1 | lu |
| 2 | heng |
| 3 | xing |
| 4 | luhengxing |
+----+------------+

Tips: “ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement”错误需要配置secure_file_priv参数

更多关于percona data recovery tool的信息请参考Percona Data Recovery Tool 单表恢复