MySQL转移大量数据技巧

HjmaAsC

已于 2024-03-13 10:24:50 修改

阅读量563

点赞数 7

CC 4.0 BY-SA版权

文章标签： mysql 数据库

于 2024-03-13 10:12:14 首次发布

本文链接：https://blog.youkuaiyun.com/hjmaAsC/article/details/136670856

本文介绍了旧有的mysqldump和mysqlpump在处理大数据表时的不足，提出使用SQL导出和loaddatainfile的新方法，包括解决安全选项限制、处理不需要的字段以及合理的导入验证流程。同时提及了导入时的索引管理和速度提升技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

旧方法

使用mysqldump，mysqlpump这两个命令行工具，但都有缺陷

mysqldump的速度太慢，3千万记录要10个小时
mysqlpump无法使用指定字段名对数据导入

新方法

参考：Mysql亿级数据大表单表备份
使用SQL语法select * from into outfile导出，再使用load data infile ‘’ into table

select * from 表名(可以使用多表联合查询) into outfile '绝对路径文件名';
load data infile '绝对路径文件名' into table 表名 (字段名1,字段名2)

面临问题

导出数据时，提示：The MySQL Server is Running with the --secure-file-priv Option So It Cannot Execute This Statement，这个错误可以通过配置my.conf文件解决，在[mysqld]段落中添加secure-file-priv配置：

[mysqld]
secure-file-priv=可用目录

导入时，如果有不需要的字段，可以用@dump占位跳过不需要的字段，或者使用变量来处理，变量是可以进行计算操作的

SET @file_name = 'myfilename'
 ;

 LOAD DATA LOCAL INFILE @file_name  
 INTO TABLE revenue_table  
 FIELDS TERMINATED BY ','   
 LINES TERMINATED BY '\n'  
 ( @total_rev
 , @monthly_rev
 , @day_rev
 )
 SET `total_rev`   = @total_rev
   , `monthly_rev` = @monthly_rev
   , `day_rev`     = @day_rev
   , `from`        = @file_name
 ;

合理的流程

先写个语句，看看能不能从A库插入记录到B库，注意限制行数，这个步骤用于避免字段不对应

insert into a (字段1,字段2...) select 字段1,字段2... from b limit 10

导出数据后，在目的数据库拷贝粘贴测试好的字段对照，这样就可以避免非常大的麻烦

补充

导入时，可以先删除索引，导入数据完成后再重建，可以提高处理速度