加快MySQL数据import速度

背景：

生产服务器每天凌晨都会定时备份生产数据库，备份完成之后，通过脚本拉回本地，然后再导入本地的数据库。为何要导入本地数据库，这是为了能够在内网进行一些数据的处理和查询，而这些处理和查询比较耗时，所以一般都不会在生产环境进行操作。

先来看下本地服务器的配置：

在数据量不大的情况下，SQL 的压缩文件大约500M, 导入时间也大约花到2个小时。平时都是定时任务在凌晨跑，所以具体耗费多长时间并不影响使用。

随着业务数据的增长, 备份的 SQL 压缩文件也增加到了1.4G，数据涨了接近2倍。所以导入的速度越来越慢，而且非常明显，从凌晨的05:50, 一直到下午的13点左右，大约7个小时。这样的速度是无法接受的，必须要进行优化

数据库配置（my.cnf）作以下调整

各参数说明：

– innodb_buffer_pool_size : 缓存访问频繁的数据大小

– innodb_log_buffer_size : 增加缓冲区可以减少写日志I/O次数

– innodb_log_file_size : 增加日志文件大小可以减少写I/O

– innodb_write_io_threads : 用于写.ibd数据文件的线程数，默认为4，每个线程最多可以向操作系统发起256个I/O请求，此参数最大可以调整到64；

– innodb_flush_log_at_trx_commit：在发生异常崩溃的情况下，0和2都会丢失大概1秒的数据。一种折衷的方案是0和2都可以提高写的性能。设置为0，是因为InnoDB Log Buffer会每秒都会刷新到Transaction Logs (ib_logfile0, ib_logfile1)，设置为2，只有事务提交时，才会把InnoDB Log Buffer刷新到Transaction Logs

修改好配置文件后这样重启数据库:

重启完成后，即可执行导入数据脚本。具体效果如何，可以通过atop命令查看，参考如下：

重点关注磁盘I/O 方面的情况，上面显示的是数据写入速度为 18.57 MB/s。

另外也可以参考InnoDB的状态

导入完数据后再重启下数据库