MySQL主机异常重启故障处理过程(表损坏)记录

MySQL主机异常重启故障处理过程(表损坏)记录

一、首先查看到挂载的磁盘不见了，/dev/vg_reportdb1/lv_iddbs找不到了。

然后使用lvdisplay查看到不可用 NOT available

--- Logical volume ---
LV Path                /dev/vg_reportdb1/lv_iddbs
LV Name                lv_iddbs
VG Name                vg_reportdb1
LV UUID                GSSbs2-CJBn-sg8H-slcv-UXN4-CxKw-L3X148
LV Write Access        read/write
LV Creation host, time reportdb1, 2014-12-16 02:05:14 +0800
LV Status              NOT available
# open                 1
LV Size                5.59 TiB
Current LE             1466303
Segments               5
Allocation             inherit
Read ahead sectors     auto
- currently set to     256
Block device           253:5

激活一下

 vgchange -a y vg_reportdb1

然后mount -a

[root@reportdb1 ~]# cat /etc/fstab | grep iddbs
/dev/mapper/vg_reportdb1-lv_iddbs /iddbs  ext4   defaults   1 2

[root@reportdb1 ~]# mount -a

二、启动数据库

/mysqld_safe --defaults-file=/iddbs/mysql/etc/my.cnf --user=iddbs --socket=/iddbs/mysql/data3306/mysql.sock &

查看error日志文件发现有表损坏，在进行修复

2021-06-16 23:08:53 6754 [ERROR] /iddbs/mysql/bin/mysqld: Table './mysql/user' is marked as crashed and should be repaired
2021-06-16 23:08:53 6754 [Warning] Checking table:   './mysql/user'
2021-06-16 23:08:53 6754 [ERROR] 1 client is using or hasn't closed the table properly
2021-06-16 23:08:53 6754 [ERROR] /iddbs/mysql/bin/mysqld: Table './mysql/db' is marked as crashed and should be repaired
2021-06-16 23:08:53 6754 [Warning] Checking table:   './mysql/db'
2021-06-16 23:08:53 6754 [ERROR] 1 client is using or hasn't closed the table properly
2021-06-16 23:08:53 6754 [Warning] 'user' entry 'root@reportdb1' ignored in --skip-name-resolve mode.
2021-06-16 23:08:53 6754 [Warning] 'user' entry '@reportdb1' ignored in --skip-name-resolve mode.
2021-06-16 23:08:53 6754 [Warning] 'proxies_priv' entry '@ root@reportdb1' ignored in --skip-name-resolve mode.
2021-06-16 23:08:53 6754 [Note] Event Scheduler: Loaded 0 events
2021-06-16 23:08:53 6754 [Note] /iddbs/mysql/bin/mysqld: ready for connections.
Version: '5.6.21-log'  socket: '/iddbs/mysql/data3306/mysql.sock'  port: 3306  Source distribution
2021-06-16 23:11:10 6754 [ERROR] /iddbs/mysql/bin/mysqld: Table './tbs_rpt/dd_bll_tot_d#P#P20201128' is marked as crashed and should be
 repaired
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'
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: 59426401268
Max_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                                     |
+-----------------------------+--------+----------+----------------------------------------+