Some time ago, I encountered the emergency treatment after the mysql table ibd file was deleted. Deleting the table file directly is strictly prohibited. Here we test the emergency recovery process in several cases.
1. The ibd file has been emptied or deleted by mistake
1) Create table sbtest1 and insert data
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) Batch insert data
[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) Delete ibd file
[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
The following will be divided into several scenarios to deal with the above faults.
- The single instance mysql process was not restarted
- Single instance mysql process restart
- Master slave replication architecture
1.1 the single instance MySQL process is not restarted
1) Check that the mysql process is running normally
[root@tango-GDB-DB01 ~]# service mysql status SUCCESS! MySQL running (1873)
2) View the file handle used by the Mysql database process
[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) This table can still be operated by DML within a certain time range
mysql> insert into sbtest1(k,c,pad) values(100,100,100); Query OK, 1 row affected (0.01 sec)
4) To be on the safe side, it is recommended to lock this table and only read it
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) Copy the ibd file in the handle to the data directory
[root@tango-GDB-DB01 fd]# cp /proc/1873/fd/37 /usr/local/mysql/data/testdb/sbtest1.ibd
6) Empower copied files
[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
If you do not perform this step, you will be prompted when restarting mysql:
[Warning] [MY-012197] [InnoDB] Unable to open './testdb/sbtest1.ibd'
7) You can see that the file has been completely found. At this time, the read lock of the table can be released
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) Restart mysql and check the mysql log. There is no abnormal error message.
[root@tango-GDB-DB01 testdb]# service mysql restart
The reason why the ibd files deleted by rm can be recovered in this way is mainly because when the rm command is used to delete, the Mysql database process still holds the handle of the deleted ibd file, that is, it can be found in the / proc/{mysql_pid}/pd directory. If the Mysql database instance is restarted at this time, the Mysql process will release the handle of the deleted file, The deleted file cannot be accessed at this time. However, many times, managers do not know what operations are done in the background, because the table access is normal from the business appearance, so it is inevitable to restart the process.
1.2 single instance MySQL process restart
1) After clearing the ibd file, restart mysql, and an error is prompted:
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) Try to delete the ibd file and restart mysql. The prompt error is:
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) Try innodb_force_recovery recovery
You can see from the above that mysql cannot be started normally. Try InnoDB at this time_ force_ Recovery restores the database. innodb_force_recovery defaults to 0, innodb_force_recovery can be set from 1 to 6. Larger values include all functions with smaller values. For example, 3 contains all the functions of 1 and 2.
Set InnoDB_ force_ If the recovery value is equal to or less than 3, the tables of MySQL database are relatively safe. At this time, only some data on a damaged single page is lost. Setting to 4 or higher is very dangerous, which may cause permanent damage to page data. To protect data, InnoDB will_ force_ INSERT, UPDATE or DELETE operations are blocked when recovery is greater than 4. innodb_ force_ Setting recovery to 0 or greater places InnoDB in read-only mode.
-
innodb_force_recovery=1(SRV_FORCE_IGNORE_CORRUPT )
At this time, the MySQL database can run even if a damaged page is detected. You can try to make the SELECT * FROM tab skip damaged index records and pages, and you can recover business data that is not damaged.
-
innodb_force_recovery=2(SRV_FORCE_NO_BACKGROUND )
Prevent master thread s and any purge threads from running. If a crash occurs during a purge operation, this recovery value prevents it.
-
innodb_force_recovery=3(SRV_FORCE_NO_TRX_UNDO )
Transaction rollbacks are not executed after crash recovery.
-
innodb_force_recovery=4(SRV_FORCE_NO_IBUF_MERGE )
Prevent the insert buffer merge operation and do not calculate tablestatistics. At this time, the data file may be permanently damaged, and all secondary indexes need to be deleted and recreated.
-
innodb_force_recovery=5(SRV_FORCE_NO_UNDO_LOG_SCAN
Do not check undo logs when starting the database: InnoDB even treats unfinished transactions as committed. This value may permanently damage the data file. Set InnoDB to read-only.
-
innodb_force_recovery=6(SRV_FORCE_NO_LOG_REDO )
Do not roll forward redo log s related to recovery. This value may permanently damage the data file. Make the database pages obsolete, which may cause more damage to the B-tree and other database structures. Set InnoDB to read-only.
4) Set InnoDB_ force_ After recovery is set to 3, you will be prompted after restarting 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) At this time, the database only has query operations, and cannot perform insert, update, delete operations.
mysql> insert into tango.test01 values(20,20,10); ERROR 1881 (HY000): Operation not allowed when innodb_force_recovery > 0.
6) Log in to mysql and you can still see the table sbtest1, but the following error will be prompted during access:
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`.
Delete the table first:
drop table sbtest1;
Reconstruction
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)
So far, the database state has returned to normal, but the table data has been lost.
1.3 master slave replication architecture
1) Configure master-slave synchronous replication. Refer to "MySQL master-slave replication environment deployment of database Series" for master-slave synchronous replication configuration
##Master node information
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)
##Slave node status
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) Create the library testdb and table sbtest1 and insert data
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) Delete table ibd file
[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) Restart mysql and an error message will be displayed
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) When you access the table at the master node, you will be prompted
mysql> select count(1) from testdb.sbtest1; ERROR 1812 (HY000): Tablespace is missing for table `testdb`.`sbtest1`.
There is a normal return value when accessing the table from the node
mysql> select count(1) from testdb.sbtest1; +----------+ | count(1) | +----------+ | 28417 | +----------+ 1 row in set (0.06 sec)
6) Copy the ibd file from the slave node to the master node
[root@tango-GDB-DB02 testdb]# scp sbtest1.ibd 192.168.112.121:/usr/local/mysql/data/testdb/
7) Modify file properties
[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) Access the table after restarting mysql
mysql> select count(1) from testdb.sbtest1; ERROR 1812 (HY000): Tablespace is missing for table `testdb`.`sbtest1`.
You will be prompted that the tablespace is missing
9) Execute import to join the tablespace
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) Reset the standby node, otherwise the following error message will appear
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
Reset copy point
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;
At this time, the data can be synchronized normally.
1.4 summary
- In the single node MySQL environment, if the misoperation of deleting the ibd file of the table occurs, if it can be found in time, the data can be recovered before the MySQL process is restarted, but in many cases, the process is not found and restarted in time. At this time, although the database can be recovered, the deleted data can not be recovered.
- In the master-slave replication architecture, because there is a slave inventory, in case of misoperation, the ibd file of the master database can be recovered through the data of the slave database.
- This misoperation is due to insufficient awareness of the harmfulness of the operation, and the normal space release is carried out through database operations such as truncate table, rather than direct violent deletion of files. In the actual production and operation environment, this behavior is strictly prohibited.
reference material:
- https://blog.csdn.net/weixin_42551260/article/details/113303546
- https://blog.csdn.net/qq_40907977/article/details/114842270
- "MySQL master-slave replication environment deployment of database Series
Please indicate the original address for Reprint: https://blog.csdn.net/solihawk/article/details/121783207
The article will be synchronized in the official account of "shepherd's direction". Interested parties can official account. Thank you!
