Share via

Replicate data into Azure Database for MySQL

  • Article

APPLIES TO: Azure Database for MySQL - Single Server

Important

Azure Database for MySQL single server is on the retirement path. We strongly recommend that you upgrade to Azure Database for MySQL flexible server. For more information about migrating to Azure Database for MySQL flexible server, see What's happening to Azure Database for MySQL Single Server?

Data-in Replication allows you to synchronize data from an external MySQL server into the Azure Database for MySQL service. The external server can be on-premises, in virtual machines, or a database service hosted by other cloud providers. Data-in Replication is based on the binary log (binlog) file position-based or GTID-based replication native to MySQL. To learn more about binlog replication, see the MySQL binlog replication overview.

When to use Data-in Replication

The main scenarios to consider about using Data-in Replication are:

  • Hybrid Data Synchronization: With Data-in Replication, you can keep data synchronized between your on-premises servers and Azure Database for MySQL. This synchronization is useful for creating hybrid applications. This method is appealing when you have an existing local database server but want to move the data to a region closer to end users.
  • Multi-Cloud Synchronization: For complex cloud solutions, use Data-in Replication to synchronize data between Azure Database for MySQL and different cloud providers, including virtual machines and database services hosted in those clouds.

For migration scenarios, use the Azure Database Migration Service(DMS).

Limitations and considerations

Data not replicated

The mysql system database on the source server isn't replicated. In addition, changes to accounts and permissions on the source server aren't replicated. If you create an account on the source server and this account needs to access the replica server, manually create the same account on the replica server. To understand what tables are contained in the system database, see the MySQL manual.

Filtering

To skip replicating tables from your source server (hosted on-premises, in virtual machines, or a database service hosted by other cloud providers), the replicate_wild_ignore_table parameter is supported. Optionally, update this parameter on the replica server hosted in Azure using the Azure portal or Azure CLI.

To learn more about this parameter, review the MySQL documentation.

Supported in General Purpose or Memory Optimized tier only

Data-in Replication is only supported in General Purpose and Memory Optimized pricing tiers.

The private link for Azure database for MySQL support only inbound connections. As data-in replication requires outbound connection from service private link is not supported for the data-in traffic.

Note

GTID is supported on versions 5.7 and 8.0 and only on servers that support storage up to 16 TB (General purpose storage v2).

Requirements

  • The source server version must be at least MySQL version 5.6.
  • The source and replica server versions must be the same. For example, both must be MySQL version 5.6 or both must be MySQL version 5.7.
  • Each table must have a primary key.
  • The source server should use the MySQL InnoDB engine.
  • User must have permissions to configure binary logging and create new users on the source server.
  • If the source server has SSL enabled, ensure the SSL CA certificate provided for the domain has been included in the mysql.az_replication_change_master or mysql.az_replication_change_master_with_gtid stored procedure. Refer to the following examples and the master_ssl_ca parameter.
  • Ensure that the source server's IP address has been added to the Azure Database for MySQL replica server's firewall rules. Update firewall rules using the Azure portal or Azure CLI.
  • Ensure that the machine hosting the source server allows both inbound and outbound traffic on port 3306.
  • Ensure that the source server has a public IP address, that DNS is publicly accessible, or that the source server has a fully qualified domain name (FQDN).

Next steps