MySQL超大SQL文件导入优化方案

巴韭特锁螺丝 2024-04-20

152

一、需求背景

存在超大备份mysql文件需要进行还原，直接使用soure还原，速度非常慢，故考虑采取优化措施。

mysql版本：5.7.34

数据库文件大小：14G左右

二、优化方案

1、调整最大接收数据包大小

查询当前数据包大小

SELECT @@max_allowed_packet (1024 * 1024) AS 'max_allowed_packet(MB)';

mysql> SELECT 
    ->   @@max_allowed_packet  (1024 * 1024) AS 'max_allowed_packet(MB)';
+------------------------+
| max_allowed_packet(MB) |
+------------------------+
|               500.0000 |
+------------------------+
1 row in set (0.00 sec)

修改my.cnf配置文件,调整为1G

vim my.conf


max_allowed_packet=1G

重启服务。

2、开启事务

1)关闭日志和自动提交

set sql_log_bin=OFF;//关闭日志
set autocommit=0;//关闭autocommit自动提交模式
set global max_allowed_packet = 20 *1024* 1024 * 1024;

2）开启事务；

START TRANSACTION;

3、执行source导入

4、提交事务

COMMIT;

3、临时修改参数

连接数据库，先修改参数为：

set global innodb_flush_log_at_trx_commit = 2;
set global sync_binlog = 2000;

执行source导入；

导入成功后，还原参数：

set global innodb_flush_log_at_trx_commit = 1;
set global sync_binlog = 1;

4、超大文件切割

使用SQLDumpSplitter预处理.sql文件，可以根据实际情况切割成1024 Megabytes(即每个文件1Gb)。

SQLDumpSplitter可以自动将结构语句(建表建库等)和数据语句(insert语句等)分开，无需担心分割出错，该程序无需安装，直接运行即可。

三、效果实测

优化前耗时：6个小时左右（13:42-19:52）

优化后耗时：（8分48秒）

文章转载自巴韭特锁螺丝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL超大SQL文件导入优化方案

评论