Mysql 导入实战

这个几天公司迁移预览版数据库，当前公司使用的是 Mysql 数据库，版本为 5.6。迁移的数据库大小也不算很大，2G 多一点，总体以小表为主，就几张表数据比较大，有业务记录表达到了 150W 的数量级，我一开始不以为然，直接将所有的表以 sql 文件的方式导出，然后再使用 Navicat 直接运行 sql 文件的方式导入。最后发现花费了很长时间也没有导入，于是回家研究了一下 Mysql 的导入导出，希望以后遇到这样的问题可以用得上。

1.首先修改 mysql 配置文件

max_allowed_packet = 256M
innodb_flush_log_at_trx_commit = 0

MySQL 会根据配置文件会限制 server 接受的数据包的大小。如果写入大数据时，因为默认的配置太小，插入和更新操作会因为 max_allowed_packet 参数限制，而导致失败。
max_allowed_packet 如果不设置，默认值在不同的 MySQL 版本表现不同，有的版本默认1M，有的版本默认4M。所以首先修改 max_allowed_packet 大小为 256M。

innodb_flush_log_at_trx_commit 参数
0：log buffer 将每秒一次地写入 log file 中，并且 log file 的 flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
1：每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，并且 flush(刷到磁盘)中去，该模式为系统默认。
2：每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，但是 flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL 会每秒执行一次 flush(刷到磁盘)操作。

当设置为 0，该模式速度最快，但不太安全，mysqld 进程的崩溃会导致上一秒钟所有事务数据的丢失。
当设置为 1，该模式是最安全的，但也是最慢的一种方式。在 mysqld 服务崩溃或者服务器主机 crash 的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。
当设置为 2，该模式速度较快，也比 0 安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

1.直接执行 sql 文件

速度慢，很慢，适合量很少的情况。

2.登录mysql后使用source命令

mysql -u<用户名> -p<密码>
mysql> use dbname;
mysql> source filename;

相较于第一种方式，速度明显快了很多，但任然很慢，在我的电脑上 5 分钟导入了 66W 条数据。

3.使用 mysql 命令行

mysql -u root - p dbname < filename

5分钟大约 80w 的数据，比前一种方法稍微快了一些。
10 分钟大约 110W 的数据。
20 分钟大约 160W 的数据，单表大小 1.2G。

4.使用 load data infile 方法

select * into outfile 'account.txt' fields terminated by ',' from va_account;

153W 行 1.1G 花费20s

LOAD DATA INFILE 'account.txt' INTO TABLE va_account;

使用 load data 后花费 7'12s 将数据倒完，和网上的平均的速度差距很大(6000W/h)，随后思考为什么使用 load data 的方式还是很慢。最后发现是表中的索引花费了导入的时间。将索引删除，我这里这张表是 3 个索引，删除后导入只花费 1 分钟左右，导入速度明显提升。

总结

使用 sql 执行的方式最慢，可能花费几个小时也无法将 150W 的数据导入，使用 load data infile 方法最快，分分钟将庞大的数据导入数据库。但是 load data infile 一次只能备份和导入一张表（或许是我没有找到多表同时的方法），所以我以后导入数据的方式使用方法 3 加方法 4 结合的方式导入和导出。将表中数据大的表单个导入导出，其他使用命令行的方式导入。

本文由个人 hexo 博客 co2fe.com 迁移
date: 2018-09-17 18:34:31