Mysql to Mysql之 OGG同步配置笔记

2022-01-17 01:53:17

1、准备OGG安装介质

注：ogg版本12.3及以上才支持mysql ddl操作，mysql版本需5.7.10以上！

 

[root@mysql software]# ll -shrt

total 66M

 66M -rw-r--r--.  1 mysql mysql  66M May  3  2018 123012_ggs_Linux_x64_MySQL_64bit.zip

4.0K drwxr-x---. 19 94110 42424 4.0K Sep  8 00:05 ggs

[root@mysql software]#

 

 

 

2、源端安装配置OGG

2.1解压OGG软件

[root@mysql software]# unzip 123012_ggs_Linux_x64_MySQL_64bit.zip

 

2.2用户环境变量配置

[root@mysql software]# cat /root/.bash_profile

export GGHOME=/software/ggs/

export PATH=$PATH:$GGHOME

export LD_LIBRARY_PATH=/software/ggs/:$LD_LIBRARY_PATH

 

2.3创建OGG相关目录

[root@mysql software]# cd /software/ggs/

[root@mysql ggs]# ./ggsci

 

Oracle GoldenGate Command Interpreter for MySQL

Version 12.3.0.1.2 OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005

Linux, x64, 64bit (optimized), MySQL Enterprise on Dec  8 2017 11:42:23

Operating system character set identified as UTF-8.

 

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

 

 

 

GGSCI (mysql) 1> CREATE subdirs

 

 

2.4编辑mgr

GGSCI (mysql) 1> edit params mgr

 

 

PORT 7840

DYNAMICPORTLIST 7840-7939

--AUTOSTART ER myext01

--AUTOSTART ER myext02

--AUTOSTART ER mypump01

--AUTOSTART ER mypump02

--AUTORESTART ER myext01,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER myext02,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER mypump01,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER mypump02,RETRIES 5,WAITMINUTES 3

PURGEOLDEXTRACTS /software/ggs/dirdat/*,usecheckpoints,minkeepdays 7

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

 

 

2.5登录源端db

GGSCI (mysql) 2> dblogin sourcedb test_mysql_ogg@127.0.0.1:3306,userid oggtb,PASSWORD oggtb

Successfully logged into database.

 

2.6添加配置extract抽取进程

GGSCI (mysql DBLOGIN as oggtb) 3> ADD EXTRACT e1,tranlog,BEGIN NOW




GGSCI (mysql DBLOGIN as oggtb) 4> ADD exttrail /software/ggs/dirdat/e1,EXTRACT e1




GGSCI (mysql DBLOGIN as oggtb) 5> edit params e1

 

 

EXTRACT e1

setenv (MYSQL_HOME="/var/lib/mysql")

tranlogoptions altlogdest /var/lib/mysql/mysqlserver.index

sourcedb test_mysql_ogg@127.0.0.1:3306,userid oggtb,PASSWORD oggtb

exttrail /software/ggs/dirdat/e1

dynamicresolution

gettruncates

ddl include mapped

TABLE test_mysql_ogg.*;

 

 

2.7配置ddl支持

[root@mysql ggs]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/tmp/mysql.sock

symbolic-links=0

log-error=/var/lib/mysql/mysqld.err

pid-file=/var/lib/mysql/mysqld.pid

server_id=333

log_bin=mysqlserver

binlog_format=row

binlog-ignore-db=oggddl

 

[client]

socket=/tmp/mysql.sock

[mysql]

socket=/tmp/mysql.sock

[root@mysql ggs]#

 

 

[root@mysql ggs]# pwd

/software/ggs

[root@mysql ggs]# ./ddl_install.sh install root "123" 3306

5.7.21-log

checking MySQL version

DDL is supported for your installed MySQL version.

plugin_dir: /usr/lib64/mysql/plugin/

 

nohup: appending output to `nohup.out'

./ddl_install.sh: line 42: [: 8543: binary operator expected

The metadata_server started successfully.

 

copying of ddl_rewriter.so succeeded

copying of ddl_metadata.so succeeded

 

Plugins installation was successful.

 

Installation was successful.

[root@mysql ggs]#

 

ddl插件命令指南

ddl_install.sh start root "123" 3306 -- 启动ddl插件

ddl_install.sh stop root "123" 3306 -- 停止ddl插件

ddl_install.sh restart root "123" 3306 -- 重启ddl插件

ddl_install.sh status root "123" 3306 -- 查询ddl插件状态

ddl_install.sh install root "123" 3306 -- 安装ddl插件

ddl_install.sh uninstall root "123" 3306 -- 卸载ddl插件

 

2.8添加配置pump投递进程

GGSCI (mysql DBLOGIN as oggtb) 6> ADD EXTRACT p1,exttrailsource /software/ggs/dirdat/e1

GGSCI (mysql DBLOGIN as oggtb) 7> ADD rmttrail /software/ggs/dirdat/r1,EXTRACT p1




GGSCI (mysql DBLOGIN as oggtb) 8> edit param p1

EXTRACT p1

rmthost 172.16.255.204,mgrport 7840

rmttrail /software/ggs/dirdat/r1

passthru

gettruncates

TABLE test_mysql_ogg.*;

 

3、目标端配置OGG

3.1解压OGG软件

[root@mysql-tag software]# unzip 123012_ggs_Linux_x64_MySQL_64bit.zip

 

3.2用户环境变量配置

[root@mysql-tag software]# cat /root/.bash_profile

export GGHOME=/software/ggs/

export PATH=$PATH:$GGHOME

export LD_LIBRARY_PATH=/software/ggs/:$LD_LIBRARY_PATH

 

3.3创建OGG相关目录

[root@mysql-tag software]# cd /software/ggs/

[root@mysql-tag ggs]# ./ggsci

 

Oracle GoldenGate Command Interpreter for MySQL

Version 12.3.0.1.2 OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005

Linux, x64, 64bit (optimized), MySQL Enterprise on Dec  8 2017 11:42:23

Operating system character set identified as UTF-8.

 

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

 

 

 

GGSCI (mysql-tag) 1> CREATE subdirs

 

 

3.4编辑mgr

GGSCI (mysql-tag) 1> edit params mgr

 

 

PORT 7840

DYNAMICPORTLIST 7840-7939

--AUTOSTART ER myext01

--AUTOSTART ER myext02

--AUTOSTART ER mypump01

--AUTOSTART ER mypump02

--AUTORESTART ER myext01,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER myext02,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER mypump01,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER mypump02,RETRIES 5,WAITMINUTES 3

PURGEOLDEXTRACTS /software/ggs/dirdat/*,usecheckpoints,minkeepdays 7

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

 

 

3.5登录源端db

GGSCI (mysql-tag) 2> dblogin sourcedb test_mysql_ogg@127.0.0.1:3306,userid oggtb,PASSWORD oggtb

Successfully logged into database.

 

3.6添加配置replicate进程

GGSCI (mysql-tag) 3> ADD checkpointtable test_mysql_ogg.checkpoint




GGSCI (mysql-tag) 4> ADD replicat r1,exttrail /software/ggs/dirdat/r1,checkpointtable test_mysql_ogg.checkpoint




GGSCI (mysql-tag) 5> edit params r1

replicat r1

dboptions HOST localhost,connectionport 3306

targetdb test_mysql_ogg,userid oggtb,PASSWORD oggtb

assumetargetdefs

DISCARDFILE /software/ggs/dirrpt/r1.dsc,append,megabytes 50

gettruncates

MAP test_mysql_ogg.*,target test_mysql_ogg.*;

 

 

4、初始化数据

主库master-data方式导出test_mysql_ogg库，该方式会记录导出时的log_file 信息

mysqldump -uroot -p --master-DATA test_mysql_ogg > test_mysql_ogg.sql



打开test_mysql_ogg.sql 找到下面内容，记录下log_file和log_pos

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=5054;



修改extract e1 从导出的时的binlog开始抽取

GGSCI (mysql) 90> ALTER EXTRACT e1,VAM,lognum 3,logpos 5054


EXTRACT altered.

 

5、开始同步

5.1源端开启抽取进程

GGSCI (mysql) 12> ALTER EXTRACT e1,VAM,lognum 3,logpos 1981

GGSCI (mysql) 13> start e1

 

解决ERROR OGG-00146 Oracle GoldenGate Capture for MySQL, EORA.prm: Call to VAMControl returned with error status 600: VAM Client Report <CAUSE OF FAILURE : The DDL version is not found in the binlog.Please check if the ddl plugins installed

 

GGSCI (mysql) 14> alter extract e1, tranlog, begin now

 

 

5.2源端开启投递进程

GGSCI (mysql) 15> start p1

 

 

5.3目标端开启应用进程

GGSCI (mysql-tag) 12> start r1

 

上一篇：In VSCode when input mi and press tab it will generate the MIT declaration automatically


下一篇：JetBrains GoLand 2021.3 (macOS Intel x64/Apple M1, Linux, Windows) with Crack