合 OGG用于跨云RDS for MySQL之间配置双主实时同步--OGG远程捕获和投递
发布日期 · 已更新
👉 本文共约1886个字，系统预计阅读时间或需8分钟。
问题引出
客户需要将华为云rds for MySQL和天翼云rds for MySQL做一个双向同步，当华为云rds宕机的时候，可以切换到天翼云继续提供服务，而且此时，天翼云的数据也可以自动同步到华为云rds，平时只使用华为云的rds，和双A方案有点差异，需要注意的是rds环境不能安装任何的软件，所以，我目前想到的方案有：
1、用MySQL自带的主从复制。这个方案最简单，但是不可行，因为华为云和天翼云都禁用了super权限，在执行change master的时候会报权限不足的错误，“ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation”。
2、使用华为云或天翼云自带的数据同步功能。这个也不可行，翻阅了一下文档，同步只能全量+增量同步，这对于双向同步来说不可行。
3、使用ogg远程捕获投递。ogg for MySQL从MySQL 5.7和ogg 19c开始支持远程捕获（Remote Capture）和远程投递（Remote Delivery），所以配置双向同步，该方案经过验证也是可行的！
- OGG用于跨云RDS之间配置双主实时同步（远程捕获和投递）：https://www.dbaup.com/oggyongyukuayunrdszhijianpeizhishuangzhushishitongbuyuanchengbuhuohetoudi.html
4、使用阿里的开源工具otter，这个方案经过验证是可行的。阿里数据同步工具Otter和Canal简介请参考：https://www.dbaup.com/alishujutongbugongjuotterhecanaljianjie.html
- otter用于跨云RDS之间配置双主实时同步参考：https://www.dbaup.com/otteryongyukuayunrdszhijianpeizhishuangzhushishitongbu.html
otter和ogg优缺点
1、otter不能同步无主键的表（会导致同步任务停止），而ogg可以
2、otter可以单向同步ddl语句，而ogg对于MySQL 5.7不可以（5.7需要安装插件），ogg对MySQL 8.0支持DDL同步
3、otter有图形界面操作，OGG只有命令行操作，但是ogg的微服务也可以全程图形化
ogg for MySQL简介
ogg19c开始支持 mysql >=5.7 远程捕获和投递，但是不支持远程捕获DDL语句。
从OGG 21C开始，对于mysql 8.0，开始支持远程捕获DDL语句。
- 官方文档：
https://docs.oracle.com/en/middleware/goldengate/core/21.3/index.html
- 完整的新功能列表可参考：
https://docs.oracle.com/en/middleware/goldengate/core/21.3/release-notes/whats-new-this-release.html
- 下载：
https://www.oracle.com/middleware/technologies/goldengate-downloads.html
下载后，校验md5值：
1 2 | [root@ecs-otter-test-0002 soft]# md5sum 213000_ggs_Linux_x64_MySQL_64bit.zip 046ea37a24620534365d07df242e3528 213000_ggs_Linux_x64_MySQL_64bit.zip |
安装：
1 2 3 4 5 | mkidr /ogg unzip 213000_ggs_Linux_x64_MySQL_64bit.zip tar -xvf ggs_Linux_x64_MySQL_64bit.tar ./ggsci create subdirs |
配置双向同步
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
注意以下问题：
1、双向同步需要配置2条链路。
2、为了避免数据回环错误，需要在extract参数中加入如下内容，且必须使用checkpoint表：
1 2 | IGNOREREPLICATES TRANLOGOPTIONS FILTERTABLE lhrdb.checkpoint |
配置华为云到天翼云
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | -- mgr cat > /ogg/dirprm/mgr.prm <<"EOF" port 7809 EOF -- 配置extract cat > /ogg/dirprm/ext1.prm <<"EOF" extract ext1 sourcedb lhrdb@124.70.97.208:3306 userid root password lhr exttrail ./dirdat/m8 TRANLOGOPTIONS ALTLOGDEST REMOTE IGNOREREPLICATES TRANLOGOPTIONS FILTERTABLE lhrdb.checkpoint table lhrdb.*; EOF add ext ext1, tranlog, begin now add exttrail ./dirdat/m8, ext ext1 -- 配置replication cat > /ogg/dirprm/rep1.prm <<"EOF" replicat rep1 targetdb lhrdb@114.116.245.109:3306 userid root password lhr map lhrdb.*, target lhrdb.*; EOF dblogin sourcedb lhrdb@114.116.245.109:3306 userid root password lhr add checkpointtable lhrdb.checkpoint edit params ./GLOBALS checkpointtable lhrdb.checkpoint add rep rep1, exttrail ./dirdat/m8, checkpointtable lhrdb.checkpoint GGSCI (ecs-otter-test-0001) 139> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 00:00:00 00:00:02 REPLICAT RUNNING REP1 00:00:00 00:00:00 |
配置天翼云到华为云
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | -- 配置extract cat > /ogg/dirprm/ext2.prm <<"EOF" extract ext2 sourcedb lhrdb@114.116.245.109:3306 userid root password lhr exttrail ./dirdat/m9 TRANLOGOPTIONS ALTLOGDEST REMOTE IGNOREREPLICATES TRANLOGOPTIONS FILTERTABLE lhrdb.checkpoint table lhrdb.*; EOF add ext ext2, tranlog, begin now add exttrail ./dirdat/m9, ext ext2 -- 配置replication cat > /ogg/dirprm/rep2.prm <<"EOF" replicat rep2 targetdb lhrdb@124.70.97.208:3306 userid root password lhr map lhrdb.*, target lhrdb.*; EOF dblogin sourcedb lhrdb@124.70.97.208:3306 userid root password lhr add checkpointtable lhrdb.checkpoint edit params ./GLOBALS checkpointtable lhrdb.checkpoint add rep rep2, exttrail ./dirdat/m9, checkpointtable lhrdb.checkpoint GGSCI (ecs-otter-test-0001 DBLOGIN as root) 53> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 00:00:00 00:00:04 EXTRACT RUNNING EXT2 00:00:00 00:00:03 REPLICAT RUNNING REP1 00:00:00 00:00:06 REPLICAT RUNNING REP2 00:00:00 00:00:04 |
经过验证，双向同步正常！
压测
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -- 在华为云rds sysbench /usr/share/sysbench/oltp_common.lua --time=100 --mysql-host=124.70.97.208 \ --mysql-port=3306 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb \ --table-size=100000 --tables=10 --threads=16 --events=999999999 prepare -- 在华为云rds sysbench /usr/share/sysbench/oltp_read_write.lua --time=100 --mysql-host=124.70.97.208 \ --mysql-port=3306 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb \ --table-size=100000 --tables=10 --threads=16 --events=999999999 --report-interval=10 \ --db-ps-mode=disable --forced-shutdown=1 run -- 在天翼云 sysbench /usr/share/sysbench/oltp_read_write.lua --time=100 --mysql-host=114.116.245.109 \ --mysql-port=3306 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb \ --table-size=100000 --tables=10 --threads=16 --events=999999999 --report-interval=10 \ --db-ps-mode=disable --forced-shutdown=1 run |
华为云到天翼云rds
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | [root@docker35 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --time=100 --mysql-host=124.70.97.208 \ > --mysql-port=3306 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb \ > --table-size=100000 --tables=10 --threads=16 --events=999999999 --report-interval=10 \ > --db-ps-mode=disable --forced-shutdown=1 run sysbench 1.0.17 (using system LuaJIT 2.0.4) Running the test with following options: Number of threads: 16 Report intermediate results every 10 second(s) Initializing random number generator from current time Forcing shutdown in 101 seconds Initializing worker threads... Threads started! [ 10s ] thds: 16 tps: 32.29 qps: 659.71 (r/w/o: 464.19/95.06/100.46) lat (ms,95%): 502.20 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 16 tps: 32.60 qps: 655.86 (r/w/o: 459.85/98.91/97.11) lat (ms,95%): 511.33 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 16 tps: 33.00 qps: 655.60 (r/w/o: 457.60/98.70/99.30) lat (ms,95%): 511.33 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 16 tps: 32.80 qps: 661.10 (r/w/o: 462.80/101.90/96.40) lat (ms,95%): 502.20 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 16 tps: 20.90 qps: 416.50 (r/w/o: 292.40/62.90/61.20) lat (ms,95%): 1427.08 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 16 tps: 29.20 qps: 580.80 (r/w/o: 406.00/89.10/85.70) lat (ms,95%): 787.74 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 16 tps: 32.40 qps: 653.59 (r/w/o: 457.99/100.10/95.50) lat (ms,95%): 511.33 err/s: 0.00 reconn/s: 0.00 [ 80s ] thds: 16 tps: 31.70 qps: 631.71 (r/w/o: 441.81/98.10/91.80) lat (ms,95%): 623.33 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 16 tps: 31.10 qps: 620.50 (r/w/o: 434.30/95.70/90.50) lat (ms,95%): 707.07 err/s: 0.00 reconn/s: 0.00 [ 100s ] thds: 16 tps: 32.50 qps: 653.30 (r/w/o: 458.00/103.80/91.50) lat (ms,95%): 511.33 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 43414 write: 9488 other: 9118 total: 62020 transactions: 3101 (30.86 per sec.) queries: 62020 (617.26 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 100.4736s total number of events: 3101 Latency (ms): min: 461.25 avg: 516.93 max: 1802.51 95th percentile: 733.00 sum: 1603008.41 Threads fairness: events (avg/stddev): 193.8125/3.56 execution time (avg/stddev): 100.1880/0.12 |
在OGG检查：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | GGSCI (lhrogg21cmysql) 9> stats ext1,total,table lhrdb.sbtest1 Sending STATS request to Extract group EXT1 ... Start of statistics at 2021-09-03 10:37:38. Output to ./dirdat/m8: Extracting from lhrdb.sbtest1 to lhrdb.sbtest1: *** Total statistics since 2021-09-03 10:28:22 *** Total inserts 300.00 Total updates 417.00 Total deletes 216.00 Total upserts 0.00 Total discards 0.00 Total operations 933.00 End of statistics. GGSCI (lhrogg21cmysql) 11> stats rep1,total,table lhrdb.sbtest1 Sending STATS request to Replicat group REP1 ... Start of statistics at 2021-09-03 10:39:18. Replicating from lhrdb.sbtest1 to lhrdb.sbtest1: *** Total statistics since 2021-09-03 10:30:44 *** Total inserts 300.00 Total updates 417.00 Total deletes 216.00 Total upserts 0.00 Total discards 0.00 Total operations 933.00 End of statistics. GGSCI (lhrogg21cmysql) 25> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 00:00:00 00:00:04 EXTRACT RUNNING EXT2 00:00:00 00:00:09 REPLICAT RUNNING REP1 00:00:00 00:00:01 REPLICAT RUNNING REP2 00:00:00 00:00:07 |
可以看到，正常同步，观察延迟Lag at Chkpt不超过1分钟。
天翼云到华为云rds
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | [root@docker35 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --time=100 --mysql-host=114.116.245.109 \ > --mysql-port=3306 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb \ > --table-size=100000 --tables=10 --threads=16 --events=999999999 --report-interval=10 \ > --db-ps-mode=disable --forced-shutdown=1 run sysbench 1.0.17 (using system LuaJIT 2.0.4) Running the test with following options: Number of threads: 16 Report intermediate results every 10 second(s) Initializing random number generator from current time Forcing shutdown in 101 seconds Initializing worker threads... Threads started! [ 10s ] thds: 16 tps: 20.59 qps: 431.92 (r/w/o: 304.87/64.87/62.17) lat (ms,95%): 1129.24 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 16 tps: 19.80 qps: 396.43 (r/w/o: 278.32/60.51/57.60) lat (ms,95%): 1191.92 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 16 tps: 20.50 qps: 408.40 (r/w/o: 285.30/64.60/58.50) lat (ms,95%): 1213.57 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 16 tps: 20.10 qps: 399.50 (r/w/o: 279.70/63.10/56.70) lat (ms,95%): 1258.08 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 16 tps: 19.20 qps: 388.40 (r/w/o: 271.90/61.80/54.70) lat (ms,95%): 1258.08 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 16 tps: 14.60 qps: 289.90 (r/w/o: 203.70/46.80/39.40) lat (ms,95%): 1739.68 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 16 tps: 19.80 qps: 395.70 (r/w/o: 276.50/62.60/56.60) lat (ms,95%): 1453.01 err/s: 0.00 reconn/s: 0.00 [ 80s ] thds: 16 tps: 18.30 qps: 361.20 (r/w/o: 251.80/58.10/51.30) lat (ms,95%): 1533.66 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 16 tps: 22.20 qps: 447.60 (r/w/o: 313.80/69.40/64.40) lat (ms,95%): 1069.86 err/s: 0.00 reconn/s: 0.00 [ 100s ] thds: 16 tps: 22.50 qps: 444.80 (r/w/o: 310.40/72.70/61.70) lat (ms,95%): 1109.09 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 27888 write: 6287 other: 5665 total: 39840 transactions: 1992 (19.77 per sec.) queries: 39840 (395.38 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 100.7602s total number of events: 1992 Latency (ms): min: 467.33 avg: 806.90 max: 2620.88 95th percentile: 1327.91 sum: 1607342.95 Threads fairness: events (avg/stddev): 124.5000/2.45 execution time (avg/stddev): 100.4589/0.24 |
在OGG检查：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | GGSCI (lhrogg21cmysql) 17> stats ext2,total,table lhrdb.sbtest1 Sending STATS request to Extract group EXT2 ... Start of statistics at 2021-09-03 10:42:13. Output to ./dirdat/m9: Extracting from lhrdb.sbtest1 to lhrdb.sbtest1: *** Total statistics since 2021-09-03 10:28:23 *** Total inserts 180.00 Total updates 263.00 Total deletes 127.00 Total upserts 0.00 Total discards 0.00 Total operations 570.00 End of statistics. GGSCI (lhrogg21cmysql) 24> stats rep2,total,table lhrdb.sbtest1 Sending STATS request to Replicat group REP2 ... Start of statistics at 2021-09-03 10:43:01. Replicating from lhrdb.sbtest1 to lhrdb.sbtest1: *** Total statistics since 2021-09-03 10:30:59 *** Total inserts 180.00 Total updates 263.00 Total deletes 127.00 Total upserts 0.00 Total discards 0.00 Total operations 570.00 End of statistics. GGSCI (lhrogg21cmysql) 26> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 00:00:00 00:00:03 EXTRACT RUNNING EXT2 00:00:00 00:00:09 REPLICAT RUNNING REP1 00:00:00 00:00:01 REPLICAT RUNNING REP2 00:00:00 00:00:05 |
可以看到，正常同步，观察延迟Lag at Chkpt不超过1分钟。
扩展
当然，也可以使用如下的架构，需要添加Pump进程，但是我感觉没有必要，除非是源端和目标端距离非常远，一个在国内，一个在国外：
其他有关更多OGG的内容，请联系麦老师。
相关文章
- 使用OGG for mysql微服务快速双向同步RDS数据库（双主）
- otter用于跨云RDS for mysql之间配置双主实时同步
- 小麦苗数据库迁移及实时同步课程
- 使用OGG 21.3远程实时互相同步Oracle 11.2.0.4（双主）
- 使用OGG for mysql微服务搭建双主架构（含DDL）
- OGG用于PG数据库之间双主实时同步（RDS for PG亦可）–OGG远程捕获和投递
- OGG从入门到高可用系列
- 使用OGG微服务将PG同步到kafka(全量+增量)
- 使用OGG传统模式将PG同步到kafka(全量+增量)
- 使用OGG传统模式将Oracle同步到kafka(全量+增量)
- 使用OGG 21c迁移Oracle 12c到MySQL 8.0并配置实时同步
- How to Replicate Data Between Oracle and MySQL Database? (Doc ID 1605674.1)
- 使用OGG for MSSQL微服务快速双向同步RDS数据库（双主）
- 使用OGG for Oracle微服务双向同步Oracle数据库搭建双主架构（含DDL）
- 使用OGG for PG微服务快速双向同步RDS数据库（双主）
- How to replicate data using GoldenGate PostgreSQL（使用OGG同步PG数据库数据）
- OGG-25715 CACHEMGR: No Swap environment and parameter CONTAINER MEM_LIMIT not set
- 阿里数据同步工具Otter和Canal简介
- 密码保护GreenPlum之搭建gpdr灾备环境（容灾环境、数据实时同步）
- 密码保护Oracle同步数据到GreenPlum
