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.

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:
  
  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.
If capturing from MariaDB versions 10.2 and later, the variable binlog-annotate-row-events=OFF must be set in the my.cnf or my.ini file when running Oracle GoldenGate 21.18 or prior versions. Restart MariaDB after configuring this variable and then start the Extract process. Oracle GoldenGate 21.19 and later does not require this variable to be set.

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.