基于GOLDENGATE实现数据实时分发

0
By   2016-09-06  

数据分发是中一对多的系统配置。Oracle GoldenGate支持源端数据库同多个目标数据库之间的数据同步，同步节点可以是同构数据库，也可以是异构数据库。根据不同的数据库平台，GoldenGate支持的功能会有所差异，但数据分发的总体模式如下：

通过GoldenGate实现数据分发，实现方式可以概括为如下三种方式：
  • 第一种方式：每个extract进程组对应一个datapump进程组，每个datapump进程组对应一个replicat进程组
  • 第二种方式：每个extract进程组对应多个datapump进程组，每个datapump进程组对应一个replicat进程组
  • 第三种方式：每个extract进程组对应一个datapump进程组，每个datapump进程组对应多个replicat进程组
当然，除了如上三种方式，根据实际情况进行灵活配置，可以演变出更多的数据分发模式，下面分别针对三种实现方式搭建实验环境，过程如下：
实验环境：
   操作系统：redhat 6.5
   数据库：mysql 5.6
   源数据库节点：redhat1 192.168.18.11
   目标数据库节点：redhat2  192.168.18.12  redhat3  192.168.18.13
    
第一种方式：
redhat1节点配置脚本如下：
  1. cd /u01/mysqlogg/
  2. ./ggsci <<EOF
  3. start mgr
  4. ADD EXTRACT ext1, TRANLOG, BEGIN NOW
  5. ADD EXTTRAIL /u01/mysqlogg/dirdat/ol, EXTRACT ext1
  6. EOF
  7. cat > /u01/mysqlogg/dirprm/ext1.prm <<EOF
  8. EXTRACT ext1
  9. tranlogoptions altlogdest '/var/lib/mysql/binlog/bin.index'
  10. SOURCEDB db1@192.168.18.11:3306, USERID ogg, PASSWORD ogg
  11. EXTTRAIL /u01/mysqlogg/dirdat/ol
  12. TABLE db1.t*;
  13. EOF
  14. ./ggsci <<EOF
  15. ADD EXTRACT pump1, EXTTRAILSOURCE /u01/mysqlogg/dirdat/ol, BEGIN NOW
  16. ADD RMTTRAIL /u01/mysqlogg/dirdat/or, EXTRACT pump1
  17. EOF
  18. cat > /u01/mysqlogg/dirprm/pump1.prm <<EOF
  19. EXTRACT pump1
  20. RMTHOST 192.168.18.12, MGRPORT 7809
  21. RMTTRAIL /u01/mysqlogg/dirdat/or
  22. TABLE db1.t*;
  23. EOF
  1. cd /u01/mysqlogg/
  2. ./ggsci <<EOF
  3. start mgr
  4. ADD EXTRACT ext2, TRANLOG, BEGIN NOW
  5. ADD EXTTRAIL /u01/mysqlogg/dirdat/sl, EXTRACT ext2
  6. EOF
  7. cat > /u01/mysqlogg/dirprm/ext2.prm <<EOF
  8. EXTRACT ext2
  9. tranlogoptions altlogdest '/var/lib/mysql/binlog/bin.index'
  10. SOURCEDB db1@192.168.18.11:3306, USERID ogg, PASSWORD ogg
  11. EXTTRAIL /u01/mysqlogg/dirdat/sl
  12. TABLE db1.t*;
  13. EOF
  14. ./ggsci <<EOF
  15. ADD EXTRACT pump2, EXTTRAILSOURCE /u01/mysqlogg/dirdat/sl, BEGIN NOW
  16. ADD RMTTRAIL /u01/mysqlogg/dirdat/sr, EXTRACT pump2
  17. EOF
  18. cat > /u01/mysqlogg/dirprm/pump2.prm <<EOF
  19. EXTRACT pump2
  20. RMTHOST 192.168.18.13, MGRPORT 7809
  21. RMTTRAIL /u01/mysqlogg/dirdat/sr
  22. TABLE db1.t*;
  23. EOF
redhat2节点配置代码如下：
  1. cd /u01/mysqlogg/
  2. ./ggsci <<EOF
  3. start mgr
  4. dblogin sourcedb ogg@192.168.18.12:3306 userid ogg password ogg
  5. add checkpointtable ogg.ggs_checkpoint
  6. ADD REPLICAT rep1, EXTTRAIL /u01/mysqlogg/dirdat/or, BEGIN now
  7. EOF
  8. cat > /u01/mysqlogg/dirprm/rep1.prm <<EOF
  9. REPLICAT rep1
  10. ASSUMETARGETDEFS
  11. TARGETDB db1@192.168.18.12:3306, USERID ogg, PASSWORD ogg
  12. MAP db1.t*, TARGET db1.*;
  13. EOF
redhat3节点配置代码如下
  1. cd /u01/mysqlogg/
  2. ./ggsci <<EOF
  3. start mgr
  4. dblogin sourcedb ogg@192.168.18.13:3306 userid ogg password ogg
  5. add checkpointtable ogg.ggs_checkpoint
  6. ADD REPLICAT rep2, EXTTRAIL /u01/mysqlogg/dirdat/sr, BEGIN now
  7. EOF
  8. cat > /u01/mysqlogg/dirprm/rep2.prm <<EOF
  9. REPLICAT rep2
  10. ASSUMETARGETDEFS
  11. TARGETDB db1@192.168.18.13:3306, USERID ogg, PASSWORD ogg
  12. MAP db1.t*, TARGET db1.*;
  13. EOF
测试结果：
ogg截图：

mysql截图：

 

结论：
1：可以实现数据的1对多分发
2：多个extract进程组重复提取数据，会增加工作负载
3：各个分发进程组之间不存在重叠区域，因此当某一进程发生故障时或需要做出调整工作时，不会影响其他分发进程组
4：比较适合于分发到不同目标数据库的数据不存在交集的情况
第二种方式：
redhat1节点配置脚本如下：
  1. cd /u01/mysqlogg/
  2. ./ggsci <<EOF
  3. start mgr
  4. ADD EXTRACT ext1, TRANLOG, BEGIN NOW
  5. ADD EXTTRAIL /u01/mysqlogg/dirdat/ol, EXTRACT ext1
  6. EOF
  7. cat > /u01/mysqlogg/dirprm/ext1.prm <<EOF
  8. EXTRACT ext1
  9. tranlogoptions altlogdest '/var/lib/mysql/binlog/bin.index'
  10. SOURCEDB db1@192.168.18.11:3306, USERID ogg, PASSWORD ogg
  11. EXTTRAIL /u01/mysqlogg/dirdat/ol
  12. TABLE db1.t*;
  13. EOF
  14. ./ggsci <<EOF
  15. ADD EXTRACT pump1, EXTTRAILSOURCE /u01/mysqlogg/dirdat/ol, BEGIN NOW
  16. ADD RMTTRAIL /u01/mysqlogg/dirdat/or, EXTRACT pump1
  17. EOF
  18. cat > /u01/mysqlogg/dirprm/pump1.prm <<EOF
  19. EXTRACT pump1
  20. RMTHOST 192.168.18.12, MGRPORT 7809
  21. RMTTRAIL /u01/mysqlogg/dirdat/or
  22. TABLE db1.t*;
  23. EOF
  24. ./ggsci <<EOF
  25. ADD EXTRACT pump2, EXTTRAILSOURCE /u01/mysqlogg/dirdat/ol, BEGIN NOW
  26. ADD RMTTRAIL /u01/mysqlogg/dirdat/sr, EXTRACT pump2
  27. EOF
  28. cat > /u01/mysqlogg/dirprm/pump2.prm <<EOF
  29. EXTRACT pump2
  30. RMTHOST 192.168.18.13, MGRPORT 7809
  31. RMTTRAIL /u01/mysqlogg/dirdat/sr
  32. TABLE db1.t*;
  33. EOF
redhat2节点配置脚本：
  1. cd /u01/mysqlogg/
  2. ./ggsci <<EOF
  3. start mgr
  4. dblogin sourcedb ogg@192.168.18.12:3306 userid ogg password ogg
  5. add checkpointtable ogg.ggs_checkpoint
  6. ADD REPLICAT rep1, EXTTRAIL /u01/mysqlogg/dirdat/or, BEGIN now
  7. EOF
  8. cat > /u01/mysqlogg/dirprm/rep1.prm <<EOF
  9. REPLICAT rep1
  10. ASSUMETARGETDEFS
  11. TARGETDB db1@192.168.18.12:3306, USERID ogg, PASSWORD ogg
  12. MAP db1.t*, TARGET db1.*;
  13. EOF
redhat3节点配置脚本：
  1. cd /u01/mysqlogg/
  2. ./ggsci <<EOF
  3. start mgr
  4. dblogin sourcedb ogg@192.168.18.13:3306 userid ogg password ogg
  5. add checkpointtable ogg.ggs_checkpoint
  6. ADD REPLICAT rep2, EXTTRAIL /u01/mysqlogg/dirdat/sr, BEGIN now
  7. EOF
  8. cat > /u01/mysqlogg/dirprm/rep2.prm <<EOF
  9. REPLICAT rep2
  10. ASSUMETARGETDEFS
  11. TARGETDB db1@192.168.18.13:3306, USERID ogg, PASSWORD ogg
  12. MAP db1.t*, TARGET db1.*;
  13. EOF
EOF测试结果：

特点：
    1、单一extract进程组对应多个datapump进程组，可以节省本地exttrail文件占用的空间
    2、extract进程故障会影响到所有的复制进程
    3、为每个target replicat分配一个专用datapump进程组，灵活性和性能较高
第三种方式：
redhat1节点的配置代码如下
  1. cd /u01/mysqlogg/
  2. ./ggsci <<EOF
  3. start mgr
  4. ADD EXTRACT ext1, TRANLOG, BEGIN NOW
  5. ADD EXTTRAIL /u01/mysqlogg/dirdat/ol, EXTRACT ext1
  6. EOF
  7. cat > /u01/mysqlogg/dirprm/ext1.prm <<EOF
  8. EXTRACT ext1
  9. tranlogoptions altlogdest '/var/lib/mysql/binlog/bin.index'
  10. SOURCEDB db1@192.168.18.11:3306, USERID ogg, PASSWORD ogg
  11. EXTTRAIL /u01/mysqlogg/dirdat/ol
  12. TABLE db1.t*;
  13. EOF
  14. ./ggsci <<EOF
  15. ADD EXTRACT pump1, EXTTRAILSOURCE /u01/mysqlogg/dirdat/ol, BEGIN NOW
  16. EOF
  17. cat > /u01/mysqlogg/dirprm/pump1.prm <<EOF
  18. EXTRACT pump1
  19. RMTHOST 192.168.18.12, MGRPORT 7809
  20. RMTTRAIL /u01/mysqlogg/dirdat/or
  21. TABLE db1.t*;
  22. RMTHOST 192.168.18.13, MGRPORT 7809
  23. RMTTRAIL /u01/mysqlogg/dirdat/sr
  24. TABLE db1.t*;
  25. EOF
  26. ./ggsci <<EOF
  27. ADD RMTTRAIL /u01/mysqlogg/dirdat/or, EXTRACT pump1
  28. ADD RMTTRAIL /u01/mysqlogg/dirdat/sr, EXTRACT pump1
  29. EOF
redhat2节点配置脚本：
  1. cd /u01/mysqlogg/
  2. ./ggsci <<EOF
  3. start mgr
  4. dblogin sourcedb ogg@192.168.18.12:3306 userid ogg password ogg
  5. add checkpointtable ogg.ggs_checkpoint
  6. ADD REPLICAT rep1, EXTTRAIL /u01/mysqlogg/dirdat/or, BEGIN now
  7. EOF
  8. cat > /u01/mysqlogg/dirprm/rep1.prm <<EOF
  9. REPLICAT rep1
  10. ASSUMETARGETDEFS
  11. TARGETDB db1@192.168.18.12:3306, USERID ogg, PASSWORD ogg
  12. MAP db1.t*, TARGET db1.*;
  13. EOF
redhat3节点配置脚本：
  1. cd /u01/mysqlogg/
  2. ./ggsci <<EOF
  3. start mgr
  4. dblogin sourcedb ogg@192.168.18.13:3306 userid ogg password ogg
  5. add checkpointtable ogg.ggs_checkpoint
  6. ADD REPLICAT rep2, EXTTRAIL /u01/mysqlogg/dirdat/sr, BEGIN now
  7. EOF
  8. cat > /u01/mysqlogg/dirprm/rep2.prm <<EOF
  9. REPLICAT rep2
  10. ASSUMETARGETDEFS
  11. TARGETDB db1@192.168.18.13:3306, USERID ogg, PASSWORD ogg
  12. MAP db1.t*, TARGET db1.*;
  13. EOF
测试结果：

 结论：
    1、不能实现数据分发，rmttrail始终分发到同一目标数据库
    2、官方文档的部分描述与结论不一致

 

发表评论

邮箱地址不会被公开。