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

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

mysqldump: Error: Query execution was interrupted, maximum statement execution time exceeded when trying to dump tablespaces OR
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:
http://blog.itpub.net/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 definerinvoker 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) OR
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) OR
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一则：

 1170820 17:44:48 InnoDB: error: space object of table 'db_name/tb_name',
 2InnoDB: space id 4335 did not exist in memory. Retrying an open.
 3170820 17:44:48 InnoDB: Error: tablespace id and flags in file './db_name/tb_name.ibd' are 0 and 0, but in the InnoDB
 4InnoDB: data dictionary they are 4335 and 0.
 5InnoDB: Have you moved InnoDB .ibd files around without using the
 6InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
 7InnoDB: Please refer to
 8InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
 9InnoDB: for how to resolve the issue.
10170820 17:44:48 InnoDB: cannot calculate statistics for table db_name/tb_name
11InnoDB: because the .ibd file is missing. For help, please refer to
12InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
13170820 17:44:48 [ERROR] MySQL is trying to open a table handle but the .ibd file for
14table db_name/tb_name does not exist.
15Have you deleted the .ibd file from the database directory under
16the MySQL datadir, or have you used DISCARD TABLESPACE?
17See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
18how you can resolve the problem.

处理思路：
从完整备份+binlog还原，对于有主或从的实例，可考虑通过物理备份还原。

该故障出现的原因可能很多，此处只列出两种。排错思路比较简单，找到这张表或库，并确认能否手动正常访问。

mysqldump: Error 2020: 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操作。
复现一：

1① session1> CREATE TABLE a (id int) ENGINE=InnoDB;
2② session2> START TRANSACTION WITH CONSISTENT SNAPSHOT;
3③ session1> ALTER TABLE a ADD COLUMN name varchar(32);
4④ session2> SELECT * FROM a;
5ERROR 1412 (HY000): Table definition has changed, please retry transaction

p.s. 如果③和④调换顺序，则ALTER TABLE无法成功，则会等待MDL。

复现二：

1① session1> START TRANSACTION WITH CONSISTENT SNAPSHOT;
2② session2> CREATE TABLE b (id int) ENGINE=InnoDB;
3③ session1> SELECT * FROM b;
4ERROR 1412 (HY000): Table definition has changed, please retry transaction