MySQL

This section lists details about configuring Oracle GoldenGate for MySQL.

Topics:

Prepare Database Users and Privileges

Learn about creating database users and assigning privileges for Oracle GoldenGate for MySQL.

Topics:

Prepare Database Users and Assign Privileges for Oracle GoldenGate for MySQL

Requirements for the database user for Oracle GoldenGate processes are as follows:

Create a database user that is dedicated to Oracle GoldenGate. It can be the same user for all the Oracle GoldenGate processes that must connect to a database.
To support DDL replication, the MySQL user must have privileges to install the database plug-ins. The required permissions for the plug-in is only required with MySQL 5.7. The INSERT privilege is required on the mysql.plugin system table.
To preserve the security of your data, and to monitor Oracle GoldenGate processing accurately, do not permit other users, applications, or processes to log on as, or operate as, the Oracle GoldenGate database user.
Keep a record of the database users. They must be specified in the Oracle GoldenGate parameter files with the USERID parameter.
The Oracle GoldenGate user requires read access to the INFORMATION_SCHEMA database.

The Oracle GoldenGate user requires the following user privileges.

Privilege	Source Extract	Target Replicat	Purpose
`SELECT`	Yes	Yes	Connect to the database and select object definitions
`REPLICATION SLAVE`	Yes	NA	Connect and receive updates from the replication master’s binary log
`CREATE` `CREATE VIEW` `EVENT` `INSERT` `UPDATE` `DELETE`	Yes	Yes	Source and target database heartbeat and checkpoint table creation, and data record generation and purging
`DROP`	Yes	Yes	Dropping a Replicat checkpoint table or deleting a heartbeat table implementation
`EXECUTE`	Yes	Yes	To execute stored procedures
`INSERT`, `UPDATE`, `DELETE` on target tables	NA	Yes	Apply replicated DML to target objects
DDL privileges on target objects (if using DDL support)	NA	Yes	Issue replicated DDL on target objects

To capture binary log events, an Administrator must provide the following privileges to the Extract user:
- Read and Execute permissions for the directory where the MySQL configuration file (my.cnf) is located.
- Read permission for the MySQL configuration file (my.cnf).
- Read and Execute permissions for the directory where the binary logs are located.
- Read and Execute permission for the tmp directory. The tmp directory is /tmp. The MySQL database connection requires access to the /tmp/mysql.sock file for versions prior to MySQL 8.0.

Prepare Database Connection, System, and Parameter Settings

Learn about configuring database connection, system, and parameter settings for Oracle GoldenGate for MySQL.

Topics:

Configure the Database Connection

Oracle GoldenGate gets the name of the database it is supposed to connect to from the SOURCEDB parameter. To configure the connection for the SOURCEDB parameter, use the following format:

SOURCEDB dbname@hostname:port, USERID mysqluser, PASSWORD welcome

The dbname is the name of the MySQL instance, hostname is the name or IP address of the MySQL database server, port is the port number of the MySQL instance. If using an unqualified host name, that name must be properly configured in the DNS database. Otherwise, use the fully qualified host name, for example myhost.company.com.

Configuring a Two-way SSL Connection in MySQL Capture and Delivery

To use the two way SSL in Oracle GoldenGate for MySQL capture and delivery, you need to supply the full paths of the certificate authority (ca.pem), the client certificate (client-cert.pem) and the client key (client-key.pem ) files to the capture and delivery.

To know more about generating the certificate files, see:

https://dev.mysql.com/doc/refman/5.7/en/creating-ssl-rsa-files-using-mysql.html

You need to provide these paths in the Extract and Replicat parameter files using the SETENV parameter.

Following are the SETENV environment parameters to set the two-way SSL connection:

OGG_MYSQL_OPT_SSL_CA: Sets the full path of the certification authority.
OGG_MYSQL_OPT_SSL_CERT: Sets the full path of the client certificate.
OGG_MYSQL_OPT_SSL_KEY: Sets the full path of the client key.

In the following example, the MySQL SSL certificate authority, client certificate, and client key paths are set to the Oracle GoldenGate MySQL Extract and Replicat parameter:

SETENV (OGG_MYSQL_OPT_SSL_CA='/var/lib/mysql.pem') 
SETENV (OGG_MYSQL_OPT_SSL_CERT='/var/lib/mysql/client-cert.pem') 
SETENV (OGG_MYSQL_OPT_SSL_KEY='/var/lib/mysql/client-key.pem')

For a MySQL user configured with X509 encryption scheme, the MySQL database requires the ssl-key and ssl-cert options at the time of logging in. So, when an Oracle GoldenGate credential store entry is created for this user, the SSL options in the credential store alias must mandatorily include sslKey and sslCert regardless of sslMode used.

Database Configuration

Learn about supported databases, database storage engine, database character set, and how to prepare tables for processing as part of configuring MySQL for Oracle GoldenGate.

Topics:

Supported Databases

Oracle GoldenGate for MySQL supports capture and delivery for MySQL, Oracle MySQL Database Service, Amazon Aurora MySQL, Amazon RDS for MariaDB, Amazon RDS for MySQL, Azure Database for MySQL, Google Cloud SQL for MySQL, and MariaDB.

With Oracle GoldenGate release 21.10, SingleStoreDB and SingleStoreDB Cloud are now supported for delivery only, using the Oracle GoldenGate for MySQL Replicat.

From Oracle GoldenGate 21c (21.7) for MySQL 8.0 and higher, capture and delivery for MySQL configured with Group Replication in single-primary mode is supported. For more information, see Using Oracle GoldenGate with MySQL Group Replication.

For a complete list of supported databases and versions, review the Certification Matrix for your version of Oracle GoldenGate.

Limitations of Support

Following are the limitations of support for Oracle GoldenGate for MySQL:

MySQL databases enabled with binary log transaction compression are not supported with Oracle GoldenGate Extract.
MySQL databases enabled with binary log encryption are not supported with Oracle GoldenGate Extract.

Database Storage Engine

Requirements for the database storage engine are as follows:

Oracle GoldenGate supports the InnoDB storage engine for a source MySQL database.
All the components of Oracle GoldenGate for MySQL, including Extract, Replicat, and Admin Client connect to the database using the MySQL native API.
Oracle GoldenGate supports capture and apply from and to the InnoDB engine. Apply to MyISAM engine works, but there might be data integrity issues as MyISAM engine in non-transactional.

Database Character Set

MySQL provides a facility that allows users to specify different character sets at different levels.

Level	Example
Database	create database test charset utf8;
Table	create table test( id int, name char(100)) charset utf8;
Column	create table test ( id int, name1 char(100) charset gbk, name2 char(100) charset utf8));

Limitations of Support

When you specify the character set of your database as utf8mb4/utf8, the default collation is utf8mb4_unicode_ci/utf8_general_ci. If you specify collation_server=utf8mb4_bin, the database interprets the data as binary. For example, specifying the CHAR column length as four means that the byte length returned is 16 (for utf8mb4) though when you try to insert data more than four bytes the target database warns that the data is too long. This is the limitation of database so Oracle GoldenGate does not support binary collation. To overcome this issue, specify collation_server=utf8mb4_bin when the character set is utf8mb4 and collation_server=utf8_bin for UTF-8.
The following character sets are not supported:
- armscii8
- keybcs2
- utf16le
- geostd8

Set the Session Character Set

The Extract and Replicat processes use a session character set when connecting to the database from the command line interface (Admin Client). For MySQL, the session character set is taken from the SESSIONCHARSET option of the SOURCEDB and the TARGETDB. Make certain that you specify a session character set in one of these ways when you configure Oracle GoldenGate.

Prepare Tables for Processing

This section describes how to prepare the tables for processing. Table preparation requires these tasks.

Topics:

Ensure Row Uniqueness for Tables

Oracle GoldenGate requires some form of unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes.

Unless a KEYCOLS clause is used in the TABLE or MAP statement, Oracle GoldenGate selects a row identifier to use in the following order of priority:

Primary key
First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.
If none of the preceding key types exist (even though there might be other types of keys defined on the table) Oracle GoldenGate constructs a pseudo key of all columns that the database allows to be used in a unique key, excluding those that are not supported by Oracle GoldenGate in a key or those that are excluded from the Oracle GoldenGate configuration.

Note:

If there are other, non-usable keys on a table or if there are no keys at all on the table, Oracle GoldenGate logs an appropriate message to the report file. Constructing a key from all of the columns impedes the performance of Oracle GoldenGate on the source system. On the target, this key causes Replicat to use a larger, less efficient WHERE clause.
If a table does not have an appropriate key, or if you prefer that the existing key(s) are not used, you can define a substitute key, if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. See TABLE | MAP in Reference for Oracle GoldenGate.

Tables with a Primary Key Derived from a Unique Index

In the absence of a primary key on a table, MySQL will promote a unique index to primary key if the indexed column is NOT NULL. If there are more than one of these not-null indexes, the first one that was created becomes the primary key. To avoid Replicat errors, create these indexes in the same order on the source and target tables.

For example, assume that source and target tables named ggvam.emp each have columns named first, middle, and last, and all are defined as NOT NULL. If you create unique indexes in the following order, Oracle GoldenGate will abend on the target because the table definitions do not match.

Source:

CREATE UNIQUE INDEX UQL ON ggvam.emp(first);
CREATE UNIQUE INDEX UQ2 on ggvam.emp(middle); 
CREATE UNIQUE INDEX UQ3 on ggvam.emp(last);

Target:

CREATE UNIQUE INDEX UQ1 ON ggvam.emp(last); 
CREATE UNIQUE INDEX UQ2 ON ggvam.emp(first); 
CREATE UNIQUE INDEX UQ3 ON ggvam.emp(middle);

The result of this sequence is that MySQL promotes the index on the source "first" column to primary key, and it promotes the index on the target "last" column to primary key. Oracle GoldenGate will select the primary keys as identifiers when it builds its metadata record, and the metadata will not match. To avoid this error, decide which column you want to promote to primary key, and create that index first on the source and target.

Specify Your Own Key for Oracle GoldenGate to Use

If a table does not have one of the preceding types of row identifiers, or if you prefer those identifiers not to be used, you can define a substitute key if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds.

Limit Row Changes in Tables That Do Not Have a Key

If a target table does not have a primary key or a unique key, duplicate rows can exist. In this case, Oracle GoldenGate could update or delete too many target rows, causing the source and target data to go out of synchronization without error messages to alert you. To limit the number of rows that are updated, use the DBOPTIONS parameter with the LIMITROWS option in the Replicat parameter file. LIMITROWS can increase the performance of Oracle GoldenGate on the target system because only one row is processed.

Triggers and Cascade Constraints Considerations

Triggers

Disable triggers on the target tables, or alter them to ignore changes made by the Oracle GoldenGate database user. Oracle GoldenGate replicates DML that results from a trigger. If the same trigger gets activated on the target table, then it becomes redundant because of the replicated version, and the database returns an error.

Cascade Constraints Considerations

Cascading updates and deletes captured by Oracle GoldenGate are not logged in binary log, so they are not captured. This is valid for both MySQL and MariaDB. For example, when you run the delete statement in the parent table with a parent child relationship between tables, the cascading deletes (if there are any) happens for child table, but they are not logged in binary log. Only the delete or update record for the parent table is logged in the binary log and captured by Oracle GoldenGate.

See https://mariadb.com/kb/en/replication-and-foreign-keys/ and https://dev.mysql.com/doc/refman/8.0/en/innodb-and-mysql-replication.html for details.

To properly handle replication of cascading operations, it is recommended to disable cascade deletes and updates on the source and code your application to explicitly delete or update the child records prior to modifying the parent record. Alternatively, you must ensure that the target parent table has the same cascade constraints configured as the source parent table, but this could lead to an out-of-sync condition between source and target, especially in cases of bi-directional replication.

Configure MySQL for Remote Capture

Oracle GoldenGate remote capture for MySQL, Amazon RDS for MySQL, Amazon Aurora MySQL, Azure Database for MySQL are used to capture transaction log data from a database located remotely to the Oracle GoldenGate installation.

Database Server Configuration

For remote capture to work, configure the MySQL server as follows:

Grant access permissions to the Oracle GoldenGate remote capture user.

Run the following statements against the remote database to create the user and grant the permissions needed for remote capture.
```
CREATE USER 'username'@'host' IDENTIFIED BY 'Password'; 
GRANT ALL PRIVILEGES ON *.* TO 'username'@'host’ WITH GRANT OPTION; 
FLUSH PRIVILEGES;
```
The server_id value of the remote MySQL server should be greater than 0. This value can be verified by issuing the following statement on the MySQL remote server:
```
SHOW VARIABLES LIKE ‘server_id’;
```
If the server_id value is 0, modify the my.cnf configuration file to set to a value greater than 0.

Oracle GoldenGate Configuration

Oracle GoldenGate configuration has the following steps:

Provide the remote database's connection information in the Extract's parameter file.
```
SOURCEDB remotedb@mysqlserver.company.com:port, USERID username, PASSWORD
password
```
Add the following parameter to the Extract's parameter file, after the connection information.
```
TRANLOGOPTIONS ALTLOGDEST REMOTE
```

Limitations of Oracle GoldenGate Remote Capture for MySQL

Co-existence of Oracle GoldenGate for MySQL remote capture with the MySQL’s native replication slave is supported with following conditions and limitations:

The native replication slave should be assigned a different server_id than the currently running slaves. The slave server_id values can be seen using the following MySQL command on the master server.
```
SHOW SLAVE HOSTS;
```
- If the Oracle GoldenGate capture abends with error "A slave with the same server_uuid or server_id as this slave has connected to the master", then change the capture's name and restart the capture.
- If the native replication slave dies with the error "A slave with the same server_uuid or server_id as this slave has connected to the master", then change the native replication slave’s server_id and restart it.
Remote capture is supported for Oracle GoldenGate on running on Linux and can support databases running on Linux or Windows.

Transaction Log Settings and Requirements

Know more about transaction log settings and requirements for Oracle GoldenGate for MySQL.

Topics:

Ensuring Data Availability

Retain enough binary log data so that if you stop Extract or there is an unplanned outage, Extract can start again from its checkpoints. Extract must have access to the binary log that contains the start of the oldest uncommitted unit of work, and all binary logs thereafter. The recommended retention period is at least 24 hours worth of transaction data, including both active and archived information. You might need to do some testing to determine the best retention time given your data volume and business requirements.

If data that Extract needs during processing was not retained, either in active or backup logs, one of the following corrective actions might be required:

Alter Extract to capture from a later point in time for which binary log data is available (and accept possible data loss on the target).
Resynchronize the source and target tables, and then start the Oracle GoldenGate environment over again.

To determine where the Extract checkpoints are, use the INFO EXTRACT command. For more information, see INFO EXTRACT in Command Line Interface Reference for Oracle GoldenGate.

Setting Logging Parameters

To capture from the MySQL transaction logs, the Oracle GoldenGate Extract process must be able to find the index file, which contains the paths of all binary log files.

Extract expects that all of the table columns are in the binary log. As a result, binlog_row_image set as full is supported and this is the default. Other values of binlog_row_image are not supported.

Note:

Oracle recommends that the binary log is retained for at least 24 hours.

In MySQL 5.7, the server_id option must be specified along with log-bin, otherwise the server will not start. For MySQL 8.0, the server_id is enabled by default.

Extract checks the following parameter settings to get this index file path:

Extract TRANLOGOPTIONS parameter with the ALTLOGDEST option. If this parameter specifies a location for the log index file, Extract accepts this location over any default that is specified in the MySQL Server configuration file. When ALTLOGDEST is used, the binary log index file must also be stored in the specified directory. This parameter should be used if the MySQL configuration file does not specify the full index file path name, specifies an incorrect location, or if there are multiple installations of MySQL on the same machine. From Oracle GoldenGate 21c onwards, ALTLOGDEST parameter is optional for local Extract, however, for remote Extract this parameter is mandatory. When ALTLOGDEST is not specified, the binary log index and binary log filepaths will be fetched from the database directly. The paths thus fetched are also subject to same accessibilitychecks as in the existing process.

To specify the index file path using TRANLOGOPTIONS with ALTLOGDEST, use a command similar to the following:

TRANLOGOPTIONS ALTLOGDEST "/mnt/rdbms/mysql/data/logs/binlog.index"

To capture from a remote server or in case of remote capture, you only need to specify the REMOTE option instead of the index file path on the remote server. For remote capture, specify the following in the Extract parameter file:

TRANLOGOPTIONS ALTLOGDEST REMOTE
The MySQL Server configuration file: The configuration file stores default startup options for the MySQL server and clients. On Windows, the name of the configuration file is my.ini. On other platforms, it is my.cnf. In the absence of TRANLOGOPTIONS with ALTLOGDEST, Extract gets information about the location of the log files from the configuration file. However, even with ALTLOGDEST, these Extract parameters must be set correctly:
- binlog-ignore-db=oggddl: This prevents DDL logging history table entries in the binlog and is set in the my.cnf or my.ini file.
- log-bin: This parameter is used to enable binary logging. This parameter also specifies the location of the binary log index file and is a required parameter for Oracle GoldenGate, even if ALTLOGDEST is used. If log-bin is not specified, binary logging will be disabled and Extract returns an error.
- log-bin-index: This parameter specifies the location of the binary log index. If it is not used, Extract assumes that the index file is in the same location as the log files. If this parameter is used and specifies a different directory from the one that contains the binary logs, the binary logs must not be moved once Extract is started.
- max_binlog_size: This parameter specifies the size, in bytes, of the binary log file.
  
  Note:
  
  The server creates a new binary log file automatically when the size of the current log reaches the max_binlog_size value, unless it must finish recording a transaction before rolling over to a new file.
- binlog_format: This parameter sets the format of the logs. It must be set to the value of ROW, which directs the database to log DML statements in binary format. Extract silently ignores the binlog events that are not written in the ROW format instead of abending when it detects a binlog_format other than ROW.
  
  Note:
  
  MySQL binary logging does not allow logging to be enabled or disabled for specific tables. It applies globally to all tables in the database.
- mysql.rds_set_configuration: When capturing from MySQL Amazon RDS instance, you need to call the mysql.rds_set_configuraton stored procedure on MySQL command line, to retain the binary logs for a specific duration. By default, the default value of binlog_retention_hours for MySQL Amazon RDS is set to NULL, which implies that the binary logs are not retained.
  
  The following example shows the command to preserve the binary log for 24 hours:
  
  mysql > call mysql.rds_set_configuration('binlog retention hours', 24);
To locate the configuration file, Extract checks the MYSQL_HOME environment variable: If MYSQL_HOME is set, Extract uses the configuration file in the specified directory. If MYSQL_HOME is not set, Extract queries the information_schema.global_variables table to determine the MySQL installation directory. If a configuration file exists in that directory, Extract uses it.
For MariaDB version 10.2 and later, Oracle GoldenGate works in the same way as for MySQL but a new variable needs to be configured in the my.cnf or my.ini file. The variable that needs to be added is "binlog-annotate-row-events=OFF". Restart MariaDB after configuring this variable and then start the Extract process.

Changing the Log-Bin Location

Modifying the binary log location by using the log-bin variable in the MySQL configuration file might result in two different path entries inside the index file, which could result in errors. To avoid any potential errors, change the log-bin location by doing the following:

Stop any new DML operations.
Let the extract finish processing all of the existing binary logs. You can verify this by noting when the checkpoint position reaches the offset of the last log.
After Extract finishes processing the data, stop the Extract group and, if necessary, back up the binary logs.
Stop the MySQL database.
Modify the log-bin path for the new location.
Start the MySQL database.
To clean the old log name entries from index file, use flush master or reset master (based on your MySQL version).
Start Extract.

Capturing using a MySQL Replication Slave

You can configure a MySQL replication slave to capture the master's binary log events from the slave.

Typically, the transactions applied by the slave are logged into the relay logs and not into the slave's binlog. For the slave to write transactions in its binlog, that it receives from the master , you must start the replication slave with the log-slave-updates option as 1 in my.cnf in conjunction with the other binary logging parameters for Oracle GoldenGate. After the master's transactions are in the slave's binlog , you can set up a regular Oracle GoldenGate capture on the slave to capture and process the slave's binlog.