【DB宝82】使用OGG for mysql微服务搭建双主架构（含DDL）

AiDBA宝典

发布于 2021-12-20 12:37:49

1.2K0

发布于 2021-12-20 12:37:49

文章被收录于专栏：小麦苗的DB宝专栏

OGG有传统的经典架构，也有最新的微服务，2个都可以远程捕获和应用数据，对数据库服务器是0侵入，而传统的经典架构是纯命令行模式，最新的微服务架构是图形化界面操作，几乎所有操作都可以在界面进行。

相关文章可以参考：

使用OGG for MySQL微服务快速双向同步RDS数据库：https://www.xmmup.com/shiyongoggweifuwukuaisushuangxiangtongburdsshujuku.html
OGG用于跨云RDS for MySQL之间配置双主实时同步–OGG远程捕获和投递：https://www.xmmup.com/oggyongyukuayunrdszhijianpeizhishuangzhushishitongbuyuanchengbuhuohetoudi.html
otter用于跨云RDS for mysql之间配置双主实时同步：https://www.xmmup.com/otteryongyukuayunrdszhijianpeizhishuangzhushishitongbu.html
OGG用于PG数据库之间双主实时同步（RDS for PG亦可）–OGG远程捕获和投递：https://www.xmmup.com/oggyongyupgshujukuzhijianshuangzhushishitongburds-for-pgyikeoggyuanchengbuhuohetoudi.html
使用OGG for PG微服务快速双向同步RDS数据库（双主）：https://www.xmmup.com/shiyongogg-for-pgweifuwukuaisushuangxiangtongburdsshujukushuangzhu.html
使用阿里的开源工具otter，这个方案经过验证是可行的。阿里数据同步工具Otter和Canal简介请参考：https://www.xmmup.com/alishujutongbugongjuotterhecanaljianjie.html
使用OGG for Oracle微服务双向同步Oracle数据库搭建双主架构（含DDL）：https://www.xmmup.com/shiyongogg-for-oracleweifuwushuangxiangtongbuoracleshujukuhanddl.html

使用OGG微服务环境搭建

https://docs.oracle.com/en/middleware/goldengate/core/21.3/gghdb/understanding-whats-supported-mysql.html#GUID-6A22FBB5-540B-4DAA-8D76-990FB38C2B3B
https://docs.oracle.com/en/middleware/goldengate/core/21.3/gghdb/using-ddl-replication.html#GUID-8AE2F268-0C6C-4806-875F-7C5A1F23C08F

-- 创建专用网络
docker network create --subnet=172.72.5.0/24 mysql-network

-- OGG 微服务
docker pull lhrbest/ogg213mamysql:v1.0

docker rm -f lhrogg213mamysql
docker run -d --name lhrogg213mamysql -h lhrogg213mamysql \
  --net=mysql-network --ip 172.72.5.95 \
  -p 9389:3389 -p 9000-9005:9000-9005 \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true lhrbest/ogg213mamysql:v1.0 \
  /usr/sbin/init


-- MySQL 8.0.27  mysqla
docker rm -f mysqla
docker run -d --name mysqla -h mysqla  \
   -p 33690:3306 --net=mysql-network --ip 172.72.5.90 \
   -e MYSQL_ROOT_PASSWORD=lhr -e TZ=Asia/Shanghai \
   mysql:8.0.27


-- MySQL 8.0.27  mysqlb
docker rm -f mysqlb
docker run -d --name mysqlb -h mysqlb \
   -p 33691:3306 --net=mysql-network --ip 172.72.5.91 \
   -e MYSQL_ROOT_PASSWORD=lhr -e TZ=Asia/Shanghai \
   mysql:8.0.27




-- 创建数据库
mysql -uroot -plhr -h192.168.66.35 -P33690
mysql -uroot -plhr -h192.168.66.35 -P33691
create database lhrdb charset utf8mb4;
set global binlog_row_metadata=full;

alter user root@'%' identified with mysql_native_password by 'lhr';
flush privileges;

访问：http://192.168.66.35:9000 ，用户名：oggadmin，密码：lhr

管理服务（Admin server）: 用于创建用户、添加附加日志、创建抽取和投递进程，类似在ggsci命令下添加附加日志、extract、replicat进程
分发服务（Distribution server）：用于创建传输进程，类似于以前的pump进程
接收方服务（Receiver server）：用于监控接收进程，类似于以前的server collector进程
性能度量服务（Performance metrics server）：性能监控，这个里面信息非常多，例如ADMINSRVR包括进程性能，线程性能以及进程状态与配置，非常详细与直观。

配置身份证明、检查点表

配置mysqla到mysqlb的同步

创建extract进程

image-20211214173500593

参数内容：

EXTRACT exta
SOURCEDB lhrdb@172.72.5.90:3306 USERIDALIAS  mysqla, DOMAIN OGGMA
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
TRANLOGOPTIONS ALTLOGDEST REMOTE
IGNOREREPLICATES
TRANLOGOPTIONS FILTERTABLE mysql.ckpt
EXTTRAIL ./dirdat/e1
TABLE lhrdb.*;

创建replicate进程

image-20211214150810940

REPLICAT repa
TARGETDB lhrdb@172.72.5.91:3306 USERIDALIAS  mysqlb, DOMAIN OGGMA
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
MAP lhrdb.*, TARGET lhrdb.*;

校验同步

create table test(id int primary key);
insert into test values(1);
select * from test;

image-20211214174613545

可以看到，DDL和DML都可以同步。

配置mysqlb到mysqla的同步

创建extract进程

EXTRACT extb
SOURCEDB lhrdb@172.72.5.91:3306 USERIDALIAS  mysqlb, DOMAIN OGGMA
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
TRANLOGOPTIONS ALTLOGDEST REMOTE
IGNOREREPLICATES
TRANLOGOPTIONS FILTERTABLE mysql.ckpt 
EXTTRAIL ./dirdat/e2
TABLE lhrdb.*;

创建replicate进程

REPLICAT repb
TARGETDB lhrdb@172.72.5.90:3306 USERIDALIAS  mysqla, DOMAIN OGGMA
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
MAP lhrdb.*, TARGET lhrdb.*;

压测同步并查看检测数据

-- 在mysqla造数据
export LD_LIBRARY_PATH=/usr/local/gsql_client/lib
sysbench /usr/share/sysbench/oltp_common.lua --time=100 --mysql-host=172.72.5.90 \
--mysql-port=3306 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb \
--table-size=100000 --tables=10 --threads=16 --events=999999999   prepare

-- 在mysqla压测
sysbench /usr/share/sysbench/oltp_read_write.lua --time=100 --mysql-host=172.72.5.90  \
--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

-- 在mysqlb压测
sysbench /usr/share/sysbench/oltp_read_write.lua --time=100 --mysql-host=172.72.5.91  \
--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 lib]# sysbench /usr/share/sysbench/oltp_common.lua --time=100 --mysql-host=172.72.5.90 \
> --mysql-port=3306 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb \
> --table-size=100000 --tables=10 --threads=16 --events=999999999   prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest2'...
Creating table 'sbtest10'...
Creating table 'sbtest3'...
Creating table 'sbtest8'...
Creating table 'sbtest7'...
Creating table 'sbtest9'...
Creating table 'sbtest6'...
Creating table 'sbtest1'...
Creating table 'sbtest4'...
Creating table 'sbtest5'...
Inserting 100000 records into 'sbtest8'
Inserting 100000 records into 'sbtest4'
Inserting 100000 records into 'sbtest9'
Inserting 100000 records into 'sbtest6'
Inserting 100000 records into 'sbtest1'
Inserting 100000 records into 'sbtest3'
Inserting 100000 records into 'sbtest7'
Inserting 100000 records into 'sbtest10'
Inserting 100000 records into 'sbtest5'
Inserting 100000 records into 'sbtest2'
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest3'...

可以看到，数据都已经同步到mysqlb了！！

在mysqla侧加压

[root@docker35 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --time=100 --mysql-host=172.72.5.90  \
> --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.20 (using bundled LuaJIT 2.1.0-beta2)

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: 71.86 qps: 1466.45 (r/w/o: 1027.68/293.45/145.33) lat (ms,95%): 383.33 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 76.40 qps: 1529.30 (r/w/o: 1070.47/306.02/152.81) lat (ms,95%): 356.70 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 73.30 qps: 1465.98 (r/w/o: 1026.19/293.20/146.60) lat (ms,95%): 397.39 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 77.60 qps: 1551.38 (r/w/o: 1086.39/309.80/155.20) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 77.60 qps: 1551.82 (r/w/o: 1086.42/310.20/155.20) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 74.80 qps: 1496.80 (r/w/o: 1047.20/300.00/149.60) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 16 tps: 83.20 qps: 1663.61 (r/w/o: 1164.80/332.40/166.40) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 16 tps: 70.80 qps: 1416.39 (r/w/o: 991.19/283.60/141.60) lat (ms,95%): 397.39 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 16 tps: 79.80 qps: 1595.61 (r/w/o: 1117.21/318.80/159.60) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 16 tps: 80.40 qps: 1608.38 (r/w/o: 1125.58/322.00/160.80) lat (ms,95%): 320.17 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            107436
        write:                           30696
        other:                           15348
        total:                           153480
    transactions:                        7674   (76.58 per sec.)
    queries:                             153480 (1531.67 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          100.1999s
    total number of events:              7674

Latency (ms):
         min:                                   59.02
         avg:                                  208.88
         max:                                 1396.41
         95th percentile:                      337.94
         sum:                              1602983.12

Threads fairness:
    events (avg/stddev):           479.6250/3.94
    execution time (avg/stddev):   100.1864/0.02

image-20211214200403811

数据同步！！！

在mysqlb侧加压

[root@docker35 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --time=100 --mysql-host=172.72.5.91  \
> --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.20 (using bundled LuaJIT 2.1.0-beta2)

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: 73.26 qps: 1495.49 (r/w/o: 1047.96/299.42/148.11) lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 73.61 qps: 1471.83 (r/w/o: 1030.56/294.05/147.22) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 85.70 qps: 1714.00 (r/w/o: 1199.80/342.80/171.40) lat (ms,95%): 320.17 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 80.80 qps: 1587.18 (r/w/o: 1108.79/317.20/161.20) lat (ms,95%): 320.17 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 83.50 qps: 1697.73 (r/w/o: 1190.72/339.61/167.40) lat (ms,95%): 308.84 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 83.90 qps: 1679.50 (r/w/o: 1175.30/336.40/167.80) lat (ms,95%): 331.91 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 16 tps: 80.90 qps: 1618.00 (r/w/o: 1132.60/323.60/161.80) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 16 tps: 83.90 qps: 1677.30 (r/w/o: 1174.60/334.90/167.80) lat (ms,95%): 356.70 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 16 tps: 76.30 qps: 1526.69 (r/w/o: 1068.19/305.90/152.60) lat (ms,95%): 390.30 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 16 tps: 82.00 qps: 1640.01 (r/w/o: 1148.01/328.00/164.00) lat (ms,95%): 308.84 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            112770
        write:                           32220
        other:                           16110
        total:                           161100
    transactions:                        8055   (80.37 per sec.)
    queries:                             161100 (1607.35 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          100.2225s
    total number of events:              8055

Latency (ms):
         min:                                   41.42
         avg:                                  199.04
         max:                                  766.78
         95th percentile:                      344.08
         sum:                              1603296.97

Threads fairness:
    events (avg/stddev):           503.4375/2.89
    execution time (avg/stddev):   100.2061/0.03