1. Microservices Architecture Documentation
  2. Prepare
  3. Prepare Databases
  4. MySQL
  5. Database Configuration
  6. Transaction Log Settings and Requirements

Transaction Log Settings and Requirements

Learn how to prepare MySQL database and supported variants for Oracle GoldenGate, including required binary logging setting and database server variables.

Configuring Logging Properties

Oracle GoldenGate relies on the MySQL binary logs to store the data that it needs to replicate source transactions. The binary logs on the source system must be configured properly before you start Oracle GoldenGate processing.

This section addresses the following binary log startup options and system variables that apply to Oracle GoldenGate for MySQL.

Logging Options Values What It Does Requirement
binlog_annotate_row_events ON, OFF System variable that tells the primary server to write these events to the binary log. Required when using MariaDB as a source database.

OFF
binlog_expire_logs_seconds 0 - 4294967295 Where supported, this system variable sets the binary log expiration period, in seconds. Recommended value is: >=86400
binlog_format ROW, STATEMENT, MIXED System variable that sets the binary logging format.

Required when MySQL is used as source database. If set to MIXED or STATEMENT, only ROW level transactions will be captured.

ROW

binlog retention hours 1 - 168 Where supported, this system variable sets the binary log expiration period, in hours. Recommended value is: >=24
binlog_row_image FULL, MINIMAL, NOBLOB System variable that sets different levels of column level logging in the binary log.

Required when MySQL is used as source database.

FULL

binlog_row_metadata FULL, MINIMAL System variable that configures the amount of metadata added to the binary log for row-based logging.

Required when MySQL 8.0 or above is used as source database for DDL support.

FULL

event_scheduler ON, OFF System variable that allows creation of Heartbeat update and purge jobs.

Required if implementing the Oracle GoldenGate Heartbeat functionality.

ON
log_bin ON, OFF System variable that shows the status of binary logging. Required when MySQL is used as source database. If not already ON, set by assigning a name, such as log-bin=binlog.
log_replica_updates ON, OFF System variable that logs updates into the secondary server’s own binary log when received from a primary server.

Required when MySQL is used as source database and is configured to capture from a replica server.

ON
server_id 0 - 4294967295 System variable required to be specified when binary logging is enabled. For a replication primary and each replication replica, ensure values are >0 and unique.

Required when MySQL is used as source database.

>=1

Verify Database-Level Logging

To verify the database’s current binary logging settings required to support Oracle GoldenGate Capture for the MySQL database, perform the following queries. If settings need to be change, follow the instructions under Enable Database-level Logging

  1. Log in to MySQL as the root or assigned database service Administrator account.
    [root@srcdbserver ~]$ mysql -uroot -p
  2. Issue the following command to determine whether the database is enabled for binary logging. If the result is ON, then database binary log is enabled.
    show variables like ‘log_bin’;

  3. Issue the following commands to determine whether the database is enabled with the correct binary log settings and server_id. Ensure that the binlog_format value is set to ROW, the binlog_row_image and binlog_row_metadata are set to FULL, and that the server_id variable is set to >=1 and are unique for a primary and any replicas. 

    show variables like ‘binlog_%’;
show variables like ‘server_id’;

  4. Repeat the process to query the variables for the other options listed in the Configuring Logging Properties, based on your requirements, such as DDL support and MariaDB support.

Enable Database-level Logging

Perform the following steps, if necessary, to enable the database binary logging settings required to support Oracle GoldenGate Capture for a MySQL database. Only modify those settings that need to be enabled or changed, based on the results from Verify Database-Level Logging.

  1. Edit the MySQL configuration file, my.cnf for Linux, or my.ini for Windows, to enable system variables that are required to support Oracle GoldenGate capture for MySQL.
    [root@srcdbserver ~]$ vi /etc/my.cnf
  2. To enable the binary log, add an entry to the configuration file, like the following, using a log-bin value of your preference.
    log-bin=binlog
  3. To set the correct binary logging format and server_id variables, set the following:
    binlog-format=ROW
binlog-row-image=FULL

#For MySQL 8.0 and above, for DDL support
binlog-row-metadata=FULL

#Set to value > 0 and <= 4294967295
server-id=1

  4. Add additional variables for the other options listed in Configuring Logging Properties, based on your requirements, such as DDL support and MariaDB support.

  5. Save and close the file.

  6. Restart the database for the settings to take effect.
    [root@srcdbserver ~]$ service mysqld restart
  7. Check the status of the database service using the following command:
    [root@srcdbserver ~]$ service mysqld status

Modifying System Variables for Amazon and Azure MySQL Database Services

To configure Amazon and Azure MySQL databases for Oracle GoldenGate Extract and heartbeat functionality, required changes must be done using the parameter groups, database creation or modification, or server parameter settings, if not already set to the required values. Use the following guidelines to modify these system variables:

  1. To enable binary logging in Amazon, you must enable backups of the database, otherwise the log_bin variable will report as OFF and Extract will abend, reporting that it cannot retrieve the list of binlog files.

    Enable backups at database creation or by modify an existing database to include a backup regiment.

  2. For Amazon MySQL databases, set the binglog_format variable to ROW, the binlog_row_image variable to FULL, the event_scheduler to ON if implementing the Oracle GoldenGate heartbeat functionality, and set the binlog_row_metadata variable to FULL if enabling DDL capture support.

    Follow the instructions provided by Amazon to create or edit an existing Parameter group in order to make these changes.

    https://aws.amazon.com/premiumsupport/knowledge-center/enable-binary-logging-aurora/

  3. For Azure MySQL databases, set the binlog_format variable to ROW and the binlog_row_image variable to FULL. Follow the instructions provided by Microsoft to modify the server parameter settings for the database instance.

    https://docs.microsoft.com/en-us/azure/mysql/howto-server-parameters

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.

Binary log data retention can be increased with different measures depending on the MySQL database or variant.

  • For MySQL Server, MySQL Heatwave, Azure Database for MySQL, and MariaDB, the variable binlog_expire_logs_seconds can be increased to adjust the retention period.

  • For Amazon Aurora for MySQL and Amazon RDS for MySQL, the default binary log retention settings are NULL, which means, for Aurora the logs are lazily purged and for RDS for MySQL, they are not retained. Use the following procedure to set the parameter binlog retention hours. For example:

    call mysql.rds_set_configuration('binlog retention hours', 24);

Capturing Against a MySQL Replication Replica

You can configure Oracle GoldenGate to capture from a MySQL replication replica.

Typically, the transactions applied at the replica are logged into the relay logs and not into the replica's binlog. For the replica to write transactions that it receives from the primary, into its binlog, you must start the replication replica with the log_replica_updates option as 1 in my.cnf in conjunction with the other binary logging parameters for Oracle GoldenGate. After the primary’s transactions are in the replica’s binlog , you can set up a regular Oracle GoldenGate Extract on the replica to begin capture.