mysqldump备份失败以及解决方法汇总

共计 7139 个字符，预计需要花费 18 分钟才能阅读完成。

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:
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 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 一则：

处理思路：
从完整备份 +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 操作。
复现一：

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

〇 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 表）：

复现二（表不存在）：

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

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

〇 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 (2013)
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 的用户权限。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2018-01/15069.htm