MySQL sources

Source preparation

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

Usage considerations

• •Database Ingestion and Replication supports MySQL, Amazon Aurora MySQL, Cloud SQL for MySQL, and RDS for MySQL sources for initial load, incremental load, and combined initial and incremental load jobs. Also, Database Ingestion and Replication supports Azure Database for MySQL sources with Snowflake targets for all load types and with Confluent Kafka for incremental loads.
• •Because Database Ingestion and Replication expects each source table row to be unique, Informatica recommends that each source table have a primary key. Database Ingestion and Replication does not honor unique indexes in place of a primary key. If no primary key is specified, Database Ingestion and Replication treats all columns as if they are part of the primary key.
• •If you update the primary key value in the MySQL source table for a record that does not exist in the target table, the record is not replicated to the target. However, the monitoring interface increments the Update count to include the primary key update. The data will replicate to the target if the record already exists in the target table before the update of the primary key value.
• •If you use a Secure Agent group with multiple agents and the active agent goes down unexpectedly, database ingestion and replication jobs can automatically switch over to another available agent in the Secure Agent group. The automatic switchover occurs after the 15 minute heartbeat interval elapses. For database ingestion and replication jobs that have a MySQL source, automatic switchovers are subject to following limitations:
• - Jobs cannot have persistent storage enabled.
• - Jobs that have Kafka targets must store checkpoint information in the Kafka header. For any jobs that existed before the July 2025 release, automatic switchovers can't occur because checkpoint information is stored in the checkpoint file in the Secure Agent.
• •Database ingestion and replication jobs that contain a MySQL source column with the SET or ENUM data type replicate the SET and ENUM column data as numeric values to the target. For initial load jobs, you can set the mysql.set.and.enum.as.numeric custom property to false on the Source page of the task wizard to replicate the SET or ENUM data in string or varchar format. The default value is true, which causes Database Ingestion and Replication to replicate the SET or ENUM data as numeric values.
Note: For incremental load jobs, you cannot toggle between the numeric and string or varchar representation of the SET or ENUM column data. If you set the mysql.set.and.enum.as.numeric custom property to false and run an initial load job, followed by an incremental load job, Database Ingestion and Replication replicates the SET and ENUM data to the target as numeric values only.
• •MySQL columns that have a LOB data type are supported for replication. The LOB data types are BLOB, LONGBLOB, MEDIUMBLOB, TINYBLOB, JSON, TEXT, LONGTEXT, MEDIUMTEXT, and TINYTEXT.
Note: JSON columns are not fully supported. The DataDirect MySQL JDBC driver does not parse Unicode characters in JSON columns correctly,which might lead to illegible characters on the target.
For information about the default mappings of supported source data types to target data types, see Default data-type mappings.