mysqldump备份失败以及解决方法汇总〇 mysqldump: Error: Query execution was interrupted, maximum statement execution time exceeded when trying to dump tablespaces
〇 mysqldump: Error 3024: Query execution was interrupted, maximum statement execution time exceeded when dumping table `$tb_name` at row: xxxx
版本：
MySQL 5.7.8+
原因：
max_execution_time过小
处理思路：
① 通过hints，增大N值(文档说，在hints用法中，将N改为0为无限制，但我测下来不生效，可设置成一个较大值如999999解决)
SELECT /*+ MAX_EXECUTION_TIME(N) */ * FROM t1 LIMIT 100000;
② 修改max_execution_time值，将该值设置为较大一个值，或设置为0(不限制)
附录：
该参数5.7.8被添加，单位为ms，动态参数，默认为0，设置为0时意味着SELECT超时不被设置(不限制超时时间)。不作用于存储过程中的SELECT语句，并且只作用于只读的SELECT，如INSERT ... SELECT ... 是不被作用的。
for more information:
/29773961/viewspace-2150443/〇 mysqldump: Couldnt execute SHOW FIELDS FROM `$view_name`: View $db_name.$view_name references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
原因：
该view引用了无效的表，列，函数或者定义者。
处理思路：
可以根据报错信息，进入db，执行SHOW CREATE VIEW $view_name\G，查看该view的定义，逐一检查该view的基表，列，或相关函数与用户是否具有相关权限。考虑重建或删除视图。〇 mysqldump: Couldnt execute show create table `$view_name`: Illegal mix of collations for operation UNION (1271)
原因：
创建view时，使用UNION时存在非法的排序规则组合。
处理思路：
检查该视图定义，检查字符集，考虑重建或删除视图。〇mysqldump: Couldnt execute SHOW FIELDS FROM `$view_name`: The user specified as a definer ($user@$host) does not exist (1449)
〇 mysqldump: Couldnt execute show table status like $view_name: SELECT command denied to user @% for column $col_name in table $tb_name (1143)
原因：
该视图的定义者$user@$host不存在。
处理思路：
检查mysql.user表，确认用户是否存在，考虑重建或删除视图。〇 Error: Couldnt read status information for table Income_config ()mysqldump: Couldnt execute show create table `Tser_table`: Table $db_name.test_table doesnt exist (1146)
〇 mysqldump: Got error: 1049: Unknown database $db_name when selecting the database
原因一：
从lower_case_table_names的0设置成1，导致部分原来含有大写字母的库表“找不到”。
处理思路：
将lower_case_table_names设置回0。
若有必须将lower_case_table_names设置为1，需先设置为0，并将含有大写字母的库表改成小写，再设置为1。
原因二(MySQL 5.5及以下版本可能出现)：
表损坏导致该表找不到(InnoDB)。frm和ibd文件都在，但无法SHOW CREATE TABLE xxx\G
error log一则：170820 17:43:17[Note]Event Scheduler:scheduler thread started with id 1
170820 17:44:48 InnoDB:error:space objectoftable'$db_name/$tb_name',
InnoDB:space id 4335 didnotexistinmemory.Retrying an open.
170820 17:44:48 InnoDB:Error:tablespace idandflagsinfile'./$db_name/$tb_name.ibd'are 0and0,butinthe InnoDB
InnoDB:data dictionary they are 4335and0.
InnoDB:Have you moved InnoDB.ibd files around without using the
InnoDB:commands DISCARD TABLESPACEandIMPORT TABLESPACE?
InnoDB:Please refer to
InnoDB:/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB:forhow to resolve the issue.
170820 17:44:48 InnoDB:cannot calculate statisticsfortable $db_name/$tb_name
InnoDB:because the.ibd file is missing.Forhelp,please refer to
InnoDB:/doc/refman/5.5/en/innodb-troubleshooting.html
170820 17:44:48[ERROR]MySQL is trying to open a table handle but the.ibd filefor
table $db_name/$tb_name doesnotexist.
Have you deleted the.ibd file from the database directory under
the MySQL datadir,orhave you used DISCARD TABLESPACE?
See /doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.
处理思路：
从完整备份+binlog还原，对于有主或从的实例，可通过物理备份还原。〇 mysqldump: Error : Got packet bigger than max_allowed_packet bytes when dumping table `$tb_name` at row: xxxx
原因：
默认的max_allowed_packet过小
处理思路：
在mysqldump时增加max_allowed_packet的大小，如mysqldump --max-allowed-packet=268435456〇 mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `$tb_name` at row: 0
原因：
在备份该表时，表定义被修改。FLUSH TABLE WITH READ LOCK只保证数据一致性，并不保证schema不被修改。
处理思路：
备份时期不做DDL操作。
复现一：① session1>CREATETABLEa(idint)ENGINE=InnoDB;
② session2>START TRANSACTION WITH CONSISTENT SNAPSHOT;
③ session1>ALTERTABLEa ADD COLUMN namevarchar(32);
④ session2>SELECT*FROMa;
ERROR 1412(HY000):Tabledefinition has changed,please retry transaction
p.s. 如果③和④调换顺序，则ALTER TABLE无法成功，则会等待MDL。
复现二：① session1>START TRANSACTION WITH CONSISTENT SNAPSHOT;
② session2>CREATETABLEb(idint)ENGINE=InnoDB;
③ session1>SELECT*FROMb;
ERROR 1412(HY000):Tabledefinition has changed,please retry transaction
〇 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表)：CREATETABLEt1(idint)ENGINE=InnoDB;
CREATETABLEt2(idint)ENGINE=MyISAM;
CREATETABLEmerge_t(idint)ENGINE=MERGE UNION=(t1,t2);
SELECT*FROMmerge_t;
ERROR 1168(HY000):Unabletoopen underlyingtablewhichisdifferently definedorof non-MyISAM typeordoesn't exist
复现二(表不存在)：CREATETABLEt1(idint)ENGINE=MyISAM;
CREATETABLEt2(idint)ENGINE=MyISAM;
CREATETABLEmerge_t(idint)ENGINE=MERGE UNION=(t1,t2);
SELECT*FROMmerge_t;
Emptyset(0.00 sec)-- 正常返回
DROPTABLEt1;
SELECT*FROMmerge_t;
ERROR 1168(HY000):Unabletoopen underlyingtablewhichisdifferently definedorof non-MyISAM typeordoesn't exist
附录：
通过check table merge_t可以检查是哪张表有问题，如此处是t1：[15:20:12]root@localhost[test]>checktablemerge_t\G
***************************1.row***************************
Table:test.merge_t
Op: check
Msg_type: Error
Msg_text:Table'test.t1'isdifferently definedorof non-MyISAM typeordoesn't exist
*************************** 2. row ***************************
Table: test.merge_t
Op: check
Msg_type: Error
Msg_text: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
***************************3.row***************************
Table:test.merge_t
Op: check
Msg_type: error
Msg_text: Corrupt
3 rowsinset(0.00 sec)
通过cat表MGR��义结构文件可以检查MERGE表的基表：
[root@hosttest]#pwd
/data/mysql-data/mysql57/data/test
[root@hosttest]#cat merge_t.MRG
t1
t2
〇 mysqldump: Couldnt execute show create table `$tb_name`: Table ./$db_name/$tb_name is marked as crashed and last (automatic?) repair failed (144)
〇 mysqldump: Couldnt execute show create table `$tb_name`: Table ./$db_name/$tb_name is marked as crashed and should be repaired (145)
〇 mysqldump: Error 1194: Table throne_tower 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盖掉了字典表。
处理思路：
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
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 ()
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)
原因：
mysqldump加了--dump-slave参数，缺少SUPER或REPLICATION CLIENT来执行SHOW SLAVE STATUS。
处理思路：
检查mysqldump的用户权限。〇 mysqldump: Couldn't execute 'STOP SLAVE SQL_THREAD': Access denied for user 'dump'@'localhost' (using password: YES) (1045)
原因：
mysqldump加了--dump-slave参数，缺少SUPER权限使用STOP SLAVE SQL_THREAD。
处理思路：
检查mysqldump的用户权限。
