Azure MySQL Database is an easy to set up, operate, and scale fully managed database service from Microsoft. It can automate your database management and maintenance, including routine updates, backups and security, enabling you to focusing on working with your data.

You can ingest data from your Azure MySQL database using Hevo Pipelines and replicate it to a Destination of your choice.

Source Considerations

• MySQL does not generate log entries for cascading deletes. So, Hevo cannot capture these deletes for log-based Pipelines.

• In log-based Pipelines, when a new column with default values is added to the Source table, the database does not generate log entries for it, as it is not treated as a row-level data change. As a result, Hevo cannot replicate this column to the Destination. To ensure that the Source schema and data are reflected accurately in the Destination, you must restart the historical load for that object.

• If your Pipeline uses BinLog ingestion mode, MySQL replicates timestamp fields such as created_at and updated_at in Coordinated Universal Time (UTC). As BinLogs do not include time zone metadata, Hevo and the Destination interpret these values in UTC. Due to this, you may observe a time difference if the Source database uses a different timezone. For example, if the Source timezone is US Eastern Time (UTC-4) and the timestamp for created_at in MySQL is 2024-05-01 10:00:00, it appears as 2024-05-01 14:00:00 in BigQuery. This behavior applies only to incremental loads via BinLogs. Any data replicated using historical load retains the original timestamp values from the Source.

  As a workaround, you can adjust the UTC timestamps to your local timezone using Python code-based Transformations by adding or subtracting the appropriate offset in the timestamp fields. For example, to convert UTC to UTC+7, add a 7-hour offset to the relevant fields before loading the data into the Destination.

• In MySQL, the value of the max_connections system variable determines the maximum number of simultaneous connections allowed for an application. If the number of active connections exceeds this value, Hevo might not be able to connect to your Source. In such cases, you should increase the value of the max_connections variable. You can modify this setting in the MySQL configuration file, which is my.cnf or my.ini, depending on your MySQL platform.

  To do this:

  1. Log in to your MySQL server host as a root user or a user with administrative privileges.

  2. Navigate to the MySQL configuration directory. For example, /etc/mysql.

  3. Open the MySQL server configuration file with any text editor.

  4. Scroll to the [mysqld] section and add or update the max_connections variable:

     max_connections = <new_value>
     

     Note: Replace the placeholder value in the command above with your own, for example, <new_value> with 500.

  5. Save the file and restart the MySQL server for the changes to take effect.

  After restarting, MySQL uses the updated value for max_connections.

• In MySQL, the max_user_connections system variable limits the maximum number of simultaneous connections that a single MySQL user, such as the one created for Hevo, can make to the Source database. By default, max_user_connections is set to 0, which means there is no limit on the number of connections. However, if this value is set to a non-zero number for the database user configured in your Pipeline and the user exceeds that value, Hevo may fail to connect to the Source database. In such cases, the max_user_connections variable should either be increased or reset to 0.

  To modify this setting for the database user configured in your Pipeline, do the following:

  1. Log in to your MySQL database instance as the root user or a user with administrative privileges. For example, mysql -u root -p.

  2. Run the following command to set or modify the limit

     ALTER USER '<username>'@'<hostname>' WITH MAX_USER_CONNECTIONS <new_value>;
     

     Note: Replace the placeholder values in the command above with your own, for example, <new_value> with 15.

     The changed value takes effect immediately and persists after server restarts.

Limitations

• Currently, Hevo does not support transaction-based replication using Global Transaction Identifiers (GTIDs).

• Logging in using SSL not supported. This setting is enabled by default. You can disable it as follows:

  1. Log in to the Azure Portal.

  2. Under Azure services, select More services.

     

  3. On the All services page, search for and select All resources.

     

  4. On the All resources page, click the database you want to connect to Hevo.

     

  5. In the left navigation pane, under Settings, click Server Parameters.

  6. Under the Top tab, update the value of require_secure_transport server parameter to OFF, and then click Save.

     

• Hevo only fetches tables from the MySQL database. It does not fetch other entities such as functions, stored procedures, views, and triggers.

  To fetch views, you can create individual Pipelines in Custom SQL mode. However, some limitations may arise based on the type of data synchronization, the query mode, or the number of Events. Contact Hevo Support for more details.

• During the historical load, Hevo reads table definitions directly from the MySQL database schema, whereas for incremental updates, Hevo reads from the BinLog. As a result, certain fields, such as nested JSON, are parsed differently during historical and incremental loads. In the Destination tables, nested JSON fields are parsed as a struct or JSON during historical loads, but as a string during incremental loads. This leads to a data type mismatch between the Source and Destination data, causing Events to be sidelined.

  To ensure JSON fields are parsed correctly during the historical load, you can apply transformations to every table containing nested JSON fields. Contact Hevo Support for more details.

• Hevo Pipelines may fail to process transactions in the BinLog if the size of the transaction exceeds 4GB. This problem is due to a MySQL bug that affects the library used by Hevo to stream Events, resulting in ingestion failures. In such cases, Hevo attempts to restart the ingestion process from the beginning of the transaction, skipping already processed Events. If the problem of transaction processing persists and the BinLog remains stuck, contact Hevo Support for assistance.

• Hevo does not load data from a column into the Destination table if its size exceeds 16 MB, and skips the Event if it exceeds 40 MB. If the Event contains a column larger than 16 MB, Hevo attempts to load the Event after dropping that column’s data. However, if the Event size still exceeds 40 MB, then the Event is also dropped. As a result, you may see discrepancies between your Source and Destination data. To avoid such a scenario, ensure that each Event contains less than 40 MB of data.

• For MySQL versions 8.0.4 and above, based on the value of the default_authentication_plugin system variable, connecting database users are authenticated using the caching_sha2_password plugin by default. However, Hevo does not currently support this plugin, and you may see the Public Key Retrieval is not allowed error. In that case, you can change the authentication plugin to mysql_native_password for the user.

  To do this, connect to your MySQL server as a root user and run the following command:

  ALTER USER '<database_username>'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
  

  Note: Replace the placeholder values in the command above with your own. For example, <database_username> with hevouser.

See Also

• Connecting Through Reverse SSH Tunnel

• MySQL Server System Variables

Revision History