MySQL主机异常重启故障处理进程(表损坏)日志1，首先验证装载的磁盘是否已丢失，然后找不到/dev/vg_reportdb1/lv_iddbs。 接下来，检查无法使用lvdisplay的NOT available
----logical volume---LV path/dev/VG _ report db1/LV _ iddbslvnamelv _ iddbsvgnamevg _ report db1 lvuidgssbs2-。 writeaccessread 2014-12-16023360053360140800 lvstatusnotavailable # open1LV size 5.59 tibcurrentle 1466303 segment S5 allllocationioninhing
vgchange -a y vg_reportdb1然后mount -a
[ root @ report db1~~ ] # cat/etc/fstab|grepiddbs/dev/mapper/VG _ report db1-LV _ iddbs/iddb sext4defaults 12
2021-06-1623360083360536754 [ error ]/iddbs/MySQL/bin/mysqld 3360 table './MySQL/user ' ismarkedascrashedandshouldberepaired 2021-06-1623360083360536754 [ warning ] checking table 3360 './MySQL/user ' 1 clientisusingorhasn ' tclosedthetableproperly 2021-06-16233600836006 MySQL/bin/mysqld 3360 table './MySQL/db ' ismarkedascrascrashedandshouldberepaired 2021-06-1623360083360536754 [ warrd ] MySQL/db ' 2021-06-1623:08:03 1 clientisusingorhasn ' tclosedthetableproperly 2021-06-162:004 ' user ' entry ' root @ report db1 ' ignored in-- skip-skip 4 [ warning ] ' user ' entry ' @ report db1 ' ignored in-- skip-name-resolve mode.2021-06-162336008360536754 [ warnt ' proxies _ priv ' entry ' @ root @ report db1 ' ignored in-- skip-name-resolve mode.2021-06-162:08360536754 event 536754 [ note ]/iddbs/MySQL/bin/mysqld 3360 readyforconnections.version : '5.6. 21-log ' socket 3360 '/iddbbid MySQL.sock ' port :3306 source distribution 2021-06-162:113360106754 [ error ]/iddbs/MySQL/bin/mysqld 3360 p 2020 p
ble: './tbs_rpt/dd_bll_tot_d'2021-06-16 23:11:16 6754 [Warning] Checking table: './tbs_rpt/dd_bll_tot_d'2021-06-16 23:11:16 6754 [Warning] Checking table: './tbs_rpt/dd_bll_tot_d' 三、仍然有一些表有问题： select * ... 报错：table .... is marked as crashed and last (automatic?) repair failed#自动修复失败然后尝试手动修复
1.首先查看表的引擎:
mysql> show table status from 表名 where name ='dd_deal_bll_voice_d' G*************************** 1. row *************************** Name: dd_deal_bll_voice_d Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 153140110 Avg_row_length: 388 Data_length: 59426401268Max_data_length: 0 Index_length: 1123328 Data_free: 0 Auto_increment: NULL Create_time: 2020-12-08 11:44:34 Update_time: 2021-06-17 11:27:42 Check_time: 2021-06-17 11:27:42 Collation: utf8_general_ci Checksum: NULL Create_options: partitioned Comment:1 row in set (0.31 sec)2.然后检查表的状态:
mysql> CHECK TABLE 表名 FAST QUICK;+-----------------------------+-------+----------+-------------------------------------------------------+| Table | Op | Msg_type | Msg_text 可耐的大米-----------------------------+-------+----------+-------------------------------------------------------+| tbs_rpt.dd_deal_bll_voice_d | check | warning | Table is marked as crashed and last repair failed || tbs_rpt.dd_deal_bll_voice_d | check | warning | 1 client is using or hasn't closed the table properly || tbs_rpt.dd_deal_bll_voice_d | check | error | Record-count is not ok; is 27400 Should be: 0 || tbs_rpt.dd_deal_bll_voice_d | check | warning | Found 435208 deleted space. Should be 0 || tbs_rpt.dd_deal_bll_voice_d | check | warning | Found 1082 deleted blocks Should be: 0 || tbs_rpt.dd_deal_bll_voice_d | check | warning | Found 53724 key parts. Should be: 0 || tbs_rpt.dd_deal_bll_voice_d | check | error | Partition P20201207 returned error || tbs_rpt.dd_deal_bll_voice_d | check | error | Corrupt 可耐的大米-----------------------------+-------+----------+-------------------------------------------------------+8 rows in set (3.44 sec)mysql> repair table dd_deal_bll_voice_d;+-----------------------------+--------+----------+----------------------------------------+| Table | Op | Msg_type | Msg_text 可耐的大米-----------------------------+--------+----------+----------------------------------------+| tbs_rpt.dd_deal_bll_voice_d | repair | warning | Number of rows changed from 0 to 27400 || tbs_rpt.dd_deal_bll_voice_d | repair | status | OK 可耐的大米-----------------------------+--------+----------+----------------------------------------+3.最后修复表：(repair只适用于修复MyISAM引擎表)
mysql> repair table 表名;+-----------------------------+--------+----------+----------------------------------------+| Table | Op | Msg_type | Msg_text 可耐的大米-----------------------------+--------+----------+----------------------------------------+| tbs_rpt.dd_deal_bll_voice_d | repair | warning | Number of rows changed from 0 to 27400 || tbs_rpt.dd_deal_bll_voice_d | repair | status | OK 可耐的大米-----------------------------+--------+----------+----------------------------------------+