Database Configuration

Learn about supported MySQL databases, required settings, and how to prepare tables for processing as part of configuring MySQL for Oracle GoldenGate.

Supported Databases

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

Oracle GoldenGate supports delivery to SingleStoreDB and SingleStoreDB Cloud, using the Oracle GoldenGate for MySQL Replicat.

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

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

  • 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 is 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

  • Binary collations are not supported for multi-byte character sets. For example, do not set the collation_server variable equal to utf8mb4_bin when the character set is utf8mb4.

  • The following character sets are not supported:

    • armscii8
    • geostd8
    • keybcs2
    • utf16le

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

Make certain that you specify a session character set in one of these ways when you configure Oracle GoldenGate.

Configure MySQL for Remote Capture

Oracle GoldenGate remote capture for MySQL, Amazon RDS for MySQL, Amazon Aurora MySQL, Azure Database for MySQL is 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:

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

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

  1. Provide the remote database's connection information in the Extract's parameter file. 

    SOURCEDB remotedb@mysqlserver.company.com:port, USERID username, PASSWORD
password

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

  • Oracle GoldenGate for MySQL remote capture does not support an update operation that results in the size of before and after image exceeding 1 GB.

    In an update operation, when the combined size of before and after image exceeds 1 GB, the remote capture API gets the following error from the MySQL server: 
    log event entry exceeded max_allowed_packet
  • 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.