Mysql千万级数据量批量快速迁移

环境

Mysql版本：8.0

迁移说明

Mysql数据的迁移，推荐两种方式

1. mysqldump mysqldump比较适合几十万上百万的较小数据的迁移使用
2. mysql load data load data infile 语句可以从一个文本文件中以很高的速度读入一个表中，性能大概是 insert 语句的几十倍，比较适合上千万级及更高的海量数据迁移使用

1.mysqldump

1.1导出

使用环境：只要是有mysql的环境就可以，不限制Linux或者是Windows系统，不同系统最后面的导出位置不一样而已

导出某个库中的表数据内容，如果是导出整个库，把–tables testtable去掉即可

mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --default-character-set=utf8 -c -t --skip-add-locks --databases testbase --tables testtable> \root\move.sql

对应

mysqldump -u用户名 -p密码 -h数据库IP地址 -P端口 --default-character-set=utf8 -c -t --skip-add-locks --databases 数据库名称 --tables 要导出的表名称> 导出的位置

部分解释

– -c：使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。

– -t：只导出数据，而不添加CREATE TABLE 语句。使用该参数，导入之前需要提前建好相同表结构，如果不加该参数则导入时不需要再建表。

– –skip-add-locks：—取消在每个表导出之前增加LOCK TABLES（默认存在锁）

1.2导入

使用环境：只要是有mysql的环境就可以，不限制Linux或者是Windows系统，不同系统最后面的导出位置不一样而已

使用命令登陆到需要被导入数据的mysql服务上

mysql -uroot -p123456 -h127.0.xxx.xxx -P3306

切换需要导入的数据库

use databasename(数据库名称);

导入数据,source 后面换成你的导出的sql文件路径，然后回车执行命令，静静地等待就好了

source /root/move.sql;

2.load data

load data使用前提

查看secure_file_priv该变量的设置

mysql> SHOW GLOBAL VARIABLES LIKE '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_file_priv         |       |
+--------------------------+-------+

secure_file_priv的Value不能是null，如果是null是不能导入数据的,可以分以下两种情况

1. Value值是一个路径，比如：/root/data/ 在不改变该值的情况下，需要将导出的数据复制到该路径下
2. Value值什么都没有，就像作者这样，这样导出的数据可以在Mysql机器的任意位置

解决secure_file_priv值问题

如果你的mysql服务是按照传统的方式安装

编辑配置文件

vim /etc/my.cnf（换成你的my.cnf路径）

在mysqld下新增一行

[mysqld]
secure_file_priv=''

如果是docker容器运行的Mysql，

1.如果在创建容器时，映射的有实际物理主机中my.cnf配置文件，同上方法即可，然后重启mysql容器

2.如果创建容器时并没有映射my.cnf配置文件,参考：Docker环境下Mysql

2.1导出

需要先登陆到mysql服务，load data导出执行一条sql

select * from table(表名) into outfile '/root/data.txt'(导出路径);

亲测大约2千万的数据，导出耗时只要272.24秒

2.2导入

同样需要先登陆到mysql服务，load data导入执行一条sql，需要先创建一张和原来结构字段数相同的表，注意几点，比如字段类型可以由原来的int>varchar,但是返过来就不行，字段名和表明可以和原来不一样都可以，但是字段数量要大于等于原来的字段数量，比如原表如下，则需要创建新表字段不能少于3个

load data infile '/root/data.txt' into table tableName(ID,NAME,AGE);

导入时可以使用@dummy丢弃不需要的字段，例如丢弃AGE字段值

load data infile '/root/data.txt' into table tableName(ID,NAME,@dummy);

执行回车静静的等待就好了

注意事项

– 如果导出前，表中数据为空值null，导出后会是\N表示

– 数据库字段如果是varchar/char，插入空时，load data 默认导入 空字符串

– 数据库字段如果是decimal，插入空时，load data 默认导入 0.00000000

– 数据库字段如果是datetime，插入空时，load data 默认导入 0000-00-00 00:00:00

– 数据库字段如果是datetime，插入yyyy-MM-dd时，load data 默认导入 yyyy-MM-dd 00:00:00，数据正确性能够保证

– 数据库字段如果是datetime，插入HH：mm:ss时（HH后面应是英文冒号），load data 默认导入 0000-00-00 00:00:00，数据正确性不能够保证

注：mysqldump的详解可参考:https://www.cnblogs.com/chenmh/p/5300370.html 注意事项有摘抄网络文章，侵告知，立即删除