[TOC]
概述
最近经常接到客户数据误操作的情况，问我们能否恢复。而且一般这一类客户可能没有做任何备份。借此机会想把MySQL数据误操作有关的所有场景归纳总结一下。
首先从数据库的角度，误操作可以分为DML和DDL；其中DML一般是指delete、update，DDL指drop table和truncate table。
还有一种场景就是误删数据文件。
那么针对如上操作，恢复决策视角可以从是否存在有效备份、是否开启binlog来考虑。
除了误操作，还会有硬件损坏，如碰到corruption等。
如上图可以看到，根据是否存在有效备份可以分几种场景：
- 有备份 + binlog：直接做PITR（基于时间点恢复）。
- 有备份 + 无binlog：恢复到备份点（可能造成数据损失）
- 无备份 + 有binlog：
- DML操作，可以尝试做binlog闪回（binlog为ROW，并且image格式为FULL）
- DDL操作，使用TwinDB等表空间扫描工具恢复
- 无备份 + 无binlog：
- DML操作，无能为力
- DDL操作，使用TwinDB等表空间扫描工具恢复
实验
操作系统：CentOS 7
数据库版本：5.7.40
场景1：DML 误操作
模拟故障现场
以delete为例，模拟delete操作时没有带条件的场景。
造数据
flush logs;
use test;
drop table if exists t ;
create table t(id int,create_time datetime);
insert into t values(1,now());
select sleep(1);
insert into t values(2,now());
select sleep(1);
insert into t values(3,now());
select * from t ;
show binary logs;
执行结果如下：
mysql> flush logs;
Query OK, 0 rows affected (0.32 sec)
mysql> use test;
Database changed
mysql> drop table if exists t ;
Query OK, 0 rows affected (0.15 sec)
mysql> create table t(id int,create_time datetime);
Query OK, 0 rows affected (0.28 sec)
mysql> insert into t values(1,now());
Query OK, 1 row affected (0.07 sec)
mysql> select sleep(1);
+----------+
| sleep(1) |
+----------+
| 0 |
+----------+
1 row in set (1.01 sec)
mysql> insert into t values(2,now());
Query OK, 1 row affected (0.06 sec)
mysql> select sleep(1);
+----------+
| sleep(1) |
+----------+
| 0 |
+----------+
1 row in set (1.01 sec)
mysql> insert into t values(3,now());
Query OK, 1 row affected (0.05 sec)
mysql> select * from t ;
+------+---------------------+
| id | create_time |
+------+---------------------+
| 1 | 2024-01-04 20:43:23 |
| 2 | 2024-01-04 20:43:24 |
| 3 | 2024-01-04 20:43:25 |
+------+---------------------+
3 rows in set (0.00 sec)
mysql> show binary logs;
+------------------+------------+
| Log_name | File_size |
+------------------+------------+
| mysql-bin.000002 | 249981168 |
| mysql-bin.000003 | 1086208 |
| mysql-bin.000004 | 180973 |
| mysql-bin.000005 | 360974 |
| mysql-bin.000006 | 2360234 |
| mysql-bin.000007 | 7200975 |
| mysql-bin.000008 | 1440001279 |
| mysql-bin.000009 | 241 |
| mysql-bin.000010 | 648002074 |
| mysql-bin.000011 | 1525 |
+------------------+------------+
10 rows in set (0.00 sec)
做一次全量逻辑备份
mysqldump --default-character-set=utf8mb4 --hex-blob --single-transaction --master-data=2 --set-gtid-purged=OFF -A -R -E > /tmp/alldb.sql
--master-data=2 必须添加，否则无法做PITR
查看备份状态
Sangfor:DBVM/host-fefcfe739ba4 ~ # tail alldb.sql
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2024-01-04 19:27:00
Dump completed 备份成功。
做一次全量物理备份
innobackupex --defaults-file=/sf/etc/my.cnf --parallevel=4 --user=root --password='Qwer1234' /tmp/full/
执行返回
......
240122 11:39:25 Executing UNLOCK TABLES
240122 11:39:25 All tables unlocked
240122 11:39:25 [00] Copying ib_buffer_pool to /tmp/full/2024-01-22_11-39-19/ib_buffer_pool
240122 11:39:25 [00] ...done
240122 11:39:25 Backup created in directory '/tmp/full/2024-01-22_11-39-19/'
MySQL binlog position: filename 'mysql-bin.000019', position '1525', GTID of the last change 'f80ca2d3-94a0-11ee-8ce0-fefcfe739ba4:1-25714'
240122 11:39:25 [00] Writing /tmp/full/2024-01-22_11-39-19/backup-my.cnf
240122 11:39:25 [00] ...done
240122 11:39:25 [00] Writing /tmp/full/2024-01-22_11-39-19/xtrabackup_info
240122 11:39:25 [00] ...done
xtrabackup: Transaction log of lsn (2300559622) to (2300559631) was copied.
240122 11:39:25 completed OK!
返回 completed OK! 表示备份成功。
继续插入数据，模拟生产业务
insert into test.t values(4,now());
select sleep(1);
insert into test.t values(5,now());
select sleep(1);
insert into test.t values(6,now());
误删数据
mysql> delete from test.t;
Query OK, 6 rows affected (0.07 sec)
继续插入数据
insert into test.t values(7,now());
select sleep(1);
insert into test.t values(8,now());
select sleep(1);
insert into test.t values(9,now());
查看数据
mysql> select * from test.t ;
+------+---------------------+
| id | create_time |
+------+---------------------+
| 7 | 2024-01-04 21:21:56 |
| 8 | 2024-01-04 21:21:57 |
| 9 | 2024-01-04 21:21:58 |
+------+---------------------+
3 rows in set (0.00 sec)
当前备份文件中的数据是1,2,3
4,5,6在binlog中，而且已经被删除
后续又插入了7、8、9
诉求是恢复数据1-6。
恢复决策-1：binlog闪回（my2sql）
适用场景：当被删除的表是静态表，误操作之后无太大变化时，可以使用闪回策略。
这种方法只要开启binlog即可，无需全量备份。
安装my2sql
使用限制：
- 闪回工具，只能闪回DML操作。
- 开启binlog，且binlog_format = ROW。
- binlog_row_image = FULL。
查看当前数据库是否支持my2sql闪回
mysql> show global variables where variable_name in ('binlog_format' ,'binlog_row_image' );
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| binlog_format | ROW |
| binlog_row_image | FULL |
+------------------+-------+
2 rows in set (0.00 sec)
源码安装my2sql
yum install -y golang
go version
go env | grep GOPATH
mkdir -p /root/go/src
cd /root/go/src
git clone https://github.com/liuhr/my2sql.git
cd my2sql/
go build .
二进制安装（推荐）
wget https://raw.githubusercontent.com/liuhr/my2sql/master/releases/centOS_release_7.x/my2sql
chmod +x my2sql
cp my2sql /usr/bin/
my2sql --help
开始闪回
1、确认当前操作的binlog。
mysql> show binary logs;
+------------------+------------+
| Log_name | File_size |
+------------------+------------+
| mysql-bin.000002 | 249981168 |
| mysql-bin.000003 | 1086208 |
| mysql-bin.000004 | 180973 |
| mysql-bin.000005 | 360974 |
| mysql-bin.000006 | 2360234 |
| mysql-bin.000007 | 7200975 |
| mysql-bin.000008 | 1440001279 |
| mysql-bin.000009 | 241 |
| mysql-bin.000010 | 648002074 |
| mysql-bin.000011 | 4903 |
| mysql-bin.000012 | 3821 |
+------------------+------------+
11 rows in set (0.00 sec)
mysql> pager grep -B 2 -A 10 'delete from t';
PAGER set to 'grep -B 2 -A 10 'delete from t''
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000011';
| mysql-bin.000011 | 1525 | Gtid | 1253 | 1590 | SET @@SESSION.GTID_NEXT= 'f80ca2d3-94a0-11ee-8ce0-fefcfe739ba4:19771' |
| mysql-bin.000011 | 1590 | Query | 1253 | 1662 | BEGIN |
| mysql-bin.000011 | 1662 | Rows_query | 1253 | 1699 | # delete from t |
| mysql-bin.000011 | 1699 | Table_map | 1253 | 1745 | table_id: 170 (test.t) |
| mysql-bin.000011 | 1745 | Delete_rows | 1253 | 1810 | table_id: 170 flags: STMT_END_F |
| mysql-bin.000011 | 1810 | Xid | 1253 | 1841 | COMMIT /* xid=87285 */ |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
30 rows in set (0.03 sec)
名词解释
| Event | 解释 |
|---|---|
| SET @@SESSION.GTID_NEXT= 'f80ca2d3-94a0-11ee-8ce0-fefcfe739ba4:19771' | GTID_EVENT，此次事务的GITD。 |
| BEGIN | Query Event。 |
| # delete from t | ROWE vent |
| table_id: 170(test.t) | Table_map |
| table_id: 170flags: STMT_END_F | Delete_rows |
| COMMIT /* xid=87285 */ | XID Event |
从上数数据中可以得出，发生误操作的binlog为mysql-bin.000011，本事务的GTID为f80ca2d3-94a0-11ee-8ce0-fefcfe739ba4:19771，Start Position和End Position为1525、1841
注意：这里1253其实是下一个GTID Event的开始Position，但并不包含。所以在指定Position时，需要指定1841，如果指定1810则无法获取回滚SQL。
也可以使用mysqlbinlog命令，解析binlog文件
Sangfor:DBVM/host-fefcfe739ba4 /db_log/mysql # mysqlbinlog -vvv --base64-output=decode-row mysql-bin.000011|grep -B 11 -A 30 'delete from t'
# at 1525
#240104 20:44:10 server id 1253 end_log_pos 1590 CRC32 0x6a733959 GTID last_committed=5 sequence_number=6 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'f80ca2d3-94a0-11ee-8ce0-fefcfe739ba4:19771'/*!*/;
# at 1590
#240104 20:44:10 server id 1253 end_log_pos 1662 CRC32 0x064c56c3 Query thread_id=8062 exec_time=0 error_code=0
SET TIMESTAMP=1704372250/*!*/;
BEGIN
/*!*/;
# at 1662
#240104 20:44:10 server id 1253 end_log_pos 1699 CRC32 0x638f495c Rows_query
# delete from t
# at 1699
#240104 20:44:10 server id 1253 end_log_pos 1745 CRC32 0xbcf31fe0 Table_map: `test`.`t` mapped to number 170
# at 1745
#240104 20:44:10 server id 1253 end_log_pos 1810 CRC32 0x9a357e94 Delete_rows: table id 170 flags: STMT_END_F
### DELETE FROM `test`.`t`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='2024-01-04 20:43:23' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`t`
### WHERE
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='2024-01-04 20:43:24' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`t`
### WHERE
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2='2024-01-04 20:43:25' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 1810
#240104 20:44:10 server id 1253 end_log_pos 1841 CRC32 0x10671311 Xid = 87285
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
-B 表示Befor，也就是显示匹配行的前多少行，以此类推，-A 表示After，显示匹配行的后多少行。
这里可以看到，StartPosition为1525，Stopposition为1810，而下一个GTID_EVENT为1841。
这里需要注意，我们本次只删除了3条数据，所以会有三个Event，而且字段越多，行也就越多，需要根据真是情况做过滤。
下面开始闪回
2、创建闪回目录
mkdir -p /bk/rollback
3、生成回滚SQL
my2sql -user root -password Qwer1234 -host 127.0.0.1 -port 3306 \
-databases test -tables t \
-work-type rollback \
-start-file mysql-bin.000011 -start-pos 1525 \
-stop-file mysql-bin.000011 -stop-pos 1841 \
-output-dir /bk/rollback
执行结果
Sangfor:DBVM/host-fefcfe739ba4 /db_log/mysql # my2sql -user root -password Qwer1234 -host 127.0.0.1 -port 3306 \
> -databases test -tables t \
> -work-type rollback \
> -start-file mysql-bin.000011 -start-pos 1525 \
> -stop-file mysql-bin.000011 -stop-pos 1841 \
> -output-dir /bk/rollback
[2024/01/04 20:53:12] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 127.0.0.1 3306 root utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2024/01/04 20:53:12] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-bin.000011, 1525)
[2024/01/04 20:53:12] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2024/01/04 20:53:12] [info] events.go:210 start thread to write redo/rollback sql into file
[2024/01/04 20:53:12] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2024/01/04 20:53:12] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2024/01/04 20:53:13] [info] repl.go:16 start to get binlog from mysql
[2024/01/04 20:53:13] [info] binlogsyncer.go:777 rotate to (mysql-bin.000011, 1525)
[2024/01/04 20:53:13] [info] com.go:58 stop to get event. StopFilePos set. currentBinlog (mysql-bin.000011, 1841) StopFilePos (mysql-bin.000011, 1841)
[2024/01/04 20:53:13] [info] repl.go:18 finish getting binlog from mysql
[2024/01/04 20:53:13] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[2024/01/04 20:53:13] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2024/01/04 20:53:13] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2024/01/04 20:53:13] [info] events.go:259 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2024/01/04 20:53:13] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2024/01/04 20:53:13] [info] rollback_process.go:41 start to revert tmp file /bk/rollback/.rollback.11.sql into /bk/rollback/rollback.11.sql
[2024/01/04 20:53:13] [info] rollback_process.go:156 finish reverting tmp file /bk/rollback/.rollback.11.sql into /bk/rollback/rollback.11.sql
[2024/01/04 20:53:13] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2024/01/04 20:53:13] [info] events.go:272 finish reverting content order of tmp files
[2024/01/04 20:53:13] [info] events.go:277 exit thread to write redo/rollback sql into file
4、查看目录
Sangfor:DBVM/host-fefcfe739ba4 /bk/rollback # ll
total 12
-rw-r--r-- 1 root root 107 Jan 4 20:53 biglong_trx.txt
-rw-r--r-- 1 root root 288 Jan 4 20:53 binlog_status.txt
-rw-r--r-- 1 root root 234 Jan 4 20:53 rollback.11.sql
rollback.11.sql 这其中数字代表binlog编号。如果分析了21号binlog，那么这个数字就是21。
5、查看DML信息
Sangfor:DBVM/host-fefcfe739ba4 /bk/rollback # cat binlog_status.txt
binlog starttime stoptime startpos stoppos inserts updates deletes database table
mysql-bin.000011 2024-01-04_20:44:10 2024-01-04_20:44:10 1699 1810 0 0 3 test t
这里可以看到stoppos为1810，但是我们在做闪回操作时，需要指定stoppos的下一个position。
6、查看回滚SQL
Sangfor:DBVM/host-fefcfe739ba4 /bk/rollback # cat rollback.11.sql
INSERT INTO `test`.`t` (`id`,`create_time`) VALUES (3,'2024-01-04 19:24:30');
INSERT INTO `test`.`t` (`id`,`create_time`) VALUES (2,'2024-01-04 19:24:29');
INSERT INTO `test`.`t` (`id`,`create_time`) VALUES (1,'2024-01-04 19:24:28');
7、应用回滚SQL
Sangfor:DBVM/host-fefcfe739ba4 /bk/rollback # mysql < /bk/rollback/rollback.11.sql
Sangfor:DBVM/host-fefcfe739ba4 /bk/rollback # mysql -e "select * from test.t;"
+------+---------------------+
| id | create_time |
+------+---------------------+
| 3 | 2024-01-04 20:43:25 |
| 2 | 2024-01-04 20:43:24 |
| 1 | 2024-01-04 20:43:23 |
+------+---------------------+
闪回成功。
恢复决策-2：基于逻辑备份做PITR
1、查看备份时binlog一致性位点
Sangfor:DBVM/host-fefcfe739ba4 ~ # head -n 50 /tmp/alldb.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE='
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=1525;
表示全备时，binlog信息记录至mysql-bin.000013，position为1525
2、数据库禁用远程连接（可选）
可以选择宕掉网卡，防止应用连接。
# ifdown eth0
也可以使用skip-network方式启动数据库，防止远程登录。
3、还原全备
-- 刷新日志，产生新的日志
Sangfor:DBVM/host-fefcfe739ba4 ~ # mysqladmin flush-logs
Sangfor:DBVM/host-fefcfe739ba4 ~ # mysql -e "show binary logs" | tail -n2
mysql-bin.000013 3868
mysql-bin.000014 194
-- 备份binlog日志
cp mysql-bin.000013 /tmp/
-- 导入数据
Sangfor:DBVM/host-fefcfe739ba4 ~ # mysql --default-character-set=utf8mb4 < /tmp/alldb.sql
Sangfor:DBVM/host-fefcfe739ba4 ~ # mysql -e "select * from test.t;"
+------+---------------------+
| id | create_time |
+------+---------------------+
| 1 | 2024-01-04 21:21:13 |
| 2 | 2024-01-04 21:21:14 |
| 3 | 2024-01-04 21:21:15 |
+------+---------------------+
数据1,2,3，已经恢复
3、增量恢复
查找需要恢复的position
mysql> pager grep -B 2 -A 10 'delete from t';
PAGER set to 'grep -B 2 -A 10 'delete from t''
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000013';
| mysql-bin.000013 | 2500 | Gtid | 1253 | 2565 | SET @@SESSION.GTID_NEXT= 'f80ca2d3-94a0-11ee-8ce0-fefcfe739ba4:19801' |
| mysql-bin.000013 | 2565 | Query | 1253 | 2637 | BEGIN |
| mysql-bin.000013 | 2637 | Rows_query | 1253 | 2674 | # delete from t |
| mysql-bin.000013 | 2674 | Table_map | 1253 | 2720 | table_id: 205 (test.t) |
| mysql-bin.000013 | 2720 | Delete_rows | 1253 | 2815 | table_id: 205 flags: STMT_END_F |
| mysql-bin.000013 | 2815 | Xid | 1253 | 2846 | COMMIT /* xid=93986 */ |
| mysql-bin.000013 | 2846 | Gtid | 1253 | 2911 | SET @@SESSION.GTID_NEXT= 'f80ca2d3-94a0-11ee-8ce0-fefcfe739ba4:19802' |
| mysql-bin.000013 | 2911 | Query | 1253 | 2991 | BEGIN |
| mysql-bin.000013 | 2991 | Rows_query | 1253 | 3049 | # insert into test.t values(7,now()) |
| mysql-bin.000013 | 3049 | Table_map | 1253 | 3095 | table_id: 205 (test.t) |
| mysql-bin.000013 | 3095 | Write_rows | 1253 | 3140 | table_id: 205 flags: STMT_END_F |
| mysql-bin.000013 | 3140 | Xid | 1253 | 3171 | COMMIT /* xid=94005 */ |
| mysql-bin.000013 | 3171 | Gtid | 1253 | 3236 | SET @@SESSION.GTID_NEXT= 'f80ca2d3-94a0-11ee-8ce0-fefcfe739ba4:19803' |
67 rows in set (0.00 sec)
delete 操作发生在position 2500，第一阶段需要将数据从1525恢复至2565
开始恢复
# mysqlbinlog --skip-gtids --start-position=1525 --stop-position=2500 /tmp/mysql-bin.000013 | mysql
检查数据
Sangfor:DBVM/host-fefcfe739ba4 /db_log/mysql # mysql -e "select * from test.t;"
+------+---------------------+
| id | create_time |
+------+---------------------+
| 1 | 2024-01-04 21:21:13 |
| 2 | 2024-01-04 21:21:14 |
| 3 | 2024-01-04 21:21:15 |
| 4 | 2024-01-04 21:21:36 |
| 5 | 2024-01-04 21:21:37 |
| 6 | 2024-01-04 21:21:39 |
+------+---------------------+
已恢复至误删除数据之前的那一刻。
如果想继续把“7、8、9”数据恢复，可以继续从position=2500开始将mysql-bin.000013回放完。那么此时，我们将数据追至停机前的那一刻，并且跳过了中途误删除的那一段操作。
当然，如果没有开启binlog，那么只能恢复至备份点，也就是只能恢复数据1,2,3，后面所有的数据将会丢失。
恢复决策-3：基于物理备份做PITR
若生产环境只有物理备份，那么此时想恢复至数据1-6，可按如下步骤执行
1、数据库禁用远程连接（可选）
可以选择宕掉网卡，防止应用连接。
# ifdown eth0
也可以使用skip-network方式启动数据库，防止远程登录。
2、关闭数据库
systemctl stop mysqld
3、移除datadir
mv /var/lib/mysql /var/lib/mysql_bak
4、准备备份
innobackupex --apply-log /bk/full/2024-01-22_11-39-19/
注意：备份文件按日期生成，如：2024-01-22_11-39-19；请指定实际生成的备份文件名。
5、执行还原
innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /bk/full/full/2024-01-22_11-39-19/
可以使用--move-back方式替换--copy-back。不过需要注意，此类方法会移除原来的备份文件
6、修改datadir权限
chown mysql:mysql -R /var/lib/mysql
7、启动MySQL
systemctl start mysqld
如上步骤中，可以忽略执行还原阶段，直接修改my.cnf文件，将datadir指定为/bk/full/2024-01-22_11-39-19，进行启动。省去了备份文件重新移动的过程，也会节省存储空间。
此时，数据1,2,3已经恢复
8、增量恢复
增量恢复与“2.1.3 基于逻辑备份做PITR”步骤一样，只需要注意一点，获取binlog一致性备份位点需要读取 xtrabackup_binlog_info 文件中的信息
例如：
Sangfor:DBVM/host-fefcfe739ba4 /tmp/full/2024-01-22_11-39-19 # cat xtrabackup_binlog_info
mysql-bin.000019 1525 f80ca2d3-94a0-11ee-8ce0-fefcfe739ba4:1-25714
查找需要恢复的position
mysql> pager grep -B 2 -A 10 'delete from t';
PAGER set to 'grep -B 2 -A 10 'delete from t''
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000013';
| mysql-bin.000013 | 2500 | Gtid | 1253 | 2565 | SET @@SESSION.GTID_NEXT= 'f80ca2d3-94a0-11ee-8ce0-fefcfe739ba4:19801' |
| mysql-bin.000013 | 2565 | Query | 1253 | 2637 | BEGIN |
| mysql-bin.000013 | 2637 | Rows_query | 1253 | 2674 | # delete from t |
| mysql-bin.000013 | 2674 | Table_map | 1253 | 2720 | table_id: 205 (test.t) |
| mysql-bin.000013 | 2720 | Delete_rows | 1253 | 2815 | table_id: 205 flags: STMT_END_F |
| mysql-bin.000013 | 2815 | Xid | 1253 | 2846 | COMMIT /* xid=93986 */ |
| mysql-bin.000013 | 2846 | Gtid | 1253 | 2911 | SET @@SESSION.GTID_NEXT= 'f80ca2d3-94a0-11ee-8ce0-fefcfe739ba4:19802' |
| mysql-bin.000013 | 2911 | Query | 1253 | 2991 | BEGIN |
| mysql-bin.000013 | 2991 | Rows_query | 1253 | 3049 | # insert into test.t values(7,now()) |
| mysql-bin.000013 | 3049 | Table_map | 1253 | 3095 | table_id: 205 (test.t) |
| mysql-bin.000013 | 3095 | Write_rows | 1253 | 3140 | table_id: 205 flags: STMT_END_F |
| mysql-bin.000013 | 3140 | Xid | 1253 | 3171 | COMMIT /* xid=94005 */ |
| mysql-bin.000013 | 3171 | Gtid | 1253 | 3236 | SET @@SESSION.GTID_NEXT= 'f80ca2d3-94a0-11ee-8ce0-fefcfe739ba4:19803' |
67 rows in set (0.00 sec)
delete 操作发生在position 2500，第一阶段需要将数据从1525恢复至2565
开始恢复
# mysqlbinlog --skip-gtids --start-position=1525 --stop-position=2500 /tmp/mysql-bin.000013 | mysql
检查数据
Sangfor:DBVM/host-fefcfe739ba4 /db_log/mysql # mysql -e "select * from test.t;"
+------+---------------------+
| id | create_time |
+------+---------------------+
| 1 | 2024-01-04 21:21:13 |
| 2 | 2024-01-04 21:21:14 |
| 3 | 2024-01-04 21:21:15 |
| 4 | 2024-01-04 21:21:36 |
| 5 | 2024-01-04 21:21:37 |
| 6 | 2024-01-04 21:21:39 |
+------+---------------------+
已恢复至误删除数据之前的那一刻。
基于物理备份做PITR，比起逻辑备份在全量恢复阶段，速度上会有明显优势；当然，逻辑备份会对表空间做一定的收缩，而物理备份并不会。
实验小结
| 恢复决策 | 适用场景 | 优势 | 劣势 |
|---|---|---|---|
| binlog闪回 | 仅适用于DML操作，binlog必须开启，并且模式为full。 被删除的数据所在表相对静态，短时间内不会做变更。 数据量不宜过大，否则闪回时间会比较长 | 数据库可对外提供服务，针对少量数据可以做到及时修复 | 支持的场景有限，要根据业务实际需求按需恢复 |
| 基于逻辑备份的PITR | 所有场景 | 可以兜底、收缩共享表空间 | 速度比较慢 |
| 基于物理备份的PITR | 所有场景 | 可以兜底、恢复速度快 | 无法收缩共享表空间 |
场景2：DDL 误操作
模拟故障现场
以drop table为例，误删表操作场景。
造数据
#导入employees
cd /root/test_db-master
mysql -t < employees.sql
示例数据库参考“附录”章节
误删表
drop table employees.dept_manager;
如果这一步直接删掉了表，想要使用TwinDB恢复，需要提前把表结构导进去，否则无法使用TwinDB恢复。本次实验不在这里删除表。
表t已经被删掉、诉求是恢复表 t。
恢复决策-1：TwinDB表空间扫描恢复
TwinDB是一款专门用于InnoDB数据恢复的工具，它还有另外一个名字叫undrop for InnoDB。它可以在MySQL实例无法启动的情况下，直接访问InnoDB表空间，并尽量地恢复数据。
也可以从文件级别恢复诸如：DROP/TRUNCATE table, 删除表中某些记录，innodb 文件被删除，文件系统损坏，磁盘 corruption 等几种情况。
适用场景：无有效备份。
工具说明：
- 重要的工具：
c_parser && stream_parser && sys_parser- sys_parser 解析数据字典
- stream_parser：解析表空间文件，生成对应的页文件
- c_parser：解析页，生成用户数据
安装
下载：
# wget https://github.com/chhabhaiya/undrop-for-innodb/archive/master.zip
或者
# git clone https://github.com/twindb/undrop-for-innodb.git
安装依赖
# yum install make gcc fiex bison flex -y
解包
unzip undrop-for-innodb-master.zip
cd undrop-for-innodb-master
使用make生成执行文件
# make
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c tables_dict.c
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c print_data.c
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c check_data.c
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include sql_parser.o c_parser.o tables_dict.o print_data.o check_data.o -o c_parser -pthread -lm
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -o innochecksum_changer innochecksum.c
注意：TwinDB相关的所有操作都在安装目录下执行，本次安装目录为/data/undrop-for-innodb-master。
开始恢复
误删表恢复，分两个步骤：
- 恢复表结构
- 恢复表数据
恢复表结构
[root@node1 undrop-for-innodb-master]# ls dictionary
SYS_COLUMNS.sql SYS_FIELDS.sql SYS_INDEXES.sql SYS_TABLES.sql
创建一个data_recovered数据库，命令如下
mysqladmin drop data_recovered --force #第一次不用执行
mysqladmin create data_recovered
data_recovered 数据库中生成 4 个数据字典表
cat dictionary/SYS_* | mysql data_recovered
检查生成的数据
[root@node1 undrop-for-innodb-master]# mysqlshow -vv data_recovered
Database: data_recovered
+-------------+----------+------------+
| Tables | Columns | Total Rows |
+-------------+----------+------------+
| SYS_COLUMNS | 7 | 0 |
| SYS_FIELDS | 3 | 0 |
| SYS_INDEXES | 7 | 0 |
| SYS_TABLES | 8 | 0 |
+-------------+----------+------------+
4 rows in set.
使用工具集中的stream_parser对表空间文件ibdata1进行解析
[root@node1 undrop-for-innodb-master]# ./stream_parser -f /var/lib/mysql/ibdata1
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file: 64768
inode number: 3784792
protection: 100640 (regular file)
number of hard links: 1
user ID of owner: 27
group ID of owner: 27
Opening file: /var/lib/mysql/ibdata1
device ID (if special file): 0
blocksize for filesystem I/O: 4096
File information:
number of blocks allocated: 155648
ID of device containing file: 64768
inode number: 3784792
protection: 100640 (regular file)
number of hard links: 1
user ID of owner: 27
group ID of owner: 27
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 155648
time of last access: 1705917271 Mon Jan 22 17:54:31 2024
time of last modification: 1705917281 Mon Jan 22 17:54:41 2024
time of last status change: 1705917281 Mon Jan 22 17:54:41 2024
time of last access: 1705917271 Mon Jan 22 17:54:31 2024
total size, in bytes: 79691776 (76.000 MiB)
time of last modification: 1705917281 Mon Jan 22 17:54:41 2024
time of last status change: 1705917281 Mon Jan 22 17:54:41 2024
Size to process: 79691776 (76.000 MiB)
total size, in bytes: 79691776 (76.000 MiB)
Size to process: 79691776 (76.000 MiB)
All workers finished in 0 sec
解析完成后将即系的数据放在当前目录下的pages-ibdata1目录下
[root@node1 undrop-for-innodb-master]# ls pages-ibdata1/*
pages-ibdata1/FIL_PAGE_INDEX:
0000000000000001.page 0000000000000004.page 0000000000000012.page 0000000000000015.page 0000000000000032.page 0000000000000347.page 18446744069414584320.page
0000000000000002.page 0000000000000005.page 0000000000000013.page 0000000000000016.page 0000000000000033.page 0000000000000348.page
0000000000000003.page 0000000000000011.page 0000000000000014.page 0000000000000017.page 0000000000000345.page 0000000000000349.page
pages-ibdata1/FIL_PAGE_TYPE_BLOB:
使用如下4条命令从这4个页中读取数据
rm -rf dumps/default #首次不用执行
mkdir -p dumps/default #创建恢复文件目录
./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql > dumps/default/SYS_TABLES 2> dumps/default/SYS_TABLES.sql
./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t dictionary/SYS_COLUMNS.sql > dumps/default/SYS_COLUMNS 2> dumps/default/SYS_COLUMNS.sql
./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql > dumps/default/SYS_INDEXES 2>dumps/default/SYS_INDEXES.sql
./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page -t dictionary/SYS_FIELDS.sql > dumps/default/SYS_FIELDS 2> dumps/default/SYS_FIELDS.sql
如下命令将数据导入这 4 个表中
cat dumps/default/SYS_TABLES.sql | mysql data_recovered
cat dumps/default/SYS_COLUMNS.sql | mysql data_recovered
cat dumps/default/SYS_INDEXES.sql | mysql data_recovered
cat dumps/default/SYS_FIELDS.sql | mysql data_recovered
导入完成后检查表中数据
[root@node1 undrop-for-innodb-master]# mysqlshow -vv data_recovered
Database: data_recovered
+-------------+----------+------------+
| Tables | Columns | Total Rows |
+-------------+----------+------------+
| SYS_COLUMNS | 7 | 349 |
| SYS_FIELDS | 3 | 143 |
| SYS_INDEXES | 7 | 107 |
| SYS_TABLES | 8 | 69 |
+-------------+----------+------------+
4 rows in set.
至此，所有表的数据字典信息都恢复到这 4 个表中了，下面编译生成sys_parser工具
make sys_parser
使用sys_parser工具可以从数据字典表中生成任意表的建表语句
./sys_parser
如下命令生成world.city表的建表语句
[root@node1 undrop-for-innodb-master]# ./sys_parser -u root -p Qwer@123 -d data_recovered employees/dept_manager | tee dept_manager.sql
CREATE TABLE `dept_manager`(
`emp_no` INT NOT NULL,
`dept_no` CHAR(4) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
`from_date` DATE NOT NULL,
`to_date` DATE NOT NULL,
PRIMARY KEY (`emp_no`, `dept_no`)
) ENGINE=InnoDB;
这一步我们将建表语句导入到了dept_manager.sql文件中，在后续恢复中需要用到。
到这里，表结构SQL已经拿到，下面开始恢复表数据。
表数据恢复也需要分两种情况
- innodb_file_per_table ON
- innodb_file_per_table OFF
当innodb_file_per_table on为OFF时，表会存在ibdata共享表空间中；如果为ON，那么每个表都是独立表空间。
本次只演示innodb_file_per_table为ON的场景。
查看原始数据
[root@node1 undrop-for-innodb-master]# mysql -e "select * from employees.dept_manager;"
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 110022 | d001 | 1985-01-01 | 1991-10-01 |
| 110039 | d001 | 1991-10-01 | 9999-01-01 |
| 110085 | d002 | 1985-01-01 | 1989-12-17 |
| 110114 | d002 | 1989-12-17 | 9999-01-01 |
| 110183 | d003 | 1985-01-01 | 1992-03-21 |
| 110228 | d003 | 1992-03-21 | 9999-01-01 |
| 110303 | d004 | 1985-01-01 | 1988-09-09 |
| 110344 | d004 | 1988-09-09 | 1992-08-02 |
| 110386 | d004 | 1992-08-02 | 1996-08-30 |
| 110420 | d004 | 1996-08-30 | 9999-01-01 |
| 110511 | d005 | 1985-01-01 | 1992-04-25 |
| 110567 | d005 | 1992-04-25 | 9999-01-01 |
| 110725 | d006 | 1985-01-01 | 1989-05-06 |
| 110765 | d006 | 1989-05-06 | 1991-09-12 |
| 110800 | d006 | 1991-09-12 | 1994-06-28 |
| 110854 | d006 | 1994-06-28 | 9999-01-01 |
| 111035 | d007 | 1985-01-01 | 1991-03-07 |
| 111133 | d007 | 1991-03-07 | 9999-01-01 |
| 111400 | d008 | 1985-01-01 | 1991-04-08 |
| 111534 | d008 | 1991-04-08 | 9999-01-01 |
| 111692 | d009 | 1985-01-01 | 1988-10-17 |
| 111784 | d009 | 1988-10-17 | 1992-09-08 |
| 111877 | d009 | 1992-09-08 | 1996-01-03 |
| 111939 | d009 | 1996-01-03 | 9999-01-01 |
+--------+---------+------------+------------+
删除表
mysql -e "drop table employees.dept_manager;"
查看分区大小
[root@node1 undrop-for-innodb-master]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sr0 11:0 1 4.3G 0 rom
vda 252:0 0 80G 0 disk
├─vda1 252:1 0 1G 0 part /boot
└─vda2 252:2 0 79G 0 part
├─centos-root 253:0 0 50G 0 lvm /
├─centos-swap 253:1 0 2G 0 lvm [SWAP]
└─centos-home 253:2 0 27G 0 lvm /home
vdb 252:16 0 100G 0 disk
└─vdb1 252:17 0 100G 0 part /data
TwinDB工具集可以用于恢复刚刚被误删除的表，如果误删除了employees.dept_manager表，这个表对应的独立表空间文件/var/lib/mysql/employees/dept_manager.ibd也被删除，前面已经拿到了表结构语句，然后使用下面的命令恢复InnoDB的页：
./stream_parser -f /dev/vda2 -t 79G
Worker(1): 95.81% done. 2024-01-22 18:19:33 ETA(in 00:00:04). Processing speed: 408.000 MiB/sec
Worker(1): 96.82% done. 2024-01-22 18:19:33 ETA(in 00:00:03). Processing speed: 408.000 MiB/sec
Worker(1): 97.82% done. 2024-01-22 18:19:36 ETA(in 00:00:04). Processing speed: 204.000 MiB/sec
Worker(1): 98.83% done. 2024-01-22 18:19:34 ETA(in 00:00:01). Processing speed: 408.000 MiB/sec
Worker(1): 99.84% done. 2024-01-22 18:19:34 ETA(in 00:00:00). Processing speed: 408.000 MiB/sec
All workers finished in 205 sec
这里的/dev/vda2是world.city表所在的硬盘分区，79G是这个分区的大小，如果这个表是放在通用表空间（innodb_file_per_table为OFF），删除这个表时不会同时删除这个表空间，应将上面命令中的/dev/vda2换成对应的表空间文件名（如：/var/lib/mysql/ibdata1）。
stream_parser会扫描这个分区，将所有的InnoDB页都解析出来放在下面的目录：
[root@node1 undrop-for-innodb-master]# ls pages-vda2/FIL_PAGE_INDEX
0000000000000001.page 0000000000000043.page 0000000000000081.page 0000000000000119.page 0000000000000166.page 0000000000000205.page 0000000000000278.page
0000000000000002.page 0000000000000044.page 0000000000000083.page 0000000000000120.page 0000000000000167.page 0000000000000206.page 0000000000000279.page
0000000000000003.page 0000000000000045.page 0000000000000084.page 0000000000000121.page 0000000000000168.page 0000000000000207.page 0000000000000280.page
0000000000000004.page 0000000000000046.page 0000000000000085.page 0000000000000122.page 0000000000000169.page 0000000000000208.page 0000000000000281.page
0000000000000005.page 0000000000000047.page 0000000000000086.page 0000000000000123.page 0000000000000170.page 0000000000000209.page 0000000000000308.page
0000000000000011.page 0000000000000048.page 0000000000000087.page 0000000000000124.page 0000000000000172.page 0000000000000210.page 0000000000000323.page
0000000000000012.page 0000000000000049.page 0000000000000088.page 0000000000000125.page 0000000000000173.page 0000000000000211.page 0000000000000324.page
0000000000000013.page 0000000000000050.page 0000000000000089.page 0000000000000126.page 0000000000000174.page 0000000000000212.page 0000000000000325.page
0000000000000014.page 0000000000000051.page 0000000000000090.page 0000000000000127.page 0000000000000175.page 0000000000000213.page 0000000000000326.page
0000000000000015.page 0000000000000052.page 0000000000000091.page 0000000000000128.page 0000000000000176.page 0000000000000214.page 0000000000000328.page
0000000000000016.page 0000000000000053.page 0000000000000092.page 0000000000000129.page 0000000000000177.page 0000000000000215.page 0000000000000329.page
0000000000000017.page 0000000000000054.page 0000000000000093.page 0000000000000130.page 0000000000000178.page 0000000000000216.page 0000000000000330.page
0000000000000018.page 0000000000000055.page 0000000000000094.page 0000000000000131.page 0000000000000179.page 0000000000000217.page 0000000000000331.page
0000000000000019.page 0000000000000056.page 0000000000000095.page 0000000000000132.page 0000000000000180.page 0000000000000218.page 0000000000000332.page
0000000000000020.page 0000000000000057.page 0000000000000096.page 0000000000000133.page 0000000000000181.page 0000000000000219.page 0000000000000333.page
0000000000000021.page 0000000000000059.page 0000000000000097.page 0000000000000134.page 0000000000000182.page 0000000000000220.page 0000000000000334.page
0000000000000022.page 0000000000000060.page 0000000000000098.page 0000000000000135.page 0000000000000183.page 0000000000000224.page 0000000000000335.page
0000000000000023.page 0000000000000061.page 0000000000000099.page 0000000000000136.page 0000000000000184.page 0000000000000225.page 0000000000000336.page
0000000000000024.page 0000000000000062.page 0000000000000100.page 0000000000000137.page 0000000000000185.page 0000000000000226.page 0000000000000337.page
0000000000000025.page 0000000000000063.page 0000000000000101.page 0000000000000138.page 0000000000000186.page 0000000000000227.page 0000000000000338.page
0000000000000026.page 0000000000000064.page 0000000000000102.page 0000000000000139.page 0000000000000187.page 0000000000000228.page 0000000000000339.page
0000000000000027.page 0000000000000065.page 0000000000000103.page 0000000000000140.page 0000000000000188.page 0000000000000229.page 0000000000000340.page
0000000000000028.page 0000000000000066.page 0000000000000104.page 0000000000000142.page 0000000000000189.page 0000000000000230.page 0000000000000341.page
0000000000000029.page 0000000000000067.page 0000000000000105.page 0000000000000143.page 0000000000000190.page 0000000000000235.page 0000000000000342.page
0000000000000030.page 0000000000000068.page 0000000000000106.page 0000000000000144.page 0000000000000191.page 0000000000000236.page 0000000000000343.page
0000000000000031.page 0000000000000069.page 0000000000000107.page 0000000000000145.page 0000000000000192.page 0000000000000238.page 0000000000000344.page
0000000000000032.page 0000000000000070.page 0000000000000108.page 0000000000000146.page 0000000000000193.page 0000000000000239.page 0000000000000345.page
0000000000000033.page 0000000000000071.page 0000000000000109.page 0000000000000148.page 0000000000000194.page 0000000000000251.page 0000000000000346.page
0000000000000034.page 0000000000000072.page 0000000000000110.page 0000000000000154.page 0000000000000196.page 0000000000000252.page 0000000000000347.page
0000000000000035.page 0000000000000073.page 0000000000000111.page 0000000000000155.page 0000000000000197.page 0000000000000253.page 0000000000000348.page
0000000000000036.page 0000000000000074.page 0000000000000112.page 0000000000000156.page 0000000000000198.page 0000000000000254.page 0000000000000349.page
0000000000000037.page 0000000000000075.page 0000000000000113.page 0000000000000157.page 0000000000000199.page 0000000000000255.page 18446744069414584320.page
0000000000000038.page 0000000000000076.page 0000000000000114.page 0000000000000158.page 0000000000000200.page 0000000000000268.page
0000000000000039.page 0000000000000077.page 0000000000000115.page 0000000000000159.page 0000000000000201.page 0000000000000269.page
0000000000000040.page 0000000000000078.page 0000000000000116.page 0000000000000163.page 0000000000000202.page 0000000000000270.page
0000000000000041.page 0000000000000079.page 0000000000000117.page 0000000000000164.page 0000000000000203.page 0000000000000276.page
0000000000000042.page 0000000000000080.page 0000000000000118.page 0000000000000165.page 0000000000000204.page 0000000000000277.page
根据之前生成的数据字典信息，使用下面的命令查询出employees.dept_manager的表ID：
mysql> select * from data_recovered.SYS_TABLES where name='employees/dept_manager';
+------------------------+-----+--------+------+--------+---------+--------------+-------+
| NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+------------------------+-----+--------+------+--------+---------+--------------+-------+
| employees/dept_manager | 247 | 4 | 33 | 0 | 80 | | 255 |
+------------------------+-----+--------+------+--------+---------+--------------+-------+
1 row in set (0.01 sec)
知道employees.dept_manager的表ID为247后，再查询主键的ID：
mysql> select * from data_recovered.SYS_INDEXES where table_id=247;
+----------+-----+---------+----------+------+-------+---------+
| TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+-----+---------+----------+------+-------+---------+
| 247 | 340 | PRIMARY | 2 | 3 | 255 | 3 |
| 247 | 341 | dept_no | 1 | 0 | 255 | 4 |
+----------+-----+---------+----------+------+-------+---------+
2 rows in set (0.00 sec)
知道主键的ID为340，因为InnoDB都是索引组织表，数据按主键进行存放，因此这个表的数据就在第340页，可以使用c_parser解析第340页，将数据输出到文件中：
./c_parser -6f pages-vda2/FIL_PAGE_INDEX/0000000000000340.page -t dept_manager.sql > dumps/default/dept_manager 2>dumps/default/dept_manager.sql
查看生成load data file
[root@node1 undrop-for-innodb-master]# cat dumps/default/dept_manager
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (24 24)
00000000423A EE000001410110 dept_manager 110022 "d001" "1985-01-01" "1991-10-01"
00000000423A EE000001410122 dept_manager 110039 "d001" "1991-10-01" "9999-01-01"
00000000423A EE000001410134 dept_manager 110085 "d002" "1985-01-01" "1989-12-17"
00000000423A EE000001410146 dept_manager 110114 "d002" "1989-12-17" "9999-01-01"
00000000423A EE000001410158 dept_manager 110183 "d003" "1985-01-01" "1992-03-21"
00000000423A EE00000141016A dept_manager 110228 "d003" "1992-03-21" "9999-01-01"
00000000423A EE00000141017C dept_manager 110303 "d004" "1985-01-01" "1988-09-09"
00000000423A EE00000141018E dept_manager 110344 "d004" "1988-09-09" "1992-08-02"
00000000423A EE0000014101A0 dept_manager 110386 "d004" "1992-08-02" "1996-08-30"
00000000423A EE0000014101B2 dept_manager 110420 "d004" "1996-08-30" "9999-01-01"
00000000423A EE0000014101C4 dept_manager 110511 "d005" "1985-01-01" "1992-04-25"
00000000423A EE0000014101D6 dept_manager 110567 "d005" "1992-04-25" "9999-01-01"
00000000423A EE0000014101E8 dept_manager 110725 "d006" "1985-01-01" "1989-05-06"
00000000423A EE0000014101FA dept_manager 110765 "d006" "1989-05-06" "1991-09-12"
00000000423A EE00000141020C dept_manager 110800 "d006" "1991-09-12" "1994-06-28"
00000000423A EE00000141021E dept_manager 110854 "d006" "1994-06-28" "9999-01-01"
00000000423A EE000001410230 dept_manager 111035 "d007" "1985-01-01" "1991-03-07"
00000000423A EE000001410242 dept_manager 111133 "d007" "1991-03-07" "9999-01-01"
00000000423A EE000001410254 dept_manager 111400 "d008" "1985-01-01" "1991-04-08"
00000000423A EE000001410266 dept_manager 111534 "d008" "1991-04-08" "9999-01-01"
00000000423A EE000001410278 dept_manager 111692 "d009" "1985-01-01" "1988-10-17"
00000000423A EE00000141028A dept_manager 111784 "d009" "1988-10-17" "1992-09-08"
00000000423A EE00000141029C dept_manager 111877 "d009" "1992-09-08" "1996-01-03"
00000000423A EE0000014102AE dept_manager 111939 "d009" "1996-01-03" "9999-01-01"
-- Page id: 3, Found records: 24, Lost records: NO, Leaf page: YES
查看生成的SQL文件：
[root@node1 undrop-for-innodb-master]# cat dumps/default/dept_manager.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/data/innodb-drop/dumps/default/dept_manager' REPLACE INTO TABLE `dept_manager` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'dept_manager\t' (`emp_no`, `dept_no`, `from_date`, `to_date`);
执行这个SQL文件将数据加载到数据库中：
mysql employees < dept_manager.sql #导入表结构
mysql -f employees < dumps/default/dept_manager.sql #导入表数据
验证数据
[root@node1 undrop-for-innodb-master]# mysql -e "select * from employees.dept_manager;"
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 110022 | d001 | 1985-01-01 | 1991-10-01 |
| 110039 | d001 | 1991-10-01 | 9999-01-01 |
| 110085 | d002 | 1985-01-01 | 1989-12-17 |
| 110114 | d002 | 1989-12-17 | 9999-01-01 |
| 110183 | d003 | 1985-01-01 | 1992-03-21 |
| 110228 | d003 | 1992-03-21 | 9999-01-01 |
| 110303 | d004 | 1985-01-01 | 1988-09-09 |
| 110344 | d004 | 1988-09-09 | 1992-08-02 |
| 110386 | d004 | 1992-08-02 | 1996-08-30 |
| 110420 | d004 | 1996-08-30 | 9999-01-01 |
| 110511 | d005 | 1985-01-01 | 1992-04-25 |
| 110567 | d005 | 1992-04-25 | 9999-01-01 |
| 110725 | d006 | 1985-01-01 | 1989-05-06 |
| 110765 | d006 | 1989-05-06 | 1991-09-12 |
| 110800 | d006 | 1991-09-12 | 1994-06-28 |
| 110854 | d006 | 1994-06-28 | 9999-01-01 |
| 111035 | d007 | 1985-01-01 | 1991-03-07 |
| 111133 | d007 | 1991-03-07 | 9999-01-01 |
| 111400 | d008 | 1985-01-01 | 1991-04-08 |
| 111534 | d008 | 1991-04-08 | 9999-01-01 |
| 111692 | d009 | 1985-01-01 | 1988-10-17 |
| 111784 | d009 | 1988-10-17 | 1992-09-08 |
| 111877 | d009 | 1992-09-08 | 1996-01-03 |
| 111939 | d009 | 1996-01-03 | 9999-01-01 |
+--------+---------+------------+------------+
恢复成功。
实验小结
TwinDB恢复不一定每一次都会成功，而且恢复出来的数据也不一定是用户想要的数据。所以强烈建议要有有效备份。
目前TwinDB仅支持5.7版本。
场景3：误删数据文件
模拟故障现场
造数据
flush logs;
create database if not exists test;
use test;
drop table if exists t ;
create table t(id int,create_time datetime);
insert into t values(1,now());
select sleep(1);
insert into t values(2,now());
select sleep(1);
insert into t values(3,now());
select * from t ;
show binary logs;
查看数据
mysql> select * from test.t;
+------+---------------------+
| id | create_time |
+------+---------------------+
| 1 | 2024-01-22 15:38:38 |
| 2 | 2024-01-22 15:38:39 |
| 3 | 2024-01-22 15:38:40 |
+------+---------------------+
3 rows in set (0.00 sec)
误删操作
[root@node1 test]# rm -rf t.ibd
插入数据，重启
insert into t values(4,now());
查询表，报错如下
mysql> select * from t;
ERROR 1812 (HY000): Tablespace is missing for table `test`.`t`.
恢复决策-1：文件句柄存在
利用lsof找回文件
[root@node1 ~]# lsof|grep t.ibd
mysqld 14961 mysql 22uW REG 253,0 98304 69165380 /var/lib/mysql/test/t.ibd
mysqld 14961 mysql 25uW REG 253,0 98304 105325304 /var/lib/mysql/mysql/server_cost.ibd
mysqld 14961 mysql 26uW REG 253,0 98304 105325306 /var/lib/mysql/mysql/engine_cost.ibd
mysqld 14961 14964 mysql 22uW REG 253,0 98304 69165380 /var/lib/mysql/test/t.ibd
mysqld 14961 14964 mysql 25uW REG 253,0 98304 105325304 /var/lib/mysql/mysql/server_cost.ibd
mysqld 14961 14964 mysql 26uW REG 253,0 98304 105325306 /var/lib/mysql/mysql/engine_cost.ibd
mysqld 14961 14965 mysql 22uW REG 253,0 98304 69165380 /var/lib/mysql/test/t.ibd
mysqld 14961 14965 mysql 25uW REG 253,0 98304 105325304 /var/lib/mysql/mysql/server_cost.ibd
mysqld 14961 14965 mysql 26uW REG 253,0 98304 105325306 /var/lib/mysql/mysql/engine_cost.ibd
mysqld 14961 14966 mysql 22uW REG 253,0 98304 69165380 /var/lib/mysql/test/t.ibd
mysqld 14961 14966 mysql 25uW REG 253,0 98304 105325304 /var/lib/mysql/mysql/server_cost.ibd
mysqld 14961 14966 mysql 26uW REG 253,0 98304 105325306 /var/lib/mysql/mysql/engine_cost.ibd
mysqld 14961 14967 mysql 22uW REG 253,0 98304 69165380 /var/lib/mysql/test/t.ibd
mysqld 14961 14967 mysql 25uW REG 253,0 98304 105325304 /var/lib/mysql/mysql/server_cost.ibd
mysqld 14961 14967 mysql 26uW REG 253,0 98304 105325306 /var/lib/mysql/mysql/engine_cost.ibd
mysqld 14961 14968 mysql 22uW REG 253,0 98304 69165380 /var/lib/mysql/test/t.ibd
mysqld 14961 14968 mysql 25uW REG 253,0 98304 105325304 /var/lib/mysql/mysql/server_cost.ibd
mysqld 14961 14968 mysql 26uW REG 253,0 98304 105325306 /var/lib/mysql/mysql/engine_cost.ibd
mysqld 14961 14969 mysql 22uW REG 253,0 98304 69165380 /var/lib/mysql/test/t.ibd
mysqld 14961 14969 mysql 25uW REG 253,0 98304 105325304 /var/lib/mysql/mysql/server_cost.ibd
mysqld 14961 14969 mysql 26uW REG 253,0 98304 105325306 /var/lib/mysql/mysql/engine_cost.ibd
mysqld 14961 14970 mysql 22uW REG 253,0 98304 69165380 /var/lib/mysql/test/t.ibd
mysqld 14961 14970 mysql 25uW REG 253,0 98304 105325304 /var/lib/mysql/mysql/server_cost.ibd
mysqld 14961 14970 mysql 26uW REG 253,0 98304 105325306 /var/lib/mysql/mysql/engine_cost.ibd
mysqld 14961 14971 mysql 22uW REG 253,0 98304 69165380 /var/lib/mysql/test/t.ibd
mysqld 14961 14971 mysql 25uW REG 253,0 98304 105325304 /var/lib/mysql/mysql/server_cost.ibd
mysqld 14961 14971 mysql 26uW REG 253,0 98304 105325306 /var/lib/mysql/mysql/engine_cost.ibd
mysqld 14961 14972 mysql 22uW REG 253,0 98304 69165380 /var/lib/mysql/test/t.ibd
mysqld 14961 14972 mysql 25uW REG 253,0 98304 105325304 /var/lib/mysql/mysql/server_cost.ibd
mysqld 14961 14972 mysql 26uW REG 253,0 98304 105325306 /var/lib/mysql/mysql/engine_cost.ibd
mysqld 14961 14973 mysql 22uW REG 253,0 98304 69165380 /var/lib/mysql/test/t.ibd
mysqld 14961 14973 mysql 25uW REG 253,0 98304 105325304 /var/lib/mysql/mysql/server_cost.ibd
mysqld 14961 14973 mysql 26uW REG 253,0 98304 105325306 /var/lib/mysql/mysql/engine_cost.ibd
mysqld 14961 14974 mysql 22uW REG 253,0 98304 69165380 /var/lib/mysql/test/t.ibd
......
文件pid为14961、文件句柄为22
cat /proc/14961/fd/22 > /var/lib/mysql/test/t.ibd
chown mysql.mysql /var/lib/mysql/test/t.ibd
重启数据库
systemctl restart mysqld
验证数据
[root@node1 test]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t ;
+------+---------------------+
| id | create_time |
+------+---------------------+
| 1 | 2024-01-22 15:38:38 |
| 2 | 2024-01-22 15:38:39 |
| 3 | 2024-01-22 15:38:40 |
| 4 | 2024-01-22 15:42:35 |
+------+---------------------+
4 rows in set (0.00 sec)
恢复决策-2：文件句柄不存在
1、停止对当前分区做任何操作，防止inode被覆盖。
2、可以使用dd命令对当前分区进行备份
dd if=/path/filename of=/dev/vdc1
3、通过umount命令，对当前设备分区卸载。或者fuser 命令。
umount /dev/vdb1 或者 umount /21yunwei
如果提示设备busy，可以用fuser命令强制卸载：fuser -m -v -i -k /21yunwei
4、下载第三方工具extundelete安装，搜索误删除的文件进行还原。
wget http://nchc.dl.sourceforge.net/project/extundelete/extundelete/0.2.4/extundelete-0.2.4.tar.bz2
tar jxvf extundelete-0.2.4.tar.bz2
cd extundelete-0.2.4
./configure
make && make install
可能需要一些依赖
yum -y install gcc-c++ e2fsprogs-devel
安装执行结果
[root@node1 extundelete-0.2.4]# ./configure
Configuring extundelete 0.2.4
Writing generated files to disk
[root@node1 extundelete-0.2.4]# make && make install
make -s all-recursive
Making all in src
extundelete.cc: In function ‘ext2_ino_t find_inode(ext2_filsys, ext2_filsys, ext2_inode*, std::string, int)’:
extundelete.cc:1272:29: warning: narrowing conversion of ‘search_flags’ from ‘int’ to ‘ext2_ino_t {aka unsigned int}’ inside { } [-Wnarrowing]
buf, match_name2, priv, 0};
^
Making install in src
/usr/bin/install -c extundelete '/usr/local/bin'
文件恢复
extundelete /dev/vdb1 --restore-file my.cnf
extundelete /dev/vdb1 --restore-directory deletetest
extundelete /dev/vdb1 --restore-all
可能是环境原因，这个工具恢复一直失败，有兴趣的小伙伴可以尝试一下。
如果这个方法不行，其实可以使用TwinDB扫描分区，来恢复误删的文件。
总结
没什么可说的，不重要的库也定期做个备份吧。可以降低频率，但是备份一定要有，而且要定期做有效性校验。
8.0版本可以在Git上查一下有没有类似工具，如：github.com/ddcw/ibd2sq…
附录
MySQL安装
1、下载MySQL
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.40-1.el7.x86_64.rpm-bundle.tar
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar
2、解压
tar xvf mysql-5.7.40-1.el7.x86_64.rpm-bundle.tar
3、安装软件
yum -y localinstall mysql-community-*
4、启动数据库
systemctl enable mysqld --now
5、查看登录密码
[root@node1 ~]# grep password /var/log/mysqld.log
2024-01-22T07:15:39.670314Z 1 [Note] A temporary password is generated for root@localhost: qjog3EsWYQ!J
6、登录并修改初始密码
[root@node1 ~]# grep password /var/log/mysqld.log
2024-01-22T07:15:39.670314Z 1 [Note] A temporary password is generated for root@localhost: qjog3EsWYQ!J
[root@node1 ~]# mysql -uroot -p'qjog3EsWYQ!J'
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 2
Server version: 5.7.40
Copyright (c) 2000, 2022, 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> alter user user() identified by 'Qwer@123';
Query OK, 0 rows affected (0.00 sec)
7、开启binlog，my.cnf设置
[mysqld]
# 开启binlog
log-bin=mysql-bin
server-id=1
重启MySQL
systemctl restart mysqld
挂载磁盘
[root@node1 extundelete-0.2.4]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sr0 11:0 1 4.3G 0 rom
vda 252:0 0 80G 0 disk
├─vda1 252:1 0 1G 0 part /boot
└─vda2 252:2 0 79G 0 part
├─centos-root 253:0 0 50G 0 lvm /
├─centos-swap 253:1 0 2G 0 lvm [SWAP]
└─centos-home 253:2 0 27G 0 lvm /home
vdb 252:16 0 100G 0 disk
[root@node1 extundelete-0.2.4]# fdisk /dev/vdb
Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0xad35fed0.
Command (m for help): n
Partition type:
p primary (0 primary, 0 extended, 4 free)
e extended
Select (default p):
Using default response p
Partition number (1-4, default 1):
First sector (2048-209715199, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-209715199, default 209715199):
Using default value 209715199
Partition 1 of type Linux and of size 100 GiB is set
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@node1 extundelete-0.2.4]# mkfs.ext4 /dev/vdb1
mke2fs 1.42.9 (28-Dec-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
6553600 inodes, 26214144 blocks
1310707 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2174746624
800 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424, 20480000, 23887872
Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done
[root@node1 extundelete-0.2.4]# mkdir /data
[root@node1 extundelete-0.2.4]# echo '/dev/vdb1 /data ext4 rw,noatime,nobarrier,data=writeback 0 0' >> /etc/fstab
[root@node1 extundelete-0.2.4]# mount -a
[root@node1 extundelete-0.2.4]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 50G 9.3G 41G 19% /
devtmpfs 903M 0 903M 0% /dev
tmpfs 919M 0 919M 0% /dev/shm
tmpfs 919M 9.3M 910M 2% /run
tmpfs 919M 0 919M 0% /sys/fs/cgroup
/dev/vda1 1014M 209M 806M 21% /boot
/dev/mapper/centos-home 27G 37M 27G 1% /home
tmpfs 184M 12K 184M 1% /run/user/42
tmpfs 184M 0 184M 0% /run/user/0
/dev/vdb1 99G 61M 94G 1% /data
卸载MySQL
systemctl stop mysqld #关闭数据库
systemctl disable mysqld
userdel -fr mysql #删除用户
yum remove -y mysql --nodeps
yum remove -y mysql-community-* --nodeps
rpm -qa | grep mysql
rpm -qa|grep mysql|xargs rpm -e --nodeps
rm -rf /var/lib/mysql
rm -rf /var/log/mysqld.log
rm -rf /usr/lib/mysql
rm -rf /usr/include/mysql
rm -rf /etc/my.cnf
rm -rf /run/lock/subsys/mysql
装载示例数据库
cd /root
wget https://downloads.mysql.com/docs/world-db.tar.gz
wget https://downloads.mysql.com/docs/world_x-db.tar.gz
wget https://downloads.mysql.com/docs/sakila-db.tar.gz
wget https://github.com/datacharmer/test_db/archive/refs/heads/master.zip
unzip test_db-master.zip
tar xvf sakila-db.tar.gz
tar xvf world-db.tar.gz
tar xvf world_x-db.tar.gz
#导入employees
cd /root/test_db-master
mysql -t < employees.sql
#导入sakila
mysql -t < /root/sakila-db/sakila-schema.sql
mysql -t < /root/sakila-db/sakila-data.sql
#导入world
mysql -t < /root/world-db/world.sql
#导入world_x
mysql -t < /root/world_x-db/world_x.sql
参考文献：
cloud.tencent.com/developer/a…
