要对愚公二开,所以先做一个测试,将愚公跑通,之前一直比较忙,今天将日志补上,以防遗忘
单张表从orale到mysql的迁移,源端oracle和目标端mysql的表结构不完全相同
项
项 | 源库 | 目标库 |
---|---|---|
数据库类型 | ORACLE | MYSQL |
用户 | user1 | root |
密码 | 123456 | root |
URL | jdbc:oracle:thin:@127.0.0.1:1521:user1 | jdbc:mysql://127.0.0.1:3306/root |
表名称 | yugong_example_oracle | yugong_example_mysql_mysql |
创建user1用户 直接授权dba
原因:oracle全量基于JDBC拉取数据,增量基于物化视图来实现,所以这里需要给oracle数据库账号开权限
create table yugong_example_oracle(
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)
);
插入2条测试数据
insert into yugong_example_oracle values(1,'herche','herche',11.2,100, NULL , NULL ,sysdate,sysdate);
insert into yugong_example_oracle values(2,'naraku','naraku',20.33,8888, NULL , NULL ,sysdate,sysdate);
commit;
create table yugong_example_mysql
( 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)
);
拉取愚公代码之后改配置
#源库配置
yugong.database.source.username=user1
yugong.database.source.password=123456
yugong.database.source.type=ORACLE
yugong.database.source.url=jdbc:oracle:thin:@127.0.0.1:1521:user1
yugong.database.source.encode=UTF-8
yugong.database.source.poolSize=30
#目标库配置
yugong.database.target.url=jdbc:mysql://127.0.0.1:3306/demo01
yugong.database.target.username=root
yugong.database.target.password=root
yugong.database.target.type=MYSQL
yugong.database.target.encode=UTF-8
yugong.database.target.poolSize=30
yugong.table.batchApply=true
yugong.table.onceCrawNum=1000
yugong.table.tpsLimit=0
# use connection default schema
yugong.table.ignoreSchema=false
# skip Applier Load Db failed data
yugong.table.skipApplierException=false
#需要同步表的黑白名单:表可加逗号分隔,白名单为空,代表整个库所有表
yugong.table.white= yugong_example_oracle
yugong.table.black=
# tables use multi-thread enable or disable
yugong.table.concurrent.enable=true
# tables use multi-thread size
yugong.table.concurrent.size=5
# retry times
yugong.table.retry.times = 3
# retry interval or sleep time (ms)
yugong.table.retry.interval = 1000
# MARK/FULL/INC/ALL(REC+FULL+INC)/CHECK/CLEAR
yugong.table.mode=ALL # (自动全量+增量模式)
# yugong extractor
yugong.extractor.dump=false
yugong.extractor.concurrent.enable=true
yugong.extractor.concurrent.size=20
yugong.extractor.noupdate.sleep=1000
yugong.extractor.noupdate.thresold=0
yugong.extractor.once=false
# {0} is all columns , {1}.{2} is schemaName.tableName , {3} is primaryKey
#yugong.extractor.sql=select /*+parallel(t)*/ {0} from {1}.{2} t
#yugong.extractor.sql=select * from (select {0} from {1}.{2} t where {3} > ? order by {3} asc) where rownum <= ?
# yugong applier
yugong.applier.concurrent.enable=true
yugong.applier.concurrent.size=20
yugong.applier.dump=false
# stats
yugong.stat.print.interval=5
yugong.progress.print.interval=1
# alarm email
yugong.alarm.email.host = smtp.163.com
yugong.alarm.email.username = test@163.com
yugong.alarm.email.password =
yugong.alarm.email.stmp.port = 465
上文准备的测试环境的源端oracle的表yugong_example_oracle和目标端mysql的表yugong_example_mysql有如下不同的地方:
table名不同. oracle中为yugong_example_oracle,mysql中为yugong_example_mysql
字段名字不同. oracle中的name字段,映射到mysql的display_name
字段逻辑处理. mysql的display_name字段数据来源为oracle库的:name+’(‘alias_name+’)’
字段类型不同. oracle中的amount为number类型,映射到mysql的amount为varchar文本型
源库多一个字段. oracle中多了一个alias_name字段
目标库多了一个字段.mysql中多了一个gmt_move字段,(简单的用迁移时的当前时间进行填充)
故,需要根据不同的地方定义DataTranslator.java,下文的YugongExampleOrackeDataTranslator.java中配置这里的6项不同之处
public class YugongExampleOracleDataTranslator extends AbstractDataTranslator implements DataTranslator {
public boolean translator(Record record) {
// 1. schema/table名不同
record.setTableName("yugong_example_mysql");
//至此可知道源表为yugong_example_oracle,目标表为yugong_example_mysql
// 2. 字段名字不同,
//源表的name字段,目标表中叫display_name
ColumnValue nameColumn = record.getColumnByName("name");
if (nameColumn != null) {
nameColumn.getColumn().setName("display_name");
}
// 3. 字段逻辑处理
ColumnValue aliasNameColumn = record.getColumnByName("alias_name");
if (aliasNameColumn != null) {
StringBuilder displayNameValue = new StringBuilder(64);
displayNameValue.append(ObjectUtils.toString(nameColumn.getValue()))
.append('(')
.append(ObjectUtils.toString(aliasNameColumn.getValue()))
.append(')');
nameColumn.setValue(displayNameValue.toString());
}
// 4. 字段类型不同
ColumnValue amountColumn = record.getColumnByName("amount");
if (amountColumn != null) {
amountColumn.getColumn().setType(Types.VARCHAR);
amountColumn.setValue(ObjectUtils.toString(amountColumn.getValue()));
}
// 5. 源库多一个字段
record.removeColumnByName("alias_name");
// 6. 目标库多了一个字段
ColumnMeta gmtMoveMeta = new ColumnMeta("gmt_move", Types.TIMESTAMP);
ColumnValue gmtMoveColumn = new ColumnValue(gmtMoveMeta, new Date());
gmtMoveColumn.setCheck(false);// 该字段不做对比
record.addColumn(gmtMoveColumn);
return super.translator(record);
}
}
以增量形式启动即可。
创建物化视图日志:
create materialized view log on user1."yugong_example_oracle" with SEQUENCE including new values