关于yugong的详细配置参考上文:
测试环境
项
源库
目标库
数据库类型
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端
createtableyugong_example_a(
idNUMBER(11),
namevarchar2(32),
alias_namechar(32)default' 'notnull,
amountnumber(11,2),
scorenumber(20),
text_bblob,
text_cclob,
gmt_createdatenotnull,
gmt_modifieddatenotnull,
CONSTRAINTyugong_example_oracle_pk_idPRIMARYKEY(id)
)tablespaceYGIS_DATA;
insertintoyugong_example_avalues(1,'ljh','agapple',10.2,100,NULL,NULL,sysdate,sysdate);
insertintoyugong_example_avalues(2,'yugong','yugong',16.88,2088,NULL,NULL,sysdate,sysdate);
commit;
createtableyugong_example_b
(AAVARCHAR2(60)notnull,
BBVARCHAR2(60)notnull
)tablespaceYGIS_DATA;
createuniqueindexIDX_CODE_DICTonyugong_example_b(AA,BB);
insertintoyugong_example_bvalues('YUJX','1');
insertintoyugong_example_bvalues('beijing','2');
insertintoyugong_example_bvalues('oracle','3');
commit;
createtableyugong_example_c
(IDVARCHAR2(60)notnull,
XVARCHAR2(60)notnull,
Ynumber
)tablespaceYGIS_DATA;
createuniqueindexIDX_C_IDonyugong_example_c(ID);
insertintoyugong_example_cvalues('c2f1b1dbbf1f4f0f897c332ca394db54','愚',1);
insertintoyugong_example_cvalues('7d53f0e673104f5fb33d5ab232155b4e','公',100);
insertintoyugong_example_cvalues('fb767295d6a2448d94c3485f065c97fa','移',1000);
commit;
目标mysql端
createtableyugong_example_mysql_a
(idbigint(20)unsigned auto_increment,
display_namevarchar(128),
amountvarchar(32),
score bigint(20)unsigned,
text_bblob,
text_c text,
gmt_createtimestampnotnull,
gmt_modifiedtimestampnotnull,
gmt_movetimestampnotnull,
CONSTRAINTyugong_example_mysql_pk_idPRIMARYKEY(id)
);
createtableyugong_example_mysql_b
(
AAVARCHAR(60)notnull,
BBINTnotnull
);
createuniqueindexIDX_CODE_DICTonyugong_example_mysql_b(AA,BB);
createtableyugong_example_mysql_c
(
IDVARCHAR(60)notnull,
AVARCHAR(60)notnull
);
createuniqueindexIDX_C_IDonyugong_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_a,mysql中为yugong_example_mysql_a
2.字段名字不同. oracle中的name字段,映射到mysql的display_name
3.字段逻辑处理. mysql的display_name字段数据来源为oracle库的:name+'('alias_name+')'
4.字段类型不同. oracle中的amount为number类型,映射到mysql的amount为varchar文本型
5.源库多一个字段. oracle中多了一个alias_name字段
6.目标库多了一个字段.mysql中多了一个gmt_move字段,(简单的用迁移时的当前时间进行填充)
yugong_example_b
1. table名不同. oracle中为yugong_example_b,mysql中为yugong_example_mysql_b
2.字段类型不同. oracle中的BB为varchar2,映射到mysql的BB为INT
yugong_example_c
1. table名不同. oracle中为yugong_example_c,mysql中为yugong_example_mysql_c
2.字段名字不同. oracle中的X字段,映射到mysql的A字段
3.源库多一个字段.oracle中多了一个Y字段
根据如上不同,配置对应的DataTranslator
YugongExampleADataTranslator.java
此表就是实验1使用的,此处省略
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
Yugong主log
]# tail -f logs/yugong/table.log
2016-03-28 15:15:09.303 [main] INFOcom.taobao.yugong.YuGongLauncher - ## start the YuGong.
2016-03-28 15:15:09.389 [main] INFOcom.taobao.yugong.controller.YuGongController - check source database connection ...
2016-03-28 15:15:09.416 [main] INFOcom.taobao.yugong.controller.YuGongController - check source database is ok
2016-03-28 15:15:09.416 [main] INFOcom.taobao.yugong.controller.YuGongController - check target database connection ...
2016-03-28 15:15:09.435 [main] INFOcom.taobao.yugong.controller.YuGongController - check target database is ok
2016-03-28 15:15:09.437 [main] INFOcom.taobao.yugong.controller.YuGongController - check source tables read privileges ...
2016-03-28 15:15:09.561 [main] INFOcom.alibaba.druid.pool.DruidDataSource - {dataSource-1} inited
2016-03-28 15:15:09.971 [main] INFOcom.taobao.yugong.controller.YuGongController - check source tables is ok.
2016-03-28 15:15:10.676 [main] INFOcom.taobao.yugong.controller.YuGongController - ## prepare start tables[3] with concurrent[5]
2016-03-28 15:15:10.990 [YuGongInstance-TEST.YUGONG_EXAMPLE_A] INFOcom.taobao.yugong.controller.YuGongInstance - table[TEST.YUGONG_EXAMPLE_A] is start
2016-03-28 15:15:11.032 [YuGongInstance-TEST.YUGONG_EXAMPLE_B] INFOcom.taobao.yugong.controller.YuGongInstance - table[TEST.YUGONG_EXAMPLE_B] is start
2016-03-28 15:15:11.074 [main] INFOcom.taobao.yugong.YuGongLauncher - ## the YuGong is running now ......
2016-03-28 15:15:11.075 [YuGongInstance-TEST.YUGONG_EXAMPLE_C] INFOcom.taobao.yugong.controller.YuGongInstance - table[TEST.YUGONG_EXAMPLE_C] is start
2016-03-28 15:15:11.078 [main] INFOcom.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] INFOcom.taobao.yugong.common.stats.ProgressTracer - {未启动:0,全量中:0,增量中:0,已追上:3,异常数:0}
2016-03-28 15:16:10.683 [pool-2-thread-1] INFOcom.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] INFOc.t.yugong.extractor.oracle.OracleRecRecordExtractor - create mlog successed. sql : CREATE MATERIALIZED VIEW LOG ON TEST.YUGONG_EXAMPLE_A withprimary key
2016-03-28 15:15:10.743 [main] INFOc.t.y.e.o.OracleFullRecordExtractor$ContinueExtractor - TEST.YUGONG_EXAMPLE_A start postion:0
2016-03-28 15:15:10.746 [main] INFOcom.alibaba.druid.pool.DruidDataSource - {dataSource-2} inited
2016-03-28 15:15:10.990 [main] INFOcom.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] INFOc.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] INFOc.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] INFOc.t.yugong.extractor.oracle.OracleRecRecordExtractor - create mlog successed. sql : CREATE MATERIALIZED VIEW LOG ON TEST.YUGONG_EXAMPLE_B withprimary key
2016-03-28 15:15:11.031 [main] INFOcom.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] INFOc.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] INFOc.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] INFOc.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] INFOc.t.yugong.extractor.oracle.OracleRecRecordExtractor - create mlog successed. sql : CREATE MATERIALIZED VIEW LOG ON TEST.YUGONG_EXAMPLE_C withprimary key
2016-03-28 15:15:11.074 [main] INFOcom.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] INFOc.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] INFOc.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] INFOc.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_C] now is NO_UPDATE ...
由log可看出 三张表的全量同步都已经完成,等待增量同步
查看目标端结果
至此,全量同步都正常
源端oracle执行增量
YUGONG_EXAMPLE_A表
源端oracle执行
insertintoyugong_example_avalues(3,'test','test',88,188,NULL,NULL,sysdate,sysdate);
updateyugong_example_asetalias_name='superman'whereid=1;
commit;
表同步log
2016-03-28 15:26:54.187 [YuGongInstance-TEST.YUGONG_EXAMPLE_A] INFOc.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] INFOc.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] INFOc.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_A] now is NO_UPDATE ...
目标端查看结果
正常增量同步
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] INFOc.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] INFOc.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] INFOc.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_B] now is NO_UPDATE ...
目标端查看结果
正常增量
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] INFOc.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] INFOc.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] INFOc.t.y.e.oracle.OracleMaterializedIncRecordExtractor - table[TEST.YUGONG_EXAMPLE_C] now is NO_UPDATE ...
目标端查看结果
正常增量