mysqldump中断的常见错误和解决措施

定位

最近统一处理了一大堆db备份失败的案例，针对mysqldump的失败的现象和处理措施总结如下

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `blt_bulletinannex` at row: 626
报错条件：一般是存在blob，text等字段，单条记录超过默认的24M
解决措施：mysqldump调大max_allow_packet参数，在服务器端修改这个参数无效


mysqldump: Couldn't execute 'show table status like 'members\_ban\_user\_view'': SELECT command denied to user ''@'%' for column 'user_id' in table 'members_ban_log' (1143)
报错条件：相应的视图的账户给的权限不足；或者是用户不存在
解决措施：需要视图定义账户的Create_view_priv和Show_view_priv权限；或者添加对应的用户和权限；删除该视图


mysqldump: Couldn't execute 'show create table `innodb_index_stats`': Table 'MySQL.innodb_index_stats' doesn't exist (1146)
报错条件：mysql5.6，系统表损坏，该表是innodb引擎
解决措施：物理删除该表的frm文件和ibd文件，找到系统表的定义sql，重建系统表


mysqldump: Couldn't execute 'show create table `view_all_packages`': View 'locker.view_all_packages' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
报错条件：视图定义不合法
解决措施：删除或修改出问题的视图定义语句


mysqldump: Got error: 1045: Access denied for user 'ucloudbackup'@'10.10.1.242' (using password: YES) when trying to connect
报错条件：无法连接，密码，账户，host，port有问题
解决措施：先保证mysql能正常连接


mysqldump: Couldn't execute 'show create table `userarenalog`': Table './tank_11/userarenalog' is marked as crashed and should be repaired (145)
报错条件：myisam表损坏
解决措施：repair table XXX修复损坏的表，最好mysqlcheck一下所有表


mysqldump: Couldn't execute 'show fields from `TB_CROWDFUNDING_PROJECT`': Incorrect key file for table 'ql-5.5/14310da6-644a-472a-b170-0e7e75cfda87/tmp/#sql_32606_0.MYI'; try to repair it (126)
报错条件：临时表使用过程中/tmp空间不足，导致myisam临时表损坏
解决措施：增大磁盘空间就好


mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'analysis'': Cannot load from mysql.proc. The table is probably corrupted (1548)
报错条件：升级导致
解决措施：运行mysql_upgrade更新db，或者更新对应版本的mysql.proc表结构
5.1执行
alter table mysql.proc MODIFY COLUMN `comment`  char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL AFTER `sql_mode`;
5.5执行
alter table mysql.proc MODIFY COLUMN `comment`  text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL AFTER `sql_mode`;


mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)
报错原因：不合理的升级mysql版本导致
解决措施：先mysql_upgrade,不行再重启db看看(不大确定)


mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mysql'': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 (1064)
报错原因：select * from information_schema.ROUTINES limit 1报一样的错误
再查看mysql.proc表发现有函数或者存储过程定义有问题，比如根本不存在的db或者user出现在定义中，猜测是备份时没有加-R参数，直接导入到db后没有正常建立对应的函数或存储过程导致的
解决措施：先尝试drop语法删除mysql.proc中定义有问题的函数或存储过程记录，如果不行就直接delete from的方式删除


mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `vitality_flow` at row: 31961089
报错原因：1 该表是分区表  2 该表是innodb，存在大量的blob text等字段 3 上传NFS或者边备份边压缩
解决措施：针对1和3的原因，需要调大net_write_timeout参数；针对2的原因，需要调大max_allow_packet；


mysqldump: Couldn't execute 'SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('15616156','mysql','test','wx00','wx01','wx02','wx03','wxid')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME': Lost connection to MySQL server during query (2013)
报错原因：备份过程中因内存不足而oom
解决措施：加大内存

mysqldump: Couldnt execute show create table `shop_his_9`: Deadlock found when trying to get lock; try restarting transaction (1213) fails
报错原因：mysqldump过程中发生死锁
解决措施：重试即可

mysqldump: Got error: 1049: Unknown database cfcara when selecting the database fails
报错原因：随意修改大小写敏感问题导致
解决措施：先解决大小写问题


mysqldump: Couldn't execute 'STOP SLAVE SQL_THREAD': Access denied for user 'root'@'172.19.%.%' (using password: NO) (1045)
报错原因：从库备份，备份账户权限不足，无法登陆


mysqldump: Couldnt execute show create table `sk_order_38`: Unable to open underlying table which is differently defined or of non-MyISAM type or doesnt exist (1168) fails
报错原因：mrg表定义出错导致的吧
解决措施：把这个表删除