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

怼烎@ 2024-03-30 14:34 94阅读 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.导入时一定要先把这张表的外键和索引全部去除，不然还是会非常耗时

1.  **首先是两者导出速度的对比**  
    navicat导出两百多万条的数据：  
    ![be427a8f6b8949e9872bbc1f2b673c1c.png][]  
    通过命令行导出相同的数据：  
    ![51dac48992d54f889b4082b1c786813b.png][]  
    字段较多的两百多万条数据仅需15.06秒，甚至八百多万条的字段较少的数据也只需要17.55秒，相比navicat的导出工具，快了非常非常非常·····的多！！！
2.  **命令行导出教程**

SELECT \* FROM tablename INTO OUTFILE 'target\_file' \[option\];

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

<table> 
 <thead> 
  <tr> 
   <td> <p style="margin-left:.0001pt;text-align:center;"><strong>命令参数</strong></p> </td> 
   <td> <p style="margin-left:.0001pt;text-align:center;"><strong>说明</strong></p> </td> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td> <p style="margin-left:.0001pt;text-align:left;">fields terminated by ‘字符’</p> </td> 
   <td> <p style="margin-left:.0001pt;text-align:left;">字段分隔符，默认字符为制表符’\t’</p> </td> 
  </tr> 
  <tr> 
   <td> <p style="margin-left:.0001pt;text-align:left;">fields [optionally] enclosed by ‘单字符’</p> </td> 
   <td> <p style="margin-left:.0001pt;text-align:left;">字段引用符，加上optionally后在数字类型上不会有引用符号</p> </td> 
  </tr> 
  <tr> 
   <td> <p style="margin-left:.0001pt;text-align:left;">fields escaped by ‘单字符’</p> </td> 
   <td> <p style="margin-left:.0001pt;text-align:left;">转义字符，默认为’’</p> </td> 
  </tr> 
  <tr> 
   <td> <p style="margin-left:.0001pt;text-align:left;">lines starting by ‘字符’</p> </td> 
   <td> <p style="margin-left:.0001pt;text-align:left;">每行前都加此支付，默认为空</p> </td> 
  </tr> 
  <tr> 
   <td> <p style="margin-left:.0001pt;text-align:left;">lines terminated by ‘字符’</p> </td> 
   <td> <p style="margin-left:.0001pt;text-align:left;">行结束符，默认为’\n’</p> </td> 
  </tr> 
 </tbody> 
</table>

命令行示例：

SELECT

FROM

dianping\_food INTO OUTFILE 'I:/bigdata/data\_mysql/food.txt'

fields terminated by "|"

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

1.  **可能遇到的问题**

> 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后面的文件位置，或者自行更改导出目录。  
![140a7ef477e748f39c682bfddae61b1f.png][]  
**如何修改目录？**  
进入mysql的安装目录下的my.ini，例如我的目录是：C:\\ProgramData\\MySQL\\MySQL Server 5.7，修改secur-file-priv为你需要导出的文件夹位置，修改之后保存重启mysql即可。

**导出目录修改好了还是报同样的错误？**  
那就很可能是导出文件的斜杠写错了，不能照抄查询的结果，应将\\替换为/就可以了。

[be427a8f6b8949e9872bbc1f2b673c1c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/4f14f571975949f7bbd471bdf96d6fac.png
[51dac48992d54f889b4082b1c786813b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/cb063dcd327c4292a8cb4fbc03c7d963.png
[140a7ef477e748f39c682bfddae61b1f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/cdf6d4c7b2b3455694d12191ab5bd1ab.png