YoungCheung

MySQL使用mysqldump导出单个表数据及表结构是报错说使用LOCK TABLES时，push_oms库中的msg_history表被标记为崩溃状态什么的

[root@SHYQ-PS-MV-SV03-PUSH-NEW-03 ~]# mysqldump -uroot -ppassword -h172.16.72.126 push_oms > /usr/local/src/push_oms.sql
mysqldump: Got error: 144: Table './push_oms/msg_history' is marked as crashed and last (automatic?) repair failed when using LOCK TABLES

解决办法：进入push_oms库，对msg_history进行检测

[root@SHYQ-PS-MV-SV03-PUSH-NEW-03 ~]# mysql -u root -p -h 172.16.72.126
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| push_oms           |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> use push_oms;
mysql> check tables msg_history;    使用check tables msg_history对msg_history进行检测
+----------------------+-------+----------+-------------------------------------------------------+
| Table                | Op    | Msg_type | Msg_text                                              |
+----------------------+-------+----------+-------------------------------------------------------+
| push_oms.msg_history | check | warning  | Table is marked as crashed and last repair failed     |
| push_oms.msg_history | check | warning  | 1 client is using or hasn't closed the table properly |
| push_oms.msg_history | check | warning  | Size of indexfile is: 221140992      Should be: 1024  |
| push_oms.msg_history | check | error    | Record-count is not ok; is 1847514   Should be: 0     |
| push_oms.msg_history | check | warning  | Found 195954932 deleted space.   Should be 0          |
| push_oms.msg_history | check | warning  | Found 228233 deleted blocks       Should be: 0        |
| push_oms.msg_history | check | warning  | Found 2139050 key parts. Should be: 0                 |
| push_oms.msg_history | check | error    | Corrupt                                               |
+----------------------+-------+----------+-------------------------------------------------------+
8 rows in set (1 min 7.27 sec)
检测发现有msg_history有两个类型出现error状态
则使用repair进行修复
mysql> repair tables msg_history;
+----------------------+--------+----------+------------------------------------------+
| Table                | Op     | Msg_type | Msg_text                                 |
+----------------------+--------+----------+------------------------------------------+
| push_oms.msg_history | repair | warning  | Number of rows changed from 0 to 1847514 |
| push_oms.msg_history | repair | status   | OK                                       |
+----------------------+--------+----------+------------------------------------------+
2 rows in set (24.81 sec)
修复完成再次进行检测
mysql> check tables msg_history; 
+----------------------+-------+----------+----------+
| Table                | Op    | Msg_type | Msg_text |
+----------------------+-------+----------+----------+
| push_oms.msg_history | check | status   | OK       |
+----------------------+-------+----------+----------+
1 row in set (5.40 sec)
mysql> \q

进行再次导出，导出前建议把上次导出错误的表先删除

[root@SHYQ-PS-MV-SV03-PUSH-NEW-03 ~]# mysqldump -uroot -ppassword -h172.16.72.126 push_oms > /usr/local/src/push_oms.sql
mysqldump: Got error: 144: Table './push_oms/tig_users' is marked as crashed and last (automatic?) repair failed when using LOCK TABLES
再次发现tig_users表又出现了相同问题，同样办法进行检测和修复
[root@SHYQ-PS-MV-SV03-PUSH-NEW-03 ~]# rm -rf /usr/local/src/push_oms.sql 
[root@SHYQ-PS-MV-SV03-PUSH-NEW-03 ~]# mysql -u root -p -h 172.16.72.126                                                 
Enter password: 
mysql> use push_oms;
Database changed
mysql> check tables tig_users;
+--------------------+-------+----------+-------------------------------------------------------+
| Table              | Op    | Msg_type | Msg_text                                              |
+--------------------+-------+----------+-------------------------------------------------------+
| push_oms.tig_users | check | warning  | Table is marked as crashed and last repair failed     |
| push_oms.tig_users | check | warning  | 1 client is using or hasn't closed the table properly |
| push_oms.tig_users | check | warning  | Size of indexfile is: 7522240512      Should be: 4096 |
| push_oms.tig_users | check | error    | Record-count is not ok; is 25620400   Should be: 0    |
| push_oms.tig_users | check | warning  | Found 80 deleted space.   Should be 0                 |
| push_oms.tig_users | check | warning  | Found 3 deleted blocks       Should be: 0             |
| push_oms.tig_users | check | warning  | Found 25795808 key parts. Should be: 0                |
| push_oms.tig_users | check | error    | Corrupt                                               |
+--------------------+-------+----------+-------------------------------------------------------+
8 rows in set (1 min 41.99 sec)
mysql> repair tables tig_users; 
+--------------------+--------+----------+-------------------------------------------+
| Table              | Op     | Msg_type | Msg_text                                  |
+--------------------+--------+----------+-------------------------------------------+
| push_oms.tig_users | repair | warning  | Number of rows changed from 0 to 25620400 |
| push_oms.tig_users | repair | status   | OK                                        |
+--------------------+--------+----------+-------------------------------------------+
2 rows in set (15 min 43.07 sec)
mysql> check tables tig_users; 
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| push_oms.tig_users | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (1 min 32.85 sec)

修复完成，再次进行导出

[root@SHYQ-PS-MV-SV03-PUSH-NEW-03 ~]# mysqldump -uroot -ppassword -h172.16.72.126 push_oms > /usr/local/src/push_oms.sql
You have mail in /var/spool/mail/root
[root@SHYQ-PS-MV-SV03-PUSH-NEW-03 ~]# ls /usr/local/src/push_oms.sql -ld
-rw-r----- 1 root root 8655495303 Jan  2 10:21 /usr/local/src/push_oms.sql

导出成功