One-way replication environment from ORACLE to mysql using goldengate: I use the VBOX virtual machine, a host that has installed the ORACLE database and GOLDENDGATE. The other one only installs the ORACLE database. Configure the database environment, goldengate users, and install goldengate. For more information, see blog. csdn. netq947817003articledeta.
One-way replication environment from ORACLE to mysql using goldengate: I use the VBOX virtual machine, a host that has installed the ORACLE database and GOLDENDGATE. The other one only installs the ORACLE database. Configure the database environment, goldengate users, install goldengate and other operations reference: http://blog.csdn.net/q947817003/article/deta
One-way replication from ORACLE to mysql through goldengate
Environment: I use the VBOX virtual machine, a host that has installed the ORACLE database and GOLDENDGATE. The other one only installs the ORACLE database.
Configure the database environment, goldengate users, install goldengate and other operations reference: http://blog.csdn.net/q947817003/article/details/13293751
1. Install MYSQL to install the MYSQL database client: [root @ bys2 ~] # Mount/dev/cdrom/mnt/cdrom/
Mount: block device/dev/cdrom is write-protected, mounting read-only
[Root @ bys2 ~] # Rpm-qa mysql
[Root @ bys2 ~] # Cd/mnt/cdrom/
[Root @ bys2 cdrom] # yum-y install mysql
Install the server
[Root @ bys2 cdrom] # yum-y install mysql-server
[Root @ bys2 cdrom] # rpm-qa mysql *
Mysql-5.0.77-4.el5_6.6
Mysql-server-5.0.77-4.el5_6.6
##########################
Start MYSQL
[Root @ bys2 cdrom] # service mysqld status
Mysqld is stopped
[Root @ bys2 cdrom] # service mysqld start
Starting MySQL: [OK]
Create a user and table for synchronization [oracle @ bys2 ~] $ Mysql-u root
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Test |
+ -------------------- +
3 rows in set (0.00 sec)
Mysql> create database oggtest;
Query OK, 1 row affected (0.00 sec)
Mysql> use oggtest;
Database changed
Mysql> grant all privileges on *. * to 'root' @ '% 'identified by '123 ';
Query OK, 0 rows affected (0.01 sec)
Mysql> grant all privileges on *. * to 'ogn' @ '% 'identified by 'ogn ';
Query OK, 0 rows affected (0.00 sec)
Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Mysql> use oggtest
Database changed
Mysql> show tables;
Empty set (0.00 sec)
Mysql> create table test5 (aa int primary key) engine = innodb;
Query OK, 0 rows affected (0.02 sec)
Mysql> show tables;
+ ------------------- +
| Tables_in_oggtest |
+ ------------------- +
| Test5 |
+ ------------------- +
1 row in set (0.00 sec)
Mysql> select * from test5;
Empty set (0.01 sec)
After exiting, use [oracle @ bys2 mysqlogg] $ mysql-u root-p for the login test. Then, confirm that you can log on using the root user and password.
If you cannot log on, you can refer to: http://blog.csdn.net/q947817003/article/details/13295099
######################################## ###
2. Install OGG-FOR-MYSQL
[Oracle @ bys2 ~] $ Tar-xvf ggs_Linux_x86_MySQL_32bit.tar-C/u01/mysqlogg/
[Oracle @ bys2 mysqlogg] $ pwd
/U01/mysqlogg
[Oracle @ bys2 mysqlogg] $./ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 11.2.1.0.1 oggcore_11.2.1.0.20.platforms_120423.0230
Linux, x86, 32bit (optimized), MySQL Enterprise on Apr 23 2012 04:29:30
Copyright (C) 1995,201 2, Oracle and/or its affiliates. All rights reserved.
GGSCI (bys2.oel.com) 1> create subdirs
Creating subdirectories under current directory/u01/mysqlogg
Parameter files/u01/mysqlogg/dirprm: already exists
Report files/u01/mysqlogg/dirrpt: created
Checkpoint files/u01/mysqlogg/dirchk: created
Process status files/u01/mysqlogg/dirpcs: created
SQL script files/u01/mysqlogg/dirsql: created
Database definitions files/u01/mysqlogg/dirdef: created
Extract data files/u01/mysqlogg/dirdat: created
Temporary files/u01/mysqlogg/dirtmp: created
Stdout files/u01/mysqlogg/dirout: created
3. Configure the target OGG. First, plan that the PORT started by the target database is 7810. MYSQL sets the table TEST5 of the oggtest database to be synchronized,
The ORACLE source uses the TEST5 table of user TEST5.
1. Configure the MGR process GGSCI (bys2.oel.com) 4> edit params mgr
PORT 7810
PURGEOLDEXTRACTS./dirdat, USECHECKPOINTS, MINKEEPDAYS 10
GGSCI (bys2.oel.com) 2> edit params./globals
Eckpointtable oggtest. chktab
2. log on to and configure the replicat process GGSCI (bys2.oel.com) 1> dblogin sourcedb oggtest, userid root
Password:
Successfully logged into database.
Run these two commands:
Add checkpointtable oggtest. chktab
Add replicat repmysql, exttrail/u01/mysqlogg/dirdat/my, checkpointtable oggtest. chktab
Edit configuration file
Edit params repmysql
The content of the -- MAPS statement is as follows:
Replicat repmysql
Sourcedefs./dirdef/to_mysql.def
Handlecollisions
Assumetargetdefs
Applynoopupdates
Sourcedb oggtest, userid root, password 123456
Discardfile./dirdat/repmysql. dsc, append, megabytes 50
Map test5.test5, target oggtest. test5;
4. Configure the source database-do not use the PUMP method. 1. The MGR process has been configured. Configure the extract process to perform the following operations in sequence:
Add extract extmysql, tranlog, begin now
Location of the target file -- here, note that rmttrail refers to the path of the target file.
Add rmttrail/u01/mysqlogg/dirdat/my, extract extmysql
Edit params extmysql
Write Data in the open edit window
Extract extmysql
Userid ogg, password ogg
Rmthost 192.168.1.213, mgrport 7810
Rmttrail/u01/mysqlogg/dirdat/my
Table test5.test5;
2. generate an object definition file on the source database and copy the file to GGSCI (node1.example.com) 21> edit params defgen under ogg/dirdef of the target database (MYSQL ).
Defsfile./dirdef/to_mysql.def
Userid ogg, password ogg
Table test5.test5;
[Oracle @ bys001 ogg] $./defgen paramfile./dirprm/defgen. prm
[Oracle @ bys001 dirdef] $ pwd
/U01/ogg/dirdef
[Oracle @ bys001 dirdef] $ ls
To_mysql.def
[Oracle @ bys001 dirdef] $ scp to_mysql.def 192.168.1.213:/u01/mysqlogg/dirdef
To_mysql.def 100% 912 0.9KB/s
5. When initializing the data and opening the initialization data of both processes, create the corresponding user and table.
Create the TEST5 table of the oggtest database in the target MYSQL database,
The ORACLE source uses the TEST5 table of user TEST5.
The statement used to create users and tables in the replicel database is as follows:
Run create user test5 inentified by test5 as the DBA user;
Grant connect, resource to test5;
Conn test5/test5
Create table test5 (aa int primary key );
The statement used in MYSQL is as follows: -- the first step has been executed.
Mysql> create database oggtest;
Mysql> use oggtest;
Mysql> grant all privileges on *. * to 'root' @ '% 'identified by '123 ';
Mysql> grant all privileges on *. * to 'ogn' @ '% 'identified by 'ogn ';
Mysql> flush privileges;
Mysql> use oggtest
Mysql> create table test5 (aa int primary key) engine = innodb;
The statements used to open a process are roughly as follows:
Start mgr
Start extract extmysql or start extmysql
Start replicat repmysql or start repmysql
The status of both processes is as follows:
Source end:EXT1 EXT3 PUMP3 REP2 is used to configure other replication operations. Do not worry about ORACLE-MYSQL replication.
GGSCI (bys001.oel.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
Extract abended EXT1 00:00:00 18:19:25
Extract abended EXT3 00:00:00 18:17:24
Extract running extmysql 00:00:00
Extract abended PUMP3 00:00:00 18:19:28
Replicat abended REP2 00:00:00 18:17:28
Target end:
GGSCI (bys2.oel.com) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
Replicat running repmysql 00:00:00 00:00:08
Vi. Data synchronization test ORACLE Source Operation: [oracle @ bys001 ogg] $ sqlplus test5/test5
TEST5 @ bys1> select * from test5;
No rows selected
TEST5 @ bys1> set time on
16:29:59 TEST5 @ bys1> insert into test5 values (100 );
1 row created.
16:30:09 TEST5 @ bys1> commit;
Commit complete.
16:30:11 TEST5 @ bys1> insert into test5 values (200 );
1 row created.
16:32:18 TEST5 @ bys1> commit;
Commit complete.
16:32:20 TEST5 @ bys1> select * from test5;
AA
----------
100
200
###################################
MYSQL:
Submit twice in the ORACLE database. In MYSQL, The chktab, audit_ts column, and last_update_ts column are all updated. It may be that ORACLE and MYSQL are not on the same host and the time is not synchronized. The update time on both ends is inconsistent.
[Oracle @ bys2 mysqlogg] $ mysql-u root-p oggtest
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
Mysql> select * from chktab;
+ ------------ + ------- + ------ + ------------------------------ + --------------------- + ------------------- + --------------- +
| Group_name | group_key | seqno | rba | audit_ts | create_ts | last_update_ts | current_dir |
+ ------------ + ------- + ------ + ------------------------------ + --------------------- + ------------------- + --------------- +
| REPMYSQL | 2345303211 | 1 | 1543 | 16:28:02. 856500 | 14:14:31 | 16:28:31 |/u01/mysqlogg |
+ ------------ + ------- + ------ + ------------------------------ + --------------------- + ------------------- + --------------- +
1 row in set (0.00 sec)
Mysql> select * from test5;
+ ----- +
| Aa |
+ ----- +
| 1, 100 |
+ ----- +
1 row in set (0.00 sec)
Mysql> select * from test5;
+ ----- +
| Aa |
+ ----- +
| 1, 100 |
| 1, 200 |
+ ----- +
2 rows in set (0.01 sec)
Mysql> select * from chktab;
+ ------------ + ------- + ------ + ------------------------------ + --------------------- + ------------------- + --------------- +
| Group_name | group_key | seqno | rba | audit_ts | create_ts | last_update_ts | current_dir |
+ ------------ + ------- + ------ + ------------------------------ + --------------------- + ------------------- + --------------- +
| REPMYSQL | 2345303211 | 1 | 1790 | 16:32:07. 728727 | 14:14:31 | 16:32:11 |/u01/mysqlogg |
+ ------------ + ------- + ------ + ------------------------------ + --------------------- + ------------------- + --------------- +
1 row in set (0.00 sec)
