data owner to mysql_利用MySQL Load data实现从Oracle到MySQL的数据迁移

1、提出背景

我们部门刚好有个项目，需要将现网Oracle的10T的数据迁移到MySQL集群上，由于数据量比较多，且甲方要求迁移的时间比较短，所以我们需要找到一个好的方案，既要保证迁移数据结果的准确性，同时需要保证迁移过程的高效性、可控性。因此，结合之前在Oracle上的经验，提出新的方案：

1)、在Oracle的Spool命令，将数据导出到TXT文件

2)、利用MySQL Load data命令，将TXT文件导入到MySQL

2、什么是MySQL Load data

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE

'file_name.txt'

[REPLACE | IGNORE]

INTO TABLE tbl_name

[FIELDS

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY

'char']

[ESCAPED BY 'char' ]

]

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

[IGNORE number LINES]

[(col_name_or_user_var,...)]

[SET col_name =

expr,...)]

LOAD DATA

INFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为一个文字字符串。

典型例子：

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE

tbl_name

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LINES

TERMINATED BY '\n'

3、MySQL Load

data怎么操作

--3.1、在plsql development

的命令行窗口执行。输出到f:\prod_inst_fz.txt

set echo off

--在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句

set feedback off

--是否显示当前sql语句查询或修改的行数

set newpage none --会在页和页之间没有任何间隔

set verify off

--

set pagesize 0 --分多少页

set term off

--在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，设置set termspool

off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度。

set trims on

--将SPOOL输出中每行后面多余的空格去掉

set linesize 20000 --设置屏幕显示行宽，默认100

set heading

off --禁止输出列标题

set timing off --默认为OFF，设置查询耗时，可用来估计SQL语句的执行时间，测试性能

set

numwidth 38

SPOOL f:\prod_inst_fz.txt

select '"'||prod_inst_id||'","'||

product_id||'","'||

acc_prod_inst_id||'","'||

address_id||'","'||

owner_cust_id||'","'||

payment_mode_cd||'","'||

product_password||'","'||

important_level||'","'||

area_code||'","'||

acc_nbr||'","'||

exch_id||'","'||

common_region_id||'","'||

replace(replace(remark,chr(10),''),chr(13),'')||'","'||

pay_cycle||'","'||

to_char(begin_rent_time,'yyyy-mm-dd

hh24:mi:ss')||'","'||

to_char(stop_rent_time,'yyyy-mm-dd

hh24:mi:ss')||'","'||

to_char(finish_time,'yyyy-mm-dd hh24:mi:ss')||'","'||

stop_status||'","'||

status_cd||'","'||

to_char(create_date,'yyyy-mm-dd hh24:mi:ss')||'","'||

to_char(status_date,'yyyy-mm-dd hh24:mi:ss')||'","'||

to_char(update_date,'yyyy-mm-dd hh24:mi:ss')||'","'||

proc_serial||'","'||

use_cust_id||'","'||

ext_prod_inst_id||'","'||

replace(replace(address_desc,chr(10),''),chr(13),'')||'","'||

area_id||'","'||

update_staff||'","'||

create_staff||'","'||

to_char(rec_update_date,'yyyy-mm-dd hh24:mi:ss')||'","'||

account||'","'||

version||'","'||

community_id||'","'||

ext_acc_prod_inst_id||'","'||

area_id||'","'||

distributor_id||'","'||

owner_cust_id||'"'

FROM TAB_PROD_INST

WHERE rownum<=6;

SPOOL

OFF

--3.2、将f:\prod_inst_fz.txt的文件用户ftp工具上传到服务器上的/home/easydb/prod_inst_fz.txt

QQ图片20151113103241.png (16.42 KB, 下载次数:

0)

prod_inst_fz.txt截图

半小时前 上传

--3.3、需要用\N替换""空值，如果不替换，导入会失败

cd /home/easydb/

sed -i "s/\"\"/\\\N/g"

prod_inst_fz.txt

--3.4、用crmds用户登录mysql,进去zzy_test的schema，用load

data

导数据到prod_inst。由于Oracle的字符集是gbk，所以导入的时候需要特别指定字符集。在windows下导出的文件中换行用\r\n表达,在linux下导出的文件中换行用\n表达。

[easydb@crmquery01

~]$ mysql -ucrmds  -S /tmp/mariadb3305.sock -p --local-infile=1 --show-warnings

-v -v -v

MariaDB [(none)]> use zzy_test;

MariaDB [(none)]> load data

infile "/home/easydb/prod_inst_fz.txt" ignore  into table prod_inst  character

set gbk  fields terminated by ',' enclosed by '"' LINES TERMINATED BY

'\r\n';

Query OK, 101 rows affected (0.01 sec)

Records:

101  Deleted: 0  Skipped: 0  Warnings:

0

warning的个数为0表示全部数据导入成功，否则表示部分数据导入异常，需要手工介入处理。