配置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软件安装

介质下载：http://dev.mysql.com/downloads/mysql/

 

本次下载的是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');

 

安装参考：

http://wenku.baidu.com/link?url=m5PD328mIs8FF6wx_cCoZ4dEV-empnlZbuvYIriuANT8BgSlujJ07ka7f_oTARk95syOBsL1__jNtd_YwkbbsaHKiMP9wPOYudiRMMApKB3

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软件

介质下载：

http://download.oracle.com/otn/goldengate/122011/ggs_Linux_x64_MySQL_64bit.zip

 

[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软件

介质下载：

http://download.oracle.com/otn/goldengate/122011/fbo_ggs_Linux_x64_shiphome.zip

通过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.安装日志

 

参考文档：

http://www.cnblogs.com/suredandan/p/3643181.html

http://blog.sina.com.cn/s/blog_a32eff280101e1si.html

http://blog.sina.com.cn/s/blog_6bbe9f580100o4cn.html

http://www.oracle.com/technetwork/cn/articles/datawarehouse/oracle-sqlserver-goldengate-1396114-zhs.html

http://blog.csdn.net/kk185800961/article/details/45749333

备份工具：

http://www.zmanda.com/download-zrm.php

http://www.zmanda.com/quick-mysql-backup.html