GoldenGate for mysql to mysql:单向同步

  GoldenGate用于在各种企业系统间以亚秒级速度复制和集成事务数据，是同类最佳的、易于部署的产品。GoldenGate 可以灵活地在同类和异类系统（包括不同版本、不同的硬件平台）之间移动数据。

  Oracle 于 2009 年收购了 GoldenGate，我们可以看到在全球各行业超过 4,000 个解决方案中实施了 Oracle GoldenGate。该软件执行实时的、基于日志的更改数据捕获 (CDC)，能够在异构数据库间以极低的延迟和很小的空间移动大量事务数据。

  GoldenGate TDM 是基于日志的实时CDC软件平台，提供异构环境下大量交易数据的实时捕捉、变换和投递，同时保持亚秒一级的数据延迟。

  基于GoldenGate TDM，我们提供高可用/容灾和实时数据集成的解决方案。

  GoldenGate TDM 采用高内聚低耦合的结构，能够支持多种拓扑结构，包括一对一，一对多，多对一，多对多，层叠和双向复制。

  

1:GoldenGate 工作原理

2:交易数据管理

  GoldenGate提供异构环境下交易数据的实时捕捉、变换、投递。

3:GoldenGate变化数据捕捉的优势

1:都提供基于日志的CDC

2:直接从数据库日志中通过自己的专有程序抽取数据

3:支持在备份系统上抓取变化数据

4:对一些数据类型如LONG,XML,BLOB都提供了支持

4:基于日志的实时数据复制

•亚秒级复制 
•不依赖源数据库的触发器和规则，对源数据库影响小。

5:GG在数据传输方面的优势

1:GG有很好的机制来保证传输的交易一致性和断点续传。 
2:事务完整性-事务级粒度 
3:只复制成功提交的事务 
4:防止源和目标的不一致性

实验：goldengate for mysql  to  mysql

1：环境Centos 6.3 + Mysql 5.5.14 + ggs_Linux_x64_MySQL_64bit.zip

2：install  mysql

GoldenGate配置：

源数据库：

[root@litong ~]# useradd  oggadm1

[root@litong ~]# usermod -g mysql oggadm1

[root@litong ~]# mkdir /ogg

[root@litong ~]# chown -R oggadm1:mysql /ogg

[root@litong ~]# chmod -R 775 /ogg

[root@litong ~]# su - oggadm1

[oggadm1@litong ~]cd /ogg

[oggadm1@litong ~]./ggsci

[oggadm1@litong ogg]create subdirs

[oggadm1@litong ogg]exit

 

mysql:

create user 'oggadm1'@'%' identified by '123456';

grant all privileges on *.* to 'oggadm1'@'%';

flush privileges;

create database oggdb1;

use oggdb1;

create table t1 (id int,name varchar(20));

exit

[root@litong ~]# rpm -q *odbc*

[root@litong ~]# yum install mysql-connector-odbc.x86_64

[root@litong ~]# cat /etc/odbcinst.ini #Mysql 驱动

[root@litong ~]# vim /etc/odbc.ini

[mysql] 
Driver = MySQL #MySQL 是 /etc/odbcinst.ini 
Server = 192.168.0.235 
Port = 3306 
User = oggadm1 
Password = 123456 
Database = oggdb1 
Option = 3 
Socket = /tmp/mysql.sock

测试：

[root@litong ~]# isql mysql

Creating Startup Files and Managers：

[oggadm1@litong ogg]./ggsci

ggsci>edit param  ./GLOBALS

CheckpointTable oggdb1.oggchkpt

ggsci>edit param ./startup.oby

DBLogin SourceDB oggdb1, UserID oggadm1, Password 123456
Start Mgr
Info Mgr
Info CheckpointTable 

ggsci>edit param mgr

Port 15001
PurgeOldExtracts ./dirdat/*, UseCheckpoints

ggsci>Add CheckpointTable

Creating Column Definitions (defgen)：

[oggadm1@litong ogg]vim dirprm/dsalesab.prm

DefsFile dirdef/dsalesab.def, Purge
SourceDB oggdb1, UserID oggadm1, Password 123456
Table oggdb1.*; 

[oggadm1@litong ogg]./defgen paramfile dirprm/dsalesab.prm

[oggadm1@litong ogg]scp dirdef/dsalesab.def oggadm2@mysql:/ogg/dirdef

Configuring the Primary Extract：

[oggadm1@litong ogg]./ggsci

ggsci>obey  startup.oby

ggsci>Edit Param esalesaa

Extract esalesaa
ExtTrail ./dirdat/aa
SourceDB oggdb1, UserID oggadm1, Password 123456
TranLogOptions AltLogDest /usr/local/mysql/data/mysql-bin.index
Table oggdb1.*;

ggsci>Add Extract esalesaa, TranLog, Begin Now

ggsci>Add ExtTrail ./dirdat/aa, Extract esalesaa, Megabytes 5

Configuring the Data Pump：

ggsci>Edit Param psalesab

Extract psalesab
RmtHost 192.168.1.21, MgrPort 15002, Compress
RmtTrail ./dirdat/ab
Passthru
Table oggdb1.*;

ggsci>Add Extract psalesab, ExtTrailSource ./dirdat/aa

ggsci>Add RmtTrail ./dirdat/ab, Extract psalesab, Megabytes 5

Verify the Extract Processes (Optional):

ggsci>Info  All

ggsci>Info ExtTrail *

Starting All Processes：

ggsci>Start Extract *

ggsci>Info All

ggsci>Info Extract *

Viewing Reports：

ggsci>Send Extract esalesaa, Report

ggsci>View Report esalesaa

Viewing Statistics：

ggsci>Send Extract esalesaa, Stats

 

 

目标数据库:

[root@mysql ~]# useradd oggadm2

[root@mysql ~]# usermod -g mysql oggadm2

[root@mysql ~]# mkdir /ogg

[root@mysql ~]# chown -R oggadm2:mysql /ogg

[root@mysql ~]# chmod -R 775 /ogg

[root@mysql ~]# su - oggadm12

[oggadm2@mysql ~]cd /ogg

[oggadm2@mysql ogg]./ggsci

[oggadm2@mysql ogg]create subdirs

[oggadm2@mysql ogg]exit

 

mysql：

create user 'oggadm2'@'%' identified by '123456';

grant all privileges on *.* to 'oggadm2'@'%';

flush privileges;

create database oggdb2;

use oggdb2;

create table t1 (id int,name varchar(20));

exit

[root@mysql ~]# rpm -qa *odbc*

[root@mysql ~]# yum install mysql-connector-odbc.x86_64

[mysql] 
Driver = MySQL 
Server = 192.168.1.21 
Port = 3306 
User = oggadm2 
Password = 123456 
Database = oggdb2 
Option = 3 
Socket = /tmp/mysql.sock

测试：

[root@mysql ~]# isql mysql

Creating Startup Files and Managers：

[oggadm2@mysql ogg]./ggsci

ggsci>edit param ./GLOBALS

CheckpointTable oggdb1.oggchkpt

ggsci>edit param ./startup.oby

DBLogin SourceDB oggdb2, UserID oggadm2, Password 123456
Start Mgr
Info Mgr
Info CheckpointTable 

ggsci>edit param mgr

Port 15002
PurgeOldExtracts ./dirdat/*, UseCheckpoints

ggsci>Add CheckpointTable

Configuring the Replicat：

ggsci>Obey startup.oby

ggsci>Edit Param rsalesab

Replicat rsalesab 
DBOptions Host 127.0.0.1, ConnectionPort 3306 
TargetDB oggdb2, UserId root, Password 123456 
SourceDefs dirdef/dsalesab.def 
DiscardFile dirrpt/rsalesab.dsc, Append 
Map oggdb1.*,        Target oggdb2.*;

 

Create the Replicat process：

ggsci>Add Replicat rsalesab, ExtTrail ./dirdat/ab

ggsci>Info All

ggsci>Start Replicat *

ggsci>Info All

ggsci>Info rsalesab

ggsci>Info rsalesab, Detail

ggsci>Send Replicat rsalesab, Report

ggsci>View Report rsalesab

ggsci>Send Replicat rsalesab, Stats