问题引出
客户需要将华为云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.zip
2046ea37a24620534365d07df242e3528 213000_ggs_Linux_x64_MySQL_64bit.zip
安装：
1mkidr /ogg
2unzip 213000_ggs_Linux_x64_MySQL_64bit.zip
3tar -xvf ggs_Linux_x64_MySQL_64bit.tar
4./ggsci
5create subdirs
配置双向同步
注意以下问题：
1、双向同步需要配置2条链路。
2、为了避免数据回环错误，需要在extract参数中加入如下内容，且必须使用checkpoint表：
1IGNOREREPLICATES
2TRANLOGOPTIONS 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 lhr
10exttrail ./dirdat/m8
11TRANLOGOPTIONS ALTLOGDEST REMOTE
12IGNOREREPLICATES
13TRANLOGOPTIONS FILTERTABLE lhrdb.checkpoint
14table lhrdb.*;
15EOF
16
17
18add ext ext1, tranlog, begin now
19add exttrail ./dirdat/m8, ext ext1
20
21
22-- 配置replication
23cat > /ogg/dirprm/rep1.prm <<"EOF"
24replicat rep1
25targetdb lhrdb@114.116.245.109:3306 userid root password lhr
26map lhrdb.*, target lhrdb.*;
27EOF
28
29
30dblogin sourcedb lhrdb@114.116.245.109:3306 userid root password lhr
31add checkpointtable lhrdb.checkpoint
32edit params ./GLOBALS
33checkpointtable lhrdb.checkpoint
34
35add rep rep1, exttrail ./dirdat/m8, checkpointtable lhrdb.checkpoint
36
37
38GGSCI (ecs-otter-test-0001) 139> info all
39
40Program Status Group Lag at Chkpt Time Since Chkpt
41
42MANAGER 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.*;
10EOF
11
12
13add ext ext2, tranlog, begin now
14add exttrail ./dirdat/m9, ext ext2
15
16
17-- 配置replication
18cat > /ogg/dirprm/rep2.prm <<"EOF"
19replicat rep2
20targetdb lhrdb@124.70.97.208:3306 userid root password lhr
21map lhrdb.*, target lhrdb.*;
22EOF
23
24
25
26dblogin sourcedb lhrdb@124.70.97.208:3306 userid root password lhr
27add checkpointtable lhrdb.checkpoint
28edit params ./GLOBALS
29checkpointtable lhrdb.checkpoint
30
31add rep rep2, exttrail ./dirdat/m9, checkpointtable lhrdb.checkpoint
32
33
34GGSCI (ecs-otter-test-0001 DBLOGIN as root) 53> info all
35
36Program Status Group Lag at Chkpt Time Since Chkpt
37
38MANAGER 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 run
11
12-- 在天翼云
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 time
11
12Forcing shutdown in 101 seconds
13
14Initializing worker threads...
15
16Threads started!
17
18[ 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
19[ 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
20[ 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
21[ 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
22[ 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
23[ 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
24[ 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
25[ 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
26[ 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
27[ 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
28SQL statistics:
29 queries performed:
30 read: 43414
31 write: 9488
32 other: 9118
33 total: 62020
34 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.)
38
39General statistics:
40 total time: 100.4736s
41 total number of events: 3101
42
43Latency (ms):
44 min: 461.25
45 avg: 516.93
46 max: 1802.51
47 95th percentile: 733.00
48 sum: 1603008.41
49
50Threads fairness:
51 events (avg/stddev): 193.8125/3.56
52 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:
10
11*** Total statistics since 2021-09-03 10:28:22 ***
12 Total inserts 300.00
13 Total updates 417.00
14 Total deletes 216.00
15 Total upserts 0.00
16 Total discards 0.00
17 Total operations 933.00
18
19End of statistics.
20
21GGSCI (lhrogg21cmysql) 11> stats rep1,total,table lhrdb.sbtest1
22
23Sending STATS request to Replicat group REP1 ...
24
25Start of statistics at 2021-09-03 10:39:18.
26
27Replicating from lhrdb.sbtest1 to lhrdb.sbtest1:
28
29*** Total statistics since 2021-09-03 10:30:44 ***
30 Total inserts 300.00
31 Total updates 417.00
32 Total deletes 216.00
33 Total upserts 0.00
34 Total discards 0.00
35 Total operations 933.00
36
37End of statistics.
38
39GGSCI (lhrogg21cmysql) 25> info all
40
41Program Status Group Lag at Chkpt Time Since Chkpt
42
43MANAGER 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 time
11
12Forcing shutdown in 101 seconds
13
14Initializing worker threads...
15
16Threads started!
17
18[ 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
19[ 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
20[ 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
21[ 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
22[ 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
23[ 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
24[ 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
25[ 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
26[ 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
27[ 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
28SQL statistics:
29 queries performed:
30 read: 27888
31 write: 6287
32 other: 5665
33 total: 39840
34 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.)
38
39General statistics:
40 total time: 100.7602s
41 total number of events: 1992
42
43Latency (ms):
44 min: 467.33
45 avg: 806.90
46 max: 2620.88
47 95th percentile: 1327.91
48 sum: 1607342.95
49
50Threads fairness:
51 events (avg/stddev): 124.5000/2.45
52 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:
10
11*** Total statistics since 2021-09-03 10:28:23 ***
12 Total inserts 180.00
13 Total updates 263.00
14 Total deletes 127.00
15 Total upserts 0.00
16 Total discards 0.00
17 Total operations 570.00
18
19End of statistics.
20
21GGSCI (lhrogg21cmysql) 24> stats rep2,total,table lhrdb.sbtest1
22
23Sending STATS request to Replicat group REP2 ...
24
25Start of statistics at 2021-09-03 10:43:01.
26
27Replicating from lhrdb.sbtest1 to lhrdb.sbtest1:
28
29*** Total statistics since 2021-09-03 10:30:59 ***
30 Total inserts 180.00
31 Total updates 263.00
32 Total deletes 127.00
33 Total upserts 0.00
34 Total discards 0.00
35 Total operations 570.00
36
37End of statistics.
38
39GGSCI (lhrogg21cmysql) 26> info all
40
41Program Status Group Lag at Chkpt Time Since Chkpt
42
43MANAGER 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的内容，请联系麦老师。
本文结束。
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
