Before You Begin
This document touches briefly on many important and complex concepts and does not provide a detailed explanation on any one topic since the intent is to present the material in the most expedient manner. The goal is simply to help the reader become familiar enough with the product to successfully design and implement an Oracle GoldenGate environment. To that end, it is important to note that the activities of design, unit testing and integration testing which are crucial to a successful implementation have been intentionally left out of the guide. All the sample scripts are provided as is. Oracle consulting service is highly recommended for any customized implementation.
This tutorial steps you through setting up Oracle GoldenGate remote Capture and Delivery for MySQL. In this tutorial, Oracle GoldenGate for MySQL is to be installed on a Linux hub server, which will be used to capture data from a remote MySQL database and then deliver that data to another remote MySQL database.
The sample steps below guide you through the setup and installation of Oracle GoldenGate on a mid-tier Linux application server.
This tutorial takes approximately 45 minutes to complete.
What Do You Need?
Review the following information before you start this tutorial.
Certification |
MySQL, MariaDB, Amazon Aurora MySQL and Amazon RDS for MySQL are supported. Specific database versions are listed in the certification and are current at the time of its posting. |
Where to Install |
Install Oracle GoldenGate on Oracle Linux 6,7 or RHEL 6,7. |
Review |
Refer to Release Notes and Oracle GoldenGate for MySQL documentation for complete information regarding instance and database requirements, including supported data types. |
Download |
Oracle GoldenGate 19.1.0.0.3 for MySQL on Linux x86-64. |
Create |
Create a source and target database and tables. |
Server Requirements |
Ensure that the time and time zone settings are the same for the database server(s) and the hub server. |
Configuring Uni-directional Replication
Configuring the Prerequisites for Source MySQL and MariaDB Databases
To configure a MySQL and MariaDB database to allow Oracle GoldenGate Capture, edit the file my.cnf
. The following is an example of the necessary settings and steps to edit the file.
- Login to the source database and confirm whether the
log_bin
variable is set toON,
thebinlog_format
variable is set toROW
and the binlog_row_image value is set to FULL.[root@srcdbserver ~]$ mysql -uroot -p mysql> show variables like ‘log_bin’;
mysql> show variables like ‘binlog_format’;
mysql> show variables like 'binlog_row_image'; - If changes are needed, open and edit the file
my.cnf
, to include the following:[root@srcdbserver ~]$ vi /etc/my.cnf [mysqld]
#
#Required if GoldenGate captures from a slave server
log-slave-updates=ON #Bin Log Setup
#Include if log_bin is not ON
log-bin=binlog
#Include if binlog_format is not ROW
binlog-format=ROW #Include if the binlog_row_image is not FULL
binlog-row-image=FULL #For MariaDB Only
binlog-annotate-row-events=OFF - Save and close the file.
- Restart the database service from the command prompt, if you had made any changes to the
my.cnf
file.[root@srcdbserver ~]$ service mysqld restart
- Check the status of the database service using the following command:
[root@srcdbserver ~]$ service mysqld status
Configuring the Prerequisites for Source Amazon RDS for MySQL and Amazon Aurora MySQL Databases
To configure Amazon RDS for MySQL and Amazon Aurora MySQL databases to allow Oracle GoldenGate Capture, perform the required changes via Parameter groups and Database creation or modification.
- To enable binary logging in Amazon, enable database backups. If not, the
log_bin
variable will report asOFF
andExtract
will report asAbend
, thereby displaying an error message that it cannot retrieve the list of binlog files. Enable the backup option during database creation or modify an existing database to include a backup regiment. - For Amazon RDS for MySQL and Amazon Aurora MySQL, set the
binlog_format
variable toROW
and thebinlog_row_image
variable toFULL
. Follow the instructions provided by Amazon to change these values by creating or editing an existing Parameter group. For more details on this, refer to - https://aws.amazon.com/premiumsupport/knowledge-center/enable-binary-logging-aurora.
Creating Source Database User Credentials
To configure a MySQL database to allow for Oracle GoldenGate Capture, create a user in the source database to be used by the Oracle GoldenGate Extract process and grant the required permissions.
- Create an Oracle GoldenGate Extract user in the source database.
[root@srcdbserver ~]$ mysql -uroot -p mysql> CREATE USER ‘oggextuser’ IDENTIFIED BY ‘Welcome1’;
mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘oggextuser’; mysql> FLUSH PRIVILEGES;
Creating Target Database User Credentials
To configure a MySQL database to allow Oracle GoldenGate Delivery, create a user in the target database that can be used by the Oracle GoldenGate Replicat process and grant the required permissions.
-
Create an Oracle GoldenGate Replicat user in the target database.
[root@tgtdbserver ~]$ mysql -uroot -p mysql> CREATE USER ‘oggrepuser’ IDENTIFIED BY ‘Welcome1’;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP on *.* to 'oggrepuser'; mysql> FLUSH PRIVILEGES;
Installing and Configuring Oracle GoldenGate
- Download Oracle GoldenGate for MySQL from the location - https://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
- Download the binaries for Oracle GoldenGate 19c: Oracle GoldenGate 19.1.0.0.3 for MySQL on Linux x86-64.
Note: Oracle recommends you to check My Oracle Support for more recent patches. Login to https://support.oracle.com/portal/ and check for Oracle GoldenGate patches from the Patches and Updates tab.
- Unzip the binaries to a new Oracle GoldenGate installation directory.
[root@oggserver ~]$ unzip 191003_ggs_Linux_x64_MySQL_64bit.zip
[root@oggserver ~]$ tar -xvf ggs_Linux_x64_MySQL_64bit.tar - Start
ggsci.sh
and create the necessary subdirectories.[root@oggserver ~]$./ggsci
GGSCI (oggserver) 1> create subdirs - Create the Manager parameter file, list a valid
PORT
for the Manager to use, save and close the file. After this, start the Manager process. For example,PORT 7809
.GGSCI (oggserver) > edit params mgr GGSCI (oggserver) > view params mgr
port 7809
GGSCI (oggserver) > start mgr
Manager started.
Setting up the Source Extract Process
- Create a credential store wallet and a new user alias that can be used by the Extract for connecting to the source database. Provide the source database user and password information that you created for the Extract user.
GGSCI (oggserver) > add credentialstore
Credential store created. GGSCI (oggserver) > alter credentialstore add user oggextuser password Welcome1 alias oggextalias
Credential store altered. - Confirm the connectivity to the source database, using the Extract database user alias.
GGSCI (oggserver) > dblogin sourcedb mysrcdb@remotehost:3306, useridalias oggextalias Successfully logged into database.
For Amazon connection strings, the
SOURCEDB
parameter values looks like:
dblogin sourcedb mysrcdb@mysql2345-aa.abcdefg.us-west-1.rds.amazonaws.com, useridlias oggextalias
- Create and save a new Extract parameter file using this sample, which has minimum required parameters for a uni-directional implementation. Explicitly list the tables for data to be captured from or use a wildcard, such as
mysrcdb.*
, with theTABLE
parameter in the Extract.- If the database is running with the default value for
lower_case_table_names = 0
, then list the database and table names exactly as they are listed in the database, for examplemysrcdb.Customer
.
lower_case_table_names = 1
(this is not the default setting), then list the database and table names in lower case.GGSCI (oggserver) > edit params ext1 EXTRACT ext1
SOURCEDB mysrcdb@srcdbserver:3306, USERIDALIAS oggextalias
EXTTRAIL ./dirdat/em
TRANLOGOPTIONS ALTLOGDEST REMOTE
TABLE mysrcdb.Customer; - If the database is running with the default value for
- Add the Extract and trail to the Oracle GoldenGate installation.
GGSCI (oggserver) > add extract ext1, tranlog, begin now GGSCI (oggserver) > add exttrail ./dirdat/em, extract ext1
- Start and verify whether the processes are running.
GSCI (oggserver) > start ext1
GGSCI (oggserver) > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:04
Setting up the Replicat Process
- Edit the credential store wallet details and add a new user alias that can be used by the Replicat for connecting to the target database. Provide the target database user and password information that you created for the Replicat user.
GGSCI (oggserver) > alter credentialstore add user oggrepuser password Welcome1 alias oggrepalias
- Confirm the connectivity to the target database using the Replicat database user alias.
GGSCI (oggserver) > dblogin sourcedb mytgtdb@tgtdbserver:3306, useridalias oggrepalias Successfully logged into database.
Note: In GGSCI, the
sourcedb
option is used withdblogin
, regardless of whether the database it is connecting to is considered to be a source or a target database.For Amazon connection strings, the
SOURCEDB
parameter values looks like:dblogin sourcedb mytgtdb@mysql2345-aa.abcdefg.us-west-1.rds.amazonaws.com, useridlias oggrepalias
- Create a new checkpoint table and a new Replicat parameter file using this sample, which has minimum required parameters for a uni-directional implementation.
GGSCI (oggserver) > dblogin sourcedb mytgtdb@tgtdbserver:3306, useridalias oggrepalias Successfully logged into database. GGSCI (oggserver) > add checkpointtable mytgtdb.ggs_chk Successfully created checkpoint table mytgtdb.ggs_chk. GGSCI (oggserver) > edit params rep1
REPLICAT rep1
TARGETDB mytgtdb@tgtdbserver, useridalias oggrepalias
MAP mysrcdb.Customer, TARGET mytgtdb.Customer;For the
For Amazon connection strings, theMAP
,TARGET
parameter pair, follow the database and table name case rules as outlined for theExtract TABLE
parameter.TARGETDB
parameter values would look similar as this:TARGETDB mytgtdb@mysql2345-aa.abcdefg.us-west-1.rds.amazonaws.com, USERIDALIAS oggextalias
- Add the Replicat to the Oracle GoldenGate installation.
GGSCI (oggserver) > add replicat rep1, exttrail ./dirdat/em, checkpointtable mytgtdb.ggs_chk REPLICAT added.
- Start and verify whether the processes are running.
GGSCI (oggserver) > start rep1
GGSCI (oggserver) > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:04
REPLICAT RUNNING REP1 00:00:00 00:00:02 - Insert data into the source table and verify whether it has reached the target.
mysql> USE mysrcdb; mysql> INSERT INTO Customer VALUES (‘John’,’Oracle’,’2020’); mysql> COMMIT; mysql> USE mytgtdb;
mysql> SELECT * FROM Customer;