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

前段时间遇到过因为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并插入数据

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| sbtest1          |
+------------------+
1 row in set (0.00 sec)

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

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

参考资料:

  1. https://blog.csdn.net/weixin_42551260/article/details/113303546
  2. https://blog.csdn.net/qq_40907977/article/details/114842270
  3. 数据库系列之MySQL主从复制环境部署”

转载请注明原文地址:https://blog.csdn.net/solihawk/article/details/121783207
文章会同步在公众号“牧羊人的方向”更新,感兴趣的可以关注公众号,谢谢!
数据库系列之MySQL表ibd文件删除恢复

上一篇:线程隔离


下一篇:2021-6-21 日记 C++(三十九)