MYSQL对千万级数据的快速迁移-蒲公英云

MYSQL对千万级数据的快速迁移

怼烎@ 2024-03-30 14:34 191阅读 0赞

MYSQL对千万级数据（10个G左右）的迁移，耗时控制在半小时内

1.show variables like ‘%secure%’;查看 secure-file-priv 当前的值是什么
2.如果没有可以在my.ini（windows）my.cnf（linux）中添加secure_file_priv的值指定具体路径
3.导出
SELECT * INTO OUTFILE ‘data.txt’ FIELDS TERMINATED BY ‘,’ FROM table2;
4.导入
LOAD DATA INFILE ‘/var/lib/mysql-files/data.txt’ INTO TABLE t_faceset_face_ref FIELDS TERMINATED BY ‘,’;
5.导入时一定要先把这张表的外键和索引全部去除，不然还是会非常耗时

首先是两者导出速度的对比
navicat导出两百多万条的数据：

通过命令行导出相同的数据：

字段较多的两百多万条数据仅需15.06秒，甚至八百多万条的字段较少的数据也只需要17.55秒，相比navicat的导出工具，快了非常非常非常·····的多！！！
命令行导出教程

SELECT * FROM tablename INTO OUTFILE ‘target_file’ [option];

其中 option 参数可以是以下选项：

命令参数	说明
fields terminated by ‘字符’	字段分隔符，默认字符为制表符’\t’
fields [optionally] enclosed by ‘单字符’	字段引用符，加上optionally后在数字类型上不会有引用符号
fields escaped by ‘单字符’	转义字符，默认为’’
lines starting by ‘字符’	每行前都加此支付，默认为空
lines terminated by ‘字符’	行结束符，默认为’\n’

命令行示例：

SELECT

FROM

dianping_food INTO OUTFILE ‘I:/bigdata/data_mysql/food.txt’

fields terminated by “|”

导出字段、需要导出的数据表以及导出格式根据个人需求修改命令行。

可能遇到的问题

1290 - The MySQL server is running with the —secure-file-priv option so it cannot execute this statement

原因：安装MySQL的时候限制了导入与导出的目录权限只能在规定的目录下才能导入，通过命令行查询规定的目录（不要忘了；）

show variables like ‘%secure%’;

下图是查询结果（我的目录经过修改过了），可以直接将导出目录更改为你所查询到的secure_file_priv后面的文件位置，或者自行更改导出目录。

如何修改目录？
进入mysql的安装目录下的my.ini，例如我的目录是：C:\ProgramData\MySQL\MySQL Server 5.7，修改secur-file-priv为你需要导出的文件夹位置，修改之后保存重启mysql即可。