问题引出
客户需要将华为云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微服务
1docker pull lhrbest/ogg213mamysql:v1.0
2
3docker rm -f lhrogg213mamysql
4docker run -d --name lhrogg213mamysql -h lhrogg213mamysql \
5 -p 9389:3389 -p 9000-9005:9000-9005 \
6 -v /sys/fs/cgroup:/sys/fs/cgroup \
7 --privileged=true lhrbest/ogg213mamysql:v1.0 \
8 /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进程
先创建2个数据库身份证明:
参数内容:
1EXTRACT exthw
2SOURCEDB lhrdb@124.70.97.208:3306 USERIDALIAS mysqlrdshuawei, DOMAIN mysqlrds
3TRANLOGOPTIONS ALTLOGDEST REMOTE
4EXTTRAIL hw
5IGNOREREPLICATES
6TRANLOGOPTIONS FILTERTABLE lhrdb.checkpoint
7TABLE lhrdb.*;
点击创建,不要运行。
创建replicate进程
1REPLICAT repty
2TARGETDB lhrdb@114.116.245.109:3306 USERIDALIAS mysqlrdstianyi, DOMAIN mysqlrds
3MAP lhrdb.*, TARGET lhrdb.*;
点击创建,不要运行。
创建检查点表
1docker exec -it lhrogg213mamysql bash
2su - oracle
3adminclient
4CONNECT http://127.0.0.1:9000 deployment deploy213 as oggadmin password lhr
5DBLOGIN USERIDALIAS mysqlrdstianyi DOMAIN mysqlrds
6add checkpointtable lhrdb.checkpoint
过程:
1[oracle@lhrogg213mamysql ~]$ adminclient
2Oracle GoldenGate Administration Client for MySQL
3Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
4
5Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
6
7Oracle Linux 7, x64, 64bit (optimized) on Jul 28 2021 17:40:31
8Operating system character set identified as UTF-8.
9
10OGG (not connected) 5> CONNECT http://127.0.0.1:9000 deployment deploy213 as oggadmin password lhr
11
12OGG (http://127.0.0.1:9000 deploy213) 6> info all
13Program Status Group Type Lag at Chkpt Time Since Chkpt
14
15ADMINSRVR RUNNING
16DISTSRVR RUNNING
17PMSRVR RUNNING
18RECVSRVR RUNNING
19EXTRACT STOPPED EXTHW CLASSIC 00:00:00 00:11:20
20REPLICAT STOPPED REPTY NONINTEGRATED 00:00:00 00:06:32
21OGG (http://127.0.0.1:9000 deploy213) 12> DBLOGIN USERIDALIAS mysqlrdstianyi DOMAIN mysqlrds
22Successfully logged into database.
23
24OGG (http://127.0.0.1:9000 deploy213 as mysqlrdstianyi@) 13> add checkpointtable lhrdb.checkpoint
25OGG (http://127.0.0.1:9000 deploy213 as mysqlrdstianyi@) 14>
启动extract和replicate进程
配置天翼云到华为云的同步
创建extract进程
1EXTRACT extty
2SOURCEDB lhrdb@114.116.245.109:3306 USERIDALIAS mysqlrdstianyi, DOMAIN mysqlrds
3TRANLOGOPTIONS ALTLOGDEST REMOTE
4EXTTRAIL ty
5IGNOREREPLICATES
6TRANLOGOPTIONS FILTERTABLE lhrdb.checkpoint
7TABLE lhrdb.*;
创建replicate进程
创建检查点表
1docker exec -it lhrogg213mamysql bash
2su - oracle
3adminclient
4CONNECT http://127.0.0.1:9000 deployment deploy213 as oggadmin password lhr
5DBLOGIN USERIDALIAS mysqlrdshuawei DOMAIN mysqlrds
6add checkpointtable lhrdb.checkpoint
启动extract和replicate进程
1OGG (http://127.0.0.1:9000 deploy213 as mysqlrdshuawei@) 7> info all
2Program Status Group Type Lag at Chkpt Time Since Chkpt
3
4ADMINSRVR RUNNING
5DISTSRVR RUNNING
6PMSRVR RUNNING
7RECVSRVR RUNNING
8EXTRACT RUNNING EXTHW CLASSIC 00:00:00 00:00:07
9EXTRACT RUNNING EXTTY CLASSIC 00:00:00 00:00:07
10REPLICAT RUNNING REPHW NONINTEGRATED 00:00:00 00:00:05
11REPLICAT RUNNING REPTY NONINTEGRATED 00:00:00 00:00:00
压测同步并查看检测数据
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 --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
在华为云侧加压
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 --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: 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
19[ 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
20[ 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
21[ 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
22[ 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
23[ 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
24[ 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
25[ 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
26[ 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
27[ 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
28SQL statistics:
29 queries performed:
30 read: 39480
31 write: 9740
32 other: 7180
33 total: 56400
34 transactions: 2820 (28.07 per sec.)
35 queries: 56400 (561.42 per sec.)
36 ignored errors: 0 (0.00 per sec.)
37 reconnects: 0 (0.00 per sec.)
38
39General statistics:
40 total time: 100.4563s
41 total number of events: 2820
42
43Latency (ms):
44 min: 455.45
45 avg: 568.98
46 max: 2847.00
47 95th percentile: 909.80
48 sum: 1604531.22
49
50Threads fairness:
51 events (avg/stddev): 176.2500/4.04
52 execution time (avg/stddev): 100.2832/0.13
在天翼云侧加压
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 --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: 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
19[ 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
20[ 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
21[ 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
22[ 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
23[ 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
24[ 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
25[ 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
26[ 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
27[ 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
28SQL statistics:
29 queries performed:
30 read: 25410
31 write: 6365
32 other: 4525
33 total: 36300
34 transactions: 1815 (18.00 per sec.)
35 queries: 36300 (359.94 per sec.)
36 ignored errors: 0 (0.00 per sec.)
37 reconnects: 0 (0.00 per sec.)
38
39General statistics:
40 total time: 100.8479s
41 total number of events: 1815
42
43Latency (ms):
44 min: 487.25
45 avg: 885.83
46 max: 2634.12
47 95th percentile: 1427.08
48 sum: 1607788.90
49
50Threads fairness:
51 events (avg/stddev): 113.4375/10.10
52 execution time (avg/stddev): 100.4868/0.23
其它内容不再截图。
双向同步测试完成!