热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

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

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

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
文章会同步在公众号“牧羊人的方向”更新,感兴趣的可以关注公众号,谢谢!
在这里插入图片描述




推荐阅读
  • VScode格式化文档换行或不换行的设置方法
    本文介绍了在VScode中设置格式化文档换行或不换行的方法,包括使用插件和修改settings.json文件的内容。详细步骤为:找到settings.json文件,将其中的代码替换为指定的代码。 ... [详细]
  • Nginx使用(server参数配置)
    本文介绍了Nginx的使用,重点讲解了server参数配置,包括端口号、主机名、根目录等内容。同时,还介绍了Nginx的反向代理功能。 ... [详细]
  • http:my.oschina.netleejun2005blog136820刚看到群里又有同学在说HTTP协议下的Get请求参数长度是有大小限制的,最大不能超过XX ... [详细]
  • 本文介绍了通过ABAP开发往外网发邮件的需求,并提供了配置和代码整理的资料。其中包括了配置SAP邮件服务器的步骤和ABAP写发送邮件代码的过程。通过RZ10配置参数和icm/server_port_1的设定,可以实现向Sap User和外部邮件发送邮件的功能。希望对需要的开发人员有帮助。摘要长度:184字。 ... [详细]
  • C++字符字符串处理及字符集编码方案
    本文介绍了C++中字符字符串处理的问题,并详细解释了字符集编码方案,包括UNICODE、Windows apps采用的UTF-16编码、ASCII、SBCS和DBCS编码方案。同时说明了ANSI C标准和Windows中的字符/字符串数据类型实现。文章还提到了在编译时需要定义UNICODE宏以支持unicode编码,否则将使用windows code page编译。最后,给出了相关的头文件和数据类型定义。 ... [详细]
  • Spring特性实现接口多类的动态调用详解
    本文详细介绍了如何使用Spring特性实现接口多类的动态调用。通过对Spring IoC容器的基础类BeanFactory和ApplicationContext的介绍,以及getBeansOfType方法的应用,解决了在实际工作中遇到的接口及多个实现类的问题。同时,文章还提到了SPI使用的不便之处,并介绍了借助ApplicationContext实现需求的方法。阅读本文,你将了解到Spring特性的实现原理和实际应用方式。 ... [详细]
  • eclipse学习(第三章:ssh中的Hibernate)——11.Hibernate的缓存(2级缓存,get和load)
    本文介绍了eclipse学习中的第三章内容,主要讲解了ssh中的Hibernate的缓存,包括2级缓存和get方法、load方法的区别。文章还涉及了项目实践和相关知识点的讲解。 ... [详细]
  • 本文介绍了Hyperledger Fabric外部链码构建与运行的相关知识,包括在Hyperledger Fabric 2.0版本之前链码构建和运行的困难性,外部构建模式的实现原理以及外部构建和运行API的使用方法。通过本文的介绍,读者可以了解到如何利用外部构建和运行的方式来实现链码的构建和运行,并且不再受限于特定的语言和部署环境。 ... [详细]
  • ZSI.generate.Wsdl2PythonError: unsupported local simpleType restriction ... [详细]
  • 本文介绍了为什么要使用多进程处理TCP服务端,多进程的好处包括可靠性高和处理大量数据时速度快。然而,多进程不能共享进程空间,因此有一些变量不能共享。文章还提供了使用多进程实现TCP服务端的代码,并对代码进行了详细注释。 ... [详细]
  • 本文介绍了计算机网络的定义和通信流程,包括客户端编译文件、二进制转换、三层路由设备等。同时,还介绍了计算机网络中常用的关键词,如MAC地址和IP地址。 ... [详细]
  • CF:3D City Model(小思维)问题解析和代码实现
    本文通过解析CF:3D City Model问题,介绍了问题的背景和要求,并给出了相应的代码实现。该问题涉及到在一个矩形的网格上建造城市的情景,每个网格单元可以作为建筑的基础,建筑由多个立方体叠加而成。文章详细讲解了问题的解决思路,并给出了相应的代码实现供读者参考。 ... [详细]
  • Linux环境变量函数getenv、putenv、setenv和unsetenv详解
    本文详细解释了Linux中的环境变量函数getenv、putenv、setenv和unsetenv的用法和功能。通过使用这些函数,可以获取、设置和删除环境变量的值。同时给出了相应的函数原型、参数说明和返回值。通过示例代码演示了如何使用getenv函数获取环境变量的值,并打印出来。 ... [详细]
  • flowable工作流 流程变量_信也科技工作流平台的技术实践
    1背景随着公司业务发展及内部业务流程诉求的增长,目前信息化系统不能够很好满足期望,主要体现如下:目前OA流程引擎无法满足企业特定业务流程需求,且移动端体 ... [详细]
  • Linux如何安装Mongodb的详细步骤和注意事项
    本文介绍了Linux如何安装Mongodb的详细步骤和注意事项,同时介绍了Mongodb的特点和优势。Mongodb是一个开源的数据库,适用于各种规模的企业和各类应用程序。它具有灵活的数据模式和高性能的数据读写操作,能够提高企业的敏捷性和可扩展性。文章还提供了Mongodb的下载安装包地址。 ... [详细]
author-avatar
mobiledu2502911857
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有