问题引出
客户需要将华为云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.xmmup.com/oggyongyukuayunrdszhijianpeizhishuangzhushishitongbuyuanchengbuhuohetoudi.html
4、使用阿里的开源工具otter，这个方案经过验证是可行的。阿里数据同步工具Otter和Canal简介请参考：https://www.xmmup.com/alishujutongbugongjuotterhecanaljianjie.html
otter用于跨云RDS之间配置双主实时同步参考：https://www.xmmup.com/otteryongyukuayunrdszhijianpeizhishuangzhushishitongbu.html
otter和ogg优缺点
1、otter不能同步无主键的表（会导致同步任务停止），而ogg可以
2、otter可以单向同步ddl语句，而ogg对于MySQL 5.7不可以，ogg对MySQL 8.0支持DDL同步
3、otter有图形界面操作，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/gghdb/understanding-whats-supported-mysql.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[root@ecs-otter-test-0002 soft]# md5sum 213000_ggs_Linux_x64_MySQL_64bit.zip2046ea37a24620534365d07df242e3528 213000_ggs_Linux_x64_MySQL_64bit.zip
安装：
1mkidr /ogg2unzip 213000_ggs_Linux_x64_MySQL_64bit.zip3tar -xvf ggs_Linux_x64_MySQL_64bit.tar4./ggsci5create subdirs
配置双向同步
注意以下问题：
1、双向同步需要配置2条链路。
2、为了避免数据回环错误，需要在extract参数中加入如下内容，且必须使用checkpoint表：
1IGNOREREPLICATES2TRANLOGOPTIONS FILTERTABLE lhrdb.checkpoint
配置华为云到天翼云
1-- mgr 2cat > /ogg/dirprm/mgr.prm <<"EOF" 3port 7809 4EOF 5 6-- 配置extract 7cat > /ogg/dirprm/ext1.prm <<"EOF" 8extract ext1 9sourcedb lhrdb@124.70.97.208:3306 userid root password lhr10exttrail ./dirdat/m811TRANLOGOPTIONS ALTLOGDEST REMOTE12IGNOREREPLICATES13TRANLOGOPTIONS FILTERTABLE lhrdb.checkpoint14table lhrdb.*;15EOF161718add ext ext1, tranlog, begin now19add exttrail ./dirdat/m8, ext ext1202122-- 配置replication23cat > /ogg/dirprm/rep1.prm <<"EOF"24replicat rep125targetdb lhrdb@114.116.245.109:3306 userid root password lhr26map lhrdb.*, target lhrdb.*;27EOF282930dblogin sourcedb lhrdb@114.116.245.109:3306 userid root password lhr 31add checkpointtable lhrdb.checkpoint 32edit params ./GLOBALS 33checkpointtable lhrdb.checkpoint3435add rep rep1, exttrail ./dirdat/m8, checkpointtable lhrdb.checkpoint363738GGSCI (ecs-otter-test-0001) 139> info all3940Program Status Group Lag at Chkpt Time Since Chkpt4142MANAGER RUNNING 43EXTRACT RUNNING EXT1 00:00:00 00:00:02 44REPLICAT RUNNING REP1 00:00:00 00:00:00
配置天翼云到华为云
1-- 配置extract 2cat > /ogg/dirprm/ext2.prm <<"EOF" 3extract ext2 4sourcedb lhrdb@114.116.245.109:3306 userid root password lhr 5exttrail ./dirdat/m9 6TRANLOGOPTIONS ALTLOGDEST REMOTE 7IGNOREREPLICATES 8TRANLOGOPTIONS FILTERTABLE lhrdb.checkpoint 9table lhrdb.*;10EOF111213add ext ext2, tranlog, begin now14add exttrail ./dirdat/m9, ext ext2151617-- 配置replication18cat > /ogg/dirprm/rep2.prm <<"EOF"19replicat rep220targetdb lhrdb@124.70.97.208:3306 userid root password lhr21map lhrdb.*, target lhrdb.*;22EOF23242526dblogin sourcedb lhrdb@124.70.97.208:3306 userid root password lhr 27add checkpointtable lhrdb.checkpoint 28edit params ./GLOBALS 29checkpointtable lhrdb.checkpoint3031add rep rep2, exttrail ./dirdat/m9, checkpointtable lhrdb.checkpoint323334GGSCI (ecs-otter-test-0001 DBLOGIN as root) 53> info all3536Program Status Group Lag at Chkpt Time Since Chkpt3738MANAGER RUNNING 39EXTRACT RUNNING EXT1 00:00:00 00:00:04 40EXTRACT RUNNING EXT2 00:00:00 00:00:03 41REPLICAT RUNNING REP1 00:00:00 00:00:06 42REPLICAT RUNNING REP2 00:00:00 00:00:04
经过验证，双向同步正常！
压测
1-- 在华为云rds 2sysbench /usr/share/sysbench/oltp_common.lua --time=100 --mysql-host=124.70.97.208 \ 3--mysql-port=3306 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb \ 4--table-size=100000 --tables=10 --threads=16 --events=999999999 prepare 5 6-- 在华为云rds 7sysbench /usr/share/sysbench/oltp_read_write.lua --time=100 --mysql-host=124.70.97.208 \ 8--mysql-port=3306 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb \ 9--table-size=100000 --tables=10 --threads=16 --events=999999999 --report-interval=10 \10--db-ps-mode=disable --forced-shutdown=1 run1112-- 在天翼云13sysbench /usr/share/sysbench/oltp_read_write.lua --time=100 --mysql-host=114.116.245.109 \14--mysql-port=3306 --mysql-user=root --mysql-password=lhr@ZR123456 --mysql-db=lhrdb \15--table-size=100000 --tables=10 --threads=16 --events=999999999 --report-interval=10 \16--db-ps-mode=disable --forced-shutdown=1 run
华为云到天翼云rds
1[root@docker35 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --time=100 --mysql-host=124.70.97.208 \ 2> --mysql-port=3306 --mysql-user=root --mysql-password=lhr@ZR123456 --mysql-db=lhrdb \ 3> --table-size=100000 --tables=10 --threads=16 --events=999999999 --report-interval=10 \ 4> --db-ps-mode=disable --forced-shutdown=1 run 5sysbench 1.0.17 (using system LuaJIT 2.0.4) 6 7Running the test with following options: 8Number of threads: 16 9Report intermediate results every 10 second(s)10Initializing random number generator from current time1112Forcing shutdown in 101 seconds1314Initializing worker threads...1516Threads started!1718[ 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.0019[ 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.0020[ 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.0021[ 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.0022[ 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.0023[ 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.0024[ 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.0025[ 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.0026[ 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.0027[ 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.0028SQL statistics:29 queries performed:30 read: 4341431 write: 948832 other: 911833 total: 6202034 transactions: 3101 (30.86 per sec.)35 queries: 62020 (617.26 per sec.)36 ignored errors: 0 (0.00 per sec.)37 reconnects: 0 (0.00 per sec.)3839General statistics:40 total time: 100.4736s41 total number of events: 31014243Latency (ms):44 min: 461.2545 avg: 516.9346 max: 1802.5147 95th percentile: 733.0048 sum: 1603008.414950Threads fairness:51 events (avg/stddev): 193.8125/3.5652 execution time (avg/stddev): 100.1880/0.12
在OGG检查：
1GGSCI (lhrogg21cmysql) 9> stats ext1,total,table lhrdb.sbtest1 2 3Sending STATS request to Extract group EXT1 ... 4 5Start of statistics at 2021-09-03 10:37:38. 6 7Output to ./dirdat/m8: 8 9Extracting from lhrdb.sbtest1 to lhrdb.sbtest1:1011*** Total statistics since 2021-09-03 10:28:22 ***12 Total inserts 300.0013 Total updates 417.0014 Total deletes 216.0015 Total upserts 0.0016 Total discards 0.0017 Total operations 933.001819End of statistics.2021GGSCI (lhrogg21cmysql) 11> stats rep1,total,table lhrdb.sbtest12223Sending STATS request to Replicat group REP1 ...2425Start of statistics at 2021-09-03 10:39:18.2627Replicating from lhrdb.sbtest1 to lhrdb.sbtest1:2829*** Total statistics since 2021-09-03 10:30:44 ***30 Total inserts 300.0031 Total updates 417.0032 Total deletes 216.0033 Total upserts 0.0034 Total discards 0.0035 Total operations 933.003637End of statistics.3839GGSCI (lhrogg21cmysql) 25> info all4041Program Status Group Lag at Chkpt Time Since Chkpt4243MANAGER RUNNING 44EXTRACT RUNNING EXT1 00:00:00 00:00:04 45EXTRACT RUNNING EXT2 00:00:00 00:00:09 46REPLICAT RUNNING REP1 00:00:00 00:00:01 47REPLICAT RUNNING REP2 00:00:00 00:00:07
可以看到，正常同步，观察延迟Lag at Chkpt
不超过1分钟。
天翼云到华为云rds
1[root@docker35 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --time=100 --mysql-host=114.116.245.109 \ 2> --mysql-port=3306 --mysql-user=root --mysql-password=lhr@ZR123456 --mysql-db=lhrdb \ 3> --table-size=100000 --tables=10 --threads=16 --events=999999999 --report-interval=10 \ 4> --db-ps-mode=disable --forced-shutdown=1 run 5sysbench 1.0.17 (using system LuaJIT 2.0.4) 6 7Running the test with following options: 8Number of threads: 16 9Report intermediate results every 10 second(s)10Initializing random number generator from current time1112Forcing shutdown in 101 seconds1314Initializing worker threads...1516Threads started!1718[ 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.0019[ 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.0020[ 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.0021[ 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.0022[ 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.0023[ 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.0024[ 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.0025[ 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.0026[ 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.0027[ 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.0028SQL statistics:29 queries performed:30 read: 2788831 write: 628732 other: 566533 total: 3984034 transactions: 1992 (19.77 per sec.)35 queries: 39840 (395.38 per sec.)36 ignored errors: 0 (0.00 per sec.)37 reconnects: 0 (0.00 per sec.)3839General statistics:40 total time: 100.7602s41 total number of events: 19924243Latency (ms):44 min: 467.3345 avg: 806.9046 max: 2620.8847 95th percentile: 1327.9148 sum: 1607342.954950Threads fairness:51 events (avg/stddev): 124.5000/2.4552 execution time (avg/stddev): 100.4589/0.24
在OGG检查：
1GGSCI (lhrogg21cmysql) 17> stats ext2,total,table lhrdb.sbtest1 2 3Sending STATS request to Extract group EXT2 ... 4 5Start of statistics at 2021-09-03 10:42:13. 6 7Output to ./dirdat/m9: 8 9Extracting from lhrdb.sbtest1 to lhrdb.sbtest1:1011*** Total statistics since 2021-09-03 10:28:23 ***12 Total inserts 180.0013 Total updates 263.0014 Total deletes 127.0015 Total upserts 0.0016 Total discards 0.0017 Total operations 570.001819End of statistics.2021GGSCI (lhrogg21cmysql) 24> stats rep2,total,table lhrdb.sbtest12223Sending STATS request to Replicat group REP2 ...2425Start of statistics at 2021-09-03 10:43:01.2627Replicating from lhrdb.sbtest1 to lhrdb.sbtest1:2829*** Total statistics since 2021-09-03 10:30:59 ***30 Total inserts 180.0031 Total updates 263.0032 Total deletes 127.0033 Total upserts 0.0034 Total discards 0.0035 Total operations 570.003637End of statistics.3839GGSCI (lhrogg21cmysql) 26> info all4041Program Status Group Lag at Chkpt Time Since Chkpt4243MANAGER RUNNING 44EXTRACT RUNNING EXT1 00:00:00 00:00:03 45EXTRACT RUNNING EXT2 00:00:00 00:00:09 46REPLICAT RUNNING REP1 00:00:00 00:00:01 47REPLICAT RUNNING REP2 00:00:00 00:00:05
可以看到，正常同步，观察延迟Lag at Chkpt
不超过1分钟。
扩展
当然，也可以使用如下的架构，需要添加Pump进程，但是我感觉没有必要，除非是源端和目标端距离非常远，一个在国内，一个在国外：
其他有关更多OGG的内容，请联系麦老师。
本文结束。
• 个人博客地址：www.xmmup.com
• 微信公众号：DB宝，作者：小麦苗，作者微信：db_bao
• 作者QQ：646634621，QQ群：230161599、618766405
• 提供Oracle OCP、OCM、高可用（rac+dg+ogg）、MySQL DBA和PostgreSQL DBA培训及其考证事宜
• 版权所有，欢迎分享本文，转载请保留出处• 若有侵权请联系小麦苗删除
长按下图识别二维码，关注小麦苗的微信公众号：DB宝，学习最实用的数据库技术。
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
