MySQL as Source

Capture

Fivetran HVR supports capturing changes from MySQL database (including Amazon RDS for MySQL). This section describes the configuration requirements for capturing changes from MySQL database. For the list of supported MySQL versions from which HVR can capture changes, see Capture changes from location in Capabilities.

Capture Methods

HVR allows the following methods for capturing (Capture) changes from MySQL:

In terms of capture speed and database resource consumption, there is not much difference between using SQL or DIRECT method for capturing from a MySQL location. By default, HVR captures changes from MySQL using the SQL capture method (Capture_Method=SQL).

Direct Binary Log Reading

In this capture method (Capture_Method=DIRECT), HVR reads transaction log records directly from the DBMS log file using the file I/O. This capture method requires:

• an HVR Agent to be installed on the MySQL source database server.
• the operating system user under which the HVR is running must have permission to read from the binary log files.

Direct binary log reading is not supported on Amazon RDS for MySQL.

Binary Logs via SQL

In this capture method (Capture_Method=SQL), HVR captures changes over an SQL connection. The benefits of this capture method are the ability to:

• capture from a local/remote database without using an HVR Agent.
• run with minimal OS and database privileges.

Grants for Capture

This section lists the grants required for capturing changes from MySQL database.

• To capture changes from MySQL, the HVR database User must be granted the following privileges:

  grant replication client on *.* to 'username'@'%';
  
  grant replication slave on *.* to 'username'@'%';
  
  grant select on *.* to 'username'@'%';
  

Configuration for Binary Logging

In MySQL, the transaction updates are recorded in the binary logs. For HVR to capture changes, the binary logging should be configured in MySQL database. MySQL allows you to define system variables (parameters) at server level (global) and at session level. The configuration for binary logging should be strictly defined as mentioned in this section. Defining parameters not mentioned in this section may lead to HVR not capturing changes.

For more information about binary logging, search for "binary logging" in MySQL Documentation.

If binary logging is not enabled in MySQL, a similar error is displayed in HVR: "hvrinit: F_JD0AC8: The 'SHOW MASTER STATUS' command returned no results. Please check that the binary logging is enabled for the source database. F_JR0015: The previous error was detected during generation of objects for channel hvr_demo. It is possible that objects have been left incomplete."

Binary Logging for Regular MySQL

The following parameters should be defined in MySQL configuration file my.cnf (Unix) or my.ini (Windows):

• log_bin=ON - to enable binary logging in MySQL.
• binlog_format=ROW - to set the binary logging format.
• binlog_row_image=full or binlog_row_image=noblob - to determine how row images are written to the binary log.

To capture changes from a MySQL replica, the log_replica_updates parameter must be set to ON in the replica database. Note that prior to MySQL version 8.0.26, this parameter was named log_slave_updates.

binlog_row_image=minimal is not supported by HVR.

Binary Logging for Amazon RDS for MySQL

This section provides information required for configuring binary logging in Amazon RDS for MySQL database. To enable binary logging, perform the steps mentioned in the Amazon documentation.

Backup retention period in Amazon RDS for MySQL

Enable automatic backups on the source MySQL DB instance by setting the backup retention period to a value greater than 0. The backup retention period setting defines the number of days for which automated backups are retained. The primary reason for this is that Amazon RDS normally purges a binary log as soon as possible, but the binary log must still be available on the instance to be accessed.

In Amazon RDS for MySQL, disabling automatic backups may implicitly disable binary logging which will lead to replication issues in HVR.

To specify the number of hours for RDS to retain binary logs, use the mysql.rds_set_configuration stored procedure and specify a period with enough time for you to access the logs.

The mysql.rds_set_configuration stored procedure is only available for MySQL version 5.6 or later.

The following example sets the retention period to 1 day:

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

To display the current setting, use the mysql.rds_show_configuration stored procedure:

call mysql.rds_show_configuration;

Capture Limitations

• HVR only supports capturing data from the primary database when GTID is enabled, and cannot automatically handle replication failover if the database switches or fails over.

Compare and Refresh from MySQL

HVR allows you to perform only Compare and Refresh from MySQL (including Amazon RDS for MySQL) database (without using Capture). This section describes the configuration requirements for performing only Compare and Refresh from MySQL database.

Grants for Compare and Refresh from MySQL

This section lists the grants required for performing only Compare and Refresh from MySQL database.

• To read from the MySQL database, the HVR database User must be granted the following privilege:

  grant select on table_name to username;