本文旨在从安装部署中了解OGG的知识
一、 OGG理论概念
1.1 简单介绍
1.2 同步过程
1.3 Oracle Golden Gate简要体系图
二、 测试实验
2.1 实验环境介绍
2.2 源端MySQL数据库配置
- 1. 创建操作系统MySQL用户
- 2. 镜像 & yum源
- 3. 安装数据库
- 4. 拷贝初始化参数文件到指定位置并修改配置文件
- 5. 设置root账户登陆密码
- 6. 查看参数是否生效
- 7. 创建即将同步的表
- 8. 安装MySQL数据库的GoldenGate软件（MySQL用户安装）
- 9. 源端配置OGG诸进程，尚不用启动进程
- 10. 目标端ogg配置
- 11. 目标端创建OGG管理用户和测试用户以及测试表
- 12. 目标端安装OGG软件
- 13. 源端配置OGG诸进程，尚不用启动进程
- 14. 按照顺序开启源端、目标端OGG进程
- 15. 模拟源端数据库业务持续运行
- 16. 查看源端、目标端数据情况
- 17. 查看目标端、远端进程信息
- 18. 进行dml测试
一、OGG理论概念
目前许多客户环境都已经在广泛使用OGG。由于工作需要，近期我也会系统的学习下OGG，目前学习主要以OGG在Oracle数据库之间的同步来说明一些基本概念，后续看时间情况可能了解OGG对其他主流数据库的同步。
1.1 简单介绍
> Oracle Golden Gate软件 是一种基于日志的结构化数据复制备份软件，它 通过解析源数据库在线日志或归档日志获得数据的增量变化，再将这些变化 应用到目标数据库，从而 实现源数据库与目标数据库同步。
> Oracle Golden Gate可以在异构的IT基础结构（包括几乎所有常用操作系统平台和数据库平台）之间实现大量数据亚秒一级的实时复制,从而可以在应急系统、在线报表、实时数据仓库供应、交易跟踪、数据同步、集中/分发、容灾、数据库升级和移植、双业务中心等多个场景下应用。
> Oracle Golden Gate可以实现一对一、广播(一对多)、聚合(多对一)、双向、点对点、级联等 多种灵活的拓扑结构。
假设已有源数据库和目标数据库，且分别部署好OGG软件。
源数据库部署的OGG，简称 源OGG；
目标数据库部署的OGG，简称 目标OGG。
1.2 同步的过程
就是源OGG的 EXTRACT进程从源数据库的 联机redologs中抽取获得变化，把它写到 本地trail文件中，然后源OGG的 Data pump进程再从 本地trail文件中抽取变化传给目标OGG的 collector进程，目标的collector进程再把这些变化写入 目标trail文件中，最后目标OGG的 Replicat进程把目标trail文件中记录的变化应用到目标数据库中。
如果要同步的表数量比较多或是业务有区分，需要分开管理，OGG是支持分开多个extract进程，多个datapump进程，多个replicat进程，这样，可以启动或停止其中任意一类同步，而不影响其他的同步。
需要注意的是，每套OGG所有的进程都是由其Manager进程来管理的，也就是说Manager进程负责管理，启动关闭其他进程。OGG用组来表示它的进程，上面简单提到的extract、replicat这些进程，实际上需要各自的参数文件、检查点文件等。这些内容共同组成一个组，且每个组需要有一个组名字。
1.3 Oracle Golden Gate的简要体系图
- OGG的检查点信息
对于源OGG只能以文件形式默认存放在GGHOME/dirchk目录下，一个组对应一个文件。
而对于目标OGG检查点信息可以存放在数据库表中，且建议存放到数据库表中。
对于源数据库，如果要使用OGG，需要配置supplemental logging，原因是OGG相当于逻辑同步，除了要记录被修改的记录，还要把能够标识这条记录的信息同时记录在日志中。这样的话，Oracle默认情况是不达标的，需要额外开启supplemental logging才可以。
具体做法是：在数据库级别开启最小附加日志，在需要同步的表级别开启详细附加日志。
# 数据库级别开启强制记录日志和最小附加日志：
SQL> alter database force logging;
SQL> alter database add supplemental log data;
#表级别开启详细附加日志（这里举例是jy用户下的t_second表）：
GGSCI> add trandata jy.t_second
对于 目标数据库，配置检查点信息到目标数据库表中，具体做法：
1.目标GG的全局参数(./globals)配置
checkpointtable ggt.chkpt
2.命令添加checkpointtable
GGSCI> add checkpointtable ggt.chkpt
此外数据库需要 启用Golden Gate复制参数：enable_goldengate_replication
SQL> alter system set enable_goldengate_replication=true;
二、测试实验
2.1 实验环境介绍
1---------- ---------- 2 | | | | 3 | | | | 4 | | | | 5 | | | | 6 | MySQL | ------------> | Oracle | 7 | | | | 8 | | | | 9 | | | | 10 ---------- ---------- 11 12Source Host :awen001(MySQL) Target Host :awen002(Oracle) 13IP Addr :192.168.1.21 IP Addr :192.168.1.22 14Manager Account:root(MySQL) Manager Account:ggt_admin(Oracle) 15Srouce DB :test01(MySQL) Target Account :result01(Oracle) 16Source Table :Mar01 Target Table :Mar02 17OS :CentOS 7.3 OS :CentOS 7.3
OGG安装文件
1191003_ggs_Linux_x64_MySQL_64bit.zip 2191004_fbo_ggs_Linux_x64_shiphome.zip
2.2 源端MySQL数据库配置
1. 创建操作系统MySQL用户
1userdel mysqlgroupadd -g 1007 mysql 2useradd -u 1103 -g mysql mysql 3echo "mysql" | passwd --stdin mysql 4id mysql
特别声明：创建MySQL用户的目的是要把Oracle Golden Gate安装到MySQL用户下，否则当OGG读取MySQL二进制索引文件
[log-bin-index=/var/lib/mysql/binary-log.index]的时候，会因为没有相关权限而影响OGG的部署。
2. 镜像 & yum源
查看服务器挂载镜像以及yum源配置
1[root@awen001 ~]# df -h | grep CentOS 2/dev/sr0 4.1G 4.1G 0 100% /run/media/root/CentOS 7 x86_64 3[root@awen001 ~]# cat /etc/yum.repos.d/yum.repo 4[redhat7.6] 5name=redhat7.6 6baseurl=file:///run/media/root/CentOS\ 7\ x86_64/ 7gpgcheck=0 8enabled=1 9[root@awen001 ~]# yum repolist 10已加载插件：fastestmirror, langpacks 11Loading mirror speeds from cached hostfile 12源标识 源名称 状态 13!redhat7.6 redhat7.6 3,831 14repolist: 3,831
3. 安装数据库
CentOS 7自带MariaDB替代默认的MySQL客户端安装包。MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。
yum install -y mariadb*
1[root@awen001 ~]# yum install -y mariadb* 2已加载插件：fastestmirror, langpacks 3redhat7.6 | 3.6 kB 00:00:00 4Loading mirror speeds from cached hostfile 5软件包 1:mariadb-libs-5.5.52-1.el7.x86_64 已安装并且是最新版本 6正在解决依赖关系 7--> 正在检查事务 8---> 软件包 mariadb.x86_64.1.5.5.52-1.el7 将被 安装 9---> 软件包 mariadb-bench.x86_64.1.5.5.52-1.el7 将被 安装 10--> 正在处理依赖关系 perl(GD)，它被软件包 1:mariadb-bench-5.5.52-1.el7.x86_64 需要 11--> 正在处理依赖关系 perl(DBI)，它被软件包 1:mariadb-bench-5.5.52-1.el7.x86_64 需要 12---> 软件包 mariadb-devel.x86_64.1.5.5.52-1.el7 将被 安装 13--> 正在处理依赖关系 openssl-devel(x86-64)，它被软件包 1:mariadb-devel-5.5.52-1.el7.x86_64 需要 14---> 软件包 mariadb-server.x86_64.1.5.5.52-1.el7 将被 安装 15--> 正在处理依赖关系 perl-DBD-MySQL，它被软件包 1:mariadb-server-5.5.52-1.el7.x86_64 需要 16---> 软件包 mariadb-test.x86_64.1.5.5.52-1.el7 将被 安装 17--> 正在处理依赖关系 perl(Test::More)，它被软件包 1:mariadb-test-5.5.52-1.el7.x86_64 需要 18--> 正在处理依赖关系 perl(Env)，它被软件包 1:mariadb-test-5.5.52-1.el7.x86_64 需要 19--> 正在检查事务 20---> 软件包 openssl-devel.x86_64.1.1.0.1e-60.el7 将被 安装 21--> 正在处理依赖关系 krb5-devel(x86-64)，它被软件包 1:openssl-devel-1.0.1e-60.el7.x86_64 需要 22---> 软件包 perl-DBD-MySQL.x86_64.0.4.023-5.el7 将被 安装 23---> 软件包 perl-DBI.x86_64.0.1.627-4.el7 将被 安装 24--> 正在处理依赖关系 perl(RPC::PlServer) >= 0.2001，它被软件包 perl-DBI-1.627-4.el7.x86_64 需要 25--> 正在处理依赖关系 perl(RPC::PlClient) >= 0.2000，它被软件包 perl-DBI-1.627-4.el7.x86_64 需要 26---> 软件包 perl-Env.noarch.0.1.04-2.el7 将被 安装 27---> 软件包 perl-GD.x86_64.0.2.49-3.el7 将被 安装 28---> 软件包 perl-Test-Simple.noarch.0.0.98-243.el7 将被 安装 29--> 正在检查事务 30---> 软件包 krb5-devel.x86_64.0.1.14.1-26.el7 将被 安装 31--> 正在处理依赖关系 libverto-devel，它被软件包 krb5-devel-1.14.1-26.el7.x86_64 需要 32--> 正在处理依赖关系 libselinux-devel，它被软件包 krb5-devel-1.14.1-26.el7.x86_64 需要 33--> 正在处理依赖关系 libcom_err-devel，它被软件包 krb5-devel-1.14.1-26.el7.x86_64 需要 34--> 正在处理依赖关系 keyutils-libs-devel，它被软件包 krb5-devel-1.14.1-26.el7.x86_64 需要 35---> 软件包 perl-PlRPC.noarch.0.0.2020-14.el7 将被 安装 36--> 正在处理依赖关系 perl(Net::Daemon) >= 0.13，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要 37--> 正在处理依赖关系 perl(Net::Daemon::Test)，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要 38--> 正在处理依赖关系 perl(Net::Daemon::Log)，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要 39--> 正在处理依赖关系 perl(Compress::Zlib)，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要 40--> 正在检查事务 41---> 软件包 keyutils-libs-devel.x86_64.0.1.5.8-3.el7 将被 安装 42---> 软件包 libcom_err-devel.x86_64.0.1.42.9-9.el7 将被 安装 43---> 软件包 libselinux-devel.x86_64.0.2.5-6.el7 将被 安装 44--> 正在处理依赖关系 libsepol-devel(x86-64) >= 2.5-6，它被软件包 libselinux-devel-2.5-6.el7.x86_64 需要 45--> 正在处理依赖关系 pkgconfig(libsepol)，它被软件包 libselinux-devel-2.5-6.el7.x86_64 需要 46--> 正在处理依赖关系 pkgconfig(libpcre)，它被软件包 libselinux-devel-2.5-6.el7.x86_64 需要 47---> 软件包 libverto-devel.x86_64.0.0.2.5-4.el7 将被 安装 48---> 软件包 perl-IO-Compress.noarch.0.2.061-2.el7 将被 安装 49--> 正在处理依赖关系 perl(Compress::Raw::Zlib) >= 2.061，它被软件包 perl-IO-Compress-2.061-2.el7.noarch 需要 50--> 正在处理依赖关系 perl(Compress::Raw::Bzip2) >= 2.061，它被软件包 perl-IO-Compress-2.061-2.el7.noarch 需要 51---> 软件包 perl-Net-Daemon.noarch.0.0.48-5.el7 将被 安装 52--> 正在检查事务 53---> 软件包 libsepol-devel.x86_64.0.2.5-6.el7 将被 安装 54---> 软件包 pcre-devel.x86_64.0.8.32-15.el7_2.1 将被 安装 55---> 软件包 perl-Compress-Raw-Bzip2.x86_64.0.2.061-3.el7 将被 安装 56---> 软件包 perl-Compress-Raw-Zlib.x86_64.1.2.061-4.el7 将被 安装 57--> 解决依赖关系完成 58 59依赖关系解决 60 61========================================================================================================================================================== 62 Package 架构 版本 源 大小 63========================================================================================================================================================== 64正在安装: 65 mariadb x86_64 1:5.5.52-1.el7 redhat7.6 8.7 M 66 mariadb-bench x86_64 1:5.5.52-1.el7 redhat7.6 387 k 67 mariadb-devel x86_64 1:5.5.52-1.el7 redhat7.6 750 k 68 mariadb-server x86_64 1:5.5.52-1.el7 redhat7.6 11 M 69 mariadb-test x86_64 1:5.5.52-1.el7 redhat7.6 8.1 M 70为依赖而安装: 71 keyutils-libs-devel x86_64 1.5.8-3.el7 redhat7.6 37 k 72 krb5-devel x86_64 1.14.1-26.el7 redhat7.6 651 k 73 libcom_err-devel x86_64 1.42.9-9.el7 redhat7.6 31 k 74 libselinux-devel x86_64 2.5-6.el7 redhat7.6 186 k 75 libsepol-devel x86_64 2.5-6.el7 redhat7.6 74 k 76 libverto-devel x86_64 0.2.5-4.el7 redhat7.6 12 k 77 openssl-devel x86_64 1:1.0.1e-60.el7 redhat7.6 1.2 M 78 pcre-devel x86_64 8.32-15.el7_2.1 redhat7.6 479 k 79 perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 redhat7.6 32 k 80 perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 redhat7.6 57 k 81 perl-DBD-MySQL x86_64 4.023-5.el7 redhat7.6 140 k 82 perl-DBI x86_64 1.627-4.el7 redhat7.6 802 k 83 perl-Env noarch 1.04-2.el7 redhat7.6 16 k 84 perl-GD x86_64 2.49-3.el7 redhat7.6 173 k 85 perl-IO-Compress noarch 2.061-2.el7 redhat7.6 260 k 86 perl-Net-Daemon noarch 0.48-5.el7 redhat7.6 51 k 87 perl-PlRPC noarch 0.2020-14.el7 redhat7.6 36 k 88 perl-Test-Simple noarch 0.98-243.el7 redhat7.6 170 k 89 90事务概要 91========================================================================================================================================================== 92安装 5 软件包 (+18 依赖软件包) 93 94总下载量：33 M 95安装大小：244 M 96Downloading packages: 97---------------------------------------------------------------------------------------------------------------------------------------------------------- 98总计 14 MB/s | 33 MB 00:00:02 99Running transaction check 100Running transaction test 101Transaction test succeeded 102Running transaction 103 正在安装 : 1:mariadb-5.5.52-1.el7.x86_64 1/23 104 正在安装 : perl-Net-Daemon-0.48-5.el7.noarch 2/23 105 正在安装 : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 3/23 106 正在安装 : libverto-devel-0.2.5-4.el7.x86_64 4/23 107 正在安装 : perl-GD-2.49-3.el7.x86_64 5/23 108 正在安装 : perl-Test-Simple-0.98-243.el7.noarch 6/23 109 正在安装 : libcom_err-devel-1.42.9-9.el7.x86_64 7/23 110 正在安装 : perl-Env-1.04-2.el7.noarch 8/23 111 正在安装 : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 9/23 112 正在安装 : perl-IO-Compress-2.061-2.el7.noarch 10/23 113 正在安装 : perl-PlRPC-0.2020-14.el7.noarch 11/23 114 正在安装 : perl-DBI-1.627-4.el7.x86_64 12/23 115 正在安装 : perl-DBD-MySQL-4.023-5.el7.x86_64 13/23 116 正在安装 : 1:mariadb-server-5.5.52-1.el7.x86_64 14/23 117 正在安装 : pcre-devel-8.32-15.el7_2.1.x86_64 15/23 118 正在安装 : libsepol-devel-2.5-6.el7.x86_64 16/23 119 正在安装 : libselinux-devel-2.5-6.el7.x86_64 17/23 120 正在安装 : keyutils-libs-devel-1.5.8-3.el7.x86_64 18/23 121 正在安装 : krb5-devel-1.14.1-26.el7.x86_64 19/23 122 正在安装 : 1:openssl-devel-1.0.1e-60.el7.x86_64 20/23 123 正在安装 : 1:mariadb-devel-5.5.52-1.el7.x86_64 21/23 124 正在安装 : 1:mariadb-test-5.5.52-1.el7.x86_64 22/23 125 正在安装 : 1:mariadb-bench-5.5.52-1.el7.x86_64 23/23 126 验证中 : 1:mariadb-test-5.5.52-1.el7.x86_64 1/23 127 验证中 : keyutils-libs-devel-1.5.8-3.el7.x86_64 2/23 128 验证中 : 1:openssl-devel-1.0.1e-60.el7.x86_64 3/23 129 验证中 : 1:mariadb-devel-5.5.52-1.el7.x86_64 4/23 130 验证中 : libsepol-devel-2.5-6.el7.x86_64 5/23 131 验证中 : pcre-devel-8.32-15.el7_2.1.x86_64 6/23 132 验证中 : libselinux-devel-2.5-6.el7.x86_64 7/23 133 验证中 : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 8/23 134 验证中 : perl-Env-1.04-2.el7.noarch 9/23 135 验证中 : libcom_err-devel-1.42.9-9.el7.x86_64 10/23 136 验证中 : perl-Test-Simple-0.98-243.el7.noarch 11/23 137 验证中 : perl-GD-2.49-3.el7.x86_64 12/23 138 验证中 : libverto-devel-0.2.5-4.el7.x86_64 13/23 139 验证中 : 1:mariadb-server-5.5.52-1.el7.x86_64 14/23 140 验证中 : 1:mariadb-bench-5.5.52-1.el7.x86_64 15/23 141 验证中 : perl-DBI-1.627-4.el7.x86_64 16/23 142 验证中 : perl-DBD-MySQL-4.023-5.el7.x86_64 17/23 143 验证中 : 1:mariadb-5.5.52-1.el7.x86_64 18/23 144 验证中 : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 19/23 145 验证中 : perl-Net-Daemon-0.48-5.el7.noarch 20/23 146 验证中 : perl-IO-Compress-2.061-2.el7.noarch 21/23 147 验证中 : perl-PlRPC-0.2020-14.el7.noarch 22/23 148 验证中 : krb5-devel-1.14.1-26.el7.x86_64 23/23 149 150已安装: 151 mariadb.x86_64 1:5.5.52-1.el7 mariadb-bench.x86_64 1:5.5.52-1.el7 mariadb-devel.x86_64 1:5.5.52-1.el7 mariadb-server.x86_64 1:5.5.52-1.el7 152 mariadb-test.x86_64 1:5.5.52-1.el7 153 154作为依赖被安装: 155 keyutils-libs-devel.x86_64 0:1.5.8-3.el7 krb5-devel.x86_64 0:1.14.1-26.el7 libcom_err-devel.x86_64 0:1.42.9-9.el7 156 libselinux-devel.x86_64 0:2.5-6.el7 libsepol-devel.x86_64 0:2.5-6.el7 libverto-devel.x86_64 0:0.2.5-4.el7 157 openssl-devel.x86_64 1:1.0.1e-60.el7 pcre-devel.x86_64 0:8.32-15.el7_2.1 perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 158 perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 perl-DBD-MySQL.x86_64 0:4.023-5.el7 perl-DBI.x86_64 0:1.627-4.el7 159 perl-Env.noarch 0:1.04-2.el7 perl-GD.x86_64 0:2.49-3.el7 perl-IO-Compress.noarch 0:2.061-2.el7 160 perl-Net-Daemon.noarch 0:0.48-5.el7 perl-PlRPC.noarch 0:0.2020-14.el7 perl-Test-Simple.noarch 0:0.98-243.el7 161 162完毕！
View Code
4. 拷贝初始化参数文件到指定位置并修改配置文件
1mv /etc/my.cnf /etc/my.cnf.bak 2cp /usr/share/mysql/my-medium.cnf /etc/my.cnf 3--参数文件
1# Example MariaDB config file for medium systems. 2# 3# This is for a system with little memory (32M - 64M) where MariaDB plays 4# an important part, or systems up to 128M where MariaDB is used together with 5# other programs (such as a web server) 6# 7# MariaDB programs look for option files in a set of 8# locations which depend on the deployment platform. 9# You can copy this option file to one of those 10# locations. For information about these locations, do: 11# 'my_print_defaults --help' and see what is printed under 12# Default options are read from the following files in the given order: 13# More information at: http://dev.mysql.com/doc/mysql/en/option-files.html 14# 15# In this file, you can use all long options that a program supports. 16# If you want to know which options a program supports, run the program 17# with the "--help" option. 18 19# The following options will be passed to all MariaDB clients 20[client] 21#password = your_password 22port = 3306 23socket = /var/lib/mysql/mysql.sock 24 25# Here follows entries for some specific programs 26 27# The MariaDB server 28[mysqld] 29port = 3306 30socket = /var/lib/mysql/mysql.sock 31skip-external-locking 32key_buffer_size = 16M 33max_allowed_packet = 1M 34table_open_cache = 64 35sort_buffer_size = 512K 36net_buffer_length = 8K 37read_buffer_size = 256K 38read_rnd_buffer_size = 512K 39myisam_sort_buffer_size = 8M 40 41# Point the following paths to different dedicated disks 42#tmpdir = /tmp/ 43 44# Don't listen on a TCP/IP port at all. This can be a security enhancement, 45# if all processes that need to connect to mysqld run on the same host. 46# All interaction with mysqld must be made via Unix sockets or named pipes. 47# Note that using this option without enabling named pipes on Windows 48# (via the "enable-named-pipe" option) will render mysqld useless! 49# 50#skip-networking 51 52# Replication Master Server (default) 53# binary logging is required for replication 54log-bin=mysql-bin 55log-bin-index=/var/lib/mysql/binary-log.index 56 57# binary logging format - mixed recommended 58binlog_format=row 59 60# required unique id between 1 and 2^32 - 1 61# defaults to 1 if master-host is not set 62# but will not function as a master if omitted 63server-id = 1 64 65# Replication Slave (comment out master section to use this) 66# 67# To configure this host as a replication slave, you can choose between 68# two methods : 69# 70# 1) Use the CHANGE MASTER TO command (fully described in our manual) - 71# the syntax is: 72# 73# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, 74# MASTER_USER=<user>, MASTER_PASSWORD=<password> ; 75# 76# where you replace <host>, <user>, <password> by quoted strings and 77# <port> by the master's port number (3306 by default). 78# 79# Example: 80# 81# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, 82# MASTER_USER='joe', MASTER_PASSWORD='secret'; 83# 84# OR 85# 86# 2) Set the variables below. However, in case you choose this method, then 87# start replication for the first time (even unsuccessfully, for example 88# if you mistyped the password in master-password and the slave fails to 89# connect), the slave will create a master.info file, and any later 90# change in this file to the variables' values below will be ignored and 91# overridden by the content of the master.info file, unless you shutdown 92# the slave server, delete master.info and restart the slaver server. 93# For that reason, you may want to leave the lines below untouched 94# (commented) and instead use CHANGE MASTER TO (see above) 95# 96# required unique id between 2 and 2^32 - 1 97# (and different from the master) 98# defaults to 2 if master-host is set 99# but will not function as a slave if omitted 100#server-id = 2 101# 102# The replication master for this slave - required 103#master-host = <hostname> 104# 105# The username the slave will use for authentication when connecting 106# to the master - required 107#master-user = <username> 108# 109# The password the slave will authenticate with when connecting to 110# the master - required 111#master-password = <password> 112# 113# The port the master is listening on. 114# optional - defaults to 3306 115#master-port = <port> 116# 117# binary logging - not required for slaves, but recommended 118#log-bin=mysql-bin 119 120# Uncomment the following if you are using InnoDB tables 121#innodb_data_home_dir = /var/lib/mysql 122#innodb_data_file_path = ibdata1:10M:autoextend 123#innodb_log_group_home_dir = /var/lib/mysql 124# You can set .._buffer_pool_size up to 50 - 80 % 125# of RAM but beware of setting memory usage too high 126#innodb_buffer_pool_size = 16M 127#innodb_additional_mem_pool_size = 2M 128# Set .._log_file_size to 25 % of buffer pool size 129#innodb_log_file_size = 5M 130#innodb_log_buffer_size = 8M 131#innodb_flush_log_at_trx_commit = 1 132#innodb_lock_wait_timeout = 50 133 134[mysqldump] 135quick 136max_allowed_packet = 16M 137 138[mysql] 139no-auto-rehash 140# Remove the next comment character if you are not familiar with SQL 141#safe-updates 142 143[myisamchk] 144key_buffer_size = 20M 145sort_buffer_size = 20M 146read_buffer = 2M 147write_buffer = 2M 148 149[mysqlhotcopy] 150interactive-timeout
View Code
1--MariaDB启停命令 2systemctl stop mariadb.service 3systemctl start mariadb.service 4systemctl status mariadb.service
1[root@awen001 ~]# systemctl status mariadb.service 2● mariadb.service - MariaDB database server 3 Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled) 4 Active: inactive (dead) 5[root@awen001 ~]# 6[root@awen001 ~]# 7[root@awen001 ~]# 8[root@awen001 ~]# systemctl start mariadb.service 9[root@awen001 ~]# systemctl status mariadb.service 10● mariadb.service - MariaDB database server 11 Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled) 12 Active: active (running) since 四 2020-03-26 14:51:37 CST; 1s ago 13 Process: 3667 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS) 14 Process: 3590 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS) 15 Main PID: 3666 (mysqld_safe) 16 CGroup: /system.slice/mariadb.service 17 ├─3666 /bin/sh /usr/bin/mysqld_safe --basedir=/usr 18 └─3949 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/awen001.u... 19 203月 26 14:51:35 awen001.us.oracle.com mariadb-prepare-db-dir[3590]: The latest information about MariaDB is available at http://mariadb.org/. 213月 26 14:51:35 awen001.us.oracle.com mariadb-prepare-db-dir[3590]: You can find additional information about the MySQL part at: 223月 26 14:51:35 awen001.us.oracle.com mariadb-prepare-db-dir[3590]: http://dev.mysql.com 233月 26 14:51:35 awen001.us.oracle.com mariadb-prepare-db-dir[3590]: Support MariaDB development by buying support/new features from MariaDB 243月 26 14:51:35 awen001.us.oracle.com mariadb-prepare-db-dir[3590]: Corporation Ab. You can contact us about this at sales@mariadb.com. 253月 26 14:51:35 awen001.us.oracle.com mariadb-prepare-db-dir[3590]: Alternatively consider joining our community based development effort: 263月 26 14:51:35 awen001.us.oracle.com mariadb-prepare-db-dir[3590]: http://mariadb.com/kb/en/contributing-to-the-mariadb-project/ 273月 26 14:51:35 awen001.us.oracle.com mysqld_safe[3666]: 200326 14:51:35 mysqld_safe Logging to '/var/lib/mysql/awen001.us.oracle.com.err'. 283月 26 14:51:35 awen001.us.oracle.com mysqld_safe[3666]: 200326 14:51:35 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 293月 26 14:51:37 awen001.us.oracle.com systemd[1]: Started MariaDB database server.
View Code
5. 设置root账户登陆密码
/usr/bin/mysql -u root -S /var/lib/mysql/mysql.sockset password for 'root'@'localhost'=password('123');flush privileges;
6. 查看参数是否生效
1show variables like 'log_bin'; 2show master status;show variables like '%binlog%';
1MariaDB [(none)]> show variables like 'log_bin'; 2+---------------+-------+ 3| Variable_name | Value | 4+---------------+-------+ 5| log_bin | ON | 6+---------------+-------+ 71 row in set (0.00 sec) 8 9MariaDB [(none)]> show master status; 10+------------------+----------+--------------+------------------+ 11| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 12+------------------+----------+--------------+------------------+ 13| mysql-bin.000003 | 458 | | | 14+------------------+----------+--------------+------------------+ 151 row in set (0.00 sec) 16 17MariaDB [(none)]> show variables like '%binlog%'; 18+-----------------------------------------+----------------------+ 19| Variable_name | Value | 20+-----------------------------------------+----------------------+ 21| binlog_annotate_row_events | OFF | 22| binlog_cache_size | 32768 | 23| binlog_checksum | NONE | 24| binlog_direct_non_transactional_updates | OFF | 25| binlog_format | ROW | 26| binlog_optimize_thread_scheduling | ON | 27| binlog_stmt_cache_size | 32768 | 28| innodb_locks_unsafe_for_binlog | OFF | 29| max_binlog_cache_size | 18446744073709547520 | 30| max_binlog_size | 1073741824 | 31| max_binlog_stmt_cache_size | 18446744073709547520 | 32| sync_binlog | 0 | 33+-----------------------------------------+----------------------+ 3412 rows in set (0.01 sec)
View Code
7. 创建即将同步的表
1create database test01; 2use test01; 3show tables; 4create table Mar01 (id int,name varchar(10)) engine=innodb;
查看表结构和表属性
1MariaDB [test01]> show table status from test01 like 'Mar01'; 2+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ 3| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | 4+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ 5| Mar01 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 10485760 | NULL | 2020-03-26 14:55:48 | NULL | NULL | latin1_swedish_ci | NULL | | | 6+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ 71 row in set (0.00 sec)
View Code
8. 安装MySQL数据库的GoldenGate软件（MySQL用户安装）
创建安装目录并解压安装包
1su - mysql 2mkdir -p /home/mysql/ogg 3mv ggs_Linux_x64_MySQL_64bit.tar ogg/ 4cd ogg/ 5tar -xvf ggs_Linux_x64_MySQL_64bit.tar
9. 源端配置OGG诸进程，尚不用启动进程
配置进程（组）名没有具体的规范：
对于复杂环境，第一位字母，local、immdiate、remote 分别对应：l、i、r；
后面根据进程属性属于extract、datapump、replicat 而分别对应：x、p、r；[mysql@awen001 ogg]$ ./ggsci
1Oracle GoldenGate Command Interpreter for MySQL 2Version 19.1.0.0.3 OGGCORE_19.1.0.0.0_PLATFORMS_190907.0144 3Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 7 2019 08:41:32 4Operating system character set identified as UTF-8. 5Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. 6 7GGSCI (awen001.us.oracle.com) 1> dblogin sourcedb test01@localhost:3306,userid root,password 123 8Successfully logged into database. 9 10GGSCI (awen001.us.oracle.com DBLOGIN as root) 2> CREATE SUBDIRS 11 12Creating subdirectories under current directory /home/mysql/ogg 13 14Parameter file /home/mysql/ogg/dirprm: created. 15Report file /home/mysql/ogg/dirrpt: created. 16Checkpoint file /home/mysql/ogg/dirchk: created. 17Process status files /home/mysql/ogg/dirpcs: created. 18SQL script files /home/mysql/ogg/dirsql: created. 19Database definitions files /home/mysql/ogg/dirdef: created. 20Extract data files /home/mysql/ogg/dirdat: created. 21Temporary files /home/mysql/ogg/dirtmp: created. 22Credential store files /home/mysql/ogg/dircrd: created. 23Masterkey wallet files /home/mysql/ogg/dirwlt: created. 24Dump files /home/mysql/ogg/dirdmp: created. 25 26GGSCI (awen001.us.oracle.com DBLOGIN as root) 3> edit params mgr 27 28GGSCI (awen001.us.oracle.com DBLOGIN as root) 4> view params mgr 29 30port 7810 ---指定mgr进程通信端口 31dynamicportlist 7800-8000 ---表示mgr进程可以为源与目的端动态通信指定端口 32autorestart extract *,waitminutes 2,resetminutes 5 ---表示自动重启extract进程组，每2分钟尝试重启所有进程，重试5次 33 34GGSCI (awen001.us.oracle.com DBLOGIN as root) 5> start mgr 35Manager started. 36 37GGSCI (awen001.us.oracle.com DBLOGIN as root) 6> info mgr 38Manager is running (IP port TCP:awen001.us.oracle.com.7810, Process ID 8021). 39 40GGSCI (awen001.us.oracle.com DBLOGIN as root) 7> info all 41 42Program Status Group Lag at Chkpt Time Since Chkpt 43 44MANAGER RUNNING 45 46GGSCI (awen001.us.oracle.com DBLOGIN as root) 8> edit param lxggs1 47 48GGSCI (awen001.us.oracle.com DBLOGIN as root) 9> view params lxggs1 49 50extract lxggs1 ---定义抓取进程名称 51setenv (MYSQL_HOME=”/var/lib/mysql”) ---设置系统环境变量，指定MYSSL_HOME 52setenv (MYSQL_UNIX_PORT=”/var/lib/mysql/mysql.sock") ---设置系统环境变量，指定MYSQL_UNIX_PORT 53tranlogoptions altlogdest /var/lib/mysql/binary-log.index ---抓取进程使用指定地址抓取binary log日志 54sourcedb test01@localhost:3306,userid root,password 123 ---指定OGG登录的数据库和端口，使用的数据库用户名及密码 55exttrail ./dirdat/e2 ---指定本地trail文件地址 56dynamicresolution ---动态解析表名 57gettruncates ---开启捕获truncate操作，两端都需要开启 58table test01.Mar01; ---定义抓取的表 59 60GGSCI (awen001.us.oracle.com DBLOGIN as root) 10> add extract lxggs1,tranlog,begin now 61EXTRACT added. 62 63GGSCI (awen001.us.oracle.com DBLOGIN as root) 11> add exttrail ./dirdat/e2,extract lxggs1 64EXTTRAIL added. 65 66GGSCI (awen001.us.oracle.com DBLOGIN as root) 12> edit params lpggs1 67 68GGSCI (awen001.us.oracle.com DBLOGIN as root) 13> view params lpggs1 69 70extract lpggs1 ---定义投递进程名称 71rmthost 192.168.1.22,mgrport 7810 ---灾备端主机IP，管理进程端口号 72rmttrail /u01/app/ogg/dirdat/e2 ---灾备端保存队列文件的目录 73passthru ---不与数据库交互，前提是源库与目标库objects必须一致 74gettruncates ---开启捕获truncate操作，两端都需要开启 75table test01.Mar01; ---定义投递的表 76 77GGSCI (awen001.us.oracle.com DBLOGIN as root) 14> add extract lpggs1,exttrailsource ./dirdat/e2 78EXTRACT added. 79 80GGSCI (awen001.us.oracle.com DBLOGIN as root) 15> add rmttrail /u01/app/ogg/dirdat/e2,extract lpggs1 81RMTTRAIL added. 82 83GGSCI (awen001.us.oracle.com DBLOGIN as root) 16> info all 84 85Program Status Group Lag at Chkpt Time Since Chkpt 86 87MANAGER RUNNING 88EXTRACT STOPPED LPGGS1 00:00:00 00:00:19 89EXTRACT STOPPED LXGGS1 00:00:00 00:01:08 90 91GGSCI (awen001.us.oracle.com DBLOGIN as root) 17> edit params defgen 92 93GGSCI (awen001.us.oracle.com DBLOGIN as root) 18> view params defgen 94 95defsfile ./dirdef/defgen.prm 96sourcedb test01@localhost:3306,userid root,password 123 97table test01.Mar01; 98 99GGSCI (awen001.us.oracle.com DBLOGIN as root) 19> exit 100[mysql@awen001 ogg]$ ./defgen paramfile dirprm/defgen.prm 101 102*********************************************************************** 103 Oracle GoldenGate Table Definition Generator for MySQL 104 Version 19.1.0.0.3 OGGCORE_19.1.0.0.0_PLATFORMS_190907.0144 105 Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 7 2019 08:46:10 106 107Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. 108 109 Starting at 2020-03-26 15:10:03 110*********************************************************************** 111 112Operating System Version: 113Linux 114Version #1 SMP Tue Nov 22 16:42:41 UTC 2016, Release 3.10.0-514.el7.x86_64 115Node: awen001.us.oracle.com 116Machine: x86_64 117 soft limit hard limit 118Address Space Size : unlimited unlimited 119Heap Size : unlimited unlimited 120File Size : unlimited unlimited 121CPU Time : unlimited unlimited 122 123Process id: 8062 124 125*********************************************************************** 126** Running with the following parameters ** 127*********************************************************************** 128defsfile ./dirdef/defgen.prm 129sourcedb test01@localhost:3306,userid root,password *** 130table test01.Mar01; 131Retrieving definition for test01.Mar01. 132 133Definitions generated for 1 table in ./dirdef/defgen.prm. 134 135[mysql@awen001 ogg]$ exit 136登出 137[mysql@awen001 ogg]$ scp dirdef/defgen.prm oracle@192.168.1.22:/u01/app/ 138The authenticity of host '192.168.1.22 (192.168.1.22)' can't be established. 139ECDSA key fingerprint is 41:d6:1f:36:5a:6b:be:d9:47:ee:aa:37:05:2b:de:8a. 140Are you sure you want to continue connecting (yes/no)? yes 141Warning: Permanently added '192.168.1.22' (ECDSA) to the list of known hosts. 142oracle@192.168.1.22's password: 143defgen.prm 100% 1198 1.2KB/s 00:00 144[mysql@awen001 ogg]$
10. 目标端ogg配置
1--创建OGG数据目录 2mkdir -p /u01/app/ogg 3 4--在目标端创建完OGG子目录以后，将defgen文件复制到指定位置 5mv defgen.prm ogg/dirdef 6 7--开启监听 8lsnrctl status 9--数据库开启GoldenGate参数 10alter system set enable_goldengate_replication=true sid='*';
11. 目标端创建OGG管理用户和测试用户以及测试表
1--创建OGG管理用户 2create user ggt_admin identified by ggt_admin; 3grant dba to ggt_admin; 4 5--创建OGG测试用户 6create user result01 identified by result01; 7grant resource,connect to result01; 8 9--在该用户下创建测试表 10conn result01/result01 11create table result01.Mar02(id int,name char(10));
12. 目标端安装OGG软件
选择11g OGG软件，下一步：
选择软件安装路径，继续：
配置无误，点击下一步：
13. 源端配置OGG诸进程，尚不用启动进程
1[oracle@awen002 ogg]$ ./ggsci 2 3Oracle GoldenGate Command Interpreter for Oracle 4Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO 5Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12 6Operating system character set identified as US-ASCII. 7Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. 8 9GGSCI (awen002.us.oracle.com) 1> CREATE SUBDIRS 10 11Creating subdirectories under current directory /u01/app/ogg 12 13Parameter file /u01/app/ogg/dirprm: created. 14Report file /u01/app/ogg/dirrpt: created. 15Checkpoint file /u01/app/ogg/dirchk: created. 16Process status files /u01/app/ogg/dirpcs: created. 17SQL script files /u01/app/ogg/dirsql: created. 18Database definitions files /u01/app/ogg/dirdef: created. 19Extract data files /u01/app/ogg/dirdat: created. 20Temporary files /u01/app/ogg/dirtmp: created. 21Credential store files /u01/app/ogg/dircrd: created. 22Masterkey wallet files /u01/app/ogg/dirwlt: created. 23Dump files /u01/app/ogg/dirdmp: created. 24 25GGSCI (awen002.us.oracle.com) 2> edit params ./GLOBALS 26 27GGSCI (awen002.us.oracle.com) 3> view params ./GLOBALS 28 29ggschema result01 30enablemonitoring 31checkpointtable result01.chkpt 32 33GGSCI (awen002.us.oracle.com) 4> dblogin userid ggt_admin@192.168.1.22/prod,password ggt_admin 34Successfully logged into database. 35 36GGSCI (awen002.us.oracle.com as ggt_admin@prod) 5> add checkpointtable 37 38No checkpoint table specified. Using GLOBALS specification ggt_admin.chkpt... 39 40Successfully created checkpoint table ggt_admin.chkpt. 41 42GGSCI (awen002.us.oracle.com as ggt_admin@prod) 6> edit params mgr 43 44GGSCI (awen002.us.oracle.com as ggt_admin@prod) 7> view params mgr 45 46PORT 7810 47dynamicportlist 7800-8000 48autorestart extract *,waitminutes 2,resetminutes 5 49 50GGSCI (awen002.us.oracle.com as ggt_admin@prod) 8> start mgr 51Manager started. 52 53GGSCI (awen002.us.oracle.com as ggt_admin@prod) 9> info mgr 54 55Manager is running (IP port TCP:awen002.us.oracle.com.7810, Process ID 4657). 56 57GGSCI (awen002.us.oracle.com as ggt_admin@prod) 10> info all 58 59Program Status Group Lag at Chkpt Time Since Chkpt 60 61MANAGER RUNNING 62JAGENT STOPPED 63PMSRVR STOPPED 64 65GGSCI (awen002.us.oracle.com as ggt_admin@prod) 11> edit param rrggt1 66 67GGSCI (awen002.us.oracle.com as ggt_admin@prod) 12> view param rrggt1 68 69replicat rrggt1 ---定义应用进程名称 70sourcedefs ./dirdef/defgen.prm ---定义defgen文件 71userid ggt_admin,password ggt_admin ---ogg库管理员账户密码 72reperror default,discard ---定义出错以后进程的响应，一般可以定义为两种： 73 ABEND，即一旦出现错误即停止复制，此为缺省配置； 74 DISCARD，出现错误后继续复制，只是把错误的数据放到discard文件中； 75discardfile ./dirrpt/rrggt1.dsc,append,megabytes 50 ---定义discardfile文件位置，文件路径必须为相对路径，如果处理中有记录出错会写入到此文件中，记录模式为追加，文件最大设置为50Mb 76dynamicresolution ---动态解析表名 77gettruncates ---开启捕获truncate操作，两端都需要开启 78map test01.Mar01,target result01.Mar02; ---配置源表目标表映射关系 79 80GGSCI (awen002.us.oracle.com as ggt_admin@prod) 13> add replicat rrggt1,exttrail ./dirdat/e2 81REPLICAT added. 82 83GGSCI (awen002.us.oracle.com as ggt_admin@prod) 14> info all 84 85Program Status Group Lag at Chkpt Time Since Chkpt 86 87MANAGER RUNNING 88JAGENT STOPPED 89PMSRVR STOPPED 90REPLICAT STOPPED RRGGT1 00:00:00 00:00:03 91 92GGSCI (awen002.us.oracle.com as ggt_admin@prod) 15>
14. 按照顺序开启源端、目标端OGG进程
受限开启源端的EXTRACT进程、DATA PUMP进程
1GGSCI (awen001.us.oracle.com) 5> start extract lxggs1 2 3Sending START request to MANAGER ... 4EXTRACT LXGGS1 starting 5 6GGSCI (awen001.us.oracle.com) 6> info all 7 8Program Status Group Lag at Chkpt Time Since Chkpt 9 10MANAGER RUNNING 11EXTRACT STOPPED LPGGS1 00:00:00 01:20:00 12EXTRACT RUNNING LXGGS1 01:20:44 00:00:05 13 14GGSCI (awen001.us.oracle.com) 7> start extract lpggs1 15 16Sending START request to MANAGER ... 17EXTRACT LPGGS1 starting 18 19GGSCI (awen001.us.oracle.com) 8> 20 21GGSCI (awen001.us.oracle.com) 8> info all 22 23Program Status Group Lag at Chkpt Time Since Chkpt 24 25MANAGER RUNNING 26EXTRACT RUNNING LPGGS1 00:00:00 01:20:20 27EXTRACT RUNNING LXGGS1 00:00:00 00:00:05 28 29GGSCI (awen001.us.oracle.com) 9> 30 31GGSCI (awen001.us.oracle.com) 9> info extract lxggs1 32 33EXTRACT LXGGS1 Last Started 2020-03-26 16:28 Status RUNNING 34Checkpoint Lag 00:00:00 (updated 00:00:03 ago) 35Process ID 8863 36VAM Read Checkpoint 2020-03-26 15:08:12.172467 37 38GGSCI (awen001.us.oracle.com) 10> info extract lpggs1 39 40EXTRACT LPGGS1 Last Started 2020-03-26 16:29 Status RUNNING 41Checkpoint Lag 00:00:00 (updated 00:00:01 ago) 42Process ID 8871 43Log Read Checkpoint File /home/mysql/ogg/dirdat/e2000000000 44 First Record RBA 1370 45 46GGSCI (awen001.us.oracle.com) 11> stats ext lxggs1 47 48Sending STATS request to EXTRACT LXGGS1 ... 49 50No active extraction maps. 51 52GGSCI (awen001.us.oracle.com) 12> stats ext lpggs1 53 54Sending STATS request to EXTRACT LPGGS1 ... 55 56No active extraction maps. 57 58GGSCI (awen001.us.oracle.com) 13>
然后开启目标端REPLICATE进程
1GGSCI (awen002.us.oracle.com as ggt_admin@prod) 15> start replicat RRGGT1 2 3Sending START request to MANAGER ... 4REPLICAT RRGGT1 starting 5 6GGSCI (awen002.us.oracle.com as ggt_admin@prod) 16> info all 7 8Program Status Group Lag at Chkpt Time Since Chkpt 9 10MANAGER RUNNING 11JAGENT STOPPED 12PMSRVR STOPPED 13REPLICAT RUNNING RRGGT1 00:00:00 00:00:01 14 15GGSCI (awen002.us.oracle.com as ggt_admin@prod) 17> info REPLICAT RRGGT1 16 17REPLICAT RRGGT1 Last Started 2020-03-26 16:36 Status RUNNING 18Checkpoint Lag 00:00:00 (updated 00:00:02 ago) 19Process ID 5104 20Log Read Checkpoint File /u01/app/ogg/dirdat/e2000000000 21 First Record RBA 0 22GGSCI (awen002.us.oracle.com as ggt_admin@prod) 18> stats REPLICAT RRGGT1 23 24Sending STATS request to REPLICAT RRGGT1 ... 25 26No active replication maps.
15. 模拟源端数据库业务持续运行
OGG的单向配置比较简单，但实际生产过程很多业务要求不间断运行， 所以往创建的表中不断插入数据，简单模拟在业务不间断运行场景下OGG的配置，
并观察进程信息。
1--创建每隔10秒向gss_admin库的Mar01表插入一条数据的procedure； 2use test01select count(*) from Mar01;delimiter // 3create procedure per2() 4begin 5declare id int; 6set id=1; 7while id < 1000 do 8insert into Mar01(id,name) values(id,concat("fan",id)); 9select sleep(10); 10set id=id+1; 11end while; 12end 13// 14 15call per2(); 16->//
16. 查看源端、目标端数据情况
1MariaDB [test01]> select count(*) from Mar01; 2+----------+ 3| count(*) | 4+----------+ 5| 7 | 6+----------+ 71 row in set (0.00 sec) 8 9 10SQL> select count(*) from result01.Mar02; 11 12 COUNT(*) 13---------- 14 7
17. 查看目标端、远端进程信息
1GGSCI (awen001.us.oracle.com) 13> info extract lxggs1 2 3EXTRACT LXGGS1 Last Started 2020-03-26 16:28 Status RUNNING 4Checkpoint Lag 00:00:00 (updated 00:00:07 ago) 5Process ID 8863 6VAM Read Checkpoint 2020-03-26 16:45:36.000000 7 Log Number: 3 8 Record Offset: 4479 9 10 11GGSCI (awen001.us.oracle.com) 14> info extract lpggs1 12 13EXTRACT LPGGS1 Last Started 2020-03-26 16:29 Status RUNNING 14Checkpoint Lag 00:00:00 (updated 00:00:07 ago) 15Process ID 8871 16Log Read Checkpoint File /home/mysql/ogg/dirdat/e2000000000 17 2020-03-26 16:45:36.000000 RBA 4762 18 19 20GGSCI (awen001.us.oracle.com) 15> stats ext lxggs1 21 22Sending STATS request to EXTRACT LXGGS1 ... 23 24Start of Statistics at 2020-03-26 16:45:56. 25 26Output to ./dirdat/e2: 27 28Extracting from test01.Mar01 to test01.Mar01: 29 30*** Total statistics since 2020-03-26 16:42:37 *** 31 Total inserts 20.00 32 Total updates 0.00 33 Total deletes 0.00 34 Total upserts 0.00 35 Total discards 0.00 36 Total operations 20.00 37 38*** Daily statistics since 2020-03-26 16:42:37 *** 39 Total inserts 20.00 40 Total updates 0.00 41 Total deletes 0.00 42 Total upserts 0.00 43 Total discards 0.00 44 Total operations 20.00 45 46*** Hourly statistics since 2020-03-26 16:42:37 *** 47 Total inserts 20.00 48 Total updates 0.00 49 Total deletes 0.00 50 Total upserts 0.00 51 Total discards 0.00 52 Total operations 20.00 53 54*** Latest statistics since 2020-03-26 16:42:37 *** 55 Total inserts 20.00 56 Total updates 0.00 57 Total deletes 0.00 58 Total upserts 0.00 59 Total discards 0.00 60 Total operations 20.00 61 62End of Statistics. 63 64 65GGSCI (awen001.us.oracle.com) 16> stats ext lpggs1 66 67Sending STATS request to EXTRACT LPGGS1 ... 68 69Start of Statistics at 2020-03-26 16:46:01. 70 71Output to /u01/app/ogg/dirdat/e2: 72 73Extracting from test01.Mar01 to test01.Mar01: 74 75*** Total statistics since 2020-03-26 16:42:40 *** 76 Total inserts 21.00 77 Total updates 0.00 78 Total deletes 0.00 79 Total upserts 0.00 80 Total discards 0.00 81 Total operations 21.00 82 83*** Daily statistics since 2020-03-26 16:42:40 *** 84 Total inserts 21.00 85 Total updates 0.00 86 Total deletes 0.00 87 Total upserts 0.00 88 Total discards 0.00 89 Total operations 21.00 90 91*** Hourly statistics since 2020-03-26 16:42:40 *** 92 Total inserts 21.00 93 Total updates 0.00 94 Total deletes 0.00 95 Total upserts 0.00 96 Total discards 0.00 97 Total operations 21.00 98 99*** Latest statistics since 2020-03-26 16:42:40 *** 100 Total inserts 21.00 101 Total updates 0.00 102 Total deletes 0.00 103 Total upserts 0.00 104 Total discards 0.00 105 Total operations 21.00 106 107End of Statistics. 108 109------------------------------------------------------------------ 110 111GGSCI (awen002.us.oracle.com as ggt_admin@prod) 19> info REPLICAT RRGGT1 112 113REPLICAT RRGGT1 Last Started 2020-03-26 16:36 Status RUNNING 114Checkpoint Lag 00:00:05 (updated 00:00:03 ago) 115Process ID 5104 116Log Read Checkpoint File /u01/app/ogg/dirdat/e2000000000 117 2020-03-26 16:46:14.531563 RBA 5475 118 119 120GGSCI (awen002.us.oracle.com as ggt_admin@prod) 20> stats REPLICAT RRGGT1 121 122Sending STATS request to REPLICAT RRGGT1 ... 123 124Start of Statistics at 2020-03-26 16:46:27. 125 126Replicating from test01.Mar01 to RESULT01.MAR02: 127 128*** Total statistics since 2020-03-26 16:42:42 *** 129 Total inserts 23.00 130 Total updates 0.00 131 Total deletes 0.00 132 Total upserts 0.00 133 Total discards 0.00 134 Total operations 23.00 135 136*** Daily statistics since 2020-03-26 16:42:42 *** 137 Total inserts 23.00 138 Total updates 0.00 139 Total deletes 0.00 140 Total upserts 0.00 141 Total discards 0.00 142 Total operations 23.00 143 144*** Hourly statistics since 2020-03-26 16:42:42 *** 145 Total inserts 23.00 146 Total updates 0.00 147 Total deletes 0.00 148 Total upserts 0.00 149 Total discards 0.00 150 Total operations 23.00 151 152*** Latest statistics since 2020-03-26 16:42:42 *** 153 Total inserts 23.00 154 Total updates 0.00 155 Total deletes 0.00 156 Total upserts 0.00 157 Total discards 0.00 158 Total operations 23.00 159 160End of Statistics.
18. 进行dml测试
对表Mar01进行inert、delete、update操作，最后truncate该表。
1MariaDB [(none)]> select count(*) from test01.Mar01; 2+----------+ 3| count(*) | 4+----------+ 5| 32 | 6+----------+ 71 row in set (0.00 sec) 8 9 10 11SQL> select count(*) from result01.Mar02; 12 13 COUNT(*) 14---------- 15 32 16 17 18 19MariaDB [(none)]> insert into test01.Mar01 values (996,'YES'); 20Query OK, 1 row affected (0.00 sec) 21 22SQL> select * from result01.Mar02 where id=996; 23 24 ID NAME 25---------- -------------------- 26 996 YES 27 28 29 30 31MariaDB [(none)]> update test01.Mar01 set name='Yeap' where id=996; 32Query OK, 1 rows affected (0.00 sec) 33Rows matched: 1 Changed: 1 Warnings: 0 34 35SQL> select * from result01.Mar02 where id=996; 36 37 ID NAME 38---------- -------------------- 39 996 Yeap
View Code
1MariaDB [(none)]> truncate test01.Mar01; 2Query OK, 0 rows affected (0.01 sec) 3 4MariaDB [(none)]> select count(*) from test01.Mar01; 5+----------+ 6| count(*) | 7+----------+ 8| 0 | 9+----------+ 101 row in set (0.00 sec) 11 12 13SQL> select count(*) from result01.Mar02; 14 15 COUNT(*) 16---------- 17 0
至此OGG异构配置完成，测试结束。
-------------------------------------------------------------------------