1. 背景
我司目前数据库之间的数据同步都是oracle goldengate(ogg)方案，该方案的特点：
优点：
基于数据库的变更日志同步（oracle redo\mysql binlog），速度很快，对数据库性能影响很小，适合大量数据同步的场景
缺点：
同步表变更字段、新增表，需要修改数据库服务器上的很多配置文件，比较繁琐，在exact、pump、replicate进程很多的情况下，易误操作；如果某个表同步失败，重建配置比较复杂；需要在每个数据库服务器上安装软件；无界面，不直观，配置分散……
为了解决ogg的上述缺点，研究了新的同步方案：kettle
kettle是通过sql，基于主键、时间戳增量同步数据，不需在数据库服务器上做任何配置，只需在kettle服务器上创建配置JOB即可，有简单直观的CS平台。
今后将以kettle为主，同步数据量很大的表（如单表日同步100万记录以上），会考虑ogg。
2. 安装
版本：pdi8.3
OS：因为linux的界面失真，用起来实在蓝瘦香菇，而且每次启动spoon都很费时，因此用windows平台。
安装过程很简单：
安装jdk1.8；解压pdi.zip；下载驱动oracle（ojdbc14.jar）、mysql（mysql-connector-java-5.1.9.jar、mysql-connector-java-6.0.6.jar）至目录pdi\data-integration\lib：
3. 配置
kettle的几个关键组件说明
SQL：顾名思义，就是执行一段SQL，可以包含多条转换：包含表输入、设置变量、排序、合并、switch、表输出、更新、插入/更新等功能，是kettle的精华所在，实现关键逻辑作业：一个同步的完整逻辑，包含SQL、转换等，用“作业定时调度”和“成功”分别开始和结束作业DB连接：数据库连接，可以在转换或作业下创建，默认在该转换或作业下生效；支持“共享”，将该连接在全局范围生效
对表的要求
表有主键；应用不能物理删除数据，只能逻辑删除数据，设置字段（delete_flag tinyint/number(1)：0：未删除，1，已删除），可通过定时任务在源和目标端物理删除delete_flag=1的数据；统一时间戳字段（update_time oracle:date/mysql datetime），所有数据变更（包含delete_flag）必须同时修改update_time；Oracle number类型同步到mysql bigint时，支持最大长度18位，不能用默认number（默认38位）；手动维护数据时需要源和目标端同时处理（truncate,delete,update without update_time)；
原理说明
创建表sync_timestamp（table_name,time_stamp），记录每张表完成同步的时间戳，第一次同步前，手工设置为源表中update_time最小值；每次同步时，先设置环境变量TIME_STAMP = sync_timestamp中的time_stamp；将源表大于等于TIME_STAMP的数据对目标表插入/更新；用目标表update_time字段的最大值，更新sync_timestamp中的time_stamp字段；
作业：job_sync_t1（oracle->mysql）
转换：trans_get_timestamp
表输入：
select DATE_FORMAT(time_stamp,'%Y-%m-%d %H:%i:%S') time_stampfrom sync_timestampwhere table_name='sync_t1'
设置变量：
转换：trans_sync_data
表输入：
select *from sync_t1where update_time >= to_date('${TIME_STAMP}','yyyy-mm-dd hh24:mi:ss')
插入/更新：
4. 调度
kettle job通过kitchen命令执行；每次执行JOB时，kitchen要初始化几百M的内存，耗时10秒以上，一个kitchen进程启动后只能执行1次JOB；如果大量kitchen同时启动，会消耗大量内存，对OS内存配置要求很高。
为了合理化利用资源，将JOB调度按调度频率划分到不同文件，每个文件根据具体同步数据的压力情况安排3-5个job：
1分钟执行1次的JOB：
file1:job_1min_1.bat，包含job1-job3
file2: job_1min_2.bat，包含job4-job6
file3: job_1min_3.bat，包含job7-job9
……
job_1min_1.bat的内容：
e:cd e:\pdi\data-integrationkitchen /file:e:\mykettle\job1\job.kjb /level:Errorkitchen /file:e:\mykettle\job2\job.kjb /level:Errorkitchen /file:e:\mykettle\job3\job.kjb /level:Error
通过kettle服务器的任务调度定期执行job_1min_1.bat文件即可。
备注：kettle自身没有防止JOB并发执行的机制（如某JOB执行频率每分钟1次，但一次执行耗时超过1分钟，就会存在并发执行的情况）；并发执行时，插入重复数据报错，可以监控到JOB错误，从而优化JOB或调度。
5. 监控
JOB配置日志表，记录JOB执行情况；
创建日志表
CREATE TABLE `sync_log` (`ID_JOB` bigint(20) NOT NULL,`JOBNAME` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,`STATUS` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,`ERRORS` bigint(20) DEFAULT NULL,`LOGDATE` datetime DEFAULT NULL,`LOG_FIELD` blob,`LINES_READ` bigint(20) DEFAULT NULL,`LINES_WRITTEN` bigint(20) DEFAULT NULL,`LINES_UPDATED` bigint(20) DEFAULT NULL,`LINES_INPUT` bigint(20) DEFAULT NULL,`LINES_OUTPUT` bigint(20) DEFAULT NULL,`LINES_REJECTED` bigint(20) DEFAULT NULL,`STARTDATE` datetime DEFAULT NULL,`DEPDATE` datetime DEFAULT NULL,`REPLAYDATE` datetime DEFAULT NULL,`ENDDATE` datetime DEFAULT NULL,PRIMARY KEY (`ID_JOB`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
job指定日志表：
sync_log
sync_timestamp
通过上述2个表的关联，确认JOB是否正常执行，配置到zabbix监控项。
判断标准：sync_timestamp表记录的table_name，在sync_log中都有最新的成功执行的日志(status=end,error=0,logdate<now-interval_seconds)
select count(*)-(select count(*) from dbcopy.sync_timestamp) errorjobfrom dbcopy.sync_log a, dbcopy.sync_timestamp bwhere (a.jobname,a.logdate) in (select jobname,max(logdate) from dbcopy.sync_log where errors=0 and status='end' group by jobname)and a.jobname=b.table_nameand a.logdate > DATE_SUB(now(),INTERVAL (b.interval_seconds + 60) second)
以上SQL返回值>0即说明JOB执行有异常。
over ~
