使用OGG for mysql微服务快速双向同步RDS数据库（双主）
Tags： OGGOGG微服务RDS互为主备双主双向同步高可用
👉 本文共约2904个字，系统预计阅读时间或需11分钟。
问题引出
客户需要将华为云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
- 使用OGG微服务快速双向同步RDS数据库：https://www.xmmup.com/shiyongoggweifuwukuaisushuangxiangtongburdsshujuku.html
4、使用阿里的开源工具otter，这个方案经过验证是可行的。阿里数据同步工具Otter和Canal简介请参考：https://www.xmmup.com/alishujutongbugongjuotterhecanaljianjie.html
- otter用于跨云RDS之间配置双主实时同步参考：https://www.xmmup.com/otteryongyukuayunrdszhijianpeizhishuangzhushishitongbu.html
OGG微服务简介
OGG 微服务架构(Microservices Architecture，简称MA)起始于12.3版本，采用各种服务来管理，通过WEB接口来创建进程、启动进程以及管理底层进程等操作以及具备完善监控功能包括进程、线程等性能数据,以前非微服务架构称为经典架构(Classic Architecture)。
在OGG的MA中，基于REST API，用户通过网页就可以完成OGG服务进程配置，监控和管理全新微服务架构。
微服务架构是后续OGG发展的一个方向，经典架构可能会放弃维护。
搭建OGG微服务
1 2 3 4 5 6 7 8 | docker pull lhrbest/ogg213mamysql:v1.0 docker rm -f lhrogg213mamysql docker run -d --name lhrogg213mamysql -h lhrogg213mamysql \ -p 9389:3389 -p 9000-9005:9000-9005 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/ogg213mamysql:v1.0 \ /usr/sbin/init |
访问：http://192.168.66.35:9000 ，用户名：oggadmin，密码：lhr
- 管理服务（Admin server）: 用于创建用户、添加附加日志、创建抽取和投递进程，类似在ggsci命令下添加附加日志、extract、replicat进程
- 分发服务（Distribution server）：用于创建传输进程，类似于以前的pump进程
- 接收方服务（Receiver server）：用于监控接收进程，类似于以前的server collector进程
- 性能度量服务（Performance metrics server）：性能监控，这个里面信息非常多，例如ADMINSRVR包括进程性能，线程性能以及进程状态与配置，非常详细与直观。
配置华为云到天翼云的同步
创建extract进程
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
先创建2个数据库身份证明：
参数内容：
1 2 3 4 5 6 7 | EXTRACT exthw SOURCEDB lhrdb@124.70.97.208:3306 USERIDALIAS mysqlrdshuawei, DOMAIN mysqlrds TRANLOGOPTIONS ALTLOGDEST REMOTE EXTTRAIL hw IGNOREREPLICATES TRANLOGOPTIONS FILTERTABLE lhrdb.checkpoint TABLE lhrdb.*; |
点击创建，不要运行。
创建replicate进程
1 2 3 | REPLICAT repty TARGETDB lhrdb@114.116.245.109:3306 USERIDALIAS mysqlrdstianyi, DOMAIN mysqlrds MAP lhrdb.*, TARGET lhrdb.*; |
点击创建，不要运行。
创建检查点表
1 2 3 4 5 6 | docker exec -it lhrogg213mamysql bash su - oracle adminclient CONNECT http://127.0.0.1:9000 deployment deploy213 as oggadmin password lhr DBLOGIN USERIDALIAS mysqlrdstianyi DOMAIN mysqlrds add checkpointtable 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 | [oracle@lhrogg213mamysql ~]$ adminclient Oracle GoldenGate Administration Client for MySQL Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047 Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved. Oracle Linux 7, x64, 64bit (optimized) on Jul 28 2021 17:40:31 Operating system character set identified as UTF-8. OGG (not connected) 5> CONNECT http://127.0.0.1:9000 deployment deploy213 as oggadmin password lhr OGG (http://127.0.0.1:9000 deploy213) 6> info all Program Status Group Type Lag at Chkpt Time Since Chkpt ADMINSRVR RUNNING DISTSRVR RUNNING PMSRVR RUNNING RECVSRVR RUNNING EXTRACT STOPPED EXTHW CLASSIC 00:00:00 00:11:20 REPLICAT STOPPED REPTY NONINTEGRATED 00:00:00 00:06:32 OGG (http://127.0.0.1:9000 deploy213) 12> DBLOGIN USERIDALIAS mysqlrdstianyi DOMAIN mysqlrds Successfully logged into database. OGG (http://127.0.0.1:9000 deploy213 as mysqlrdstianyi@) 13> add checkpointtable lhrdb.checkpoint OGG (http://127.0.0.1:9000 deploy213 as mysqlrdstianyi@) 14> |
启动extract和replicate进程
配置天翼云到华为云的同步
创建extract进程
1 2 3 4 5 6 7 | EXTRACT extty SOURCEDB lhrdb@114.116.245.109:3306 USERIDALIAS mysqlrdstianyi, DOMAIN mysqlrds TRANLOGOPTIONS ALTLOGDEST REMOTE EXTTRAIL ty IGNOREREPLICATES TRANLOGOPTIONS FILTERTABLE lhrdb.checkpoint TABLE lhrdb.*; |
创建replicate进程
创建检查点表
1 2 3 4 5 6 | docker exec -it lhrogg213mamysql bash su - oracle adminclient CONNECT http://127.0.0.1:9000 deployment deploy213 as oggadmin password lhr DBLOGIN USERIDALIAS mysqlrdshuawei DOMAIN mysqlrds add checkpointtable lhrdb.checkpoint |
启动extract和replicate进程
1 2 3 4 5 6 7 8 9 10 11 | OGG (http://127.0.0.1:9000 deploy213 as mysqlrdshuawei@) 7> info all Program Status Group Type Lag at Chkpt Time Since Chkpt ADMINSRVR RUNNING DISTSRVR RUNNING PMSRVR RUNNING RECVSRVR RUNNING EXTRACT RUNNING EXTHW CLASSIC 00:00:00 00:00:07 EXTRACT RUNNING EXTTY CLASSIC 00:00:00 00:00:07 REPLICAT RUNNING REPHW NONINTEGRATED 00:00:00 00:00:05 REPLICAT RUNNING REPTY NONINTEGRATED 00:00:00 00:00:00 |
压测同步并查看检测数据
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 |
在华为云侧加压
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=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: 31.99 qps: 658.62 (r/w/o: 463.71/111.55/83.37) lat (ms,95%): 502.20 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 16 tps: 32.90 qps: 654.35 (r/w/o: 458.03/113.21/83.11) lat (ms,95%): 511.33 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 16 tps: 32.60 qps: 652.70 (r/w/o: 456.60/112.10/84.00) lat (ms,95%): 539.71 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 16 tps: 27.90 qps: 555.20 (r/w/o: 388.40/94.50/72.30) lat (ms,95%): 1013.60 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 16 tps: 25.10 qps: 501.49 (r/w/o: 350.99/85.60/64.90) lat (ms,95%): 1032.01 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 16 tps: 25.50 qps: 513.20 (r/w/o: 359.90/89.00/64.30) lat (ms,95%): 893.56 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 16 tps: 29.10 qps: 581.51 (r/w/o: 406.11/102.00/73.40) lat (ms,95%): 802.05 err/s: 0.00 reconn/s: 0.00 [ 80s ] thds: 16 tps: 28.20 qps: 565.09 (r/w/o: 396.19/96.70/72.20) lat (ms,95%): 787.74 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 16 tps: 19.50 qps: 383.10 (r/w/o: 267.10/67.00/49.00) lat (ms,95%): 1352.03 err/s: 0.00 reconn/s: 0.00 [ 100s ] thds: 16 tps: 27.60 qps: 555.30 (r/w/o: 389.00/97.40/68.90) lat (ms,95%): 977.74 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 39480 write: 9740 other: 7180 total: 56400 transactions: 2820 (28.07 per sec.) queries: 56400 (561.42 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 100.4563s total number of events: 2820 Latency (ms): min: 455.45 avg: 568.98 max: 2847.00 95th percentile: 909.80 sum: 1604531.22 Threads fairness: events (avg/stddev): 176.2500/4.04 execution time (avg/stddev): 100.2832/0.13 |
在天翼云侧加压
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: 17.99 qps: 375.04 (r/w/o: 265.19/62.07/47.78) lat (ms,95%): 1352.03 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 16 tps: 18.10 qps: 364.15 (r/w/o: 255.34/64.11/44.71) lat (ms,95%): 1427.08 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 16 tps: 17.50 qps: 344.59 (r/w/o: 239.89/61.10/43.60) lat (ms,95%): 1427.08 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 16 tps: 17.90 qps: 361.19 (r/w/o: 253.59/63.40/44.20) lat (ms,95%): 1479.41 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 16 tps: 18.10 qps: 364.60 (r/w/o: 256.00/63.20/45.40) lat (ms,95%): 1506.29 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 16 tps: 18.50 qps: 366.20 (r/w/o: 255.60/63.60/47.00) lat (ms,95%): 1401.61 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 16 tps: 17.60 qps: 355.80 (r/w/o: 249.30/63.70/42.80) lat (ms,95%): 1427.08 err/s: 0.00 reconn/s: 0.00 [ 80s ] thds: 16 tps: 18.10 qps: 357.40 (r/w/o: 249.60/63.40/44.40) lat (ms,95%): 1427.08 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 16 tps: 17.90 qps: 357.60 (r/w/o: 250.00/61.90/45.70) lat (ms,95%): 1401.61 err/s: 0.00 reconn/s: 0.00 [ 100s ] thds: 16 tps: 18.20 qps: 367.70 (r/w/o: 258.60/64.50/44.60) lat (ms,95%): 1258.08 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 25410 write: 6365 other: 4525 total: 36300 transactions: 1815 (18.00 per sec.) queries: 36300 (359.94 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 100.8479s total number of events: 1815 Latency (ms): min: 487.25 avg: 885.83 max: 2634.12 95th percentile: 1427.08 sum: 1607788.90 Threads fairness: events (avg/stddev): 113.4375/10.10 execution time (avg/stddev): 100.4868/0.23 |
其它内容不再截图。
双向同步测试完成！
