alswl / yugong Goto Github PK

View Code? Open in Web Editor NEW

This project forked from alibaba/yugong

120.0 5.0 35.0 2.64 MB

阿里巴巴去Oracle数据迁移同步工具(全量+增量,目标支持MySQL/DRDS)，本 Fork 版本支持 MSSQL / MySQL 作为源数据库，并支持全量增量两种模式。

License: GNU General Public License v2.0

Shell 0.40% Java 99.60%

yugong's Introduction

yugong

[TOC]

目的

SQL Server -> MySQL 的一致性检查（CHECK）
MySQL -> SQL Server 的回滚（SYNC）

获得 yugong jar 包

方法一：编译 yugong jar 包：

git [email protected]:hjarch-practice/yugong.git
cd yugong
mvn clean package
cp target/yugong-shaded.jar .

方法二：不想编译的同学，直接点击 Tags · HJArch-Internal / yugong · GitLab , 找到里面最新的版本，里面有 jar 包下载。

配置文件

有两个配置文件：

properties，配置数据库信息和作业信息
YAML 配置文件，做 Translator 定制化

修改配置文件，可以参考其他产线已经在用的配置： Files · master · HJArch-Internal / yugong-conf · GitLab

运行

HJ 使用的 yugong 已经改为 fat jar 模式运行，摒弃了官方的打包流程。将生成的 fat jar yugong-shaded.jar 拷贝到服务器，即可运行。

运行参数：

-c：使用的 yugong properties，配置数据库信息和作业信息
-y：使用的 YAML 配置文件，做 Translator 定制化

运行命令：

java -jar yugong-shaded.jar -c sync-mssql-mysql.properties -y mssql-mysql.yaml

PS：如果想优化运行速度，可以加入 JAVA_OPTIONS：

JAVA_OPTIONS=("-Xms2048m" "-Xmx3072m" "-Xmn1024m" "-XX:SurvivorRatio=2" "-XX:PermSize=96m" "-XX:MaxPermSize=256m" "-Xss256k" "-XX:-UseAdaptiveSizePolicy" "-XX:MaxTenuringThreshold=15" "-XX:+DisableExplicitGC" "-XX:+UseConcMarkSweepGC" "-XX:+CMSParallelRemarkEnabled" "-XX:+UseCMSCompactAtFullCollection" "-XX:+UseFastAccessorMethods" "-XX:+UseCMSInitiatingOccupancyOnly" "-XX:+HeapDumpOnOutOfMemoryError")
java -server $JAVA_OPTIONS -jar yugong-shaded.jar -c sync-mssql-mysql.properties -y mssql-mysql.yaml

运行之后的错误检查

所有错误日志在 logs 目录下面，范例如下：

logs
├── HJ_OpenPlatform.UserGroups  # 每张表的日志
│   ├── applier.log  # Applier 日志
│   ├── check.log  # 一致性检查日志
│   ├── extractor.log  # Extractor 日志
│   └── table.log  # 表操作日志
└── yugong
    └── table.log  # yugong 的系统日志

运行之后，需要重点观察 check.log 和 table.log 确保里面没有 ERROR 信息。

可以通过 grep -r ERROR logs 检查错误。

每次操作会记录进度，用来断点续接。想重新开始跑应用，需要删除当前目录下的 logs / positioner_data。

Quick Start

获取 yugong-shaded.jar 之后，做一下操作可以快速使用 yugong：

配置自己的配置文件
- 从 yugong-conf 里面拷贝 dict-dev/check-mssql-mysql.properties · master · HJArch-Internal / yugong-conf · GitLab
- 从 yugong-conf 里面拷贝 dict-dev/mssql-mysql.yaml · master · HJArch-Internal / yugong-conf · GitLab

修改 properties 里面的数据库配置：

yugong.database.source.url=
yugong.database.source.username=
yugong.database.source.password=
yugong.database.target.url=
yugong.database.target.username=
yugong.database.target.password=

修改 properties 里面的表配置，添加需要迁移的表（yugong 是白名单模式）：
```
yugong.table.white=
```
修改 properties 里面的运行模式，CHECK（检查模式），FULL（写入模式）：
```
yugong.table.mode=
```
运行

Tips

odjbc missing:

ould not resolve dependencies for project com.taobao.yugong:yugong:jar:1.2.0: The following artifacts could not be resolved: com.oracle:ojdbc14:jar:10.2.0.4.0, com.alibaba.otter:canal.client:jar:1.0.25-SNAPSHOT: Failure to find com.oracle:ojdbc14:jar:10.2.0.4.0 in http://maven.aliyun.com/nexus/content/groups/public/ was cached in the local repository, resolution will not be reattempted until the update interval of maven.aliyun.com has elapsed or updates are forced

open http://www.oracle.com/technetwork/apps-tech/jdbc-10201-088211.html
# download ojbc14_g.jar
mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc14 -Dversion=10.2.0.4.0 -Dpackaging=jar -Dfile=ojdbc14_g.jar -DgeneratePom=true

canal-client missing:

Could not resolve dependencies for project com.taobao.yugong:yugong:jar:1.2.0: Failure to find com.alibaba.otter:canal.client:jar:1.0.25-SNAPSHOT in https://oss.sonatype.org/content/repositories/snapshots was cached in the local repository, resolution will not be reattempted until the update interval of sonatype has elapsed or updates are forced

git clone https://github.com/alibaba/canal
cd canal
git checkout 2cebfa0
mvn clean install

需要帮助怎么办？

请联系作者 3D，CCTalk 找我「狄敬超」。

Contributors

Stargazers

Watchers

yugong's Issues

小数据量没有问题，大数据量，直接报错

2021-07-21 16:07:11.462 [YuGongInstance-AukeysOrder.OrderInfo] ERROR com.taobao.yugong.controller.YuGongInstance - retry 1 ,something error happened. caused by java.lang.RuntimeException: java.util.concurrent.ExecutionException: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 10000, active 30, maxActive 30
at com.taobao.yugong.common.utils.thread.ExecutorTemplate.waitForResult(ExecutorTemplate.java:79)
at com.taobao.yugong.applier.MultiThreadCheckRecordApplier.apply(MultiThreadCheckRecordApplier.java:97)
at com.taobao.yugong.controller.YuGongInstance$1.processTable(YuGongInstance.java:271)
at com.taobao.yugong.controller.YuGongInstance$1.run(YuGongInstance.java:201)
at java.lang.Thread.run(Unknown Source)
Caused by: java.util.concurrent.ExecutionException: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 10000, active 30, maxActive 30
at java.util.concurrent.FutureTask.report(Unknown Source)
at java.util.concurrent.FutureTask.get(Unknown Source)
at com.taobao.yugong.common.utils.thread.ExecutorTemplate.waitForResult(ExecutorTemplate.java:77)
... 4 more
Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 10000, active 30, maxActive 30
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:331)
at com.taobao.yugong.common.db.meta.TableMetaGenerator.queryColumns(TableMetaGenerator.java:294)
at com.taobao.yugong.common.db.meta.TableMetaGenerator.lambda$getTableMeta$0(TableMetaGenerator.java:86)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:342)
at com.taobao.yugong.common.db.meta.TableMetaGenerator.getTableMeta(TableMetaGenerator.java:50)
at com.taobao.yugong.applier.CheckRecordApplier.getSqlUnit(CheckRecordApplier.java:350)
at com.taobao.yugong.applier.CheckRecordApplier.queryByBatch(CheckRecordApplier.java:111)
at com.taobao.yugong.applier.CheckRecordApplier.doApply(CheckRecordApplier.java:93)
at com.taobao.yugong.applier.MultiThreadCheckRecordApplier$1.run(MultiThreadCheckRecordApplier.java:86)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
... 1 more
Caused by: com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 10000, active 30, maxActive 30
at com.alibaba.druid.pool.DruidDataSource.getConnectionInternal(DruidDataSource.java:1155)
at com.alibaba.druid.pool.DruidDataSource.getConnectionDirect(DruidDataSource.java:977)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:957)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:947)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:102)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:113)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79)
... 16 more

提供的地址下载不了

请问包下载不了是什么原因

gitlab配置文件链接失效了

请问能再发个新链接吗？

迁移到MySQL 8.0.15会有驱动问题

Caused by: java.sql.SQLException: Unknown system variable 'query_cache_size'

2019-05-08 18:01:13.305 [main] WARN c.alibaba.druid.pool.vendor.MySqlValidConnectionChecker - Cannot resolve com.mysq.jdbc.Connection.ping method. Will use 'SELECT 1' instead.

需要升级MySQL驱动包以及druid版本。

链接失效

配置链接失效

我启动的时候报unsupport SQL_SERVER错，帮忙看看

启动的时候就报这个错

我的配置文件
yugong.database.source.username=sa yugong.database.source.password=Aa123456 yugong.database.source.type=SQL_SERVER yugong.database.source.url=jdbc:sqlserver://127.0.0.1:1433;databaseName=test yugong.database.source.encode=UTF-8 yugong.database.source.poolSize=30 yugong.database.target.url=jdbc:mysql://127.0.0.1:3306/test?autoReconnect=true 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.mode=ALL

报的错
com.taobao.yugong.exception.YuGongException: unsupport SQL_SERVER at com.taobao.yugong.controller.YuGongController.chooseExtractor(YuGongController.java:407) at com.taobao.yugong.controller.YuGongController.chooseExtractor(YuGongController.java:411) at com.taobao.yugong.controller.YuGongController.start(YuGongController.java:206) at com.taobao.yugong.YugongApp.run(YugongApp.java:67) at com.taobao.yugong.YugongApp.main(YugongApp.java:55)

SqlServerIncrementRecordApplier没有在代码中使用吗？

请问哪个分支或者tag是最新可用的？是默认分支吗？

两个配置文件的样例能提供下嘛?链接打不开了!

现在在配置SQLserver到MySQL的数据同步，使用的sync-mssql-mysql.properties和mssql-mysql.yaml是从官方的yugong.properties.sample和yugong.yaml.sample修改而来，其中 yugong.database.source.type=SQLSERVER直接报错了:
java.lang.IllegalArgumentException: No enum constant com.taobao.yugong.common.model.DbType.SQLSERVE

sqlserver到mysql，表配置能读取到，目标表库配置读取不到

2020-03-17 09:27:53.663 [YuGongInstance-AdventureWorks.test123] ERROR com.taobao.yugong.common.alarm.LogAlarmService - Alarm:com.taobao.yugong.exception.YuGongException: table[null.test123] is not found
at com.taobao.yugong.common.db.meta.TableMetaGenerator.lambda$getTableMeta$0(TableMetaGenerator.java:83)
at com.taobao.yugong.common.db.meta.TableMetaGenerator$$Lambda$4/293508253.doInConnection(Unknown Source)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:342)
at com.taobao.yugong.common.db.meta.TableMetaGenerator.getTableMeta(TableMetaGenerator.java:50)
at com.taobao.yugong.applier.FullRecordApplier.getSqlUnit(FullRecordApplier.java:391)
at com.taobao.yugong.applier.FullRecordApplier.doApply(FullRecordApplier.java:82)
at com.taobao.yugong.applier.MultiThreadFullRecordApplier.apply(MultiThreadFullRecordApplier.java:108)
at com.taobao.yugong.controller.YuGongInstance$1.processTable(YuGongInstance.java:271)
at com.taobao.yugong.controller.YuGongInstance$1.run(YuGongInstance.java:201)
at java.lang.Thread.run(Unknown Source)
, Receiver:

有没有删字段或者加字段的配置方式？

有没有加字段删字段的配置方式？

sqlserver导入到mysql失败

用yugong做sqlserver数据库迁移到mysql的时候，部分表只能导过去部分数据，而且这些数据是随机的。大部分的表数据可以正常迁移过去。附上后台日志
log.txt

多键或键不是数字这个与单键同步有什么区别么？

/**

is only one primary key, and this key is number
*/
private boolean isOnlyPkIsNumber(Table table) {
if (table.getPrimaryKeys() != null && table.getPrimaryKeys().size() == 1) {
return YuGongUtils.isNumber(table.getPrimaryKeys().get(0).getType());
}

return false;

}

没有太明白，同步表的时候，为什么只支持到单主键，或者键必须是数值。这样有什么区别么？

gitlab的配置文件链接已经失效了

请问能再发个新链接吗？

从mysq 5.7.27 往sqlserver 2012同步数据启动报错，帮忙看下

h resource [org/springframework/jdbc/support/sql-error-codes.xml]
2020-01-19 11:13:22.914 [MultiThreadFullRecordApplier-SyncTestMysql.sysuser1] INFO org.springframework.jdbc.support.SQLErrorCodesFactory - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
2020-01-19 11:13:22.953 [YuGongInstance-SyncTestMysql.sysuser1] ERROR com.taobao.yugong.controller.YuGongInstance - retry 1 ,something error happened. caused by java.lang.RuntimeException: java.util.concurrent.ExecutionException: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SET IDENTITY_INSERT [SyncTest].dbo.[SysUser_copy1] ON;
MERGE [SyncTest].dbo.[SysUser_copy1] AS target
USING (values (?, ?, ?, ?, ?, ?, ?, ?)) AS source (UpdateTime, UpdatePerson, CreatTime, UserId, CreatPerson, UserSex, UserPassWord, UserName)
ON
WHEN MATCHED THEN
UPDATE SET UpdateTime = source.UpdateTime, UpdatePerson = source.UpdatePerson, CreatTime = source.CreatTime, UserId = source.UserId, CreatPerson = source.CreatPerson, UserSex = source.UserSex, UserPassWord = source.UserPassWord, UserName = source.UserName
WHEN NOT MATCHED THEN
INSERT (UpdateTime, UpdatePerson, CreatTime, UserId, CreatPerson, UserSex, UserPassWord, UserName) VALUES (source.UpdateTime, source.UpdatePerson, source.CreatTime, source.UserId, source.CreatPerson, source.UserSex, source.UserPassWord, source.UserName);]; SQL state [null]; error code [0]; failed Record Data : Record[sourcePkeys=,schemaName=SyncTest,tableName=SysUser_copy1,primaryKeys=[],columns=[ColumnValue [column=ColumnMeta[name=UpdateTime,type=TIMESTAMP], value=2020-01-16 18:45:34.0], ColumnValue [column=ColumnMeta[name=UpdatePerson,type=VARCHAR], value=aokeikao1211], ColumnValue [column=ColumnMeta[name=CreatTime,type=TIMESTAMP], value=2020-01-16 18:45:34.0], ColumnValue [column=ColumnMeta[name=UserId,type=INTEGER], value=24822], ColumnValue [column=ColumnMeta[name=CreatPerson,type=VARCHAR], value=1211_createperson], ColumnValue [column=ColumnMeta[name=UserSex,type=VARCHAR], value=1], ColumnValue [column=ColumnMeta[name=UserPassWord,type=VARCHAR], value=qs_1211], ColumnValue [column=ColumnMeta[name=UserName,type=VARCHAR], value=userName1211]],checkCompositeKeys=[],enableCompositeIndexes=false]; nested exception is java.sql.SQLException: failed Record Data : Record[sourcePkeys=,schemaName=SyncTest,tableName=SysUser_copy1,primaryKeys=[],columns=[ColumnValue [column=ColumnMeta[name=UpdateTime,type=TIMESTAMP], value=2020-01-16 18:45:34.0], ColumnValue [column=ColumnMeta[name=UpdatePerson,type=VARCHAR], value=aokeikao1211], ColumnValue [column=ColumnMeta[name=CreatTime,type=TIMESTAMP], value=2020-01-16 18:45:34.0], ColumnValue [column=ColumnMeta[name=UserId,type=INTEGER], value=24822], ColumnValue [column=ColumnMeta[name=CreatPerson,type=VARCHAR], value=1211_createperson], ColumnValue [column=ColumnMeta[name=UserSex,type=VARCHAR], value=1], ColumnValue [column=ColumnMeta[name=UserPassWord,type=VARCHAR], value=qs_1211], ColumnValue [column=ColumnMeta[name=UserName,type=VARCHAR], value=userName1211]],checkCompositeKeys=[],enableCompositeIndexes=false]
at com.taobao.yugong.common.utils.thread.ExecutorTemplate.waitForResult(ExecutorTemplate.java:79)
at com.taobao.yugong.applier.MultiThreadFullRecordApplier.apply(MultiThreadFullRecordApplier.java:103)
at com.taobao.yugong.controller.YuGongInstance$1.processTable(YuGongInstance.java:271)
at com.taobao.yugong.controller.YuGongInstance$1.run(YuGongInstance.java:201)
at java.lang.Thread.run(Thread.java:748)

我使用INC模式运行，CDC修改的数据和新增加的数据，并没有同步，帮忙看看呗

相关配置文件
yugong.table.mode=INC
yugong.cdc.time.start=2021-07-20
yugong.cdc.steptime=72000

运行输出信息
2021-07-20 14:42:18.252 [main] INFO com.taobao.yugong.YugongApp - ## start the YuGong.
2021-07-20 14:42:18.310 [main] INFO com.taobao.yugong.controller.YuGongController - check source database connection ...
2021-07-20 14:42:18.322 [main] INFO com.taobao.yugong.controller.YuGongController - check source database is ok
2021-07-20 14:42:18.322 [main] INFO com.taobao.yugong.controller.YuGongController - check target database connection ...
2021-07-20 14:42:18.332 [main] INFO com.taobao.yugong.controller.YuGongController - check target database is ok
2021-07-20 14:42:18.333 [main] INFO com.taobao.yugong.controller.YuGongController - check source tables read privileges ...
2021-07-20 14:42:18.365 [main] INFO com.alibaba.druid.pool.DruidDataSource - {dataSource-1} inited
2021-07-20 14:42:18.524 [main] INFO com.alibaba.druid.pool.DruidDataSource - {dataSource-2} inited
2021-07-20 14:42:19.292 [main] INFO com.taobao.yugong.controller.YuGongController - check source tables is ok.
2021-07-20 14:42:19.333 [main] INFO com.taobao.yugong.controller.YuGongController - ## prepare start tables[1] with concurrent[5]
2021-07-20 14:42:19.340 [main] INFO com.taobao.yugong.controller.YuGongInstance - table[BestCPS.Persons] start successful. extractor:com.taobao.yugong.extractor.sqlserver.SqlServerCdcExtractor , applier:com.taobao.yugong.applier.MultiThreadIncrementRecordApplier, translator:[]
2021-07-20 14:42:19.340 [YuGongInstance-BestCPS.Persons] INFO com.taobao.yugong.controller.YuGongInstance - table[BestCPS.Persons] is start
2021-07-20 14:42:19.342 [main] INFO com.taobao.yugong.YugongApp - ## the YuGong is running now ......
2021-07-20 14:42:19.345 [main] INFO com.taobao.yugong.YugongApp -
[YuGong Version Info]
[version ]Unknown
[hexVeision]
[date ]2021-07-19 12:11:08
[branch ]Unknown
[url ]file:///e/Download/yugong-feature-sql-server-to-mysql-overview

2021-07-20 14:43:19.336 [pool-3-thread-2] INFO com.taobao.yugong.common.stats.ProgressTracer - {未启动:0,增量中:0,已追上:1,异常数:0}
2021-07-20 14:43:19.337 [pool-3-thread-2] INFO com.taobao.yugong.common.stats.ProgressTracer - 已完成:[BestCPS.Persons]

运行后CDC列表中的变更的数据，并没有更新到MYSQL，数据无变化，是不是还有哪里要配置