GoldenGate 12c - MySQL Active-Active Replication Setup
- by Jinyu Wang-Oracle
Active-active (also called Master-Master or Bi-Directional) replication captures data changes from two or more systems and replicat the changes to synchronize the data. Active-Active replication is often needed for high availability, load balancing and scaling out purposes.
Oracle GoldenGate is known to be one of the first and the best replication tool handling active-active replications. As of Oracle GoldenGate 12c, it provides (Refer to Oracle GoldenGate 12.1.2 Documentation - Configuring Oracle GoldenGate for Active-Active High Availability for more information) the followings:
Robust loop-back prevention
Comprehensive conflict resolution and detection support
Heterogeneous support across different database versions and operation systems.
Oracle GoldenGate supports active-active configurations for DB2 on z/OS, LUW, and IBM i, MySQL, Oracle, SQL/MX,SQL Server, Sybase, and Teradata. However, the setup is different from database to database. In this example, I will show you how to setup an active-active data replication between two MySQL database instances. The example setup below is to have active-active replication between MySQL 5.5 and MySQL 5.6 instances and is shown as follows:
MySQL 5.5 (Manager Port: 15105)
Extract
EXTRACT demoex01
SETENV (MYSQL_UNIX_PORT='/home/oracle/software/mysql_5.5.38/data/mysql.sock')
DBOPTIONS CONNECTIONPORT 3305
DBOPTIONS HOST oraclelinux6.localdomain
SOURCEDB test USERID root, PASSWORD mysql
EXTTRAIL ./dirdat/extract/de
TRANLOGOPTIONS ALTLOGDEST "/home/oracle/software/mysql_5.5.38/data/binlog/bin-log.index"
FILTERTABLE test.checkpoint_tbl
REPORTROLLOVER AT 05:30 ON saturday
TABLE test.TCUSTMER;
TABLE test.TCUSTORD;
Pump
EXTRACT demopm01
RMTHOST localhost, MGRPORT 15106, COMPRESS, TIMEOUT 30
RMTTRAIL ./dirdat/replicat/ps
PASSTHRU
TABLE test.TCUSTMER;
TABLE test.TCUSTORD;
Replicat
replicat demorp01
setenv (MYSQL_UNIX_PORT='/home/oracle/software/mysql_5.5.38/data/mysql.sock')
dboptions host oraclelinux6.localdomain, connectionport 3305
targetdb test, userid root, password mysql
sourcedefs ./dirdat/replicat/democust.def
discardfile ./dirrpt/demprp01.dsc, purge
REPERROR (DEFAULT, ABEND)
REPERROR(1062, IGNORE)
map test.TCUSTMER, target test.TCUSTMER,colmap(usedefaults, region_code="region code");
map test.TCUSTORD, target test.TCUSTORD;
MySQL 5.6 (Manager Port: 15106)
Replicat
replicat demorp01
setenv (MYSQL_UNIX_PORT='/home/oracle/software/mysql_5.6.19/data/mysql.sock')
dboptions host oraclelinux6.localdomain, connectionport 3306
targetdb test, userid root, password mysql
--assumetargetdefs
sourcedefs ./dirdat/replicat/democust.def
discardfile ./dirrpt/demprp01.dsc, purge
map test.TCUSTMER, target test.TCUSTMER, colmap(usedefaults, "region code"=region_code);
map test.TCUSTORD, target test.TCUSTORD;
Extract
EXTRACT demoex01
SETENV (MYSQL_UNIX_PORT='/home/oracle/software/mysql_5.6.19/data/mysql.sock')
DBOPTIONS CONNECTIONPORT 3306
DBOPTIONS HOST oraclelinux6.localdomain
SOURCEDB test USERID root, USERID mysql
EXTTRAIL ./dirdat/extract/de
TRANLOGOPTIONS ALTLOGDEST "/usr/local/mysql56/data/binlog/bin-log.index"
FILTERTABLE test.checkpoint_tbl
TABLE test.TCUSTMER;
TABLE test.TCUSTORD;
Pump
EXTRACT demopm01
RMTHOST localhost, MGRPORT 15105, COMPRESS, TIMEOUT 30
RMTTRAIL ./dirdat/replicat/ps
PASSTHRU
TABLE test.TCUSTMER;
TABLE test.TCUSTORD;
The setup parameters are quite self-explanatory. The key setup is to avoid the replication data looping. Oracle GoldenGate for MySQL uses the information in the replication checkpoint table to identify the transaction applied by replicats and thus avoid extracting those transactions by Oracle GoldenGate extracts. The example setup in the extract in MySQL 5.5 instance is shown as follows.
TRANLOGOPTIONS ALTLOGDEST "/home/oracle/software/mysql_5.5.38/data/binlog/bin-log.index" FILTERTABLE test.checkpoint_tbl
Setting up an active-active replication is often more complicated than this and requires the following additional considerations. I would elaborate on this in the follow-up discussions.
