如何理解ogg12mysqltooracle单向同步

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

成都创新互联长期为超过千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为义县企业提供专业的成都网站设计、做网站，义县网站改版等技术服务。拥有10余年丰富建站经验和众多成功案例,为您定制开发。

环境：
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.cnf /etc/my.cnf
[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.cnf

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.cnf 只配置 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)

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> 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版本的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.

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)

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

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单向同步，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注创新互联行业资讯频道。

分享文章：如何理解ogg12mysqltooracle单向同步
路径分享：http://chengdu.cdxwcx.cn/article/gohogi.html

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

首页

网站建设

网站制作案例

解决方案

网站设计报价

网站制作动态

关于我们

联系我们

成都网站建设设计将想法与焦点和您一起共享

如何理解ogg12mysqltooracle单向同步

其他资讯

SAP系统中文图示的简单介绍

php根据数据表生成表格 php怎么创建数据表

无线路由器腾达300m 无线路由器腾达和tplink哪个好

linux的命令 linux的命令窗口怎么进入

阿里云服务器需要堡垒机吗阿里云服务器机房

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

成都网站建设设计 将想法与焦点和您一起共享

如何理解ogg12mysqltooracle单向同步

其他资讯

SAP系统中文图示的简单介绍

php根据数据表生成表格 php怎么创建数据表

无线路由器腾达300m 无线路由器腾达和tplink哪个好

linux的命令 linux的命令窗口怎么进入

阿里云服务器需要堡垒机吗 阿里云服务器机房

成都网站建设设计将想法与焦点和您一起共享

阿里云服务器需要堡垒机吗阿里云服务器机房