甜橘子，专注成都网站制作网站设计与营销型网站建设与优化
QQ :532337155
电话：028-86922220
本篇文章为大家展示了如何理解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)
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单向同步，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注创新互联行业资讯频道。