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.

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.

1. Login to the source database and confirm whether the log_bin variable is set to ON, the binlog_format variable is set to ROW 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';

2. 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

3. Save and close the file.
4. Restart the database service from the command prompt, if you had made any changes to the my.cnf file.

   [root@srcdbserver ~]$ service mysqld restart

5. 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.

1. To enable binary logging in Amazon, enable database backups. If not, the log_bin variable will report as OFF and Extract will report as Abend, 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.
2. For Amazon RDS for MySQL and Amazon Aurora MySQL, set the binlog_format variable to ROW and the binlog_row_image variable to FULL. 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

1. Download Oracle GoldenGate for MySQL from the location - https://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
2. 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.

3. 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

4. Start ggsci.sh and create the necessary subdirectories.

   [root@oggserver ~]$./ggsci
   GGSCI (oggserver) 1> create subdirs

5. 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

1. 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.                              

2. 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        

3. 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 the TABLE 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 example mysrcdb.Customer.

   If the database is running with 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;                            

4. 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           

5. 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

1. 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                            

2. 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 with dblogin, 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

3. 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 MAP, TARGET parameter pair, follow the database and table name case rules as outlined for the Extract TABLE parameter.

   For Amazon connection strings, the TARGETDB parameter values would look similar as this:

   TARGETDB mytgtdb@mysql2345-aa.abcdefg.us-west-1.rds.amazonaws.com, USERIDALIAS oggextalias                         

4. Add the Replicat to the Oracle GoldenGate installation.

   GGSCI (oggserver) > add replicat rep1, exttrail ./dirdat/em, checkpointtable mytgtdb.ggs_chk
   
   REPLICAT added.

5. 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

6. 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;