【MySQL】mysqldump备份失败与解决方案合集（下）

在云厂商工作，经常遇到过各种各样备份失败的情况，
本文整理了通过mysqldump工具备份失败的情况与解决思路。

mysqldump: Couldnt execute show create table $tb_name: Unable to open underlying table which is differently defined or of non-MyISAM type or doesnt exist (1168)

原因：
出现在表引擎为MERGE时，备份到该表时，发现该表定义存在问题。可能merge的表不存在，或者该表合并的基表包含非MyISAM引擎的表。
处理思路：
删除或者重建该MERGE表。

复现一（merge表中定义包含了非MyISAM表）：

1CREATE TABLE t1(id int) ENGINE=InnoDB;
2CREATE TABLE t2(id int) ENGINE=MyISAM;
3CREATE TABLE merge_t(id int)ENGINE=MERGE UNION=(t1, t2); 
4SELECT * FROM merge_t;
5ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

复制

复现二（表不存在）：

1CREATE TABLE t1(id int) ENGINE=InnoDB;
2CREATE TABLE t2(id int) ENGINE=MyISAM;
3CREATE TABLE merge_t(id int)ENGINE=MERGE UNION=(t1, t2); 
4SELECT * FROM merge_t;
5ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

复制

附录：
通过check table merge_t可以检查是哪张表有问题，如此处是t1：

 1[15:20:12] root@localhost [test]> check table merge_t\G
 2*************************** 1. row ***************************
 3  Table: test.merge_t
 4     Op: check
 5Msg_type: Error
 6Msg_text: Table 'test.t1' is differently defined or of non-MyISAM type or doesn't exist
 7*************************** 2. row ***************************
 8  Table: test.merge_t
 9     Op: check
10Msg_type: Error
11Msg_text: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
12*************************** 3. row ***************************
13  Table: test.merge_t
14     Op: check
15Msg_type: error
16Msg_text: Corrupt
173 rows in set (0.00 sec)

复制

通过cat表MGR定义结构文件可以检查MERGE表的基表：

1[root@host test]# pwd
2/data/mysql-data/mysql57/data/test
3[root@host test]# cat merge_t.MRG 
4t1
5t2

复制

mysqldump: Couldnt execute show create table tb_name: Table ./db_name/tb_name is marked as crashed and last (automatic?) repair failed (144) OR
mysqldump: Couldnt execute show create table $tb_name: Table ./db_name/tb_name is marked as crashed and should be repaired (145) OR
mysqldump: Error 1194: Table tb_name is marked as crashed and should be repaired when dumping table tb_name at row: xxxxx

原因：
mysqldump在拉取表定义时报错，表损坏。
处理思路：
该损坏发生在非事务表如MyISAM，通过mysqlcheck
或者repair table
修复即可。

mysqldump: Couldnt execute SHOW FUNCTION STATUS WHERE Db = db_name: Cannot load from mysql.tb_name. The table is probably corrupted (1728)

原因：
字典表不正确，可能是表本身损坏，也有可能是导入了其他版本的mysql schema盖掉了字典表。
处理思路：
需要分情况，有的myisam系统表可以用repair table
修复，有的则可以尝试mysql_upgrade
来修复，或找到对应版本的mysql_system_tables_fix.sql
来导入。

mysqldump: Couldnt execute show events: Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)

原因：
字典表不正确，极大可能是导入了其他版本的mysql schema盖掉了字典表。
处理思路：
尝试mysql_upgrade
来修复，或找到对应版本的mysql_system_tables_fix.sql
来导入。可能有的情况，需要在upgrade操作之后重启实例。

mysqldump: Error: Got error 28 from storage engine when trying to dump tablespaces OR
mysqldump: Couldnt execute show fields from $tb_name: Got error 28 from storage engine (1030)

原因：
@@tmpdir满了。
处理思路：
清除@@tmpdir
，可以通过SELECT @@tmpdir;
检查具体目录。

mysqldump: Lost connection to MySQL server during query (2013) OR
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '@@socket' (111)

原因：
mysqldump执行过程中mysqld
被关闭。
处理思路：
检查mysqld被关闭的原因，一般常见原因是发生OOM。

mysqldump: Couldn't execute 'SHOW SLAVE STATUS': Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation (1227) OR
mysqldump: Couldn't execute 'STOP SLAVE SQL_THREAD': Access denied for user 'dump'@'localhost' (using password: YES) (1045)

原因：
mysqldump加了--dump-slave
参数，缺少SUPER
或REPLICATION CLIENT
来执行SHOW SLAVE STATUS
。或缺少SUPER
权限使用STOP SLAVE SQL_THREAD
。
处理思路：
检查使用mysqldump的用户权限。