How to understand ogg12 mysql to oracle one-way synchronization
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.
