- Loop-back prevention
- Conflict resolution and detection
- Oracle GoldenGate 12.1.2 Documentation - Configuring Oracle GoldenGate for Active-Active High Availability
- Nick Wagner, Oracle GoldenGate Active-Active Part 1, Part 2, June-July 2014
An bidirectional replication is also called a master-master or an active-active replication. The replication concurrently captures data from two (or more) databases to synchronize the data. An active-active replication is often used for high availability, zero downtime migration and scaling out use cases. Oracle GoldenGate is known to be one of the first and the best replication tool handling active-active replications. Let's discuss how you can configure an active-active replication between MySQL databases using Oracle GoldenGate.
First, to configure bi-directional replication you need to use the following GoldenGate features :
What database are supported by Oracle GoldenGate for bidirectional replication?
Answer:Oracle GoldenGate supports active-active configurations for Oracle Database, DB2 on z/OS, LUW, and IBM i, MySQL, SQL/MX,SQL Server, and Sybase. However, the setup is different from database to database. After GoldenGate 12.1.2, Teradata capture is not supported by Oracle GoldenGate. Therefore, there is no active-active replication support.
The followings are the configuration on the MYSQL 5.5 side:
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-lo g.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;
The following is the configraton on the MySQL 5.6 database side:
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"=regio n_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" FILTERT ABLE 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 is to avoid the replication data looping. Oracle GoldenGate for MySQL uses the information in the replication checkpoint table with TRANLOGOPTIONS FILTERTABLE to identify the transactions 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 additional considerations. I would elaborate this in the follow-up discussions - What need to consider when enabling MySQL active-active replication?
Created: 2/26/2015 Last Updated: 1/6/2016
GoldenGate for MySQL
Oracle GoldenGate for MySQL replicates DML operations for MySQL databases. Here, I discuss Oracle GoldenGate for MySQL, MySQL database and MySQL replication.
Heterogeneity Security High Performance Real-Time DML Globalization