📢📢📢📣📣📣
哈嘍！大家好，我是【IT邦德】，江湖人稱jeames007，10余年DBA及大數(shù)據(jù)工作經(jīng)驗
一位上進心十足的【大數(shù)據(jù)領(lǐng)域博主】！😜😜😜
中國DBA聯(lián)盟(ACDU)成員，目前服務(wù)于工業(yè)互聯(lián)網(wǎng)
擅長主流Oracle、MySQL、PG、高斯及Greenplum運維開發(fā)，備份恢復(fù)，安裝遷移，性能優(yōu)化、故障應(yīng)急處理等。
? 如果有對【數(shù)據(jù)庫】感興趣的【小可愛】，歡迎關(guān)注【IT邦德】💞💞💞
??????感謝各位大可愛小可愛！??????

前言

本文詳細闡述了基于OGG實現(xiàn)MySQL實時同步全過程

---

📣 1.架構(gòu)設(shè)計

📣 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.環(huán)境變量設(shè)置

vi .bash_profileexport 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:46Copyright (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=0server_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參數(shù)說明：
symbolic-links=0為是否支持符號鏈接，即數(shù)據(jù)庫或表可以存儲在my.cnf中指定datadir之外的分區(qū)或目錄，為0不開啟。
禁用symbolic-links選項  、服務(wù)配置，禁用符號鏈接以防止各種安全風險--重啟mysql
systemctl restart mysqld[root@mysql8p /]# systemctl restart mysqld
[root@mysql8p /]# systemctl status mysqld
● mysqld.service - MySQL ServerLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)Active: active (running) since Sun 2023-11-26 19:20:59 CST; 14s agoDocs: man:mysqld(8)http://dev.mysql.com/doc/refman/en/using-systemd.htmlProcess: 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/mysqldNov 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=0server_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參數(shù)說明：
symbolic-links=0為是否支持符號鏈接，即數(shù)據(jù)庫或表可以存儲在my.cnf中指定datadir之外的分區(qū)或目錄，為0不開啟。
禁用symbolic-links選項  、服務(wù)配置，禁用符號鏈接以防止各種安全風險--重啟mysql
[root@mysql8s /]# systemctl restart mysqld
[root@mysql8s /]# systemctl status mysqld
● mysqld.service - MySQL ServerLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)Active: active (running) since Sun 2023-11-26 19:25:46 CST; 10s agoDocs: man:mysqld(8)http://dev.mysql.com/doc/refman/en/using-systemd.htmlProcess: 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/mysqldNov 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 用戶及庫創(chuàng)建

1.主備數(shù)據(jù)庫創(chuàng)建同步用戶并附權(quán)
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.創(chuàng)建數(shù)據(jù)庫
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 ChkptMANAGER     STOPPED                                           
EXTRACT     STOPPED     EXTA        00:00:00      00:01:30    
REPLICAT    STOPPED     REPA        00:00:00      00:00:05

注意：配置REPLICAT進程,在21C的版本中已經(jīng)不用配置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 allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     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 allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     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.528816Replicat   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/ma000000000First Record  RBA 1326

📣 5.數(shù)據(jù)驗證

--源端創(chuàng)建數(shù)據(jù)
[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.新建表插入數(shù)據(jù)，驗證數(shù)據(jù)
DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(cust_id VARCHAR(255) DEFAULT NULL
);
INSERT `Customers` VALUES ('A'),('B'),('C');##目標端數(shù)據(jù)同步
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.源端建表,驗證數(shù)據(jù)
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
);##目標端數(shù)據(jù)同步
mysql> show tables;
+-----------------+
| Tables_in_rptdb |
+-----------------+
| Customers       |
| product_tb      |
+-----------------+
2 rows in set (0.00 sec)3.源端新建一列，驗證數(shù)據(jù)
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)##目標端數(shù)據(jù)同步
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都可以同步。