MySql超大Sql文件导入效率优化 —— 筑梦之路

使用场景

日常我们对mysql数据库、mariadb数据库进行定时备份，而随着时间增长，导出来的备份文件越来越大，使用备份sql文件进行还原的时候，大文件非常慢，有些要执行很长时间，效率很低。

如何优化？

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

# 查询当前的值，常见的值有64 128 512

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

# 修改配置文件my.cnf

max_allowed_packet=1G

2. 打开事务，避免失败不能回滚

# 关闭日志和自动提交

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

# 开启事务

START TRANSACTION;

3. source命令导入

mysql>source xxxx.sql;

4. 提交事务

COMMIT;

5. 超大文件切割

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

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

其他

# 临时修改参数

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

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

2）执行source导入；

3）导入成功后，还原参数：

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