Mysql导入导出亿条数据（排坑篇）

---

声明:这是我在大学毕业后进入第一家互联网工作学习的内容

---

背景：公司业务需要给某个活动的支付宝用户发送模板消息

经调查发现，此次活动的用户数量大约2亿，为了模拟生产环境，领导让我把生产库的用户信息（脱敏）拉到开发库，让开发写好程序读取数据并推送消息。

排坑1：Navicat

由于我对Mysql不是很了解，在数据同步前我事先问了下公司的DBA应该如何操作：使用navicat的数据传输功能。

环境：Navicat Premium 12

• 操作：
• 1.点击工具列表的数据传输
• 2.选择源数据库及目标数据库
• 3.选择表（默认全表）
• 4.点击开始
  

过程

数据传输速度大约1分钟20w条数据，传完大概得8个多小时，我挂机了一段时间后再看进度发现数据传输到百分之11的时候失败了，报错如下：

Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage; increase this mysqld variable and try again

百度之后发现
这是由于更新和删除的大事务会写入大量binlog，可能会造成binlog cache过小而导致执行失败。

具体解决方法为如下：

查看max_binlog_cache_size大小。

mysql> show variables like 'max_binlog_cache_size';
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| max_binlog_cache_size | 134217728 |
+-----------------------+-----------+

查看该参数设置为134217728B，即128MB。

此参数可以动态修改，修改该参数为40GB。

mysql> set global max_binlog_cache_size=40*1024*1024*1024;
Query OK, 0 rows affected (0.05 sec)

为了防止数据库重启后还出现此问题，还需要在/etc/my.cnf里修改max_binlog_cache_size的值。

排坑2：mysqldump

由于用navicat传输速度实在过慢，听取DBA建议后直接采用mysqldump的方式。

基本用法如下：

导出
mysqldump -uxxx -pxx 库名   表名  > bak.sql
还原
mysql -uxxx -pxxx 库名 < bak.sql

导出完之后再还原到目标数据库后出现了和上个坑一样的问题：max_binlog_cache_size过小。

排坑3：select into outfile

在上述问题还没找到解决方法的时候继续百度发现了还有一种方法可以解决问题。

MySQL自带的导出语句：select into outfile语句

SELECT * FROM db --可以加where条件
INTO OUTFILE "/data/bak.txt" --导出文件位置
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' -- 字段分割符和包含符
LINES TERMINATED BY '\n';--换行符

但是在执行的时候出现报错：

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

查看官方文档，secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。

• secure_file_priv 为 null 时，表示限制mysqld不允许导入或导出。
• secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。
• secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。

查看secure_file_priv命令如下：

show global variables like '%secure_file_priv%';

而且此参数不能直接修改，必须在/ete/my.cnf里添加（默认没有这个参数）并且重启mysql。

secure_file_priv='/data'

然后执行完导出语句后发现又报错：文件权限不足，原因是data文件夹是root所有，而mysql导出到文件的执行者为mysql用户

chmod 777 /data

赋权后导出文件终于没问题了，然后再执行导入文件的的命令。

load data infile '/data/bak.txt' --默认指定服务器文件夹
ignore into table xx --允许重复记录插入
fields terminated by ','  --判断字段通过逗号标识来分隔开
lines terminated by '\n'(CustID,DeviceNo,logintype);--通过换行标识来解析成为每一条数据和插入到我指定的字段

总结

2亿多条数据的文件29G，导出速度最快的还是mysqldump方法，但是导入最稳定的我认为还是select into outfile方法，因为容错高，mysqldump的文件就一句insert（29G
的数据），执行失败就浪费了太多时间，但是select into outfile即使执行失败也能插入执行成功的数据（要是一条都没插进去当我没说- -）不会太浪费时间。最后提醒大家，大数据量的传输千万不要用navicat，很容易假死。最后补一句：太依赖可视化界面的软件很难成长起来，以后我尽量进服务器敲命令。

任务总算完成了，花了不少时间，趁机补习了好多mysql的知识，对于上面的问题其实还有优化的思路，只不过没时间去实验了，如果有幸看到我的博客并且也出现了我遇到的问题可以尝试下我的想法。

• 临时关闭binlog日志。
• 如果你的目标表数据为空，可以先删索引，再导入数据，最后再加上索引。

参考资料

MySQL 亿级数据导入导出/数据迁移笔记

mysql参数max_binlog_cache_size设置不当引发的血案

mysql官方文档

---

版权声明：

原创不易，洗文可耻。除非注明，本博文章均为原创，转载请以链接形式标明本文地址。