MySQL数据库允许不同的表使用不同的存储引擎。它用来存储与检索数据。较流行的存储引擎是MyISAM与InnoDB。
MyISAM表最终“将”崩溃。这是个不争的事实。
幸运的是,在多数情况下,MyISAM表崩溃很容易修复。
修复单一表,连接你的数据库执行:
repair table TABLENAME ;
修复所有的表,执行:
/usr/local/mysql/bin/mysqlcheck --all-databases -uUSERNAME -pPASSWORD -r
多数情况,只有当你浏览日志文件时,才知道MyISAM表崩溃了。
我强烈建议在你的/etc/my.cnf配置文件中添加此行。一旦表崩溃它将进行自动修复。
[mysqld]
myisam-recover=backup,force
实验的时候,实验docker安装的8.0版本,由于新特性及存储文件变动,实验失败,因为mysql8.0存储的文件变成了.sdi文件,故重新在docker下安装了一个mysql5.6版本,实验成功了
实验过程:
启动mysql之后,用mysqlyog客户端连接好,新建一个test数据库
[root@localhost hbk]# docker pull mysql:5.6
[root@localhost hbk]# docker run --name mysql5.6_recovery -p 3319:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6
[root@localhost hbk]# docker cp /root/hbk/mysql_recovery/gzfxv53dn mysql5.6_recovery:/
[root@localhost hbk]# docker exec -it mysql5.6_recovery /bin/bash
root@9e1f167d9513:/# cd gzfxv53dn/
root@9e1f167d9513:/gzfxv53dn# mv * /var/lib/mysql/test/
root@9e1f167d9513:/gzfxv53dn# cd /var/lib/mysql/test/
root@9e1f167d9513:/var/lib/mysql/test# chown mysql.mysql *
root@9e1f167d9513:/var/lib/mysql/test# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.46 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> repair table gzfx_admin_sessions ;
_content_1 ;
repair table gzfx_content_2 ;
repair table gzfx_content_fields ;
repair table gzfx_content_index ;
repair table gzfx_content_table ;
repair table gzfx_contribution_1 ;
repair table gzfx_contribution_2 ;
repair table gzfx_contribution_note ;
repair table gzfx_cwps_action ;
repair table gzfx_cwps_admin_sessions ;
repair table gzfx_cwps_group ;
repair table gzfx_cwps_oas ;
repair table gzfx_cwps_operator ;
repair table gzfx_cwps_privilege ;
repair table gzfx_cwps_resource ;
repair table gzfx_cwps_role ;
repair table gzfx_cwps_sessions ;
repair table gzfx_cwps_soap ;
repair table gzfx_cwps_user_extra ;
repair table gzfx_cwps_user_fields ;
repair table gzfx_cwps_user ;
repair table gzfx_extra_publish ;
repair table gzfx_group ;
repair table gzfx_keywords ;
repair table gzfx_log_admin ;
repair table gzfx_log_login ;
repair table gzfx_node_fields ;
repair table gzfx_plugin_base_comment ;
repair table gzfx_plugin_base_count ;
repair table gzfx_plugin_base_setting ;
repair table gzfx_plugins ;
repair table gzfx_psn ;
repair table gzfx_pubadminmasks ;
repair table gzfx_publish_1 ;
repair table gzfx_publish_2 ;
repair table gzfx_publish_log ;
repair table gzfx_resource ;
repair table gzfx_resource_ref ;
repair table gzfx_sessions ;
repair table gzfx_site ;
repair table gzfx_sys ;
repair table gzfx_tasks ;
repair table gzfx_tpl_cate ;
repair table gzfx_tpl_data ;
repair table gzfx_tpl_vars ;
repair table gzfx_user ;
repair table gzfx_workflow ;
repair table gzfx_workflow_record ;
repair table gzfx_workflow_state ;
+--------------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+--------+----------+----------+
| test.gzfx_admin_sessions | repair | status | OK |
+--------------------------+--------+----------+----------+
1 row in set (0.07 sec)
mysql> repair table gzfx_block_ip ;
+--------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+--------+----------+----------+
| test.gzfx_block_ip | repair | status | OK |
+--------------------+--------+----------+----------+
1 row in set (0.01 sec)
mysql> repair table gzfx_category ;
+--------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+--------+----------+----------+
| test.gzfx_category | repair | status | OK |
+--------------------+--------+----------+----------+
1 row in set (0.00 sec)
,,,,,,,,,,类似的信息
用客户端mysqlyog发现数据恢复成功。
还有一种选项 repair table gzfx_admin_sessions use_frm;用在MYI文件丢失的情况下用。
语法:repair table 表名 [选项]
选项如下:
QUICK 用在数据表还没被修改的情况下,速度最快
EXTENDED 试图去恢复每个数据行,会产生一些垃圾数据行,万般无奈的情况下用
USE_FRM 用在.MYI文件丢失或者头部受到破坏的情况下。利用.frm的定义来重建索引