ogg mysql安装_配置mysql5.7.10到oracle的ogg同步复制报告

配置mysql5.7.10到oracle的ogg同步复制报告

1.实验目的：

通过OGG测试mysql5.7.10到oracle的表数据dml同步，包括truncate动作

2.实验环境：

源端

目标端

备注

操作系统

Centos6.5

Centos6.5

数据库

Mysql5.7.10

Oracle11.2.0.4

OGG版本

12.2.0.1.1

12.2.0.1.1

介质获取地址：

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

主机名

master

master

IP

192.168.89.118

192.168.89.118

端口

7810

7809

数据库名

test

db

用户

jyc/jyc

jyc/1234

同步的表

test

test

2.1mysql软件安装

本次下载的是5.7.10版本：mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz

shell>

cd /opt/mysql/product/5.7

shell>

ln -s /home/mysql/mysql-5.7.10 mysql #在/usr/local/目录下创建一个mysql链接

shell>

cd mysql

shell>

mkdir data #作为存放数据的目录

shell>

chmod 770 data

shell>

chown -R mysql .

shell>

chgrp -R mysql .

shell>

bin/mysqld --initialize --user=mysql

--basedir=/opt/mysql/product/5.7/mysql

--datadir=/opt/mysql/product/5.7/mysql/data

# MySQL

5.7.6 and up

执行该命令后，注意最后一行的[Note]信息

[Note] A

temporary password is generated for root@localhost: )

#Fzu)-oj6f8 生成的初始密码在首次以root用户登入时使用。

shell>

bin/mysql_ssl_rsa_setup --user=mysql

--basedir=/opt/mysql/product/5.7/mysql

--datadir=/opt/mysql/product/5.7/mysql/data

# MySQL

5.7.6 and up

shell>

cd /etc/

shell>

vi my.cnf #修改或添加以下内容

[mysqld]

log_bin

binlog_format = ROW

basedir=/opt/mysql/product/5.7/mysql

datadir=/opt/mysql/product/5.7/mysql/data

socket=/tmp/mysql.sock

port =

3306

server_id = 1

shell>

vi /etc/profile #配置环境变量

添加如下内容

export

MYSQL_HOME="/opt/mysql/product/5.7/mysql"

export

PATH="$PATH:$MYSQL_HOME/bin"

shell>

. /etc/profile #使配置及时生效

如果报错，运行 bash

/etc/profile

实现使用 service mysqll

(start|status|stop) 命令操作mysql.

cp

/opt/mysql/product/5.7/mysql/support-files/mysql.server

/etc/init.d/mysql

至此可用service mysql

start来启动mysql数据库。

配置开机启动mysql服务

shell>

chkconfig --add mysql

shell>

chkconfig --level 2345 mysql on

首次登入mysql

shell>

mysql -u root -p

Enter

password:

输入之前生成的临时密码

mysql>

SET PASSWORD = PASSWORD('123456');

安装参考：

2.2mysql数据库安装

[root@master data]# mysql -u root –p123456

mysql>

create user 'jyc'@'%' identified by 'jyc';

Query OK,

0 rows affected (0.01 sec)

mysql>

grant all on *.* to 'jyc'@'%';

Query OK,

0 rows affected (0.00 sec)

mysql>

flush privileges;

Query OK,

0 rows affected (0.00 sec)

mysql>

exit

Bye

[mysql@master mysqlogg]$ mysql -u jyc -p

Enter

password:

Welcome to

the MySQL monitor. Commands end with ; or

\g.

Your MySQL

connection id is 7

Server

version: 5.7.10-log MySQL Community Server (GPL)

Copyright

(c) 2000, 2015, 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>

create database test charset utf8;

Query OK,

1 row affected (0.06 sec)

mysql>

use test

Database

changed

mysql>

show tables;

Empty set

(0.00 sec)

mysql>

create table test (id int,name varchar(10));

Query OK,

0 rows affected (0.13 sec)

mysql>

show create table test;

+-------+---------------------------------------------------------------------------------------------------------------------------+

| Table |

Create

Table |

+-------+---------------------------------------------------------------------------------------------------------------------------+

|

test | CREATE TABLE `test` (

`id` int(11) DEFAULT NULL,

`name` varchar(10) DEFAULT

NULL

)

ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+---------------------------------------------------------------------------------------------------------------------------+

1 row in

set (0.00 sec)

mysql> alter table test add primary

key(id);

mysql>

exit

3.配置步骤

3.1源端mysql

3.1.1安装ogg软件

介质下载：

[root@master ~]# mkdir /mysqlogg

[root@master ~]# chown -R oracle:dba /mysqlogg

[root@master ~]# pwd

/root

[root@master ~]# chown -R mysql:mysql /mysqlogg

[root@master ~]# id mysql

uid=503(mysql) gid=5002(mysql)

groups=5002(mysql)

[root@master data]# cd /mysqlogg/

[root@master mysqlogg]# ls -l

total

195032

-rw-r--r--. 1 root root 199712439 Feb 4

18:25 ggs_Linux_x64_MySQL_64bit.zip

[root@master mysqlogg]# chown -R mysql:mysql

ggs_Linux_x64_MySQL_64bit.zip

[root@master mysqlogg]# su - mysql

[mysql@master ~]$ cd /mysqlogg

[mysql@master mysqlogg]$ ls

ggs_Linux_x64_MySQL_64bit.zip

[mysql@master mysqlogg]$ unzip *.zip

[mysql@master mysqlogg]$ ls

ggs_Linux_x64_MySQL_64bit.tar ggs_Linux_x64_MySQL_64bit.zip OGG-12.2.0.1.1-ReleaseNotes.pdf OGG-12.2.0.1-README.txt

[mysql@master mysqlogg]$ tar -xvf *.tar

[mysql@master mysqlogg]$ ./ggsci

Oracle

GoldenGate Command Interpreter for MySQL

Version

12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401

Linux,

x64, 64bit (optimized), MySQL Enterprise on Dec 11 2015

16:23:51

Operating

system character set identified as UTF-8.

Copyright

(C) 1995, 2015, Oracle and/or its affiliates. All rights

reserved.

GGSCI

(master) 1> create subdirs

Creating

subdirectories under current directory /mysqlogg

Parameter

files /mysqlogg/dirprm: created

Report

files /mysqlogg/dirrpt: created

Checkpoint

files /mysqlogg/dirchk: created

Process

status

files /mysqlogg/dirpcs: created

SQL script

files /mysqlogg/dirsql: created

Database

definitions

files /mysqlogg/dirdef: created

Extract

data

files /mysqlogg/dirdat: created

Temporary

files /mysqlogg/dirtmp: created

Credential

store

files /mysqlogg/dircrd: created

Masterkey

wallet

files /mysqlogg/dirwlt: created

Dump

files /mysqlogg/dirdmp: created

GGSCI

(master) 2> exit

[mysql@master mysqlogg]$ ./ggsci

3.2目标端oracle

3.2.1安装ogg软件

介质下载：

通过winscp等工具上传介质到目标机器。

[root@master ogg]# chown -R oracle:dba /ogg

[root@master ogg]# pwd

/ogg

[root@master ogg]# ls -l

total

464472

-rw-r--r--. 1 oracle dba 475611228 Jan 28 19:08

fbo_ggs_Linux_x64_shiphome.zip

通过unzip解压。

通过xshell工具图形化安装ogg软件：

[oracle@master ogg]$ ls

fbo_ggs_Linux_x64_shiphome fbo_ggs_Linux_x64_shiphome.zip OGG-12.2.0.1.1-ReleaseNotes.pdf OGG-12.2.0.1-README.txt

[oracle@master ogg]$ pwd

/ogg

[oracle@master ogg]$ mv fbo_ggs_Linux_x64_shiphome

soft

[oracle@master ogg]$ pwd

/ogg

[oracle@master ogg]$ cd soft

[oracle@master soft]$ ls

Disk1

[oracle@master soft]$ cd Disk1

[oracle@master Disk1]$ ls

install response runInstaller stage

[oracle@master Disk1]$ pwd

/ogg/soft/Disk1

[oracle@master Disk1]$ export

DISPLAY=192.168.89.1:0.0

[oracle@master Disk1]$ ./runInstaller

Starting

Oracle Universal Installer...

Checking

Temp space: must be greater than 120

MB. Actual 21434

MB Passed

Checking

swap space: must be greater than 150

MB. Actual 3994

MB Passed

Checking

monitor: must be configured to display at least 256

colors

>>> Could not execute auto check for display colors using

command /usr/bin/xdpyinfo. Check if the DISPLAY variable is

set. Failed

<<<<

Some

requirement checks failed. You must fulfill these requirements

before

continuing

with the installation,

Continue?

(y/n) [n] n

User

Selected: No

Exiting

Oracle Universal Installer, log for this session can be found at

/home/oracle/app/oraInventory/logs/installActions2016-01-28_07-16-26PM.log

[oracle@master Disk1]$ exit

logout

[root@master ogg]# xhost +

access

control disabled, clients can connect from any host

[root@master ogg]# su - oracle

[oracle@master ~]$ cd /ogg/soft/Disk1/

[oracle@master Disk1]$ export

DISPLAY=192.168.89.1:0.0

[oracle@master Disk1]$ ./runInstaller

提示目录不为空直接忽略即可。

[oracle@master ogg]$ pwd

/ogg

[oracle@master ogg]$ ./ggsci

Oracle

GoldenGate Command Interpreter for Oracle

Version

12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux,

x64, 64bit (optimized), Oracle 11g on Dec 12 2015

00:54:38

Operating

system character set identified as UTF-8.

Copyright

(C) 1995, 2015, Oracle and/or its affiliates. All rights

reserved.

GGSCI

(master) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

3.2.2初始化表结构

SQL> create table test(id int,demo

nvarchar2(10));

Table created.

SQL> alter table test add primary

key(id);

Table altered.

3.3源端配置：

GGSCI

(master) 10> add extract extmysql,tranlog,begin now

GGSCI

(master) 11> add exttrail /mysqlogg/dirdat/mb,extract

extmysql

GGSCI

(master) 21> add extract psmysql,exttrailsource

/mysqlogg/dirdat/mb

GGSCI

(master) 22> add rmttrail /ogg/dirdat/mb,extract

psmysql

GGSCI

(master) 24> view param extmysql

extract

extmysql

setenv

(MYSQL_HOME="/opt/mysql/product/5.7/mysql")

sourcedb

test@localhost:3306,userid jyc,password jyc

tranlogoptions altlogdest

/opt/mysql/product/5.7/mysql/data/master-bin.index

exttrail

/mysqlogg/dirdat/mb

--dynamicresolution

gettruncates

table

test.*;

GGSCI

(master) 25> view param psmysql

extract

psmysql

rmthost

192.168.89.118,mgrport 7809

rmttrail

/ogg/dirdat/mb

passthru

table

test.*;

GGSCI

(master) 26> view param mgr

port

7810

GGSCI

(master) 28> view param def

defsfile

/mysqlogg/dirdef/def.prm

sourcedb

test@localhost:3306,userid jyc,password jyc

table

test.*;

GGSCI

(master) 3> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

EXTRACT ABENDED EXTMYSQL 00:00:00 20:15:31

EXTRACT ABENDED PSMYSQL 00:00:00 20:15:33

[mysql@master mysqlogg]$ ./defgen paramfile

dirprm/def.prm

3.4目标端配置：

GGSCI

(master) 2> add replicat myrep,exttrail

/ogg/dirdat/mb,checkpointtable jyc.chkpt

REPLICAT

added.

GGSCI

(master) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

REPLICAT ABENDED MSREP 00:00:00 413:47:29

REPLICAT ABENDED MYREP 00:00:00 21:05:47

GGSCI

(master) 3> view param mgr

PORT

7809

GGSCI

(master) 4> view param msrep

REPLICAT

MSREP

SOURCEDEFS

/ogg/dirdef/ip.def

USERID

jyc, PASSWORD 1234

MAP

dbo.ip, TARGET jyc.ip;

GGSCI

(master) 5> view param myrep

replicat

myrep

sourcedefs

/ogg/dirdef/def.prm

userid

jyc,password 1234

reperror

default,discard

discardfile /ogg/dirrpt/myrep.dsc,append,megabytes

50

dynamicresolution

GETTRUNCATES

--map

test.*, target jyc.*;

map

test.test, target jyc.test,colmap (USEDEFAULTS, demo =

name);

4.安装日志

参考文档：

备份工具：