MySQL 8.0下mysqlpump导入报错及mysqldump排库失效求助

问题概述 #

• 需求：编写脚本将MySQL 8.0数据库备份至D-7或D-1备份服务器
• 选型原因：选用mysqlpump是因其支持--exclude-databases参数，方便排除系统库
• 核心问题：导入mysqlpump生成的备份时，随机触发ERROR 1075（提示自增列需设为键），但源库中自增列已明确设为主键
• 附加问题：原mysqldump排除数据库的脚本在MySQL 8.0环境下失效

失效的旧mysqldump脚本（排除数据库部分） #

REM Export Database except 5 from Server A

for /f "delims=" %%A in ('mysql -h mysql02.francelink.net -u dumpuser -pxxxxxxxxx -e "SELECT GROUP_CONCAT(schema_name SEPARATOR '\" \"') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','performance_schema','information_schema')"') do @set tablestodump=%%A

mysqldump -h ServerA_IP -udumpuser -pxxxxxxxxxxx --opt --databases "%tablestodump%" --result-file "D:\DumpsToImport\dump-MySQL02-%mydate%.sql"

当前使用的mysqlpump脚本 #

REM Today date in variables
For /f "tokens=1-3 delims=/ " %%a in ('date /t') do (set mydate=%%a-%%b-%%c)

REM Export rights and users from Server A
mysqldump -h ServerA_IP  --port=3306 --user=importuser --password=xxxxxxxxx --opt mysql user --result-file "D:\DumpsToImport\dump mysql users.sql" 
mysqldump -h ServerA_IP  --port=3306 --user=importuser --password=xxxxxxxxx --opt mysql db --result-file "D:\DumpsToImport\dump mysql db.sql" 

REM Export Database except 5 from Server A
mysqlpump -h ServerA_IP  --port=3306 --exclude-databases=mysql,performance_schema,information_schema,sys,phpmyadmin --add-drop-table --user=importuser --password=XitbLxxxxxxxxx L5r8 > D:\DumpsToImport\dump-MySQL03-%mydate%.sql

REM Import rights and users to Server B
mysql -h ServerB_IP  --port=3306 --user=importuser --password=xxxxxxxxx mysql < "D:\DumpsToImport\dump mysql users.sql"
mysql -h ServerB_IP --port=3306 --user=importuser --password=xxxxxxxxx mysql < "D:\DumpsToImport\dump mysql db.sql" 

REM Reset rights
mysql -h ServerB_IP --port=3306 --user=importuser --password=xxxxxxxxx -e "FLUSH PRIVILEGES" 

REM Import DUMP .sql to Server B
mysql -h ServerB_IP --port=3306 --user=importuser --password=xxxxxxxxx < "D:\DumpsToImport\dump-MySQL03-%mydate%.sql"

修复方案 #

一、解决mysqlpump导入的ERROR 1075问题 #

ERROR 1075的本质是MySQL 8.0对表结构定义的语法顺序要求更严格，mysqlpump的并行备份可能导致DDL语句顺序混乱（比如先定义AUTO_INCREMENT再定义主键）。

具体修复：

1. 禁用并行备份：在mysqlpump命令中添加--default-parallelism=1参数，确保表结构DDL按正确顺序生成。修改后的导出命令：

   mysqlpump -h ServerA_IP  --port=3306 --exclude-databases=mysql,performance_schema,information_schema,sys,phpmyadmin --add-drop-table --default-parallelism=1 --user=importuser --password=XitbLxxxxxxxxx L5r8 > D:\DumpsToImport\dump-MySQL03-%mydate%.sql
   

2. 极端情况处理：若并行禁用后仍报错，可添加--skip-create-options参数，手动确保导入时自增列的主键定义逻辑正确，或备份后检查SQL文件，调整DDL语句顺序（将主键定义放在AUTO_INCREMENT之前）。

二、修复旧mysqldump脚本使其适配MySQL 8.0 #

旧脚本失效的原因是MySQL 8.0默认的GROUP_CONCAT长度限制，以及命令行引号转义、表头干扰的问题。

修复后的脚本：

REM Export Database except 5 from Server A

REM 临时扩大GROUP_CONCAT长度，避免数据库名过多被截断
for /f "delims=" %%A in ('mysql -h mysql02.francelink.net -u dumpuser -pxxxxxxxxx -e "SET SESSION group_concat_max_len = 1024000; SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','performance_schema','information_schema','sys','phpmyadmin')"') do @set tablestodump=%%A

REM 移除查询结果中的表头（MySQL命令行默认返回表头）
for /f "tokens=1,*" %%B in ("%tablestodump%") do @if "%%B"=="GROUP_CONCAT(schema_name SEPARATOR ' ')" set tablestodump=%%C

mysqldump -h ServerA_IP -udumpuser -pxxxxxxxxxxx --opt --databases %tablestodump% --result-file "D:\DumpsToImport\dump-MySQL02-%mydate%.sql"

修复说明：

• 新增SET SESSION group_concat_max_len = 1024000;解决数据库名过多时结果被截断的问题
• 添加表头过滤逻辑，避免mysqldump将查询表头识别为数据库名
• 移除%tablestodump%的引号包裹，确保多个数据库名被正确解析为独立参数

内容的提问来源于stack exchange，提问作者Moinnou