数据库备份恢复是运维工作的重点，当发生数据丢失或异常，需要通过备份等手段恢复数据，保护数据安全。
备份类型分为三种：冷备、热备和温备，冷备即将数据库服务停止后对文件进行拷贝；热备即在线备份，备份过程中不影响业务正常运行；温备即对数据库全局加锁，禁止数据更新以达到备份一致性。
备份方式通常采用逻辑备份或物理备份，逻辑备份速度较慢，备份灵活，可针对实例、数据库、表、对象等进行备份，且不受版本影响；物理备份速度较快，备份不够灵活，跨版本之前可能存在兼容性问题。
制定备份策略应该综合考虑备份空间大小和恢复速度，全备周期短，空间使用就大；全备周期长，恢复起来就慢。
逻辑备份
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，来根据其中的信息分析一个备份执行过程：
- 导出选项设置了master_data，它会通过flush table with read lock来获取全局读锁
- 导出选项设置了single-transaction，它会将事务隔离级别设置为REPEATABLE READ(仅针对当前会话)，并开启一个一致性快照读事务来保证备份数据一致性
- show master status获取binlog_file和binlog_pos
- 释放全局读锁
- 创建一个savepoint保存点
- 查询表的结构及数据并导出
- 数据表导出后，回滚到之前的save point，这样就不会阻塞已备份的表的DDL操作，需要注意的是这并不会回滚之前快照读事务
- 循环执行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设置并行线程数。
相对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
- 可采用守护进程的方式定时扫描和输出连续的二进制日志
- 支持按照指定文件大小切割备份文件
工作流程
- FLUSH TABLES WITH READ LOCK，施加全局只读锁
- 读取当前时间点的二进制日志文件名和postion记录在metadata中，
- START TRANSACTION WITH CONSISTENT SNAPSHOT，开启读一致性事务
- 启动N个线程导出数据，默认4个
- dump non-InnoDB tables，备份非事务型数据表
- UNLOCK TABLES，释放全局只读锁
- dump InnoDB tables，基于事务导出innodb表
安装使用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
- 备份结束
示例
全备份
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 |
+----------+
|
恢复流程概述
- 先根据库、表、时间做一次筛选，再根据位置信息做更准确的过滤
- 由于数据一直写入，要确保undo sql不包含其它数据，可根据是否是同一事务、误操作行数、字段值等条件来判断
- 如果只回滚某张表，并且该表与其它表存在关联，关联表并不会被回滚，需要与业务确认清楚
- 无法闪回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提供针对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工具
合并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 单表恢复