1.通过拷贝文件的方式将windows mysql上的ibd 文件恢复到linux mysql;
window8 :mysql5.7.34 数据库 (拷贝ibd文件恢复)---> linux7.9 mysql5.7.34
alter table xxx discard tablespace;
alter table xxx import tablespace;
同步数据时,报:索引无法找到。
Record in index `MF_NetValuePerformanceHis_JSID` of table `jydb`.`mf_netvalueperformancehis` was not found on update: TUPLE (info_bits=0
同时还报如下错误:有三个事务需要回滚。紧接着数据库宕机,无法启动。
2023-02-14T18:21:52.813979+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 99557818880
2023-02-14T18:21:53.007409+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 99563061760
2023-02-14T18:21:53.160841+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 99568304640
2023-02-14T18:21:53.264592+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 99573547520
2023-02-14T18:21:53.405821+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 99578790400
2023-02-14T18:21:53.518016+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 99580770377
2023-02-14T18:21:53.519268+08:00 0 [Note] InnoDB: Database was not shutdown normally!
2023-02-14T18:21:53.519277+08:00 0 [Note] InnoDB: Starting crash recovery.
2023-02-14T18:21:54.271808+08:00 0 [Note] InnoDB: 3 transaction(s) which must be rolled back or cleaned up in total 801 row operations to undo
2023-02-14T18:21:54.271838+08:00 0 [Note] InnoDB: Trx id counter is 366592
2023-02-14T18:21:54.271897+08:00 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10
2.通过添加参数依然无法启动数据库。
innodb_force_recovery = 6
innodb_purge_threads = 0
3.将如下两个报错的文件从数据库里面移除。
mf_netvalueperformancehis.frm
mf_netvalueperformancehis.ibd
4.重新启动。
2023-02-14T21:20:52.417272+08:00 0 [Note] InnoDB: At LSN: 99576853973: unable to open file ./jydb/mf_netvalueperformancehis.ibd for tablespace 2243
2023-02-14T21:20:52.488092+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 99578790400
2023-02-14T21:20:52.599782+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 99580770377
2023-02-14T21:20:52.601050+08:00 0 [Note] InnoDB: Database was not shutdown normally!
2023-02-14T21:20:52.601059+08:00 0 [Note] InnoDB: Starting crash recovery.
2023-02-14T21:20:52.606919+08:00 0 [Warning] InnoDB: Tablespace 2243 was not found at ./jydb/mf_netvalueperformancehis.ibd, and innodb_force_recovery was set. All redo log for this tablespace will be ignored!
2023-02-14T21:20:53.416516+08:00 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2023-02-14T21:25:20.952742+08:00 0 [Note] InnoDB: Apply batch completed
发现报:mf_netvalueperformancehis.ibd 文件找不到,但是数据库成功启动了。
但是依然报错:
2023-02-14T21:25:21.678874+08:00 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`server_cost` in the cache. Attempting to load the tablespace with space id 19
2023-02-14T21:25:21.679007+08:00 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`engine_cost` in the cache. Attempting to load the tablespace with space id 20
2023-02-14T21:25:21.698676+08:00 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone_leap_second` in the cache. Attempting to load the tablespace with space id 12
2023-02-14T21:25:21.698801+08:00 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone_name` in the cache. Attempting to load the tablespace with space id 8
2023-02-14T21:25:21.698905+08:00 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone` in the cache. Attempting to load the tablespace with space id 9
2023-02-14T21:25:21.699001+08:00 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone_transition_type` in the cache. Attempting to load the tablespace with space id 11
2023-02-14T21:25:21.699103+08:00 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone_transition` in the cache. Attempting to load the tablespace with space id 10
2023-02-14T21:25:21.708111+08:00 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`servers` in the cache. Attempting to load the tablespace with space id 3
2023-02-14T21:25:21.719491+08:00 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`slave_master_info` in the cache. Attempting to load the tablespace with space id 16
2023-02-14T21:25:21.727300+08:00 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`slave_relay_log_info` in the cache. Attempting to load the tablespace with space id 15
2023-02-14T21:25:21.738586+08:00 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`slave_worker_info` in the cache. Attempting to load the tablespace with space id 17
2023-02-14T21:25:21.743057+08:00 0 [Note] Event Scheduler: Loaded 0 events
2023-02-14T21:25:21.743176+08:00 0 [Note] /usr/sbin/mysqld: ready for connections.
5.Failed to find tablespace for table 修复。
mysqlcheck -u root -p'hjzq1qaz!QAZ' --repair -A
--innodb 存储引擎的不支持,修复失败。
note : The storage engine for the table doesn't support repair
mysql.servers
note : The storage engine for the table doesn't support repair
mysql.slave_master_info
note : The storage engine for the table doesn't support repair
mysql.slave_relay_log_info
note : The storage engine for the table doesn't support repair
mysql.slave_worker_info
note : The storage engine for the table doesn't support repair
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone_transition_type
note : The storage engine for the table doesn't support repair
mysql.user OK
6.修复:ibdata1
[root@jydb1 performance_schema]# innochecksum /data/mysql/data/ibdata1
Error: Unable to lock file:: /data/mysql/data/ibdata1
fcntl: Resource temporarily unavailable
基本没啥用。
7.数据库可以启动,但是里面报很多错误。
Failed to find tablespace for table `mysql`.`server_cost` in the cache
Failed to find tablespace for table `xxxxxxxxxx`.`xxxxxxx` in the cache
2023-02-14T22:18:13.793732+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 12197ms. The settings might not be optimal. (flushed=2000 and evicted=0, during the time.)
2023-02-14T22:18:27.678251+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 12882ms. The settings might not be optimal. (flushed=2000 and evicted=0, during the time.)
2023-02-14T22:18:40.742940+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 12062ms. The settings might not be optimal. (flushed=2000 and evicted=0, during the time.)
于是用mydumper备份数据,在其他节点导入恢复。
8.总结:
通过拷贝文件的方式恢复ibd文件到数据库,不一定安全,可能存在其他问题。
比如我这里,虽然数据库启动了,但是里面很多报错,且通过 innodb_force_recovery参数启动后,
数据库只读,不可修改。只能用于备份,然后在其他节点恢复。所以拷贝文件的方式适合抢救数据,
不适合用于做正常的业务使用。可能存在坏块等问题。