基于GOLDENGATE实现数据实时分发

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、官方文档的部分描述与结论不一致

 

发表评论

邮箱地址不会被公开。