mysql
hostname：mysqldb1
操作系统：redhat 7.7
数据库：mysql 5.7
IP地址：192.168.1.151
Oracle
hostname:oracledb1
操作系统：redhat 6.4
数据库 oracle 11.2.0.4
IP地址：192.168.1.152
Mysql：
关闭防火墙:
[root@localhost ~]# systemctl stop
firewalld
[root@localhost ~]# systemctl disable
firewalld
安装：
1、 检查是否安装mysql，如果安装需要卸载
rpm
-qa|grep -i mysql
2、 创建用户和组
groupadd mysql
useradd -r -g mysql -p root mysql
3、 通过ssh软件传输到/usr/local路径，并进行解压
win scp软件传输
[root@mysqldb1 local]# tar -xvf
mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar
mysql-community-embedded-devel-5.7.29-1.el7.x86_64.rpm
mysql-community-test-5.7.29-1.el7.x86_64.rpm
mysql-community-embedded-5.7.29-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.29-1.el7.x86_64.rpm
mysql-community-libs-5.7.29-1.el7.x86_64.rpm
mysql-community-client-5.7.29-1.el7.x86_64.rpm
mysql-community-server-5.7.29-1.el7.x86_64.rpm
mysql-community-devel-5.7.29-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.29-1.el7.x86_64.rpm
mysql-community-common-5.7.29-1.el7.x86_64.rpm
4、 卸载mariadb
rpm -e mariadb-libs-5.5.64-1.el7.x86_64
--nodeps
5、 安装mysql
[root@mysqldb1
local]# rpm -ivh mysql-community-common-5.7.29-1.el7.x86_64.rpm
[root@mysqldb1
local]# rpm -ivh mysql-community-libs-5.7.29-1.el7.x86_64.rpm
[root@mysqldb1
local]# rpm -ivh mysql-community-client-5.7.29-1.el7.x86_64.rpm
[root@mysqldb1
local]# rpm -ivh mysql-community-server-5.7.29-1.el7.x86_64.rpm
6、 启动mysql
[root@mysqldb1
~]# systemctl stop mysqld
[root@mysqldb1
~]# systemctl restart mysqld
mysql> update user set authentication_string=PASSWORD('root')
where User='root';
[root@mysqldb1 ~]# mysql -u root -p
Enter password:root
ogg软件安装配置：
[root@mysqldb1 ogg]# ./ggsci
Oracle GoldenGate Command Interpreter for
MySQL
Version 12.2.0.2.2
OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419
Linux, x64, 64bit (optimized), MySQL
Enterprise on Jun 30 2017 06:32:17
Operating system character set identified
as UTF-8.
Copyright (C) 1995, 2017, Oracle and/or its
affiliates. All rights reserved.
GGSCI (mysqldb1) 1> info all
Program
Status Group Lag at Chkpt Time Since Chkpt
MANAGER
STOPPED
GGSCI (mysqldb1) 2> create subdirs
Creating subdirectories under current
directory /u01/ogg
Parameter files /u01/ogg/dirprm: created
Report files /u01/ogg/dirrpt: created
Checkpoint files /u01/ogg/dirchk: created
Process status files /u01/ogg/dirpcs: created
SQL script files /u01/ogg/dirsql: created
Database definitions files /u01/ogg/dirdef: created
Extract data files /u01/ogg/dirdat: created
Temporary files /u01/ogg/dirtmp: created
Credential store files /u01/ogg/dircrd: created
Masterkey wallet files /u01/ogg/dirwlt: created
Dump files /u01/ogg/dirdmp: created
[root@mysqldb1 ogg]# vi /etc/my.cnf
server-id=1
log-bin=mysql-bin
binlog_format=row
########重启进入mysql
[root@mysqldb1 mysql]# systemctl restart
mysqld
[root@mysqldb1 mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29-log MySQL Community
Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its
affiliates. All rights reserved.
Oracle is a registered trademark of Oracle
Corporation and/or its
affiliates. Other names may be trademarks
of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to
clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql>
create table test2 (id int,name char(10)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
mysql>
show tables;
+----------------+
| Tables_in_test |
+----------------+
| test2 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from test2;
Empty set (0.00 sec)
[root@mysqldb1 ogg]# ./ggsci
Oracle GoldenGate Command Interpreter for
MySQL
Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419
Linux, x64, 64bit (optimized), MySQL
Enterprise on Jun 30 2017 06:32:17
Operating system character set identified
as UTF-8.
Copyright (C) 1995, 2017, Oracle and/or its
affiliates. All rights reserved.
GGSCI (mysqldb1) 1> dblogin sourcedb
test@localhost:3306,userid root,password root;
2020-04-21 11:08:00 WARNING OGG-00769 MySQL Login failed: . SQL error (2002). Can't
connect to local MySQL server through socket '/tmp/mysql.sock' (2).
ERROR: Failed to connect to MySQL database
engine for HOST localhost, DATABASE test, USER root, PORT 3306.
GGSCI (mysqldb1) 2> exit
[root@mysqldb1 ogg]# cat /etc/my.cnf
# For advice on how to change settings
please see
#
http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of
RAM for the most important data
# cache in MySQL. Start at 70% of total RAM
for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important
data integrity option: logging
# changes to the binary log between
backups.
# log_bin
#
#skip-grant-tables
# Remove leading # to set options mainly
useful for reporting servers.
# The server defaults are faster for
transactions and fast SELECTs.
# Adjust sizes as needed, experiment to
find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
binlog_format=row
# Disabling symbolic-links is recommended
to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@mysqldb1 ogg]# ll
/var/lib/mysql/mysql.sock
srwxrwxrwx. 1 mysql mysql 0 4月 20 16:38
/var/lib/mysql/mysql.sock
#######根据dblogin报错，进行/tmp目录下的软连接
[root@mysqldb1 ogg]# ln -s
/var/lib/mysql/mysql.sock /tmp/mysql.sock
[root@mysqldb1 ogg]# ./ggsci
Oracle GoldenGate Command Interpreter for
MySQL
Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419
Linux, x64, 64bit (optimized), MySQL
Enterprise on Jun 30 2017 06:32:17
Operating system character set identified
as UTF-8.
Copyright (C) 1995, 2017, Oracle and/or its
affiliates. All rights reserved.
GGSCI (mysqldb1) 1> dblogin sourcedb
test@localhost:3306,userid root,password root;
Successfully logged into database.
##########修改mysql源端ogg mgr参数
GGSCI (mysqldb1 DBLOGIN as root) 2> edit
param mgr
port 7809
dynamicportlist 7800-8000
autorestart extract *,waitminutes
2,resetminutes 5
GGSCI (mysqldb1 DBLOGIN as root) 3>
start mgr
Manager started.
GGSCI (mysqldb1 DBLOGIN as root) 4> info
all
Program
Status Group Lag at Chkpt Time Since Chkpt
MANAGER
RUNNING
##########修改mysql源端ogg extract参数
GGSCI (mysqldb1 DBLOGIN as root) 6> edit
param ext_1
extract ext_1
setenv (MYSQL_HOME=”/var/lib/mysql”)
tranlogoptions altlogdest
/var/lib/mysql/mysql-bin.index
sourcedb test@localhost:3306,userid
root,password root
exttrail ./dirdat/e2
dynamicresolution
gettruncates
table test.test2;
GGSCI (mysqldb1 DBLOGIN as root) 7> add
extract ext_1,tranlog,begin now
EXTRACT added.
GGSCI (mysqldb1 DBLOGIN as root) 8> add exttrail ./dirdat/e2,extract ext_1
EXTTRAIL added.
##########修改mysql源端ogg pump进程参数
GGSCI (mysqldb1 DBLOGIN as root) 9> edit
params pump_1
extract pump_1
rmthost 192.168.1.152,mgrport 7809
rmttrail /u01/ogg/dirdat/e2
passthru
gettruncates
table test.test2;
GGSCI (mysqldb1 DBLOGIN as root) 10> add
extract pump_1,exttrailsource ./dirdat/e2
EXTRACT added.
GGSCI (mysqldb1 DBLOGIN as root) 11> add
rmttrail /u01/ogg/dirdat/e2,extract
pump_1
RMTTRAIL added.
##############异构平台配置defgen：
GGSCI (mysqldb1 DBLOGIN as root) 12>
edit params defgen
defsfile /u01/ogg/dirdef/defgen.prm
sourcedb test@localhost:3306, userid
root,password root
table test.test2;
[root@mysqldb1 ogg]# pwd
/u01/ogg/
[root@mysqldb1 ogg]# ./defgen paramfile
dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for MySQL
Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419
Linux, x64, 64bit (optimized), MySQL
Enterprise on Jun 30 2017 07:27:58
Copyright (C) 1995, 2017, Oracle and/or its
affiliates. All rights reserved.
Starting at 2020-04-21
11:20:06
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Thu Jul 18 20:25:13 UTC
2019, Release 3.10.0-1062.el7.x86_64
Node: mysqldb1
Machine: x86_64
soft limit hard limit
Address Space Size :
unlimited unlimited
Heap Size :
unlimited unlimited
File Size :
unlimited unlimited
CPU Time : unlimited
unlimited
Process id: 26376
***********************************************************************
** Running with the following
parameters **
***********************************************************************
defsfile /u01/ogg/dirdef/defgen.prm
sourcedb test@localhost:3306, userid
root,password ***
table test.test2;
Retrieving definition for test.test2.
Definitions generated for 1 table in
/u01/ogg/dirdef/defgen.prm.
#################传输文件至目标端
[root@mysqldb1 ogg]# scp dirdef/defgen.prm root@192.168.1.152:/u01/ogg/dirdef
###################启动所有进程至runnning状态
GGSCI (mysqldb1 DBLOGIN as root) 20>
info all
Program
Status Group Lag at Chkpt Time Since Chkpt
MANAGER
RUNNING
EXTRACT
RUNNING EXT_1 00:00:00 00:00:07
EXTRACT
RUNNING PUMP_1 00:00:00 00:46:21
Oracle:
关闭防火墙
[root@oracledb1
~]# chkconfig iptables off
[root@oracledb1
~]# service iptables stop
#####建立用户和测试表
[oracle@oracledb1
~]$ sqlplus / as sysdba
SQL*Plus: Release
11.2.0.4.0 Production on Mon Apr 20 17:43:42 2020
Copyright (c)
1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database
11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create
user test identified by test ;
User created.
SQL> grant
connect,resource to test;
Grant succeeded.
SQL> conn
test/test
Connected.
SQL> create
table test2 (id int,name varchar2(10));
Table created
##################修改源端传入文件的属组
[root@oracledb1
u01]# chown -R oracle:oinstall /u01/ogg/dirdef/defgen.prm
配置目标端OGG进程：
#######################配置目标端mgr进程
GGSCI (oracledb1)
4> edit param mgr
PORT 7809
dynamicportlist
7800-8000
autorestart
extract *,waitminutes 2,resetminutes 5
#######################配置目标端复制rep进程
GGSCI (oracledb1)
6> edit param rep_1
replicat rep_1
sourcedefs
/u01/ogg/dirdef/defgen.prm
userid
ogg,password ogg
reperror
default,discard
discardfile
/u01/ogg/dirrpt/rep_1.dsc,append,megabytes 50
dynamicresolution
map test.test2,
target test.test2;
GGSCI (oracledb1)
8> dblogin userid ogg,password ogg
Successfully
logged into database.
GGSCI (oracledb1
as ogg@orcl) 9> add checkpointtable ogg.checkpoint
Successfully
created checkpoint table ogg.checkpoint.
GGSCI (oracledb1
as ogg@orcl) 10> edit params ./GLOBALS
CHECKPOINTTABLE ogg.checkpoint
GGSCI (oracledb1
as ogg@orcl) 12> add replicat rep_1,exttrail
/u01/ogg/dirdat/e2,nodbcheckpoint
REPLICAT added.
启动进程报错：
2020-04-21
12:14:30 ERROR OGG-02091
Operation not supported because enable_goldengate_replication is not set
to true.
2020-04-21
12:14:30 ERROR OGG-01668
PROCESS ABENDING.
根据提示修改参数：
ALTER SYSTEM SET
ENABLE_GOLDENGATE_REPLICATION = TRUE;
##########启动参数
GGSCI (oracledb1)
14> start *
Sending START
request to MANAGER ...
REPLICAT REP_1
starting
GGSCI (oracledb1)
15> info all
Program Status
Group Lag at Chkpt
Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING
REP_1 00:00:00 00:00:02
同步测试：
插入数据:
删除数据：
