【DB宝79】使用OGG微服务快速双向同步RDS数据库

AiDBA宝典

发布于 2021-12-17 19:26:39

3.7K0

发布于 2021-12-17 19:26:39

问题引出

客户需要将华为云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微服务

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进程

image-20210907150953369

image-20210907151105630

先创建2个数据库身份证明：

image-20210907151324184

参数内容：

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进程

image-20210907151839064

image-20210907155131308

REPLICAT repty
TARGETDB lhrdb@114.116.245.109:3306 USERIDALIAS  mysqlrdstianyi, DOMAIN mysqlrds
MAP lhrdb.*, TARGET lhrdb.*;

点击创建，不要运行。

创建检查点表

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

过程：

[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进程

image-20210907155249571

配置天翼云到华为云的同步

创建extract进程

image-20210907160108907

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进程

image-20210907160258246

image-20210907160440577

创建检查点表

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进程

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

压测同步并查看检测数据

-- 在华为云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

在华为云侧加压

[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

image-20210907161807995

在天翼云侧加压

[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