Edit

Replicate data into Azure Database for MySQL - Flexible Server

  • Article
  • 3 minutes to read

APPLIES TO: Azure Database for MySQL - Flexible Server

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

Note

GTID-based replication is currently not supported for Azure Database for MySQL - Flexible Servers.
Configuring Data-in replication for zone-redundant high-availability servers is not supported.

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 - Flexible Server. 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 - Flexible Server and different cloud providers, including virtual machines and database services hosted in those clouds.
  • Migration: Customers can do Minimal Time migration using open-source tools such as MyDumper/MyLoader with Data-in replication. A selective cutover of production load from source to destination database is possible with Data-in replication.

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 the tables in the system database, see the MySQL manual.

Data-in replication not supported on High Availability (HA) enabled servers

It isn't supported to configure Data-in replication for servers that have high availability (HA) option enabled. On HA-enabled servers, the stored procedures for replication mysql.az_replication_* won't be available.

Tip

If you are using the HA server as a source server, MySQL native binary log (binlog) file position-based replication will fail when failover happens on the server. If replica server supports GTID based replication, we should configure GTID based replication.

Filter

Parameter replicate_wild_ignore_table is used to create replication filter for tables on the replica server. To modify this parameter from Azure portal, navigate to Azure Database for MySQL flexible server used as replica and select "Server Parameters" to view/edit the replicate_wild_ignore_table parameter.

Requirements

  • The source server version must be at least MySQL version 5.7.
  • Our recommendation is to have the same version for source and replica server versions. For example, both must be MySQL version 5.7, or both must be MySQL version 8.0.
  • Our recommendation is to have a primary key in each table. If we have a table without primary key, you might face slowness in replication.
  • The source server should use the MySQL InnoDB engine.
  • User must have the right permissions to configure binary logging and create new users on the source server.
  • Binary log files on the source server shouldn't be purged before the replica applies those changes. If the source is Azure Database for MySQL refer how to configure binlog_expire_logs_seconds for flexible server or Single 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 stored procedure. Refer to the following examples and the master_ssl_ca parameter.
  • 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).
  • With public access, 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).
  • With private access, ensure that the source server name can be resolved and is accessible from the VNet where the Azure Database for MySQL instance is running. (For more details, visit Name resolution for resources in Azure virtual networks).

Next steps