常见的mysqldump错误

为了便于阅读，以下场景的mysqldump脚本均作了精简，部分参数未做展示。

写在开头

通常，加快导入速度的办法：

set global innodb_flush_log_at_trx_commit=0;
set global sync_binlog=500000;
show variables like 'sync_binlog';

另外，备份单个库的推荐语法：

mysqldump -h 127.0.0.1 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --triggers --events --routines --skip-tz-utc -uroot -p --databases xxx > /xxx.2022.sql

场景1：Error 1143: Couldn't execute 'show fields from xxx'

2018.01.17.执行 mysqldump -h127.0.0.1 -uroot -p -A > /data/20220117.sql 报错：

mysqldump: Couldn't execute 'SHOW FIELDS FROM `v_m_invo_loan`': SELECT command denied to user ''@'%' for column 'loan_id' in table 'sbct_rela_cosl' (1143)

• 1.执行 select table_schema from information_schema.tables where table_name = 'sbct_rela_cosl' limit 10; 找到 sbct_rela_cosl表所在的库为 xxx
• 2.执行 use xxx; SHOW FIELDS FROM v_m_invo_loan; 报错 ERROR 1143 (42000): SELECT command denied to user ''@'%' for column 'loan_id' in table 'sbct_rela_cosl'
• 3.执行 select * from v_m_invo_loan limit 1; 报错 ERROR 1449 (HY000): The user specified as a definer ('root'@'%') does not exist
• 4.执行 show create table v_m_invo_loan; 看到 CREATE ALGORITHM=UNDEFINED DEFINER=root@% SQL SECURITY DEFINER VIEWv_m_invo_loanAS
• 5.执行 select user,host from mysql.user; 看到只有 root@localhost，并无 root@%
• 6.执行 update mysql.user set host = '%' where user='root' and host ='localhost'; 再次执行mysqldump则ok了

场景2：华为云mysql导入后丢失function

2021.11.19.由于华为云RDS提供的root账号其实没有super权限，所以会导致source导入.sql备份文件的时候，function/view/procedure/trigger/event会失败而缺失，需要将备份文件中的 DEFINER=root@% 去掉之后再导入。

参考：https://support.huaweicloud.com/rds_faq/rds_faq_0075.html

场景3：报错 Error: Binlogging on server not active

2018.11.17.在一台未启用binlog的实例上执行 mysqldump 报错 mysqldump: Error: Binlogging on server not active

[root@localhost]# mysqldump --single-transaction --master-data=2 ...
Enter password: 
mysqldump: Error: Binlogging on server not active

解决办法：去掉参数 --master-data=2

场景4：current_timestamp字段导出后偏差8小时

2021.10.21.添加--skip-tz-utc参数可解决current_timestamp字段导出后偏差8小时

场景5：Error 2013: Lost connection to MySQL server

2020.06.10.在一台配置较低的机器备份2.3G的库报错如下：

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `info_user_data` at row: 16858

解决办法：

show global variables where variable_name in ('max_allowed_packet','net_read_timeout','net_write_timeout'); 
set global max_allowed_packet=134217728;
set global net_read_timeout = 120;
set global net_write_timeout = 900;

场景6：Error 2020: Got packet bigger than 'max_allowed_packet' bytes

2016.12.22.执行 mysqldump -uroot -p --databases cat > /data/20161222.sql 报错：

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `daily_report_content` at row: 9086

解决办法：

show variables like 'max_allowed_packet';
mysqldump -uroot -p --databases cat --max_allowed_packet=512M > /data/20161222.sql

场景7：Error 2006 (HY000) at line 41: MySQL server has gone away

2016.07.18.执行 mysql -uroot -p -Dxxx < d:\xxx.sql 导入一个800MB的.sql报错 Error 2006 (HY000) at line 41: MySQL server has gone away

解决办法：

• 1.加快导入速度

set global innodb_flush_log_at_trx_commit=0;
set global sync_binlog=500000;

• 2.修改 max_allowed_packet (由4194304改为41943040)和 wait_timeout 和 interactive_timeout 参数

show variables where variable_name in ('wait_timeout','interactive_timeout','max_allowed_packet'); 
set global wait_timeout=2147483;
set global interactive_timeout=2880000;
set global max_allowed_packet=134217728;

场景8：Couldn't execute 'SHOW PACKAGE STATUS WHERE Db = 'xxx''

2021.10.27.执行 mysqldump 报错 mysqldump: Couldn't execute 'SHOW PACKAGE STATUS WHERE Db = 'xxx'': 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 'PACKAGE STATUS WHERE Db = 'xxx'' at line 1 (1064)

解决办法：

• 1.确认一下备份失败的mysqldump版本

[root@aaa ~]# mysqldump --version
mysqldump  Ver 10.16 Distrib 10.3.9-MariaDB, for Linux (x86_64)

• 2.更换mysqldump版本（不用MariaDB版的mysqldump），再次备份就好了

[root@bbb ~]# mysqldump --version
mysqldump  Ver 10.13 Distrib 5.7.33, for Linux (x86_64)

场景9：备份文件的开头找不到 GTID_PURGED

2023.06.29.查找备份文件 head -n 50 mysqldump.20230629.sql | grep GTID_PURGED 找不到 SET @@GLOBAL.GTID_PURGED，从而无法搭建GTID复制。

[root@localhost data]# head -n 50 mysqldump.20230629.sql | grep PURGED

[root@localhost data]# mysql --version    
mysql  Ver 14.14 Distrib 5.7.42-45, for Linux (x86_64) using  6.2

[root@localhost data]# mysqldump --version
mysqldump  Ver 10.13 Distrib 5.7.42-45, for Linux (x86_64)

解决办法：

• 查找 Release Notes，在 5.7.36 的 Bugs Fixed（搜索 gtid_executed）里讲到 gtid_purged 的顺序有调整，将 SET @@GLOBAL.GTID_PURGED 放到了文件的结尾，而此 MySQL 版本为 5.7.42-45，点此看Bug

[root@localhost data]# head -n 50 mysqldump.20230629.sql | grep PURGED

[root@localhost data]# tail -n 50 mysqldump.20230629.sql | grep GTID_PURGED
SET @@GLOBAL.GTID_PURGED='47ce8a1c-1656-11ee-936f-76ff8b5e01b3:1-15';