如何优化MariaDB 10.6基于mysqldump的SQL恢复性能

针对35GB dump文件导入耗时2小时的问题，结合你的AWS环境，从导入命令、数据库配置、dump处理、资源利用几个维度给出具体优化建议：

一、调整mysql导入命令参数 #

1. 临时关闭二进制日志：目标库用于脱敏导入，无需记录binlog，可大幅减少IO开销。在导入前执行：

   SET sql_log_bin = 0;
   

   导入完成后执行SET sql_log_bin = 1;恢复。也可以直接在mysql命令中初始化该配置：

   mysql -h ${targetDbParams.host} -u ${targetDbParams.user} -p${targetDbParams.password} --init-command="SET sql_log_bin=0" ${targetDbParams.database} < /tmp/database_dump.sql
   

2. 关闭约束检查：导入时临时禁用外键和唯一键校验，避免插入时的约束验证开销：
   在dump文件开头添加以下语句，或导入前执行：

   SET foreign_key_checks = 0;
   SET unique_checks = 0;
   

   导入完成后恢复：

   SET foreign_key_checks = 1;
   SET unique_checks = 1;
   

3. 优化客户端缓存与提交：添加--quick（减少客户端内存占用，直接发送语句到服务器）和--max_allowed_packet=64M（避免大插入包报错）参数：

   mysql -h ${targetDbParams.host} -u ${targetDbParams.user} -p${targetDbParams.password} --quick --max_allowed_packet=64M ${targetDbParams.database} < /tmp/database_dump.sql
   

二、优化目标RDS数据库参数组 #

修改AWS RDS对应的参数组（修改后需重启实例，建议安排维护窗口）：

• 调整InnoDB缓冲池大小：目标库16GB内存，建议设置innodb_buffer_pool_size = 10G（留足内存给系统进程），提升数据缓存率，减少磁盘IO。
• 增大InnoDB日志文件大小：设置innodb_log_file_size = 2G（RDS中最大可设为4G），减少redo log切换和checkpoint频率，提升写入性能。
• 临时调整刷盘策略：导入期间设置innodb_flush_log_at_trx_commit = 2（每秒刷盘一次，而非每次事务），导入完成后改回1保证数据安全。
• 提升IO线程数：设置innodb_write_io_threads = 8和innodb_read_io_threads = 8，利用多CPU处理IO请求。

三、优化dump文件的生成与传输 #

1. 压缩dump文件：导出时直接压缩，导入时边解压缩边导入，减少磁盘占用和网络传输量（若EC2与RDS跨AZ部署效果更明显）：
   导出命令：

   mysqldump -h ${sourceDbParams.host} -u ${sourceDbParams.user} --single-transaction --extended-insert --quick --disable-keys --set-charset -p${sourceDbParams.password} ${sourceDbParams.database} | gzip > /tmp/database_dump.sql.gz
   

   导入命令：

   zcat /tmp/database_dump.sql.gz | mysql -h ${targetDbParams.host} -u ${targetDbParams.user} -p${targetDbParams.password} ${targetDbParams.database}
   

2. 并行导出/导入：使用mydumper和myloader替代原生工具，支持并行处理大表，充分利用目标库的8vCPU资源。示例导入命令：

   myloader -h ${targetDbParams.host} -u ${targetDbParams.user} -p${targetDbParams.password} -d /path/to/dumped_files -o -t 8
   

   （-t 8表示用8个线程并行导入）

四、环境资源优化 #

1. EC2与RDS同AZ部署：确保运行cron任务的EC2和目标RDS在同一个AWS可用区，避免跨AZ的网络延迟和带宽限制。
2. 临时升级EC2实例：当前EC2的2vCPU/4GB内存可能成为解压缩、传输的瓶颈，导入期间可临时升级到c5.xlarge（4vCPU/8GB），完成后再降配控制成本。
3. 提升RDS存储IOPS：若目标RDS使用GP2存储，切换到GP3并将IOPS调整至10000以上，或使用IO1/IO2存储，满足导入时的高写入IO需求。

五、其他细节 #

• 禁用自动提交：导入前执行SET autocommit = 0;，导入完成后执行COMMIT;，减少频繁提交事务的开销（大表导入时需注意事务大小，避免锁等待）。
• 拆分大表单独处理：若数据库中有单个超过10GB的大表，可单独导出该表，用多线程并行导入，避免单表拖慢整体进度。

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