数据文件被删除后的恢复
常规情况下如数据库出现坏块，如果数据库出现极端情况导致innodb_force_recovery=6，无法恢复，需要直接读取ibd 文件数据进行恢复，因此整体的恢复思路是把数据从ibd 读取出来，恢复到另外一个实例中
1.创建测试表
mysql> create table student(stu_id int(12),stu_name varchar(20));
Query OK, 0 rows affected (0.06 sec)
mysql> DELIMITER $
mysql> CREATE PROCEDURE proc_initData()
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> WHILE i< =10000 DO
-> INSERT INTO student (stu_id,stu_name) VALUES(i,concat("张三",i));
-> SET i = i+1;
-> END WHILE;
-> END $
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> CALL proc_initData();
Query OK, 1 row affected (4 min 3.81 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.01 sec)
2.将数据文件student.ibd模拟损坏其中一个数据块
[root@localhost test]# dd if=/dev/zero of=/mysqldata/mysql/test/student.ibd bs=16384 skip=2 count=1 1+0 records in 1+0 records out 16384 bytes (16 kB) copied, 0.00080267 s, 20.4 MB/s [root@localhost undrop-for-innodb-master]# service mysqld start Starting MySQL.. ERROR! The server quit without updating PID file (/mysqldata/mysql/mysqld.pid). 2024-03-15T07:53:59.455587Z 0 [ERROR] InnoDB: The size of tablespace file ./test/student.ibd is only 16384, should be at least 65536! 2024-03-15 15:53:59 0x7f53857fa700 InnoDB: Assertion failure in thread 139996698748672 in file fil0fil.cc line 793
3.设置恢复参数innodb_force_recovery
设置innodb_force_recovery=1-6分别尝试启动mysql服务。
[root@localhost undrop-for-innodb-master]# service mysqld start Starting MySQL.. SUCCESS! mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | intable | | iuser | | student | | t2 | +----------------+ 4 rows in set (0.01 sec) mysql> drop table student; ERROR 1051 (42S02): Unknown table 'test.student'
4.在其它mysql实例中创建一个同名的表并将表的frm文件拷贝到被意外删除表相关文件的目录下后再执行删除
mysql> drop table student; Query OK, 0 rows affected (0.16 sec) 2024-03-15T08:27:07.906624Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path. 2024-03-15T08:27:07.906835Z 0 [Note] /mysqlsoft/mysql/bin/mysqld (mysqld 5.7.26-log) starting as process 3036 ... 2024-03-15T08:27:07.916535Z 0 [Note] InnoDB: PUNCH HOLE support available 2024-03-15T08:27:07.916618Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2024-03-15T08:27:07.916719Z 0 [Note] InnoDB: Uses event mutexes 2024-03-15T08:27:07.916771Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2024-03-15T08:27:07.916831Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2024-03-15T08:27:07.917573Z 0 [Note] InnoDB: Number of pools: 1 2024-03-15T08:27:07.917926Z 0 [Note] InnoDB: Using CPU crc32 instructions 2024-03-15T08:27:07.922739Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2024-03-15T08:27:07.939838Z 0 [Note] InnoDB: Completed initialization of buffer pool 2024-03-15T08:27:07.945196Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2024-03-15T08:27:07.987840Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 2024-03-15T08:27:08.275126Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2024-03-15T08:27:08.275233Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2024-03-15T08:27:08.275297Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2024-03-15T08:27:08.275331Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './test/student.ibd' OS error: 71 2024-03-15T08:27:08.275371Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2024-03-15T08:27:08.275422Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2024-03-15T08:27:08.275450Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2024-03-15T08:27:08.275481Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `test/student`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2024-03-15T08:27:08.275521Z 0 [Warning] InnoDB: Ignoring tablespace `test/student` because it could not be opened. 2024-03-15T08:27:08.277095Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2024-03-15T08:27:08.277447Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2024-03-15T08:27:08.411733Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2024-03-15T08:27:08.414031Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2024-03-15T08:27:08.414082Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2024-03-15T08:27:08.414841Z 0 [Note] InnoDB: Waiting for purge to start 2024-03-15T08:27:08.465455Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 6008194 2024-03-15T08:27:08.465540Z 0 [Note] InnoDB: !!! innodb_force_recovery is set to 1 !!! 2024-03-15T08:27:08.465938Z 0 [Note] InnoDB: Loading buffer pool(s) from /mysqldata/mysql/ib_buffer_pool 2024-03-15T08:27:08.466538Z 0 [Note] Plugin 'FEDERATED' is disabled. 2024-03-15T08:27:08.485021Z 0 [Note] InnoDB: Buffer pool(s) load completed at 240315 16:27:08 2024-03-15T08:27:08.555227Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them. 2024-03-15T08:27:08.556469Z 0 [Warning] CA certificate ca.pem is self signed. 2024-03-15T08:27:08.559933Z 0 [Note] Server hostname (bind-address): '*'; port: 3306 2024-03-15T08:27:08.560137Z 0 [Note] IPv6 is available. 2024-03-15T08:27:08.560258Z 0 [Note] - '::' resolves to '::'; 2024-03-15T08:27:08.560370Z 0 [Note] Server socket created on IP: '::'. 2024-03-15T08:27:08.632833Z 0 [Note] Failed to start slave threads for channel '' 2024-03-15T08:27:08.652361Z 0 [Note] Event Scheduler: Loaded 0 events 2024-03-15T08:27:08.652743Z 0 [Note] /mysqlsoft/mysql/bin/mysqld: ready for connections. Version: '5.7.26-log' socket: '/mysqlsoft/mysql/mysql.sock' port: 3306 Source distribution 2024-03-15T08:27:24.889709Z 2 [ERROR] Invalid (old?) table or database name 'mysql-keyring' 2024-03-15T08:36:28.778461Z 2 [ERROR] InnoDB: Failed to find tablespace for table `test`.`student` in the cache. Attempting to load the tablespace with space id 45 2024-03-15T08:36:28.789158Z 2 [ERROR] InnoDB: In file './test/student.ibd', tablespace id and flags are 47 and 33, but in the InnoDB data dictionary they are 45 and 33. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2024-03-15T08:36:28.789251Z 2 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2024-03-15T08:36:28.789297Z 2 [ERROR] InnoDB: The error means the system cannot find the path specified. 2024-03-15T08:36:28.789365Z 2 [ERROR] InnoDB: Could not find a valid tablespace file for `test/student`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.