mysql报错xamp table 'pma__recent' is read only

问题描述

由于断电，或异常导致mysql挂掉，无法重启mysql报Ignoring the redo log due to missing MLOG_CHECKPOINT between 或者启动后在phpmyadmin中报 table ‘xxx’ is read only.

mysql版本：7.4.29
系统版本：windows + xamp

mysql的error log的信息如下:

2022-08-29T21:44:46.562061+08:00 0 [ERROR] InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT between the checkpoint 63593957 and the end 63593472.
2022-08-29T21:44:46.562090+08:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2022-08-29T21:44:47.062885+08:00 0 [ERROR] Plugin InnoDB init function returned error.
2022-08-29T21:44:47.062936+08:00 0 [ERROR] Plugin InnoDB registration as a STORAGE ENGINE failed.
2022-08-29T21:44:47.062945+08:00 0 [ERROR] Failed to initialize plugins.
2022-08-29T21:44:47.062951+08:00 0 [ERROR] Aborting

2022-08-29T21:44:47.062962+08:00 0 [Note] Binlog end

在redo log里找不到checkpoint点，innodb引擎启动失败

解决办法

解决办法有两个

方法1

设置innodb_force_recovery=6，然后启动mysql，能够顺利启动mysql
error log信息如下：

2022-08-29T22:07:25.235397+08:00 0 [Warning] db entry sys mysql.sys@localhost ignored in --skip-name-resolve mode.
2022-08-29T22:07:25.235408+08:00 0 [Warning] proxies_priv entry @ root@localhost ignored in --skip-name-resolve mode.
2022-08-29T22:07:25.235540+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 46
2022-08-29T22:07:25.256456+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 47
2022-08-29T22:07:25.258414+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 45
2022-08-29T22:07:25.263576+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 49
2022-08-29T22:07:25.267569+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 48
2022-08-29T22:07:25.272648+08:00 0 [ERROR] InnoDB: Failed to find tablespace for table mysql.servers in the cache. Attempting to load the tablespace with space id 41
2022-08-29T22:07:25.276466+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 42
2022-08-29T22:07:25.280225+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 43
2022-08-29T22:07:25.286619+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 44
2022-08-29T22:07:25.292450+08:00 0 [ERROR] Error writing master configuration.
2022-08-29T22:07:25.292468+08:00 0 [ERROR] Error reading master configuration.
2022-08-29T22:07:25.294535+08:00 0 [Warning] Recovery from master pos 1065 and file mysql-bin.000003 for channel . Previous relay log pos and relay log file had been set to 4, /data/mysql/mysql3306/logs/mysql-relay.000003 respectively.
2022-08-29T22:07:25.294643+08:00 0 [ERROR] Error writing relay log configuration.
2022-08-29T22:07:25.294656+08:00 0 [ERROR] Error reading relay log configuration.
2022-08-29T22:07:25.294983+08:00 0 [ERROR] Slave: Failed to initialize the master info structure for channel ; its record may still be present in mysql.slave_master_info table, consider deleting it.
2022-08-29T22:07:25.294996+08:00 0 [ERROR] Failed to create or recover replication info repositories.
2022-08-29T22:07:25.295000+08:00 0 [Note] Some of the channels are not created/initialized properly. Check for additional messages above. You will not be able to start replication on those channels until the issue is resolved and the server restarted.

由于损坏的这个库是从库，所以显示无法初始化master info表
由于mysql库里有几个表都显示找不到表空间，那么会存在丢失数据的风险

方法2

按照网上说的，把实例的所有ib_logfilex文件删除，再启动mysql，能够顺利启动
参照：http://blog.csdn.net/weiwangsisoftstone/article/details/52954228
error log信息如下：

2022-08-29T21:54:17.694175+08:00 0 [Note] Server socket created on IP: ::.
2022-08-29T21:54:17.696000+08:00 0 [ERROR] InnoDB: Page [page id: space=40, page number=3] log sequence number 3725922 is in the future! Current system log sequence number 2440232.
2022-08-29T21:54:17.696018+08:00 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2022-08-29T21:54:17.696287+08:00 0 [ERROR] InnoDB: Page [page id: space=40, page number=1] log sequence number 3720522 is in the future! Current system log sequence number 2440232.
2022-08-29T21:54:17.696300+08:00 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2022-08-29T21:54:17.696535+08:00 0 [ERROR] InnoDB: Page [page id: space=40, page number=2] log sequence number 3724576 is in the future! Current system log sequence number 2440232.
2022-08-29T21:54:17.696551+08:00 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2022-08-29T21:54:17.697469+08:00 0 [ERROR] InnoDB: Page [page id: space=32, page number=3] log sequence number 2483079 is in the future! Current system log sequence number 2440232.
2022-08-29T21:54:17.697485+08:00 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2022-08-29T21:54:17.697809+08:00 0 [ERROR] InnoDB: Page [page id: space=32, page number=1] log sequence number 2479069 is in the future! Current system log sequence number 2440232.
2022-08-29T21:54:17.697821+08:00 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2022-08-29T21:54:17.698049+08:00 0 [ERROR] InnoDB: Page [page id: space=32, page number=2] log sequence number 2482784 is in the future! Current system log sequence number 2440232.
2022-08-29T21:54:17.698061+08:00 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2022-08-29T21:54:17.700092+08:00 0 [Warning] user entry root@localhost ignored in --skip-name-resolve mode.
2022-08-29T21:54:17.700140+08:00 0 [Warning] user entry mysql.session@localhost ignored in --skip-name-resolve mode.
2022-08-29T21:54:17.700153+08:00 0 [Warning] user entry mysql.sys@localhost ignored in --skip-name-resolve mode.
2022-08-29T21:54:17.701714+08:00 0 [Warning] db entry performance_schema mysql.session@localhost ignored in --skip-name-resolve mode.
2022-08-29T21:54:17.701727+08:00 0 [Warning] db entry sys mysql.sys@localhost ignored in --skip-name-resolve mode.
2022-08-29T21:54:17.701881+08:00 0 [Warning] proxies_priv entry @ root@localhost ignored in --skip-name-resolve mode.
2022-08-29T21:54:17.702485+08:00 0 [ERROR] InnoDB: Page [page id: space=46, page number=3] log sequence number 3754946 is in the future! Current system log sequence number 2440232.
2022-08-29T21:54:17.702502+08:00 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2022-08-29T21:54:17.702718+08:00 0 [ERROR] InnoDB: Page [page id: space=46, page number=1] log sequence number 3751039 is in the future! Current system log sequence number 2440232.
2022-08-29T21:54:17.702741+08:00 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2022-08-29T21:54:17.702962+08:00 0 [ERROR] InnoDB: Page [page id: space=46, page number=2] log sequence number 3754946 is in the future! Current system log sequence number 2440232.
2022-08-29T21:54:17.702974+08:00 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.

按照报错信息，数据库也是无法回滚到之前一致性状态，有丢数据风险

总结

一般来说，mysql有坏块或者服务器异常关机
解决方法：

1. 在my.cnf里设置innodb_force_recovery=1 ，如果1不行改成2，最大是6 ，然后启动mysql， 将数据dump出来再 导入到新的实例
2. 根据主键id ，逐个区间去导出，用mysqldump -w where条件，再导入到新实例

innodb_force_recovery影响整个innodb存储引擎的恢复状况，该值默认为0，表示当需要恢复时，需要执行所有的恢复操作，当不能进行有效恢复时，如数据页发生了corruption，mysql数据库可能宕机，并把错误写入错误日志中。

innodb_force_recovery=6表示mysql数据库已经有比较严重的损坏，就算把数据dump出来也不能保证dump出来的数据是没有问题的

最后建议选择第一个解决办法修改innodb_force_recovery的方法来恢复mysql，而不要选择第二个办法