首页 > 密码编程 >正文

yugong之多张表oracle到mysql迁移

关于yugong的详细配置参考上文:

http://blog.itpub.net/27000195/viewspace-2072904/

测试环境

源库

目标库

数据库类型

ORACLE

MYSQL

用户

test

test

密码

test

test

URL

jdbc:oracle:thin:@127.0.0.1:1521:test

jdbc:mysql://127.0.0.1:3306/test

表名称

yugong_example_a

yugong_example_mysql_a

yugong_example_b

yugong_example_mysql_b

yugong_example_c

yugong_example_mysql_c

使用三张表同步

创建表和数据

Oracle

create table yugong_example_a(

id NUMBER(11)  ,

name varchar2(32) ,

alias_name  char(32) default ' ' not null,

amount number(11,2),

score  number(20),

text_b blob,

text_c clob,

gmt_create date not null,

gmt_modified date not null,

CONSTRAINT yugong_example_oracle_pk_id  PRIMARY   KEY (id)

)tablespace YGIS_DATA;

insert into yugong_example_a values(1,'ljh','agapple',10.2,100, NULL , NULL ,sysdate,sysdate);

insert into yugong_example_a values(2,'yugong','yugong',16.88,2088, NULL , NULL ,sysdate,sysdate);

commit;

 

create table yugong_example_b

(  AA  VARCHAR2(60) not null,

  BB  VARCHAR2(60) not null

)tablespace YGIS_DATA;

create unique index IDX_CODE_DICT on yugong_example_b (AA, BB);

insert into yugong_example_b values ('YUJX', '1');

insert into yugong_example_b values('beijing','2');

insert into yugong_example_b values ('oracle', '3');

commit;

 

create table yugong_example_c

(  ID  VARCHAR2(60) not null,

  X  VARCHAR2(60) not null,

Y number

)tablespace YGIS_DATA;

create unique index IDX_C_ID on yugong_example_c (ID);

insert into yugong_example_c values ('c2f1b1dbbf1f4f0f897c332ca394db54','',1);

insert into yugong_example_c values ('7d53f0e673104f5fb33d5ab232155b4e','',100);

insert into yugong_example_c values ('fb767295d6a2448d94c3485f065c97fa','',1000);

commit;

目标mysql

create table yugong_example_mysql_a

 (   id bigint(20) unsigned auto_increment,

     display_name varchar(128) ,

     amount varchar(32),

     score bigint(20) unsigned ,

     text_b blob,

     text_c text,

     gmt_create timestamp not null,

     gmt_modified timestamp not null,

     gmt_move timestamp not null,

     CONSTRAINT yugong_example_mysql_pk_id  PRIMARY KEY (id)

 );

 

create table yugong_example_mysql_b

(

AA  VARCHAR(60) not null,

BB  INT not null

);

create unique index IDX_CODE_DICT on yugong_example_mysql_b (AA, BB);

 

create table yugong_example_mysql_c

(

ID VARCHAR(60) not null,

A VARCHAR(60) not null

);

create unique index IDX_C_ID on yugong_example_mysql_c (ID);

配置yugong

配置属性文件

参考实验一,只需把迁移表的白名单改成如下:

]# grep white conf/yugong.properties 

#yugong.table.white=yugong_example_join,yugong_example_oracle,yugong_example_two

yugong.table.white=yugong_example_a,yugong_example_b,yugong_example_c

配置数据转换逻辑

分析3张表的不同,如下:

表名称

不同项

yugong_example_a

1. table名不同. oracle中为yugong_example_amysql中为yugong_example_mysql_a

2. 字段名字不同. oracle中的name字段,映射到mysqldisplay_name

3. 字段逻辑处理. mysqldisplay_name字段数据来源为oracle库的:name+'('alias_name+')'

4. 字段类型不同. oracle中的amountnumber类型,映射到mysqlamountvarchar文本型

5. 源库多一个字段. oracle中多了一个alias_name字段

6. 目标库多了一个字段.mysql中多了一个gmt_move字段,(简单的用迁移时的当前时间进行填充)

yugong_example_b

1. table名不同. oracle中为yugong_example_bmysql中为yugong_example_mysql_b

2. 字段类型不同. oracle中的BBvarchar2,映射到mysqlBBINT

yugong_example_c

1. table名不同. oracle中为yugong_example_cmysql中为yugong_example_mysql_c

2. 字段名字不同. oracle中的X字段,映射到mysqlA字段

3. 源库多一个字段.oracle中多了一个Y字段

根据如上不同,配置对应的DataTranslator

YugongExampleADataTranslator.java

此表就是实验1使用的,此处省略

参考:http://blog.itpub.net/27000195/viewspace-2072904/

YugongExampleBDataTranslator.java

]# vi conf/translator/YugongExampleBDataTranslator.java

package com.taobao.yugong.translator;

 

import java.sql.Types;

import java.util.Date;

 

import org.apache.commons.lang.ObjectUtils;

 

import com.taobao.yugong.common.db.meta.ColumnMeta;

import com.taobao.yugong.common.db.meta.ColumnValue;

import com.taobao.yugong.common.model.record.Record;

 

public class YugongExampleBDataTranslator extends AbstractDataTranslator implements DataTranslator {

 

    public boolean translator(Record record) {

        // 1. schema/table名不同

        // record.setSchemaName("test");

        record.setTableName("yugong_example_mysql_b");

 

        // 2. 字段类型不同:源端varchar到目标端int不用转换

 

        return super.translator(record);

    }

}

YugongExampleCDataTranslator.java

]# vi conf/translator/YugongExampleCDataTranslator.java

package com.taobao.yugong.translator;

 

import java.sql.Types;

import java.util.Date;

 

import org.apache.commons.lang.ObjectUtils;

 

import com.taobao.yugong.common.db.meta.ColumnMeta;

import com.taobao.yugong.common.db.meta.ColumnValue;

import com.taobao.yugong.common.model.record.Record;

 

public class YugongExampleCDataTranslator extends AbstractDataTranslator implements DataTranslator {

 

    public boolean translator(Record record) {

        // 1. schema/table名不同

        // record.setSchemaName("test");

        record.setTableName("yugong_example_mysql_c");

 

        // 2. 字段名字不同

        ColumnValue nameColumn = record.getColumnByName("x");

        if (nameColumn != null) {

            nameColumn.getColumn().setName("a");

        }

 

        //3.源库多一个字段

        record.removeColumnByName("y");

 

        return super.translator(record);

    }

}

]# cd /data/yugong

]# bin/startup.sh

启动yugong

]# cd /data/yugong

]# bin/startup.sh

查看Log

Yugonglog

]# tail -f logs/yugong/table.log

2016-03-28 15:15:09.303 [main] INFO  com.taobao.yugong.YuGongLauncher - ## start the YuGong.

2016-03-28 15:15:09.389 [main] INFO  com.taobao.yugong.controller.YuGongController - check source database connection ...

2016-03-28 15:15:09.416 [main] INFO  com.taobao.yugong.controller.YuGongController - check source database is ok

2016-03-28 15:15:09.416 [main] INFO  com.taobao.yugong.controller.YuGongController - check target database connection ...

2016-03-28 15:15:09.435 [main] INFO  com.taobao.yugong.controller.YuGongController - check target database is ok

2016-03-28 15:15:09.437 [main] INFO  com.taobao.yugong.controller.YuGongController - check source tables read privileges ...

2016-03-28 15:15:09.561 [main] INFO  com.alibaba.druid.pool.DruidDataSource - {dataSource-1} inited

2016-03-28 15:15:09.971 [main] INFO  com.taobao.yugong.controller.YuGongController - check source tables is ok.

2016-03-28 15:15:10.676 [main] INFO  com.taobao.yugong.controller.YuGongController - ## prepare start tables[3] with concurrent[5]

2016-03-28 15:15:10.990 [YuGongInstance-TEST.YUGONG_EXAMPLE_A] INFO  com.taobao.yugong.controller.YuGongInstance - table[TEST.YUGONG_EXAMPLE_A] is start

2016-03-28 15:15:11.032 [YuGongInstance-TEST.YUGONG_EXAMPLE_B] INFO  com.taobao.yugong.controller.YuGongInstance - table[TEST.YUGONG_EXAMPLE_B] is start

2016-03-28 15:15:11.074 [main] INFO  com.taobao.yugong.YuGongLauncher - ## the YuGong is running now ......

2016-03-28 15:15:11.075 [YuGongInstance-TEST.YUGONG_EXAMPLE_C] INFO  com.taobao.yugong.controller.YuGongInstance - table[TEST.YUGONG_EXAMPLE_C] is start

2016-03-28 15:15:11.078 [main] INFO  com.taobao.yugong.YuGongLauncher -

[YuGong Version Info]

[version ]

[hexVeision]

[date    ]2016-03-05 02:02:14

[branch  ]master

[url     ]git@github.com:alibaba/yugong.git

 

2016-03-28 15:16:10.682 [pool-2-thread-1] INFO  com.taobao.yugong.common.stats.ProgressTracer - {未启动:0,全量中:0,增量中:0,已追上:3,异常数:0}

2016-03-28 15:16:10.683 [pool-2-thread-1] INFO  com.taobao.yugong.common.stats.ProgressTracer - 已完成:[TEST.YUGONG_EXAMPLE_C, TEST.YUGONG_EXAMPLE_A, TEST.YUGONG_EXAMPLE_B]

YUGONG_EXAMPLE_A同步log

~]# tail -f /data/yugong/logs/TEST.YUGONG_EXAMPLE_A/table.log

]# more /data/yugong/logs/TEST.YUGONG_EXAMPLE_A/table.log       

2016-03-28 15:15:10.730 [main] INFO  c.t.yugong.extractor.oracle.OracleRecRecordExtractor - create mlog successed. sql : CREATE MATERIALIZED VIEW LOG ON TEST.YUGONG_EXAMPLE_A with  primary key

2016-03-28 15:15:10.743 [main] INFO  c.t.y.e.o.OracleFullRecordExtractor$ContinueExtractor - TEST.YUGONG_EXAMPLE_A start postion:0

2016-03-28 15:15:10.746 [main] INFO  com.alibaba.druid.pool.DruidDataSource - {dataSource-2} inited

2016-03-28 15:15:10.990 [main] INFO  com.taobao.yugong.controller.YuGongInstance - table[TEST.YUGONG_EXAMPLE_A] start successful. extractor:com.taobao.yugong.extractor.oracle.OracleAllRecordEx

tractor , applier:com.taobao.yugong.applier.AllRecordApplier

2016-03-28 15:15:11.070 [YuGongInstance-TEST.YUGONG_EXAMPLE_A] INFO  c.t.yugong.extractor.oracle.OracleAllRecordExtractor - table [TEST.YUGONG_EXAMPLE_A] full extractor is end , next auto star

t inc extractor

2016-03-28 15:15:11.178 [YuGongInstance-TEST.YUGONG_EXAMPLE_A] INFO  c.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_A] now is NO_UPDATE ...

YUGONG_EXAMPLE_B同步log

~]# tail -f /data/yugong/logs/TEST.YUGONG_EXAMPLE_B/table.log         

2016-03-28 15:15:11.019 [main] INFO  c.t.yugong.extractor.oracle.OracleRecRecordExtractor - create mlog successed. sql : CREATE MATERIALIZED VIEW LOG ON TEST.YUGONG_EXAMPLE_B with  primary key

2016-03-28 15:15:11.031 [main] INFO  com.taobao.yugong.controller.YuGongInstance - table[TEST.YUGONG_EXAMPLE_B] start successful. extractor:com.taobao.yugong.extractor.oracle.OracleAllRecordEx

tractor , applier:com.taobao.yugong.applier.AllRecordApplier

2016-03-28 15:15:11.070 [YuGongInstance-TEST.YUGONG_EXAMPLE_B] INFO  c.t.yugong.extractor.oracle.OracleAllRecordExtractor - table [TEST.YUGONG_EXAMPLE_B] full extractor is end , next auto star

t inc extractor

2016-03-28 15:15:11.195 [YuGongInstance-TEST.YUGONG_EXAMPLE_B] INFO  c.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_B] now is NO_UPDATE ...

2016-03-28 15:15:12.198 [YuGongInstance-TEST.YUGONG_EXAMPLE_B] INFO  c.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_B] now is NO_UPDATE ...

YUGONG_EXAMPLE_C同步log

]# tail -f  /data/yugong/logs/TEST.YUGONG_EXAMPLE_C/table.log              

2016-03-28 15:15:11.067 [main] INFO  c.t.yugong.extractor.oracle.OracleRecRecordExtractor - create mlog successed. sql : CREATE MATERIALIZED VIEW LOG ON TEST.YUGONG_EXAMPLE_C with  primary key

2016-03-28 15:15:11.074 [main] INFO  com.taobao.yugong.controller.YuGongInstance - table[TEST.YUGONG_EXAMPLE_C] start successful. extractor:com.taobao.yugong.extractor.oracle.OracleAllRecordEx

tractor , applier:com.taobao.yugong.applier.AllRecordApplier

2016-03-28 15:15:11.549 [YuGongInstance-TEST.YUGONG_EXAMPLE_C] INFO  c.t.yugong.extractor.oracle.OracleAllRecordExtractor - table [TEST.YUGONG_EXAMPLE_C] full extractor is end , next auto star

t inc extractor

2016-03-28 15:15:11.718 [YuGongInstance-TEST.YUGONG_EXAMPLE_C] INFO  c.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_C] now is NO_UPDATE ...

2016-03-28 15:15:12.721 [YuGongInstance-TEST.YUGONG_EXAMPLE_C] INFO  c.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_C] now is NO_UPDATE ...

log可看出 三张表的全量同步都已经完成,等待增量同步

查看目标端结果

yugong之多张表oracle到mysql迁移

yugong之多张表oracle到mysql迁移

yugong之多张表oracle到mysql迁移

至此,全量同步都正常

源端oracle执行增量

YUGONG_EXAMPLE_A

源端oracle执行

insert into yugong_example_a values(3,'test','test',88,188, NULL , NULL ,sysdate,sysdate);

update yugong_example_a set alias_name = 'superman' where id = 1;

commit;

表同步log

2016-03-28 15:26:54.187 [YuGongInstance-TEST.YUGONG_EXAMPLE_A] INFO  c.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_A] now is NO_UPDATE ...

2016-03-28 15:26:55.191 [YuGongInstance-TEST.YUGONG_EXAMPLE_A] INFO  c.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_A] now is CATCH_UP ...

2016-03-28 15:26:55.243 [YuGongInstance-TEST.YUGONG_EXAMPLE_A] INFO  c.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_A] now is NO_UPDATE ...

目标端查看结果

yugong之多张表oracle到mysql迁移

正常增量同步

YUGONG_EXAMPLE_B

源端oracle执行

insert into yugong_example_b values ('oracle', '4');

commit;

表同步log

2016-03-28 15:31:20.036 [YuGongInstance-TEST.YUGONG_EXAMPLE_B] INFO  c.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_B] now is NO_UPDATE ...

2016-03-28 15:31:21.038 [YuGongInstance-TEST.YUGONG_EXAMPLE_B] INFO  c.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_B] now is CATCH_UP ...

2016-03-28 15:31:21.058 [YuGongInstance-TEST.YUGONG_EXAMPLE_B] INFO  c.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_B] now is NO_UPDATE ...

目标端查看结果

yugong之多张表oracle到mysql迁移

正常增量

YUGONG_EXAMPLE_C

源端oracle执行

insert into yugong_example_c values ('fb767295d6a2448d94c3485f065c97fc','',10000);

insert into yugong_example_c values ('fb767295d6a2448d94c3485f065csdad','',2);

insert into yugong_example_c values ('fb767295d6a2448d94c3485f065dsadd','',45);

commit;

表同步log

2016-03-28 15:35:26.796 [YuGongInstance-TEST.YUGONG_EXAMPLE_C] INFO  c.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_C] now is NO_UPDATE ...

2016-03-28 15:35:27.798 [YuGongInstance-TEST.YUGONG_EXAMPLE_C] INFO  c.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_C] now is CATCH_UP ...

2016-03-28 15:35:27.822 [YuGongInstance-TEST.YUGONG_EXAMPLE_C] INFO  c.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_C] now is NO_UPDATE ...

目标端查看结果

yugong之多张表oracle到mysql迁移

正常增量