Shulou(Shulou.com)05/31 Report--

This article shows you how to understand ogg12 mysql to oracle one-way synchronization, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Environment:

192.168.91.137 (Mysql)-> 192.168.91.138 (Oracle)

Version:

Operating system: 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

1. Source installation configuration [Mysql]

1 、 vim / etc/profile

Export MYSQL_HOME=/usr/local/mysql

Export PATH=$PATH:$MYSQL_HOME/bin

Source / etc/profile

2. Add a mysql group

Groupadd-g 1000 mysql

Passwd mysql

3. Add a user

Useradd-u 1000-g mysql mysql

Passwd mysql

4. Extract the downloaded package

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. Then mv unzips the package and renames mysql

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

6. Enter the mysql package and authorize the package to mysql

Chown-R mysql:mysql / usr/local/mysql

Chmod-R 755 / usr/local/mysql

7. Enter the path where the mysql file name basedir is mysql, and datadir is the data package of mysql, which stores mysql's own package, such as 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

Note: note: note that the temporary password generated by recording should be noted here, as above:

8. Install 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. Enter support-files and copy the parameter file

[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. Modify the contents of the configuration file

[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

Note: if vim / etc/my.cnf is configured only with log-bin=mysql-bin, not with server-id=1. The following error will be reported

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

11. Start mysql

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

12. Open another window and enter the temporary password: EdgZSS;6=hqD (see the end of step 7)

[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. Change the password

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. Check whether the set parameters are valid:

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 | NULL | 2017-04-07 14:56:21 | NULL | NULL | utf8_general_ci | NULL |

+- -- +

1 row in set (0.00 sec)

Second, install the mysql version of goldengate

Download the appropriate OGG software. Http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

The source side installs gg with the mysql user:

1. Set environment variables

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. Configure gg parameters

[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

GGSCI (ogg1) 4 > start mgr

Manager started.

GGSCI (ogg1) 5 > info mgr

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

3. Configure the extract process

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. Configure the push process

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 extract pump01

RMTTRAIL added.

5. Configure defgen (required for heterogeneous databases):

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.

3. Install and configure the target end GG [Oracle]

1. Install the instance of oracle database software, skip here.

2. Install OGG software

Download the appropriate OGG software. 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. Configure environment variables

3.1Configuring environment variables for oracle users

[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 modify database parameters

[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 size size 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 create a test user

SQL > create tablespace TESTTBS01 datafile'/ u01 size 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 configuration:

4.1Configuring GG necessary paths and configuring mgr parameters

[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

ACCESSRULE, PROG REPLICAT, IPADDR 192.168.91.137, ALLOW / / is only configured on the destination side, and the IP address is the peer IP

GGSCI (ogg2) 4 > start mgr

4.2 configure 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 ogg.checkpoint

REPLICAT added.

4. Start OGG

1. Source side:

Copy the defgen.prm to the destination side:

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

2. Start the extract and pump processes

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. Destination side

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

Validating data

1. Source end

[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. Destination side

[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

Note: heterogeneous databases only support DML operations.

The above content is how to understand ogg12 mysql to oracle one-way synchronization. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.