This section describes the MySQL database requirements. These requirements fall into one of the following categories:
This release of Oracle GoldenGate for MySQL supports both the MySQL Enterprise and Community Editions.
MySQL provides a facility that allows users to specify different character sets at different levels.
create database test charset utf8;
create table test( id int, name char(100)) charset utf8;
create table test ( id int, name1 char(100) charset gbk, name2 char(100) charset utf8));
Although you can specify entirely different character sets at all these different levels, for data conversion from source (one character set) to target (another character set), Oracle GoldenGate currently supports only character sets at the database level.
Limitations of Support
When you specify the character set of your database as utf8mb4/utf8, the default collation is
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 utf8.
Requirements for the database storage engine are as follows:
Oracle GoldenGate supports the InnoDB storage engine for a source MySQL database.
On a target MySQL database, the Oracle GoldenGate Replicat process connects to the database through the MySQL native API.
The NDB cluster engine is supported.
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 of the Oracle GoldenGate processes that must connect to a database:
Extract (source database)
Replicat (target database)
DEFGEN (source or target database)
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
The Oracle GoldenGate user requires read access to the
The Oracle GoldenGate user requires the following user privileges.
Table 1-1 Oracle GoldenGate database user privileges for MySQL
If using the checkpoint table feature (recommended)
To execute stored procedures
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 (
Read and Execute permissions for the directory where the binary logs are located
Read and Execute permission for the
MySQL supports the following data types:
When running Oracle GoldenGate for MySQL, be aware of the following:
Oracle GoldenGate does not support
TEXT types when used as a primary key.
Oracle GoldenGate supports UTF8 and UCS2 character sets. UTF8 data is converted to UTF16 by Oracle GoldenGate before writing it to the trail.
UTF32 is not supported by Oracle GoldenGate.
Oracle GoldenGate supports a
TIME type range from 00:00:00 to 23:59:59.
Oracle GoldenGate supports timestamp data from
9999/12/31:23:59:59. If a timestamp is converted from GMT to local time, these limits also apply to the resulting timestamp. Depending on the time zone, conversion may add or subtract hours, which can cause the timestamp to exceed the lower or upper supported limit.
Oracle GoldenGate does not support negative dates.
The support of range and precision for floating-point numbers depends on the host machine. In general, the precision is accurate to 16 significant digits, but you should review the database documentation to determine the expected approximations. Oracle GoldenGate rounds or truncates values that exceed the supported precision.
When you use
ENUM type in non-strict
sql_mode, the non-strict
sql_mode does not prevent you from entering an invalid
ENUM value and an error will be returned. To avoid this situation, do one of the following:
STRICT and restart Extract. This prevents users from entering invalid values for any of the data types. An IE user can only enter valid values for those data types.
Continue using non-strict
sql_mode, but do not use
ENUM data types.
Continue using non-strict
sql_mode and use
ENUM data types with valid values in the database. If you specify invalid values, the database will silently accept them and Extract will abend.
To preserve transaction boundaries for a MySQL target, create or alter the target tables to the InnoDB transactional database engine instead of the MyISAM engine. MyISAM will cause Replicat records to be applied as they are received, which does not guarantee transaction integrity even with auto-commit turned off. You cannot roll back a transaction with MyISAM.
Extraction and replication from and to views is not supported.
Capturing from MySQL replication target is supported so you can capture from a MySQL replication slave.
MySQL does not support the following data types:
SET, and Geometry data types and similar are not supported.
There is limited support for the Interval data type.
Oracle GoldenGate for MySQL supports to following objects and operations:
Oracle GoldenGate supports the extraction and replication of transactional tables.
Oracle GoldenGate supports transactional tables up to the full row size and maximum number of columns that are supported by MySQL and the database storage engine that is being used. InnoDB supports up to 1000 columns.
Oracle GoldenGate supports the
AUTO_INCREMENT column attribute. The increment value is captured from the binary log by Extract and applied to the target table in a Replicat insert operation.
Oracle GoldenGate supports the following DML operations on source and target database transactional tables:
Update operation (compressed included)
Delete operation (compressed included); cascade delete queries result in the deletion of the child of the parent operation
Oracle GoldenGate supports the following initial load methods from a source MySQL database to a MySQL or other target database:
Loading data with Replicat
Loading data with an Oracle GoldenGate direct load
Oracle GoldenGate can operate concurrently with MySQL native replication.
Oracle GoldenGate supports the
DYNSQL feature for MySQL.
Limitations on Automatic Heartbeat Table support are as follows:
Ensure that the database in which the heartbeat table is to be created already exists to avoid errors when adding the heartbeat table.
In the heartbeat history lag view, the information in fields like
outgoing_heartbeat_age are shown with respect to the system time. You should ensure that the operating system time is setup with the correct and current time zone information.
Oracle GoldenGate for MySQL does not support the following objects and operations:
Extraction or replication of DDL (data definition language) operations.
The Oracle GoldenGate
Array fetching during initial load.
The following character sets are not supported: