如何理解ogg12 mysql to oracle单向同步

本篇文章为大家展示了如何理解ogg12 mysql to oracle单向同步，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

环境：

192.168.91.137 (Mysql) —> 192.168.91.138 ( Oracle )

版本：

操作系统：centOS 6.5

Mysql: 5.7.17

Oracle： 11.2.0.4

goldengate:

12.2.0.1 for oracle

12.2.0.1 for mysql

一、源端安装配置【Mysql】

1、vim /etc/profile

export MYSQL_HOME=/usr/local/mysql

export PATH=$PATH:$MYSQL_HOME/bin

source /etc/profile

2、添加一个mysql组

groupadd -g 1000 mysql

passwd mysql

3、添加一个用户

useradd -u 1000 -g mysql mysql

passwd mysql

4、解压缩-的包

cd /usr/local/

tar -xzvf /usr/local/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

tar -xzvf /usr/local/mysql-test-5.7.17-linux-glibc2.5-x86_64.tar.gz

5、然后 mv 解压后的包,重命名mysql

mv mysql-5.7.17-linux-glibc2.5-x86_64 mysql

6、进入mysql包中， 给这个包授权 给mysql

chown -R mysql:mysql /usr/local/mysql

chmod -R 755 /usr/local/mysql

7、进入mysql文件名  basedir 为mysql 的路径， datadir 为mysql的 data 包，里面存放着mysql自己的包， 如user

[root@ogg1 local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

2017-04-07T02:50:57.483315Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-04-07T02:51:00.090256Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-04-07T02:51:00.505229Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-04-07T02:51:00.708487Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 08775cba-1b3d-11e7-8520-000c29439f01.

2017-04-07T02:51:00.733003Z 0 [Warning] Gtid table is not ready to be used. Table mysql.gtid_executed cannot be opened.

2017-04-07T02:51:00.735871Z 1 [Note] A temporary password is generated for root@localhost: EdgZSS;6=hqD

注：此处需要注意记录生成的临时密码，如上文：

8、安装SSL

[root@ogg1 local]# /usr/local/mysql/bin/mysql_ssl_rsa_setup  --datadir=/usr/local/mysql/data

Generating a 2048 bit RSA private key

.......................................................................................+++

...............................................+++

writing new private key to ca-key.pem

-----

Generating a 2048 bit RSA private key

....................................+++

.........+++

writing new private key to server-key.pem

-----

Generating a 2048 bit RSA private key

................................................................................................................................................................+++

..............................................................................+++

writing new private key to client-key.pem

9、进入support-files，拷贝参数文件

[root@ogg1 local]# cp /usr/local/mysql/support-files/my-default-f /etc/my-f

[root@ogg1 local]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

10、修改配置文件中的内容

[root@ogg1 local]# vim /etc/init.d/mysql

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

[root@ogg1 local]# vim /etc/my-f

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

port=3306

socket=/tmp/mysql.sock

character-set-server=utf8

log_timestamps=system

user=mysql

binlog_format=row

server-id=1

log-bin=mysql-bin

注：如果vim /etc/my-f 只配置 log-bin=mysql-bin，没配置server-id=1。会报如下错误

Starting MySQL. ERROR! The server quit without updating PID file (/usr/local/mysql/data/ogg1.pid).

11、启动mysql

[root@ogg1 local]# /usr/local/mysql/bin/mysqld_safe --user=mysql &

12、另开个窗口，输入临时密码:EdgZSS;6=hqD (见第七步操作最后)

[root@ogg1 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.17

Copyright (c) 2000, 2016, 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.

13、修改密码

mysql> set password=password(manager2017);

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

14、查看设置的参数是否生效：

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      154 |              |                  |                   |

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

1 row in set (0.00 sec)

mysql> show variables like %log_bin%;

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

| Variable_name                   | Value                                 |

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

| log_bin                         | ON                                    |

| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |

| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |

| log_bin_trust_function_creators | OFF                                   |

| log_bin_use_v1_row_events       | OFF                                   |

| sql_log_bin                     | ON                                    |

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

6 rows in set (0.01 sec)

mysql> show variables like %binlog%;

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

| Variable_name                           | Value                |

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

| binlog_cache_size                       | 32768                |

| binlog_checksum                         | CRC32                |

| binlog_direct_non_transactional_updates | OFF                  |

| binlog_error_action                     | ABORT_SERVER         |

| binlog_format                           | ROW                  |

| binlog_group_commit_sync_delay          | 0                    |

| binlog_group_commit_sync_no_delay_count | 0                    |

| binlog_gtid_simple_recovery             | ON                   |

| binlog_max_flush_queue_time             | 0                    |

| binlog_order_commits                    | ON                   |

| binlog_row_image                        | FULL                 |

| binlog_rows_query_log_events            | OFF                  |

| binlog_stmt_cache_size                  | 32768                |

| innodb_api_enable_binlog                | OFF                  |

| innodb_locks_unsafe_for_binlog          | OFF                  |

| log_statements_unsafe_for_binlog        | ON                   |

| max_binlog_cache_size                   | 18446744073709547520 |

| max_binlog_size                         | 1073741824           |

| max_binlog_stmt_cache_size              | 18446744073709547520 |

| sync_binlog                             | 1                    |

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

20 rows in set (0.01 sec)

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| sys                |

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

mysql> create database test;

Query OK, 1 row affected (0.07 sec)

mysql> create table t1 (name char(10));

Query OK, 0 rows affected (0.13 sec)

mysql> show table status from test like t1;

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

| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |

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

| t1    | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2017-04-07 14:56:21 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |

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

1 row in set (0.00 sec)

二、安装mysql版本的goldengate

-相应的OGG软件。http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

源端用mysql用户安装gg：

1、设置环境变量

vi .bash_profile

export GG_HOME=/home/mysql/ggs

export MYSQL_HOME=/usr/local/mysql

export LD_LIBRARY_PATH=$MYSQL_HOME/lib:/usr/lib:/usr/local/lib

export PATH=$GG_HOME:$PATH

source ~/.bash_profile

[mysql@ogg1]$ unzip ggs_Linux_x64_MySQL_64bit.zip

[mysql@ogg1]$ mkdir -p ./ggs

[mysql@ogg1]$ tar -xvf ggs_Linux_x64_MySQL_64bit.tar -C ./ggs

[mysql@ogg1]$ cd ./ggs

2、配置gg参数

[mysql@ogg1 ggs]$ ./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 (ogg1) 1> create subdirs

Creating subdirectories under current directory /home/mysql/ggs

Parameter files                /home/mysql/ggs/dirprm: created

Report files                   /home/mysql/ggs/dirrpt: created

Checkpoint files               /home/mysql/ggs/dirchk: created

Process status files           /home/mysql/ggs/dirpcs: created

SQL script files               /home/mysql/ggs/dirsql: created

Database definitions files     /home/mysql/ggs/dirdef: created

Extract data files             /home/mysql/ggs/dirdat: created

Temporary files                /home/mysql/ggs/dirtmp: created

Credential store files         /home/mysql/ggs/dircrd: created

Masterkey wallet files         /home/mysql/ggs/dirwlt: created

Dump files                     /home/mysql/ggs/dirdmp: created

GGSCI (ogg1) 2> edit params mgr

GGSCI (ogg1) 3> view params mgr

PORT 7809

autorestart extract *,waitminutes 2,resetminutes 5

GGSCI (ogg1) 4> start mgr

Manager started.

GGSCI (ogg1) 5> info mgr

Manager is running (IP port ogg1.7809, Process ID 11623).

3、配置extract进程

GGSCI (ogg1) 6> edit params extr01

GGSCI (ogg1) 7> view params extr01

extract extr01

setenv (MYSQL_HOME="/usr/local/mysql")

tranlogoptions altlogdest /usr/local/mysql/data/mysql-bin.index

sourcedb test@localhost:3306,userid root,password manager2017

exttrail ./dirdat/e2

dynamicresolution

gettruncates

table test.t1;

GGSCI (ogg1) 8> add extract extr01,tranlog,begin now

EXTRACT added.

GGSCI (ogg1) 9> add exttrail ./dirdat/e2,extract extr01

EXTTRAIL added.

4、配置推送进程

GGSCI (ogg1) 10> edit params pump01

GGSCI (ogg1) 11> view params pump01

extract pump01

rmthost 192.168.91.138,mgrport 7809

rmttrail /u01/app/goldengate/dirdat/e2

passthru

gettruncates

table test.t1;

GGSCI (ogg1) 12> add extract pump01,exttrailsource ./dirdat/e2

EXTRACT added.

GGSCI (ogg1) 13> add rmttrail /u01/app/goldengate/dirdat/e2,extract pump01

RMTTRAIL added.

5、配置defgen (异构数据库需要)：

GGSCI (ogg1) 14> edit params defgen

GGSCI (ogg1) 15> view params defgen

defsfile /home/mysql/ggs/dirdef/defgen.prm

sourcedb test@localhost:3306, userid root,password manager2017

table test.t1;

[mysql@ogg1 ggs]$ ./defgen  paramfile ./dirprm/defgen.prm

***********************************************************************

        Oracle GoldenGate Table Definition Generator 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 17:21:39

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

Starting at 2017-04-07 15:28:50

***********************************************************************

Operating System Version:

Linux

Version #1 SMP Fri Nov 22 03:15:09 UTC 2013, Release 2.6.32-431.el6.x86_64

Node: ogg1

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: 11704

***********************************************************************

**            Running with the following parameters                  **

***********************************************************************

defsfile /home/mysql/ggs/dirdef/defgen.prm

sourcedb test@localhost:3306, userid root,password ***

table test.t1;

Retrieving definition for test.t1.

Definitions generated for 1 table in /home/mysql/ggs/dirdef/defgen.prm.

三、安装配置目标端GG【Oracle】

1、安装oracle数据库软件实例，此处略过。

2、安装OGG软件

-相应的OGG软件。http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

[root@ogg2 ~]# su - root

[root@ogg2 ~]# mkdir -p /u01/app/goldengate

[root@ogg2 ~]# chown -R oracle:oinstall /u01/app/goldengate

[root@ogg2 ~]# su - oracle

[oracle@ogg2 ~]$ ll unzip fbo_ggs_Linux_x64_shiphome.zip

[oracle@ogg2 ~]$ cd /home/oracle/fbo_ggs_Linux_x64_shiphome/Disk1

[oracle@ogg2 Disk1]$ sed -i "s/INSTALL_OPTION=/INSTALL_OPTION=ORA11g/" /home/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

[oracle@ogg2 Disk1]$ sed -i "s|SOFTWARE_LOCATION=|SOFTWARE_LOCATION=/u01/app/goldengate|" /home/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

[oracle@ogg2 Disk1]$ sed -i "s/START_MANAGER=/START_MANAGER=false/" /home/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

[oracle@ogg2 Disk1]$ ./runInstaller -silent -responseFile /home/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

3、配置环境变量

3.1 配置oracle用户的环境变量

[oracle@ogg2 Disk1]$ vi ~/.bash_profile

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

export ORACLE_SID=burton

export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin:$ORACLE_HOME/jdk/bin

export PATH=$ORACLE_HOME/bin:/usr/sbin:$GG_HOME:$PATH

export TNS_ADMIN=$ORACLE_HOME/network/admin

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/local/lib

export GG_HOME=/u01/app/goldengate

umask 022

3.2 修改数据库参数

[oracle@ogg2 Disk1]$ source  ~/.bash_profile

[oracle@ogg2 Disk1]$ mkdir -p /u01/app/archivelog

[oracle@ogg2 Disk1]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 7 15:36:30 2017

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> alter system set log_archive_dest_1=LOCATION=/u01/app/archivelog scope=spfile;

System altered.

SQL> alter system set log_archive_format=burton_%t_%s_%r.arc scope=spfile;

System altered.

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

SQL> archive log list;

SQL> alter database add supplemental log data;

SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

SQL> alter database force logging;

SQL> create tablespace tbs_ogg datafile /u01/app/oracle/oradata/burton/tbs_ogg.dat size 200M autoextend on next 5M;

Tablespace created.

SQL> create user ogg identified by ogg default tablespace tbs_ogg temporary tablespace temp account unlock;

User created.

SQL> alter system set enable_goldengate_replication = true scope=both;

System altered.

SQL> alter user ogg quota unlimited on tbs_ogg;

User altered.

SQL> grant connect,resource,dba to ogg;

Grant succeeded.

SQL> grant execute on utl_file to ogg;

Grant succeeded.

3.3 创建测试用户

SQL> create tablespace TESTTBS01 datafile /u01/app/oracle/oradata/burton/testtbs01.dat size 100M autoextend on next 5M;

Tablespace created.

SQL> create user test identified by test2017 default tablespace TESTTBS01 temporary tablespace temp;

User created.

SQL> grant connect,resource,select_catalog_role to test;

Grant succeeded.

SQL> conn test/test2017

Connected.

SQL> create table t1 (name char(10));

Table created.

4、OGG配置：

4.1 配置GG必要的路径和配置mgr参数

[oracle@ogg2 ~]$ cd $GG_HOME

[oracle@ogg2 goldengate]$ ./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 (ogg2) 1> create subdirs

Creating subdirectories under current directory /u01/app/goldengate

Parameter files                /u01/app/goldengate/dirprm: created

Report files                   /u01/app/goldengate/dirrpt: created

Checkpoint files               /u01/app/goldengate/dirchk: created

Process status files           /u01/app/goldengate/dirpcs: created

SQL script files               /u01/app/goldengate/dirsql: created

Database definitions files     /u01/app/goldengate/dirdef: created

Extract data files             /u01/app/goldengate/dirdat: created

Temporary files                /u01/app/goldengate/dirtmp: created

Credential store files         /u01/app/goldengate/dircrd: created

Masterkey wallet files         /u01/app/goldengate/dirwlt: created

Dump files                     /u01/app/goldengate/dirdmp: created

GGSCI (ogg2) 2> edit params mgr

GGSCI (ogg2) 3> view params mgr

PORT 7809

autorestart extract *,waitminutes 2,resetminutes 5

ACCESSRULE, PROG REPLICAT, IPADDR 192.168.91.137, ALLOW       // 只在目标端配置，IP地址是对端IP

GGSCI (ogg2) 4> start mgr

4.2 配置GLOBALS：

GGSCI (ogg2) 5> edit params ./GLOBALS

GGSCI (ogg2) 6> view params ./GLOBALS

ggschema ogg

checkpointtable ogg.checkpoint

GGSCI (ogg2) 7> dblogin userid ogg, password ogg

Successfully logged into database.

GGSCI (ogg2) 8> add trandata test.t1

GGSCI (ogg2 as ogg@burton) 9> add checkpointtable ogg.checkpoint

Successfully created checkpoint table ogg.checkpoint.

GGSCI (ogg2 as ogg@burton) 10> edit params repl01

GGSCI (ogg2 as ogg@burton) 11> view params repl01

replicat repl01

sourcedefs /u01/app/goldengate/dirdef/defgen.prm

userid ogg,password ogg

reperror default,discard

discardfile /u01/app/goldengate/dirrpt/repl01.dsc,append,megabytes 50

dynamicresolution

map test.t1, target test.t1;

GGSCI (ogg2 as ogg@burton) 12> dblogin userid ogg,password ogg

Successfully logged into database.

GGSCI (ogg2 as ogg@burton) 13> add replicat repl01,exttrail /u01/app/goldengate/dirdat/e2,checkpointtable ogg.checkpoint

REPLICAT added.

四、启动OGG

1、源端：

拷贝defgen.prm到目标端:

[mysql@mysql2 ggs]$ scp -r /home/mysql/ggs/dirdef/defgen.prm oracle@192.168.91.138:/u01/app/goldengate/dirdef/

2、启动extract和pump进程

GGSCI (ogg1) 1> start extract extr01

Sending START request to MANAGER ...

EXTRACT EXTR01 starting

GGSCI (ogg1) 2> start extract pump01

Sending START request to MANAGER ...

EXTRACT PUMP01 starting

GGSCI (ogg1) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     EXTR01      00:00:00      00:00:06   

EXTRACT     RUNNING     PUMP01      00:00:00      00:00:01

3、目标端

GGSCI (ogg2) 1> start replicat repl01

Sending START request to MANAGER ...

REPLICAT REPL01 starting

GGSCI (ogg2) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

REPLICAT    RUNNING     REPL01      00:00:00      00:00:04  

五、验证数据

1、源端

[root@ogg1 Desktop]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> insert into t1 values (abcd);

Query OK, 1 row affected (0.00 sec)

2、目标端

[oracle@ogg2 ~]$ sqlplus test/test2017

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 10 10:19:36 2017

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> select * from t1;

NAME

----------

abcd

注：异构数据库只支持DML操作。

上述内容就是如何理解ogg12 mysql to oracle单向同步，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注行业资讯频道。