数据迁移

发表于 更新于 分类于
本文字数： 2.6k 阅读时长 ≈ 2 分钟
简单整理一下数据库中数据迁移相关内容。

数据迁移

一. 概述

1.1 什么是数据迁移？

数据迁移（英语：data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外，验证迁移数据的完整性和退役原来旧的数据存储，也被认为是整个数据迁移过程的一部分。数据迁移是任何系统实现、升级或集成的关键考虑因素，通常以尽可能自动化的方式执行，从而将人力资源从繁琐的任务中解放出来。数据迁移有多种原因，包括服务器或存储设备更换、维护或升级、应用程序迁移、网站集成、灾难恢复和数据中心迁移。

1.2 为什么需要迁移数据？

  • 数据备份
  • 数据转换
  • 业务需求
  • ……

1.3 常见方案

  • mysqldump：导出一个SQL文件，再在新数据库中执行。效率低耗时高，失败率高。
  • 脚本迁移：
    • 实现一个迁移脚本，远程连接源数据库，分块读取后再写入目标数据库。可以通过切分然后多线程执行的方式进行优化，但只能先读后写。
    • 通过 Redis 搭建一个生产者+ 消费者的迁移方案，在源数据服务器运行一个多线程脚本，并行读取数据，并生产到Redis队列；在目标服务器也运行一个多线程脚本，远程连接Redis，并行读取队列中的数据，并把数据写入目标数据库。相比前一个方案，这种方式是异步的，读和写可以同时进行。
  • 文件迁移：SqlLoader（Oracle）或 load data infile（MySql）如： select data into outfile file.txt、load data infile file.txt into table 的命令。

二. 方案实战

数据准备，一张日志表：

数据量为一千万：

将其从 test 库迁移到 test_b 库。

2.1 mysqldump

Oracle：

1
2
3
4
5
6
7
8
9
10
11
12
13
# 1.dmp是用数据泵expdp/exp导出
# 2.数据字典是dbtmp，如没有需创建
$ sqlplus /nolog
$ connect / as sysdba
# 或
$ sqlplus / as sysdba
SQL> create directory dbtmp as '/home/oracle/dbtmp';
mkdir dbtmp
# 数据字典创建后，给数据库用户赋权
SQL> GRANT READ,WRITE ON DIRECTORY dbtmp to 数据库用户;
# 4.导入，注意要和导出指令匹配
impdp <username>/<password> directory=dbtmp dumpfile=xxx.dmp logfile=data.log
imp <username>/<password> file=xxx.dmp full=y
1
2
3
4
IMP-00058: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'XXX'
SQL> alter user <username> quota unlimited on <tablespace_name>;
SQL> GRANT UNLIMITED TABLESPACE TO <username>;

2.2 MySql SqlLoader

1.在源数据库导出数据文件：

1
select * from logs1 into outfile 'logs1.txt';

执行耗时：（只需半分钟）

文件大小：676MB

生成文件格式：

1
2
3
4
5
6
1811	1	/index	0:0:0:0:0:0:0:1	\N	\N	null	2020-01-01 00:00:00	首页
1812	2	/index	0:0:0:0:0:0:0:1	\N	\N	null	2020-01-01 00:00:01	首页
1813	1	/index	0:0:0:0:0:0:0:1	\N	\N	null	2020-01-01 00:00:02	首页
1814	1	/index	0:0:0:0:0:0:0:1	\N	\N	null	2020-01-01 00:00:03	首页
1815	1	/index	0:0:0:0:0:0:0:1	\N	\N	null	2020-01-01 00:00:04	首页
......

2.复制数据文件到目标服务器：（如果是在服务器端操作）

1
2
3
4
zip fans.zip /data/fans.txt
scp fans.zip root@ip:/data/ 
# 在目标数据库导入文件
unzip /data/fans.zip

3.目标数据库执行命令：（需要先导入表结构）

1
load data infile 'logs1.txt' into table logs1(id, logtype, logurl, logip, logdz, ladduser, lfadduser, laddtime, htmlname);

执行耗时：（只需5分钟左右）

对比导出SQL文件，耗时从将近1小时缩短到5分钟。

在mysql执行 load data infile 和 into outfile 命令都需要在mysql开启了 secure_file_priv 选项， 可以通过 show global variables like '%secure%'; 查看mysql是否开启了此选项，默认值Null标识不允许执行导入导出命令。通过 vim /etc/my.cnf （Windows修改 my.ini ）修改mysql配置项，将 secure_file_priv 的值设置为空：

1
2
[mysqld]
  secure_file_priv=''

在导入数据的时候，可以通过设置字段名来匹配目标字段的数据，可以通过@dummy丢弃掉不需要的目标字段数据。

1
load data infile '/data/fans.txt' into table wxa_fans(id,appid,openid,unionid,@dummy,created_at,@dummy,nickname,gender,avatar_url,@dummy,@dummy,@dummy,@dummy,language,country,province,city,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy);

参考：

🔗 Mysql百万级数据迁移实战笔记

🔗 数据库迁移：概念和原则（第 1 部分）

相关文章