MySQL Case-在线表误删除恢复

姚崇·恒生电子运维专家

今天晚上业务系统升级，提前跑到表结构中，表需要减字段，在执行drop colum时报如下错误：

登录到服务器上，查看表真的不存在了，只剩下frm文件

[root@zjedunode1 book]# ls -l | grep zjedu
-rw-r----- 1 mysql mysql 14247 Aug 10 19:48 zjedu_cart_3301.frm

索性，实例并没有关闭

[root@centos7 test]# ps -ef | grep mysqld
root     12392 23223  0 2020 pts/4    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=mysql
mysql    13380 12392  7 2020 pts/4    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql_data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=error.log --pid-file=/var/run/mysqld/mysqld3306.pid --socket=/tmp/mysql3306.sock
[root@zjedunode1 book]# cd /proc/13380/fd
[root@zjedunode1 fd]# ls -l | grep zjedu_cart_3301
lrwx------ 1 root root 64 Aug 10 19:49 154 -> /data/mysql_data/book/zjedu_cart_3301.ibd

看到文件描述符尾54，下面拷贝到ibd文件

[root@zjedunode1 fd]# ls -l /proc/13380/fd/54 
lrwx------ 1 root root 64 Aug 10 19:52 /proc/13380/fd/54 -> /data/mysql_data/book/zjedu_cart_3301.ibd (deleted)

[root@zjedunode1 fd]# cp /proc/13380/fd/154  /data/mysql_data/book/zjedu_cart_3301.ibd
[root@zjedunode1 fd]# 
[root@zjedunode1 fd]# chown -R mysql:mysql /data/mysql_data/book/zjedu_cart_3301.ibd

[root@zjedunode1 fd]# du -sh /data/mysql_data/book/zjedu_cart_3301.ibd
9M    /data/mysql_data/book/zjedu_cart_3301.ibd

上述的过程中，即便是没有ibd文件，表数据是可以写入的，没有问题，这个结论我们后面测试会验证。那么拷贝文件后可以直接执行alter table drop column操作。

rm 删除时并非是被真正地擦除，特别是当文件还在被进程打开时，是可以完整地恢复的。 linux 的每个文件对应一个 inode 号，它有两个计数 i_count (内存引用计数，当前使用这个文件的进程数量) 和 i_nlink （磁盘引用计数，硬链接数量）。rm 操作时，把 i_nlink 数量减1 ,但只要 i_nlink 和 i_count 任意一个不为 0 ，该文件并没有被系统删除，所占用的空间不会被回收，占用 df 里面显示的硬盘容量，数据不会被覆盖。

测试

下面我使用sysbench，建立一张100万的大表，建立过程中我用其他文件覆盖ibd文件

[root@centos7 test]# cp /data/undolog/undo001 sbtest1.ibd 
cp: overwrite ‘sbtest1.ibd’? y

压测完后执行如下命令

mysql> alter table sbtest1 add c_3 varchar(10);
Query OK, 0 rows affected (1 min 17.80 sec)
Records: 0  Duplicates: 0  Warnings: 0

看到，居然可以执行成功，同时文件发生了下面变化，做文件移动添加列。

[root@centos7 test]# ls -ltr
total 1257508
-rw-r----- 1 mysql mysql         60 Aug 10 20:42 db.opt
-rw-r----- 1 mysql mysql       8660 Aug 10 20:45 sbtest1.frm
-rw-r----- 1 mysql mysql 1073741824 Aug 10 20:47 sbtest1.ibd
-rw-r----- 1 mysql mysql       8688 Aug 10 20:48 #sql-64ab_8.frm
-rw-r----- 1 mysql mysql  213909504 Aug 10 20:48 #sql-ib41-1797660830.ibd
[root@centos7 test]# ls -ltr
total 266260
-rw-r----- 1 mysql mysql        60 Aug 10 20:42 db.opt
-rw-r----- 1 mysql mysql      8688 Aug 10 20:48 sbtest1.frm
-rw-r----- 1 mysql mysql 272629760 Aug 10 20:49 sbtest1.ibd

可以正常关闭和重启数据库

后面我又压测了大表，error log发生如下错误，实例挂掉了。。

2021-08-10T21:04:56.226283+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1076870656
2021-08-10T21:04:56.292347+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1079492096
2021-08-10T21:04:56.349326+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1082113536
2021-08-10T21:04:56.407244+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1084734976
2021-08-10T21:04:56.458629+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1087356416
2021-08-10T21:04:56.503297+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1089977856
2021-08-10T21:04:56.587941+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1092599296
2021-08-10T21:04:56.657158+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1095220736
2021-08-10T21:04:56.706030+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1097842176
2021-08-10T21:04:56.761245+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1100463616
2021-08-10T21:04:56.819183+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1103085056
2021-08-10T21:04:56.867487+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1105706496
2021-08-10T21:04:56.953582+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1108327936
2021-08-10T21:04:57.004460+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1110949376
2021-08-10T21:04:57.062936+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1113570816
2021-08-10T21:04:57.120497+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1116192256
2021-08-10T21:04:57.176332+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1118813696
2021-08-10T21:04:57.227015+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1121435136
2021-08-10T21:04:57.305997+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1124056576
2021-08-10T21:04:57.362210+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1126678016
2021-08-10T21:04:57.421145+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1129299456
2021-08-10T21:04:57.460069+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1131920896
2021-08-10T21:04:57.504071+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1134542336
2021-08-10T21:04:57.559499+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1137163776
2021-08-10T21:04:57.606916+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1139785216
2021-08-10T21:04:57.687921+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1142406656
2021-08-10T21:04:57.736950+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1145028096
2021-08-10T21:04:57.782839+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1147649536
2021-08-10T21:04:57.845319+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1150270976
2021-08-10T21:04:57.893591+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1152892416
2021-08-10T21:04:57.955149+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1155513856
2021-08-10T21:04:58.024954+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1158135296
2021-08-10T21:04:58.069940+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1160756736
2021-08-10T21:04:58.128675+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1163378176
2021-08-10T21:04:58.196792+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1165999616
2021-08-10T21:04:58.241943+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1168621056
2021-08-10T21:04:58.295918+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1171242496
2021-08-10T21:04:58.360563+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1173863936
2021-08-10T21:04:58.428210+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1176485376
2021-08-10T21:04:58.492683+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1179106816
2021-08-10T21:04:58.535589+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1181728256
2021-08-10T21:04:58.590320+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1184349696
2021-08-10T21:04:58.641311+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1186971136
2021-08-10T21:04:58.712511+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1189592576
2021-08-10T21:04:58.764200+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1192214016
2021-08-10T21:04:58.812713+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1194835456
2021-08-10T21:04:58.873750+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1197456896
2021-08-10T21:04:58.932192+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1200078336
2021-08-10T21:04:58.983502+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1202699776
2021-08-10T21:04:59.050752+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1205321216
2021-08-10T21:04:59.110153+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1207942656
2021-08-10T21:04:59.162529+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1210564096
2021-08-10T21:04:59.242433+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1213185536
2021-08-10T21:04:59.287224+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1215806976
2021-08-10T21:04:59.337811+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1218428416
2021-08-10T21:04:59.416785+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1221049856
2021-08-10T21:04:59.478312+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1223671296
2021-08-10T21:04:59.535700+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1226292736
2021-08-10T21:04:59.584196+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1228914176
2021-08-10T21:04:59.630582+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1231535616
2021-08-10T21:04:59.716552+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1234157056
2021-08-10T21:04:59.771982+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1236778496
2021-08-10T21:04:59.824743+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1239399936
2021-08-10T21:04:59.875324+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1242021376
2021-08-10T21:04:59.917318+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1244642816
2021-08-10T21:04:59.983742+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1247264256
2021-08-10T21:05:00.053646+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1249885696
2021-08-10T21:05:00.101652+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1252507136
2021-08-10T21:05:00.166341+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1255128576
2021-08-10T21:05:00.209384+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1257750016
2021-08-10T21:05:00.268043+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1260371456
2021-08-10T21:05:00.318967+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1262992896
2021-08-10T21:05:00.385160+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1265614336
2021-08-10T21:05:00.443878+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1268235776
2021-08-10T21:05:00.510935+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1270857216
2021-08-10T21:05:00.555194+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1273478656
2021-08-10T21:05:00.604821+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1276100096
2021-08-10T21:05:00.652140+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1278721536
2021-08-10T21:05:00.722351+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1281342976
2021-08-10T21:05:00.797249+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1283964416
2021-08-10T21:05:00.843349+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1286585856
2021-08-10T21:05:00.901191+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1289207296
2021-08-10T21:05:00.981130+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1291828736
2021-08-10T21:05:01.025455+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1294450176
2021-08-10T21:05:01.094802+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1297071616
2021-08-10T21:05:01.140032+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1299693056
2021-08-10T21:05:01.218791+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1302314496
2021-08-10T21:05:01.276813+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1304935936
2021-08-10T21:05:01.320079+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1307557376
2021-08-10T21:05:01.367485+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1310178816
2021-08-10T21:05:01.431033+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1312800256
2021-08-10T21:05:01.505286+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1315421696
2021-08-10T21:05:01.563323+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1318043136
2021-08-10T21:05:01.610009+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1320664576
2021-08-10T21:05:01.676233+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1323286016
2021-08-10T21:05:01.745959+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1325907456
2021-08-10T21:05:01.786304+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1328528896
2021-08-10T21:05:01.857025+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1331150336
2021-08-10T21:05:01.918199+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1333771776
2021-08-10T21:05:01.986758+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1336393216
2021-08-10T21:05:02.041922+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1339014656
2021-08-10T21:05:02.161339+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1341636096
2021-08-10T21:05:02.272757+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1344257536
2021-08-10T21:05:02.342122+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1346878976
2021-08-10T21:05:02.392506+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1349500416
2021-08-10T21:05:02.455181+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1352121856
2021-08-10T21:05:02.525594+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1354743296
2021-08-10T21:05:02.570738+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1357364736
2021-08-10T21:05:02.625662+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1359986176
2021-08-10T21:05:02.693207+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1362607616
2021-08-10T21:05:02.770030+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1365229056
2021-08-10T21:05:02.824075+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1367850496
2021-08-10T21:05:02.878030+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1370471936
2021-08-10T21:05:02.931296+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1373093376
2021-08-10T21:05:03.005646+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1375714816
2021-08-10T21:05:03.051307+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1378336256
2021-08-10T21:05:03.153900+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1380957696
2021-08-10T21:05:03.222433+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1383579136
2021-08-10T21:05:03.292495+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1386200576
2021-08-10T21:05:03.344133+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1388822016
2021-08-10T21:05:03.392146+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1391443456
2021-08-10T21:05:03.464498+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1394064896
2021-08-10T21:05:03.539752+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1396686336
2021-08-10T21:05:03.600282+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1399307776
2021-08-10T21:05:03.652687+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1401929216
2021-08-10T21:05:03.699520+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1404550656
2021-08-10T21:05:03.770490+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1407172096
2021-08-10T21:05:03.845307+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1409793536
2021-08-10T21:05:03.898261+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1412414976
2021-08-10T21:05:03.951515+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1415036416
2021-08-10T21:05:04.029008+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1417657856
2021-08-10T21:05:04.072634+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1420279296
2021-08-10T21:05:04.130533+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1422900736
2021-08-10T21:05:04.193872+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1425522176
2021-08-10T21:05:04.261130+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1428143616
2021-08-10T21:05:04.333009+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1430765056
2021-08-10T21:05:04.376987+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1433386496
2021-08-10T21:05:04.433517+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1436007936
2021-08-10T21:05:04.490440+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1438629376
2021-08-10T21:05:04.575521+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1441250816
2021-08-10T21:05:04.635721+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1443872256
2021-08-10T21:05:04.687090+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1446493696
2021-08-10T21:05:04.744352+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1449115136
2021-08-10T21:05:04.828518+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1451736576
2021-08-10T21:05:04.870856+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1454358016
2021-08-10T21:05:04.967214+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1456979456
2021-08-10T21:05:05.034472+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1459600896
2021-08-10T21:05:05.099743+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1462222336
2021-08-10T21:05:05.158730+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1464843776
2021-08-10T21:05:05.216350+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1467465216
2021-08-10T21:05:05.282462+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1470086656
2021-08-10T21:05:05.346250+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1472708096
2021-08-10T21:05:05.396592+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1475329536
2021-08-10T21:05:05.453580+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1477950976
2021-08-10T21:05:05.534414+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1480572416
2021-08-10T21:05:05.600307+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1483193856
2021-08-10T21:05:05.661337+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1485815296
2021-08-10T21:05:05.716918+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1488436736
2021-08-10T21:05:05.769187+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1491058176
2021-08-10T21:05:05.847980+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1493679616
2021-08-10T21:05:05.892631+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1496301056
2021-08-10T21:05:05.973929+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1498922496
2021-08-10T21:05:06.082849+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1501543936
2021-08-10T21:05:06.136049+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1504165376
2021-08-10T21:05:06.152180+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1505042462
2021-08-10T21:05:06.152589+08:00 0 [Note] InnoDB: Database was not shutdown normally!
2021-08-10T21:05:06.152728+08:00 0 [Note] InnoDB: Starting crash recovery.
2021-08-10T21:05:06.156707+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91183] from the doublewrite buffer.
2021-08-10T21:05:06.158760+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91184] from the doublewrite buffer.
2021-08-10T21:05:06.161507+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91185] from the doublewrite buffer.
2021-08-10T21:05:06.163872+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91186] from the doublewrite buffer.
2021-08-10T21:05:06.168061+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91187] from the doublewrite buffer.
2021-08-10T21:05:06.170209+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91188] from the doublewrite buffer.
2021-08-10T21:05:06.172204+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91189] from the doublewrite buffer.
2021-08-10T21:05:06.174640+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91190] from the doublewrite buffer.
2021-08-10T21:05:06.177686+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91191] from the doublewrite buffer.
2021-08-10T21:05:06.179713+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91192] from the doublewrite buffer.
2021-08-10T21:05:06.181739+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91193] from the doublewrite buffer.
2021-08-10T21:05:06.183849+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91194] from the doublewrite buffer.
2021-08-10T21:05:06.185810+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91195] from the doublewrite buffer.
2021-08-10T21:05:06.187840+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91196] from the doublewrite buffer.
2021-08-10T21:05:06.189942+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91197] from the doublewrite buffer.
2021-08-10T21:05:06.192185+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91198] from the doublewrite buffer.
2021-08-10T21:05:06.194744+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91199] from the doublewrite buffer.
2021-08-10T21:05:06.198140+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91648] from the doublewrite buffer.
2021-08-10T21:05:06.201173+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91649] from the doublewrite buffer.
2021-08-10T21:05:06.203242+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91650] from the doublewrite buffer.
2021-08-10T21:05:06.205367+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91651] from the doublewrite buffer.
2021-08-10T21:05:06.209344+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91652] from the doublewrite buffer.
2021-08-10T21:05:06.211372+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91653] from the doublewrite buffer.
2021-08-10T21:05:06.214428+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91654] from the doublewrite buffer.
2021-08-10T21:05:06.216609+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91655] from the doublewrite buffer.
2021-08-10T21:05:06.219521+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91656] from the doublewrite buffer.
2021-08-10T21:05:06.222568+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91657] from the doublewrite buffer.
2021-08-10T21:05:06.226539+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91658] from the doublewrite buffer.
2021-08-10T21:05:06.228726+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91659] from the doublewrite buffer.
2021-08-10T21:05:06.231771+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91660] from the doublewrite buffer.
2021-08-10T21:05:06.234819+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91661] from the doublewrite buffer.
2021-08-10T21:05:06.237872+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91662] from the doublewrite buffer.
2021-08-10T21:05:06.240909+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91663] from the doublewrite buffer.
2021-08-10T21:05:06.243952+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91664] from the doublewrite buffer.
2021-08-10T21:05:06.247095+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91665] from the doublewrite buffer.
2021-08-10T21:05:06.250139+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91666] from the doublewrite buffer.
2021-08-10T21:05:06.253185+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91667] from the doublewrite buffer.
2021-08-10T21:05:06.256244+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91668] from the doublewrite buffer.
2021-08-10T21:05:06.259290+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91669] from the doublewrite buffer.
2021-08-10T21:05:06.261323+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91670] from the doublewrite buffer.
2021-08-10T21:05:06.264369+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91671] from the doublewrite buffer.
2021-08-10T21:05:06.267480+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91672] from the doublewrite buffer.
2021-08-10T21:05:06.271484+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91673] from the doublewrite buffer.
2021-08-10T21:05:06.273513+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91674] from the doublewrite buffer.
2021-08-10T21:05:06.275547+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91675] from the doublewrite buffer.
2021-08-10T21:05:06.278594+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91676] from the doublewrite buffer.
2021-08-10T21:05:06.281740+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91677] from the doublewrite buffer.
2021-08-10T21:05:06.285740+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91678] from the doublewrite buffer.
2021-08-10T21:05:06.289796+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91679] from the doublewrite buffer.
2021-08-10T21:05:06.292177+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91680] from the doublewrite buffer.
2021-08-10T21:05:06.293966+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91681] from the doublewrite buffer.
2021-08-10T21:05:06.296926+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91682] from the doublewrite buffer.
2021-08-10T21:05:06.299932+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91683] from the doublewrite buffer.
2021-08-10T21:05:06.305358+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91684] from the doublewrite buffer.
2021-08-10T21:05:06.307661+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91685] from the doublewrite buffer.
2021-08-10T21:05:06.310108+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91686] from the doublewrite buffer.
2021-08-10T21:05:06.312471+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91687] from the doublewrite buffer.
2021-08-10T21:05:06.316561+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91688] from the doublewrite buffer.
2021-08-10T21:05:06.319419+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91689] from the doublewrite buffer.
2021-08-10T21:05:06.323320+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91690] from the doublewrite buffer.
2021-08-10T21:05:06.326558+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91691] from the doublewrite buffer.
2021-08-10T21:05:06.330396+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91692] from the doublewrite buffer.
2021-08-10T21:05:06.332448+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91693] from the doublewrite buffer.
2021-08-10T21:05:06.336888+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91694] from the doublewrite buffer.
2021-08-10T21:05:06.338665+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91695] from the doublewrite buffer.
2021-08-10T21:05:06.340766+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91696] from the doublewrite buffer.
2021-08-10T21:05:06.343707+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91697] from the doublewrite buffer.
2021-08-10T21:05:06.345748+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91698] from the doublewrite buffer.
2021-08-10T21:05:06.347779+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91699] from the doublewrite buffer.
2021-08-10T21:05:06.350838+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91700] from the doublewrite buffer.
2021-08-10T21:05:06.352909+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91701] from the doublewrite buffer.
2021-08-10T21:05:06.355945+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91702] from the doublewrite buffer.
2021-08-10T21:05:06.365065+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91703] from the doublewrite buffer.
2021-08-10T21:05:06.367096+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91704] from the doublewrite buffer.
2021-08-10T21:05:06.370134+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91705] from the doublewrite buffer.
2021-08-10T21:05:06.372166+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91706] from the doublewrite buffer.
2021-08-10T21:05:06.374197+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91707] from the doublewrite buffer.
2021-08-10T21:05:06.377308+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91708] from the doublewrite buffer.
2021-08-10T21:05:06.379943+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91709] from the doublewrite buffer.
2021-08-10T21:05:06.382363+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91710] from the doublewrite buffer.
2021-08-10T21:05:06.385415+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91711] from the doublewrite buffer.
2021-08-10T21:05:06.387446+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=92160] from the doublewrite buffer.
2021-08-10T21:05:06.389512+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=92161] from the doublewrite buffer.
2021-08-10T21:05:06.392561+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=92162] from the doublewrite buffer.
2021-08-10T21:05:06.395613+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=92163] from the doublewrite buffer.
2021-08-10T21:05:06.398670+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=92164] from the doublewrite buffer.
2021-08-10T21:05:06.408240+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=92165] from the doublewrite buffer.
2021-08-10T21:05:06.411275+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=92166] from the doublewrite buffer.
2021-08-10T21:05:06.414269+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=92167] from the doublewrite buffer.
2021-08-10T21:05:06.417356+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=92168] from the doublewrite buffer.
2021-08-10T21:05:06.419387+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=92169] from the doublewrite buffer.
2021-08-10T21:05:06.422436+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=92170] from the doublewrite buffer.
2021-08-10T21:05:06.425473+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=92171] from the doublewrite buffer.
2021-08-10T21:05:06.427516+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=92172] from the doublewrite buffer.
2021-08-10T21:05:06.430558+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91878] from the doublewrite buffer.
2021-08-10T21:05:06.433605+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91879] from the doublewrite buffer.
2021-08-10T21:05:06.435715+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91435] from the doublewrite buffer.
2021-08-10T21:05:06.438016+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91436] from the doublewrite buffer.
2021-08-10T21:05:06.441929+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91437] from the doublewrite buffer.
2021-08-10T21:05:06.444042+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91438] from the doublewrite buffer.
2021-08-10T21:05:06.447886+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91439] from the doublewrite buffer.
2021-08-10T21:05:06.450923+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91440] from the doublewrite buffer.
2021-08-10T21:05:06.453056+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91441] from the doublewrite buffer.
2021-08-10T21:05:06.455245+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91442] from the doublewrite buffer.
2021-08-10T21:05:06.458089+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91443] from the doublewrite buffer.
2021-08-10T21:05:06.460159+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91444] from the doublewrite buffer.
2021-08-10T21:05:06.462952+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91445] from the doublewrite buffer.
2021-08-10T21:05:06.465247+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91446] from the doublewrite buffer.
2021-08-10T21:05:06.467350+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91447] from the doublewrite buffer.
2021-08-10T21:05:06.470381+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91448] from the doublewrite buffer.
2021-08-10T21:05:06.475435+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91449] from the doublewrite buffer.
2021-08-10T21:05:06.478455+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91450] from the doublewrite buffer.
2021-08-10T21:05:06.480492+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91451] from the doublewrite buffer.
2021-08-10T21:05:06.482535+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91452] from the doublewrite buffer.
2021-08-10T21:05:06.484612+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=91453] from the doublewrite buffer.
2021-08-10T21:05:06.491676+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=90529] from the doublewrite buffer.
2021-08-10T21:05:06.494728+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=90530] from the doublewrite buffer.
2021-08-10T21:05:06.497776+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=90531] from the doublewrite buffer.
2021-08-10T21:05:06.500841+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=90532] from the doublewrite buffer.
2021-08-10T21:05:06.502925+08:00 0 [Note] InnoDB: Recovered page [page id: space=31, page number=90533] from the doublewrite buffer.
2021-08-10T21:05:06.813579+08:00 0 [Note] InnoDB: Transaction 4010 was in the XA prepared state.
2021-08-10T21:05:06.871378+08:00 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 0 row operations
 to undo
2021-08-10T21:05:06.871414+08:00 0 [Note] InnoDB: Trx id counter is 4352
2021-08-10T21:05:06.871424+08:00 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 2021-08-10T21:05:06.884440+08:00 0 [ERROR] InnoDB: Trying to access page number 138144 in space 31, spa
ce name test/sbtest1, which is outside the tablespace bounds. Byte offset 0, len 8192, i/o type read. If you get this error at mysql
d startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2021-08-10T21:05:06.884483+08:00 0 [ERROR] InnoDB: Server exits.

/etc/my.cnf加入参数innodb_force_recovery=1启动无效

innodb_force_recovery= 6 可以启动数据库，使用mysqldump备份数据，不过被覆盖的数据只能备份表结构了，数据不存在了。

后面我们看下这种情况下的数据恢复

更多文章欢迎关注本人公众号，搜dbachongzi或扫二维码

作者：姚崇 Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA认证，擅长基于Oracle、MySQL Performance Turning及多种关系型 NoSQL数据库。

MySQL/PostgreSQL/Oracle优化案例

MySQL Case-在线表误删除恢复

测试

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐