基于OGG实现MySQL实时同步

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10余年DBA及大数据工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前服务于工业互联网
擅长主流Oracle、MySQL、PG、高斯及Greenplum运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录

前言
- 📣 1.架构设计
- 📣 2.OGG for MySQL部署
- - ✨ 2.1 安装客户端
  - ✨ 2.2 安装目录
  - ✨ 2.3.解压缩安装
  - ✨ 2.4.环境变量设置
  - ✨ 2.5.初始化
- 📣 3.MySQL配置
- - ✨ 3.1 源端配置
  - ✨ 3.2 目标端配置
  - ✨ 3.3 用户及库创建
- 📣 4.MySQL OGG配置
- - ✨ 4.1 配置mgr进程
  - ✨ 4.2 配置EXTRACT进程
  - ✨ 4.3 配置REPLICAT进程
  - ✨ 4.4 启动所有进程
- 📣 5.数据验证

前言

本文详细阐述了基于OGG实现MySQL实时同步全过程

📣 1.架构设计

📣 2.OGG for MySQL部署

✨ 2.1 安装客户端

yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum install mysql-community-server --nogpgcheck

✨ 2.2 安装目录

groupadd -g 1005 ogg
useradd -g ogg -u 1005 -m oggm
mkdir -p /oggmysql
cp /opt/213000_ggs_Linux_x64_MySQL_64bit.zip /oggmysql
chown -R oggm:ogg /oggmysql
chmod -R 775 /oggmysql

✨ 2.3.解压缩安装

su - oggm
cd /oggmysql/
unzip 213000_ggs_Linux_x64_MySQL_64bit.zip
tar -xf ggs_Linux_x64_MySQL_64bit.tar

✨ 2.4.环境变量设置

vi .bash_profile

export GG_HOME=/oggmysql
export PATH=$PATH:$HOME/bin:$GG_HOME
alias ggsci='cd $GG_HOME;ggsci'

source .bash_profile

[oggm@oggmysql ~]$ ggsci -V
Oracle GoldenGate Command Interpreter for MySQL
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
Oracle Linux 7, x64, 64bit (optimized), MySQL  on Jul 28 2021 18:17:46

Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.

✨ 2.5.初始化

[oggm@oggmysql ~]$ ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
Oracle Linux 7, x64, 64bit (optimized), MySQL on Jul 28 2021 18:17:46
Operating system character set identified as US-ASCII.
Copyright © 1995, 2021, Oracle and/or its affiliates. All rights reserved.
GGSCI (oggmysql) 1> create subdirs

📣 3.MySQL配置

✨ 3.1 源端配置

# vi /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
character-set-server=utf8mb4
symbolic-links=0

server_id         = 80350
log_bin           = mysql-bin
expire_logs_days  = 1
binlog_format     = row
binlog_row_metadata=full

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

参数说明：
symbolic-links=0为是否支持符号链接，即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录，为0不开启。
禁用symbolic-links选项  、服务配置，禁用符号链接以防止各种安全风险

--重启mysql
systemctl restart mysqld

[root@mysql8p /]# systemctl restart mysqld
[root@mysql8p /]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2023-11-26 19:20:59 CST; 14s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 267 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 288 (mysqld)
   Status: "Server is operational"
   CGroup: /docker/5e0b5d157afd83dfc5d78352343bf241bafcd7e02685033ee314bf8fbbb7f836/system.slice/mysqld.service
           └─288 /usr/sbin/mysqld
           ‣ 288 /usr/sbin/mysqld

Nov 26 19:20:58 mysql8p systemd[1]: Stopped MySQL Server.
Nov 26 19:20:58 mysql8p systemd[1]: Starting MySQL Server...
Nov 26 19:20:59 mysql8p systemd[1]: Started MySQL Server.

--登录Mysql
mysql -uroot -proot -h172.18.12.66

✨ 3.2 目标端配置

# vi /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
character-set-server=utf8mb4
symbolic-links=0

server_id         = 80351
log_bin           = mysql-bin
expire_logs_days  = 1
binlog_format     = row
binlog_row_metadata=full

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

参数说明：
symbolic-links=0为是否支持符号链接，即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录，为0不开启。
禁用symbolic-links选项  、服务配置，禁用符号链接以防止各种安全风险

--重启mysql
[root@mysql8s /]# systemctl restart mysqld
[root@mysql8s /]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2023-11-26 19:25:46 CST; 10s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 217 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 238 (mysqld)
   Status: "Server is operational"
   CGroup: /docker/65c131b1ebf8f0ae267e48b986a9cffe9b44b232461e479e47d641222f32677e/system.slice/mysqld.service
           └─238 /usr/sbin/mysqld
           ‣ 238 /usr/sbin/mysqld

Nov 26 19:25:45 mysql8s systemd[1]: Stopped MySQL Server.
Nov 26 19:25:45 mysql8s systemd[1]: Starting MySQL Server...
Nov 26 19:25:46 mysql8s systemd[1]: Started MySQL Server.

--登录Mysql
mysql -uroot -proot -h172.18.12.67

✨ 3.3 用户及库创建

1.主备数据库创建同步用户并附权
mysql -uroot -proot -h172.18.12.66 -P3306
mysql -uroot -proot -h172.18.12.67 -P3306
CREATE USER ‘ogg’@‘%’ IDENTIFIED BY ‘Jeames@123’;
GRANT ALL PRIVILEGES ON . TO ‘ogg’@‘%’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

2.创建数据库
create database rptdb charset utf8mb4;

📣 4.MySQL OGG配置

✨ 4.1 配置mgr进程

[oggm@oggmysql ~]$ ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
Oracle Linux 7, x64, 64bit (optimized), MySQL on Jul 28 2021 18:17:46
Operating system character set identified as US-ASCII.
Copyright © 1995, 2021, Oracle and/or its affiliates. All rights reserved.
GGSCI (oggmysql) 1> edit param mgr

PORT 7809
DYNAMICPORTLIST 7810-7909
AUTORESTART EXTRACT ,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/,usecheckpoints, minkeepdays 3

GGSCI (oggmysql) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED

✨ 4.2 配置EXTRACT进程

GGSCI (oggmysql) 1> edit param exta
extract exta
sourcedb rptdb@172.18.12.66:3306, userid ogg, password Jeames@123
tranLogOptions altlogDest REMOTE
ddl include mapped
exttrail ./dirdat/ma
table rptdb.*;

GGSCI (oggmysql) 2> add ext exta, tranlog, begin now
GGSCI (oggmysql) 3> add exttrail ./dirdat/ma, ext exta

✨ 4.3 配置REPLICAT进程

GGSCI (oggmysql) 1> edit param repa
replicat repa
targetdb rptdb@172.18.12.67:3306, userid ogg, password Jeames@123
DDLERROR DEFAULT IGNORE RETRYOP
map rptdb., target rptdb.;

GGSCI (oggmysql) 2> add replicat repa, exttrail ./dirdat/ma , nodbcheckpoint
GGSCI (oggmysql) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           
EXTRACT     STOPPED     EXTA        00:00:00      00:01:30    
REPLICAT    STOPPED     REPA        00:00:00      00:00:05

注意：配置REPLICAT进程,在21C的版本中已经不用配置pump进程

✨ 4.4 启动所有进程

[oggm@oggmysql ~]$ ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
Oracle Linux 7, x64, 64bit (optimized), MySQL on Jul 28 2021 18:17:46
Operating system character set identified as US-ASCII.

Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
GGSCI (oggmysql) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           
EXTRACT     STOPPED     EXTA        00:00:00      00:02:36    
REPLICAT    STOPPED     REPA        00:00:00      00:01:10

GGSCI (oggmysql) 2> start mgr
Manager started.

GGSCI (oggmysql) 3> start exta
Sending START request to Manager …
Extract group EXTA starting.

GGSCI (oggmysql) 4> start repa
Sending START request to Manager …
Replicat group REPA starting.

GGSCI (oggmysql) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXTA        00:02:58      00:00:10    
REPLICAT    RUNNING     REPA        00:00:00      00:00:02    

GGSCI (oggmysql) 6> info *  

Extract    EXTA      Last Started 2023-11-26 20:11   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Process ID           188
VAM Read Checkpoint  2023-11-26 20:08:38.528816


Replicat   REPA      Last Started 2023-11-26 20:11   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Process ID           196
Log Read Checkpoint  File ./dirdat/ma000000000
                     First Record  RBA 1326

📣 5.数据验证

--源端创建数据
[root@mysql8p /]# mysql -uroot -proot -h172.18.12.66
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| rptdb              |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use rptdb
mysql> show tables;


1.新建表插入数据，验证数据
DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
    cust_id VARCHAR(255) DEFAULT NULL
);
INSERT `Customers` VALUES ('A'),('B'),('C');

##目标端数据同步
mysql> show tables;
+-----------------+
| Tables_in_rptdb |
+-----------------+
| Customers       |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from Customers;
+---------+
| cust_id |
+---------+
| A       |
| B       |
| C       |
+---------+
3 rows in set (0.00 sec)



2.源端建表,验证数据
drop table if exists product_tb;
CREATE TABLE product_tb(
item_id char(10) NOT NULL,
style_id char(10) NOT NULL,
tag_price int(10) NOT NULL,
inventory int(10) NOT NULL
);



##目标端数据同步
mysql> show tables;
+-----------------+
| Tables_in_rptdb |
+-----------------+
| Customers       |
| product_tb      |
+-----------------+
2 rows in set (0.00 sec)

3.源端新建一列，验证数据
ALTER TABLE product_tb ADD COLUMN address VARCHAR(50);
mysql> show create table product_tb\G
*************************** 1. row ***************************
       Table: product_tb
Create Table: CREATE TABLE `product_tb` (
  `item_id` char(10) NOT NULL,
  `style_id` char(10) NOT NULL,
  `tag_price` int NOT NULL,
  `inventory` int NOT NULL,
  `address` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

##目标端数据同步
mysql> show create table product_tb\G
*************************** 1. row ***************************
       Table: product_tb
Create Table: CREATE TABLE `product_tb` (
  `item_id` char(10) NOT NULL,
  `style_id` char(10) NOT NULL,
  `tag_price` int NOT NULL,
  `inventory` int NOT NULL,
  `address` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


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