数据库系列之MySQL表ibd文件删除恢复

作者：mobiledu2502911857 | 来源：互联网 | 2023-09-23 09:36

前段时间遇到过因为mysql表ibd文件被删除后的应急处理，直接删除表文件是严厉禁止的操作，这里测试下几种情况下的应急恢复过程。1、ibd文件被清空或误删除1）创建表sbtest1

前段时间遇到过因为mysql表ibd文件被删除后的应急处理，直接删除表文件是严厉禁止的操作，这里测试下几种情况下的应急恢复过程。

1、ibd文件被清空或误删除

1）创建表sbtest1并插入数据

CREATE TABLE `sbtest1` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `k` int unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `c1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2）批量插入数据

[root@tango-GDB-DB01 sysbench-1.0]# sysbench ./tests/include/oltp_legacy/insert.lua --mysql-host=192.168.112.121 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=testdb --oltp-test-mode=complex --oltp-tables-count=1 --oltp-table-size=100000 --events=100000 --time=1000 --report-interval=1 run

3）删除ibd文件

[root@tango-GDB-DB01 testdb]# ll -rw-r-----. 1 mysql mysql 15728640 Nov 20 19:35 sbtest1.ibd [root@tango-GDB-DB01 testdb]# rm -f sbtest1.ibd

以下将分为几种场景来处理以上故障。

单实例mysql进程没有重启
单实例mysql进程重启
主从复制架构

1.1 单实例MySQL进程没有重启

1）查看mysql进程正常运行

[root@tango-GDB-DB01 ~]# service mysql status SUCCESS! MySQL running (1873)

2）查看Mysql数据库进程所用使用的文件句柄

[root@tango-GDB-DB01 1873]# cd /proc/1873/fd [root@tango-GDB-DB01 fd]# ll total 0 lr-x------. 1 root root 64 Nov 21 19:39 0 -> /dev/null l-wx------. 1 root root 64 Nov 21 19:39 1 -> /usr/local/mysql/data/tango-GDB-DB01.err lrwx------. 1 root root 64 Nov 21 19:39 10 -> /usr/local/mysql/data/#ib_16384_0.dblwr lrwx------. 1 root root 64 Nov 21 19:39 11 -> /usr/local/mysql/data/#ib_16384_1.dblwr lrwx------. 1 root root 64 Nov 21 19:39 12 -> /usr/local/mysql/data/undo_001 lrwx------. 1 root root 64 Nov 21 19:39 13 -> /tmp/ibQ3dqia (deleted) lrwx------. 1 root root 64 Nov 21 19:39 14 -> /usr/local/mysql/data/undo_002 lrwx------. 1 root root 64 Nov 21 19:39 15 -> /usr/local/mysql/data/ibtmp1 lrwx------. 1 root root 64 Nov 21 19:39 16 -> /usr/local/mysql/data/mysql.ibd lrwx------. 1 root root 64 Nov 21 19:39 17 -> anon_inode:[eventpoll] lr-x------. 1 root root 64 Nov 21 19:39 18 -> pipe:[23795] l-wx------. 1 root root 64 Nov 21 19:39 19 -> pipe:[23795] l-wx------. 1 root root 64 Nov 21 19:39 2 -> /usr/local/mysql/data/tango-GDB-DB01.err lrwx------. 1 root root 64 Nov 21 19:39 20 -> anon_inode:[eventfd] lrwx------. 1 root root 64 Nov 21 19:39 21 -> socket:[23797] lrwx------. 1 root root 64 Nov 21 19:39 22 -> socket:[23798] lrwx------. 1 root root 64 Nov 21 19:39 23 -> socket:[23800] l-wx------. 1 root root 64 Nov 21 19:39 24 -> /usr/local/mysql/data/binlog.000036 lrwx------. 1 root root 64 Nov 21 19:39 25 -> socket:[23801] lrwx------. 1 root root 64 Nov 21 19:39 26 -> /usr/local/mysql/data/#innodb_temp/temp_1.ibt lrwx------. 1 root root 64 Nov 21 19:39 27 -> /usr/local/mysql/data/#innodb_temp/temp_2.ibt lrwx------. 1 root root 64 Nov 21 19:39 28 -> /usr/local/mysql/data/#innodb_temp/temp_3.ibt lrwx------. 1 root root 64 Nov 21 19:39 29 -> /usr/local/mysql/data/#innodb_temp/temp_4.ibt lrwx------. 1 root root 64 Nov 21 19:39 3 -> /usr/local/mysql/data/binlog.index lrwx------. 1 root root 64 Nov 21 19:39 30 -> /usr/local/mysql/data/#innodb_temp/temp_5.ibt lrwx------. 1 root root 64 Nov 21 19:39 31 -> /usr/local/mysql/data/#innodb_temp/temp_6.ibt lrwx------. 1 root root 64 Nov 21 19:39 32 -> /usr/local/mysql/data/#innodb_temp/temp_7.ibt lrwx------. 1 root root 64 Nov 21 19:39 33 -> /usr/local/mysql/data/#innodb_temp/temp_8.ibt lrwx------. 1 root root 64 Nov 21 19:39 34 -> /usr/local/mysql/data/#innodb_temp/temp_9.ibt lrwx------. 1 root root 64 Nov 21 19:39 35 -> /usr/local/mysql/data/#innodb_temp/temp_10.ibt lrwx------. 1 root root 64 Nov 21 19:39 36 -> socket:[25252] lrwx------. 1 root root 64 Nov 21 19:39 37 -> /usr/local/mysql/data/testdb/sbtest1.ibd (deleted) lrwx------. 1 root root 64 Nov 21 19:39 4 -> /usr/local/mysql/data/ib_logfile0 lrwx------. 1 root root 64 Nov 21 19:39 5 -> /tmp/ibvnyRpe (deleted) lrwx------. 1 root root 64 Nov 21 19:39 6 -> /tmp/ibTk4WGX (deleted) lrwx------. 1 root root 64 Nov 21 19:39 7 -> /tmp/ibJF9ehq (deleted) lrwx------. 1 root root 64 Nov 21 19:39 8 -> /usr/local/mysql/data/ibdata1 lrwx------. 1 root root 64 Nov 21 19:39 9 -> /usr/local/mysql/data/ib_logfile1

3）这个表在一定的时间范围内，还是可以进行DML操作

mysql> insert into sbtest1(k,c,pad) values(100,100,100); Query OK, 1 row affected (0.01 sec)

4）为了保险起见，建议将这个表进行lock操作，只能read操作

mysql> lock table sbtest1 read; Query OK, 0 rows affected (0.00 sec) mysql> insert into sbtest1(k,c,pad) values(100,100,100); ERROR 1099 (HY000): Table 'sbtest1' was locked with a READ lock and can't be updated

5）将句柄中的ibd文件拷贝到data目录

[root@tango-GDB-DB01 fd]# cp /proc/1873/fd/37 /usr/local/mysql/data/testdb/sbtest1.ibd

6）对拷贝文件赋权

[root@tango-GDB-DB01 testdb]# ll total 30752 -rw-r-----. 1 root root 15728640 Nov 21 20:49 sbtest1.ibd [root@tango-GDB-DB01 testdb]# chown mysql:mysql sbtest1.ibd [root@tango-GDB-DB01 testdb]# ll total 30752 -rw-r-----. 1 mysql mysql 15728640 Nov 21 20:49 sbtest1.ibd

如果不执行此步，在重启mysql时候会提示：

[Warning] [MY-012197] [InnoDB] Unable to open './testdb/sbtest1.ibd'

7）可以看到文件已经完全找回去了。这个时候，可以将表的read lock释放了

mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> insert into sbtest1(k,c,pad) values(100,100,101); Query OK, 1 row affected (0.01 sec)

8）重启mysql，查看mysql日志，没有异常报错信息。

[root@tango-GDB-DB01 testdb]# service mysql restart

为什么能通过这种方式恢复rm掉的ibd文件呢，主要是因为用rm命令删除的时候，Mysql数据库进程还在持有被删除的ibd文件的句柄，也就是在/proc/{mysql_pid}/pd目录下可以找到，如果这个时候重启了Mysql数据库实例，Mysql进程就会释放掉删除文件的句柄，此时就访问不到被删除的文件了。但是很多时候管理人员是不知道后台做了什么操作，因为从业务表象上看表的访问时正常的，所以不可避免会出现重启进程的情况出现。

1.2 单实例MySQL进程重启

1）清空ibd文件后重启mysql，提示错误：

2021-11-21T09:30:19.873859Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release. 2021-11-21T09:30:19.874006Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.25) starting as process 2078 2021-11-21T09:30:19.887308Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2021-11-21T09:30:21.106799Z 1 [ERROR] [MY-012224] [InnoDB] Header page consists of zero bytes in datafile: ./testdb/sbtest1.ibd, Space ID:0, Flags: 0. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2021-11-21T09:30:21.125942Z 1 [ERROR] [MY-012237] [InnoDB] Corrupted page [page id: space=66, page number=0] of datafile './testdb/sbtest1.ibd' could not be found in the doublewrite buffer. 2021-11-21T09:30:21.126361Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: fil0fil.cc:6077:err == DB_SUCCESS || err == DB_INVALID_ENCRYPTION_META thread 140142532699904 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 09:30:21 UTC - mysqld got signal 6 ; Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware. Thread pointer: 0x6380c20 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f7579e11cf0 thread_stack 0x46000 /usr/local/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x2079c3e] /usr/local/mysql/bin/mysqld(handle_fatal_signal+0x323) [0x10616b3] /lib64/libpthread.so.0(+0xf630) [0x7f7587eda630] /lib64/libc.so.6(gsignal+0x37) [0x7f7585eef3d7] /lib64/libc.so.6(abort+0x148) [0x7f7585ef0ac8] /usr/local/mysql/bin/mysqld() [0xdafadb] /usr/local/mysql/bin/mysqld(Fil_shard::ibd_open_for_recovery(unsigned int, std::string const&, fil_space_t*&)+0x4fd) [0x249706d] /usr/local/mysql/bin/mysqld(Fil_system::open_for_recovery(unsigned int)+0x99) [0x24977b9] /usr/local/mysql/bin/mysqld(fil_tablespace_redo_extend(unsigned char*, unsigned char const*, page_id_t const&, unsigned long, bool)+0x9c) [0x249805c] /usr/local/mysql/bin/mysqld() [0x220b094] /usr/local/mysql/bin/mysqld() [0x220e349] /usr/local/mysql/bin/mysqld(recv_recovery_from_checkpoint_start(log_t&, unsigned long)+0x20d6) [0x22129c6] /usr/local/mysql/bin/mysqld(srv_start(bool)+0x1bf8) [0x22fcbb8] /usr/local/mysql/bin/mysqld() [0x2167507] /usr/local/mysql/bin/mysqld(dd::bootstrap::DDSE_dict_init(THD*, dict_init_mode_t, unsigned int)+0x81) [0x1d83521] /usr/local/mysql/bin/mysqld(dd::upgrade_57::do_pre_checks_and_initialize_dd(THD*)+0x18b) [0x204f8cb] /usr/local/mysql/bin/mysqld() [0x1111b76] /usr/local/mysql/bin/mysqld() [0x25aef7c] /lib64/libpthread.so.0(+0x7ea5) [0x7f7587ed2ea5] /lib64/libc.so.6(clone+0x6d) [0x7f7585fb79fd]

2）尝试删除ibd文件，重启mysql提示错误：

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 2021-11-21T09:42:50.756717Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release. 2021-11-21T09:42:50.756869Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.25) starting as process 2532 2021-11-21T09:42:50.769934Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2021-11-21T09:42:53.337067Z 1 [Warning] [MY-013541] [InnoDB] Doublewrite page 0 for {space: 66, page_no:1688} could not be restored. File name unknown for tablespace ID 66 2021-11-21T09:42:53.337371Z 1 [Warning] [MY-013541] [InnoDB] Doublewrite page 1 for {space: 66, page_no:1689} could not be restored. File name unknown for tablespace ID 66 2021-11-21T09:42:53.337731Z 1 [Warning] [MY-013541] [InnoDB] Doublewrite page 2 for {space: 66, page_no:1690} could not be restored. File name unknown for tablespace ID 66 2021-11-21T09:42:53.338022Z 1 [Warning] [MY-013541] [InnoDB] Doublewrite page 3 for {space: 66, page_no:1691} could not be restored. File name unknown for tablespace ID 66 2021-11-21T09:42:53.338316Z 1 [Warning] [MY-013541] [InnoDB] Doublewrite page 4 for {space: 66, page_no:1692} could not be restored. File name unknown for tablespace ID 66 2021-11-21T09:42:53.338665Z 1 [Warning] [MY-013541] [InnoDB] Doublewrite page 5 for {space: 66, page_no:1693} could not be restored. File name unknown for tablespace ID 66 2021-11-21T09:42:53.338974Z 1 [Warning] [MY-013541] [InnoDB] Doublewrite page 6 for {space: 66, page_no:1694} could not be restored. File name unknown for tablespace ID 66 2021-11-21T09:42:53.339286Z 1 [Warning] [MY-013541] [InnoDB] Doublewrite page 7 for {space: 66, page_no:1695} could not be restored. File name unknown for tablespace ID 66 2021-11-21T09:42:53.339834Z 1 [Warning] [MY-013541] [InnoDB] Doublewrite page 8 for {space: 66, page_no:1696} could not be restored. File name unknown for tablespace ID 66 2021-11-21T09:42:53.340661Z 1 [Warning] [MY-013541] [InnoDB] Doublewrite page 9 for {space: 66, page_no:1697} could not be restored. File name unknown for tablespace ID 66 2021-11-21T09:42:53.340923Z 1 [Warning] [MY-013541] [InnoDB] Doublewrite page 10 for {space: 66, page_no:1698} could not be restored. File name unknown for tablespace ID 66 2021-11-21T09:42:53.341152Z 1 [Warning] [MY-013541] [InnoDB] Doublewrite page 11 for {space: 66, page_no:1699} could not be restored. File name unknown for tablespace ID 66 2021-11-21T09:42:53.341412Z 1 [ERROR] [MY-012179] [InnoDB] Could not find any file associated with the tablespace ID: 66 2021-11-21T09:42:53.341728Z 1 [ERROR] [MY-012964] [InnoDB] Use --innodb-directories to find the tablespace files. If that fails then use --innodb-force-recovery=1 to ignore this and to permanently lose all changes to the missing tablespace(s) 2021-11-21T09:42:53.442823Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error. 2021-11-21T09:42:53.843317Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine 2021-11-21T09:42:53.843895Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. 2021-11-21T09:42:53.844653Z 0 [ERROR] [MY-010119] [Server] Aborting 2021-11-21T09:42:53.965610Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.25) MySQL Community Server - GPL.

3）尝试innodb_force_recovery恢复

从上面可以看到mysql无法正常启动，此时尝试innodb_force_recovery恢复数据库。innodb_force_recovery默认为0，innodb_force_recovery可以设置1到6。较大的值包括较小值所有功能。例如，3包含1和2的所有功能。

设置innodb_force_recovery值等于或小于3，MySQL数据库的表是相对安全，此时仅丢失了损坏的单个页面上的某些数据。设置成4或更大的值是非常危险的，此时可能会导致页数据永久损坏。为保护数据，InnoDB会在innodb_force_recovery大于4时阻止INSERT，UPDATE或DELETE操作。innodb_force_recovery设置为0或更大时会将InnoDB置于只读模式。

innodb_force_recovery=1(SRV_FORCE_IGNORE_CORRUPT )

此时MySQL数据库即使检测到损坏的page也可以运行。可以尝试使SELECT * FROM tab跳过损坏的索引记录和页面，可以恢复没有损坏的业务数据。
innodb_force_recovery=2(SRV_FORCE_NO_BACKGROUND )

阻止master thread和任何purge threads运行。如果在purge操作期间发生崩溃，则此恢复值将阻止它。
innodb_force_recovery=3(SRV_FORCE_NO_TRX_UNDO )

在crash recovery之后不执行事务rollbacks。
innodb_force_recovery=4(SRV_FORCE_NO_IBUF_MERGE )

防止insert buffer合并操作，不计算 tablestatistics。此时可能会永久损坏数据文件，需要删除并重新创建所有二级索引。
innodb_force_recovery=5(SRV_FORCE_NO_UNDO_LOG_SCAN

启动数据库时不检查undo logs：InnoDB甚至将未完成的事务都视为已提交。此值可能会永久损坏数据文件。将InnoDB设置为只读。
innodb_force_recovery=6(SRV_FORCE_NO_LOG_REDO )

不进行与恢复有关的redo log前滚。此值可能会永久损坏数据文件。使数据库页面处于过时状态，从而可能导致 B 树和其他数据库结构遭受更多破坏。将InnoDB设置为只读。

4）将innodb_force_recovery设置为3后重启mysql后会提示信息：

2021-11-21T09:51:00.885712Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.25) starting as process 2755 2021-11-21T09:51:00.898658Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2021-11-21T09:51:02.536329Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2021-11-21T09:51:02.663827Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 66, name 'testdb/sbtest1', file './testdb/sbtest1.ibd' is missing! 2021-11-21T09:51:02.666281Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation. 2021-11-21T09:51:02.666753Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified. 2021-11-21T09:51:02.667161Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './testdb/sbtest1.ibd' OS error: 71 2021-11-21T09:51:02.669023Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation. 2021-11-21T09:51:02.669538Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified. 2021-11-21T09:51:02.670040Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './testdb/sbtest1.ibd' OS error: 71 2021-11-21T09:51:02.706609Z 1 [Warning] [MY-010005] [Server] Skip re-populating collations and character sets tables in InnoDB read-only mode. 2021-11-21T09:51:02.722699Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock 2021-11-21T09:51:02.723545Z 2 [Warning] [MY-011018] [Server] Skip updating information_schema metadata in InnoDB read-only mode. 2021-11-21T09:51:02.725217Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode. 2021-11-21T09:51:02.725765Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode. 2021-11-21T09:51:02.861962Z 0 [System] [MY-010229] [Server] Starting XA crash recovery... 2021-11-21T09:51:02.867876Z 0 [System] [MY-010232] [Server] XA crash recovery finished. 2021-11-21T09:51:03.026461Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2021-11-21T09:51:03.027069Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2021-11-21T09:51:03.061947Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.25' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server - GPL.

5）此时数据库只有查询操作，不能执行增删改（insert，update，delete）的操作。

mysql> insert into tango.test01 values(20,20,10); ERROR 1881 (HY000): Operation not allowed when innodb_force_recovery > 0.

6）登录到mysql中还是能看到表sbtest1，但是访问时候会提示如下报错：

mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | sbtest1 | +------------------+ 1 row in set (0.01 sec) mysql> select count(1) from sbtest1; ERROR 1812 (HY000): Tablespace is missing for table `testdb`.`sbtest1`.

先删除该表：

drop table sbtest1;

再重建

mysql> use testdb; Database changed mysql> CREATE TABLE `sbtest1` ( -> `id` int unsigned NOT NULL AUTO_INCREMENT, -> `k` int unsigned NOT NULL DEFAULT '0', -> `c` char(120) NOT NULL DEFAULT '', -> `pad` char(60) NOT NULL DEFAULT '', -> PRIMARY KEY (`id`), -> KEY `c1` (`k`) -> ) ENGINE=InnoDB AUTO_INCREMENT=8591563 DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.03 sec) mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | sbtest1 | +------------------+ 1 row in set (0.00 sec)

至此数据库状态恢复正常，但是表数据已经丢失。

1.3 主从复制架构

1）配置主从同步复制，主从同步配置参考“数据库系列之MySQL主从复制环境部署”

##主节点信息 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000005 | 156 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ##从节点状态 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.112.121 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 156 Relay_Log_File: tango-GDB-DB02-relay-bin.000002 Relay_Log_Pos: 324 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 156 Relay_Log_Space: 542 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 101 Master_UUID: 33069334-e5d2-11eb-9eef-000c298b8089 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.02 sec)

2）创建库testdb和表sbtest1并插入数据

3）删除表ibd文件

[root@tango-GDB-DB01 testdb]# ll total 12288 -rw-r-----. 1 mysql mysql 12582912 Nov 21 09:43 sbtest1.ibd [root@tango-GDB-DB01 testdb]# rm -f sbtest1.ibd

4）此时重启mysql提示错误信息

2021-11-21T01:44:57.655311Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 74, name 'testdb/sbtest1', file './testdb/sbtest1.ibd' is missing! 2021-11-21T01:44:57.740055Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock 2021-11-21T01:44:58.184794Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2021-11-21T01:44:58.185393Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2021-11-21T01:44:58.244657Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.25' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server - GPL. 2021-11-21T01:45:07.507786Z 8 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation. 2021-11-21T01:45:07.507937Z 8 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified. 2021-11-21T01:45:07.507960Z 8 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './testdb/sbtest1.ibd' OS error: 71 2021-11-21T01:45:07.508345Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `testdb`.`sbtest1` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue. 2021-11-21T01:45:07.508830Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `testdb`.`sbtest1` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue. 2021-11-21T01:45:10.485517Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `testdb`.`sbtest1` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue. (END)

5）在主节点访问表会提示

mysql> select count(1) from testdb.sbtest1; ERROR 1812 (HY000): Tablespace is missing for table `testdb`.`sbtest1`.

在从节点访问表是有正常返回值

mysql> select count(1) from testdb.sbtest1; +----------+ | count(1) | +----------+ | 28417 | +----------+ 1 row in set (0.06 sec)

6）将从节点的ibd文件拷贝到主节点

[root@tango-GDB-DB02 testdb]# scp sbtest1.ibd 192.168.112.121:/usr/local/mysql/data/testdb/

7）修改文件属性

[root@tango-GDB-DB01 testdb]# ll total 15360 -rw-r-----. 1 root root 15728640 Nov 21 09:49 sbtest1.ibd [root@tango-GDB-DB01 testdb]# chown mysql:mysql sbtest1.ibd [root@tango-GDB-DB01 testdb]# ll total 15360 -rw-r-----. 1 mysql mysql 15728640 Nov 21 09:49 sbtest1.ibd

8）重启mysql后访问表

mysql> select count(1) from testdb.sbtest1; ERROR 1812 (HY000): Tablespace is missing for table `testdb`.`sbtest1`.

此时提示表空间丢失

9）执行import将表空间加入

mysql> alter table testdb.sbtest1 import tablespace; mysql> select count(1) from testdb.sbtest1; +----------+ | count(1) | +----------+ | 28417 | +----------+ 1 row in set (0.06 sec)

10）备节点重新设置，否则会出现如下错误信息

Last_SQL_Errno: 1813 Last_SQL_Error: Error 'Tablespace 'testdb/sbtest1' exists.' on query. Default database: 'testdb'. Query: 'alter table testdb.sbtest1 import tablespace' Replicate_Ignore_Server_Ids: Master_Server_Id: 101

重新设置复制点

mysql> stop slave; CHANGE MASTER TO MASTER_HOST='192.168.112.121', MASTER_PORT=3306, MASTER_USER='repl', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=3915236;

此时可以正常同步数据了。

1.4 总结

在单节点MySQL环境下，当出现误操作删除表的ibd文件，如果能够及时发现在mysql进程没有重启之前是可以恢复数据的，但是很多情况下并没有及时的发现并重启了进程，此时虽然能恢复数据库但是删除的数据已经无法恢复了。
在主从复制架构下，因为有从库存在，当出现误操作的时候可以通过从库的数据进行主库ibd文件的恢复。
出现这种误操作还是因为对操作的危害性认知不够，而正常的空间释放是通过truncate表等数据库操作来进行的，不是直接暴力的对文件进行删除操作。在实际的生产运行环境，这种行为是严厉禁止的。