- •To deploy and run a database ingestion and replication task that includes a MySQL source, the source connection must specify a database user who has the required privileges. Use the following SQL statements to grant these privileges to the user:
GRANT SELECT ON database_name.* TO 'user_name'@'%';
GRANT SELECT ON TABLE database_name.* TO 'user_name'@'%';
For incremental load jobs, grant the following additional privileges to the user:
/* To fetch table and column details from system tables */
GRANT SELECT ON sys.* TO 'user_name'@'%';
/* To allow the user to monitor binary log information such as file name, position, and GTID */
GRANT REPLICATION CLIENT ON *.* TO 'user_name;
/* Required for a multi-node MySQL cluster with master and slave configuration */
GRANT REPLICATION SLAVE ON *.* TO 'user_name;
- •For incremental load jobs, set the default_storage_engine variable to InnoDB in the [mysqld] section of the my.cnf file. Then restart the MySQL server. To verify the default_storage_engine setting, use the following statement:
SHOW VARIABLES LIKE '%engine%';
In the output, make sure that the default_storage_engine variable is set to InnoDB.
- •Database Ingestion and Replication uses binary log files generated by MySQL for capturing change events at the source. The binlog is a set of log files that contain information about data modifications made to a MySQL server instance.
To enable binary logging, start the server with the --log-bin option or use the key-value log-bin="[HostName]-bin" setting in the my.cnf file. Replace [HostName] with the name of your host. Then restart the MySQL server. To verify that binary logging is enabled, use the following statement:
SHOW VARIABLES LIKE 'log_bin';
In the output, make sure that the log_bin variable is set to ON.
- •For incremental load jobs, enable row-based logging by using the following statement:
SET GLOBAL binlog_format = 'ROW';
To verify that row-based logging is enabled, use the following statement:
SHOW VARIABLES LIKE 'binlog_format';
In the output, make sure that the binlog_format system variable is set to ROW.
- •To enable database ingestion and replication incremental load and combined load jobs to process Updates and Deletes, set the following system variable for binary logging:
binlog_row_image=full
This setting causes both before images and after images to be logged to the binary log for all columns. It's applicable to any MySQL source type and version that Database Ingestion and Replication supports.
To verify this setting, use the following statement:
SHOW VARIABLES LIKE 'binlog_row_image';
- •Database Ingestion and Replication can read the binlog files in either one of the following ways:
- - Global Transaction ID (GTID) - If you enable MySQL GTID mode, every transaction in MySQL is assigned a GTID to uniquely identify the transaction. Use GTID mode in a multi-cluster environment.
- - Binlog file name and position - All transactions in MySQL are saved as anonymous and fetched by using the binlog file name and position. Do not use this method if the MySQL GTID mode is enabled or if you have a multi-cluster environment. In a multi-cluster environment, the binlog file position might vary if a failover occurs, causing inconsistent data.
To enable the GTID mode, use the following statements on each MySQL server:
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
On each server, wait until the status variable 'Ongoing_anonymous_transaction_count' is 0 (zero). You can check the status variable's value by using the following statement:
SHOW STATUS LIKE 'Ongoing_anonymous_transaction_count';
When the count is 0, use the following statement to enable GTID mode:
SET @@GLOBAL.GTID_MODE = ON;
- • You must download the MySQL driver file and copy it to a specific installation subdirectory to be able to connect to a MySQL source, if you use the following load type and source edition combinations:
- - Incremental load jobs that have MySQL Community Edition or MySQL Enterprise Edition sources
- - Initial load jobs that have MySQL Community Edition sources
- - Initial load jobs that have Amazon Relational Database Service (RDS) for MySQL sources
Note: You do not need to download the driver if you only run initial load jobs that have MySQL Enterprise Edition sources.
Download the MySQL JDBC driver file, mysql-connector-java-<version>.jar, from the MySQL Community Downloads website and copy it to the following directory:
<Secure_Agent_installation_directory>/ext/connectors/thirdparty/com.mysql/
If you want to be able to test a connection in Administrator after defining connection properties, you must also set the MySQL_JDBC_DRIVER_JARNAME parameter for the Data Integration Server service in the Secure Agent's system configuration details. After the test, you can remove the parameter. This parameter is not used when you use the connection to create a database ingestion and replication task or run the associated job.