首页 > 数据库 >【Percona】innodb_corrup_table_action参数拯救你的数据库

【Percona】innodb_corrup_table_action参数拯救你的数据库

时间:2023-02-10 17:32:30浏览次数:42  
标签:02 10T16 read t1 Percona innodb action table

当mysqld服务进程访问损坏的表时,数据库会直接崩溃退出。如果在遭遇到损坏的表时,不希望出现这种结果,请将参数innodb_corrupt_table_action变量值设置为​​salvage​​,该值会跳过损坏的表,不会使mysqld服务进程崩溃,导致整个数据库无法启动。

注:

1)MySQL没有此参数,请切换至Percona或者MariaDB

2)该参数只能适配独立表空间 innodb_file_per_table = 1

故障复现:

1)创建一个t1表,然后到/data/目录下,用重定向命令清空数据

echo > t1.ibd

2)直接shutdown关闭mysqld进程

3)启动mysqld进程后,如果没有在my.cnf文件里增加该参数,启动时会报错,报错日志如下:

2023-02-10T16:30:18.491764+08:00 0 [Warning] [MY-012637] [InnoDB] 1024 bytes should have been read. Only 0 bytes read. Retrying for the remaining bytes.

2023-02-10T16:30:18.491777+08:00 0 [Warning] [MY-012638] [InnoDB] Retry attempts for reading partial data failed.

2023-02-10T16:30:18.491789+08:00 0 [ERROR] [MY-012642] [InnoDB] Tried to read 1024 bytes at offset 0, but was only able to read 0

2023-02-10T16:30:18.491801+08:00 0 [ERROR] [MY-012646] [InnoDB] File ./test/t1.ibd: 'read' returned OS error 0. Cannot continue operation

2023-02-10T16:30:18.491812+08:00 0 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.

4)将参数innodb_corrupt_table_action变量值设置为​​salvage​​,启动mysqld进程,再次查看该表

mysql> select * from t1;
ERROR 1812 (HY000): Tablespace is missing for table `test`.`t1`.

5)你可以在其他节点上DUMP数据还原,从而恢复t1表。

总结

在甲骨文MySQL里,解决方案是将参数innodb_force_recovery值设置为1-6,确保数据库可以正常启动,但值大于0后,可以对表进行 select, create, drop 操作,而 insert, update 或者 delete 这类操作是不允许的,影响的是全局。

在Perocna/MariaDB里,可以用innodb_corrupt_table_action参数代替innodb_force_recovery,这样没有损坏的表,可以正常读写操作,影响的是局部。


标签:02,10T16,read,t1,Percona,innodb,action,table
From: https://blog.51cto.com/hcymysql/6049650

相关文章