Setting Up MySQL Replication on Azure

1. Prerequisites

Before you begin, ensure you have the following:

• An Azure subscription.
• An existing Azure Database for MySQL server (source/primary).
• A second Azure Database for MySQL server (replica/secondary). Ensure it's the same or a later version of MySQL.
• Network connectivity between the two servers is crucial. If using VNet integration or private endpoints, ensure appropriate firewall rules and private DNS configurations are in place.
• Appropriate permissions to manage both MySQL servers.

2. Configure the Source Server

For replication to work, certain server parameters on the source server need to be configured.

Enable Binary Logging

Binary logging is essential for replication. In Azure Database for MySQL, this is typically enabled by default for versions that support replication. You can verify this in the Azure portal under your source server's Server parameters.

• Navigate to your source Azure Database for MySQL server in the Azure portal.
• Under Settings, select Server parameters.
• Search for log_bin and ensure it is set to ON.
• Search for binlog_format and set it to ROW or MIXED. ROW is generally recommended for consistency.
• Restart the server if you made any changes to these parameters.

Create a Replication User

You need a dedicated user on the source server with replication privileges.

-- Connect to your source MySQL server
-- Then execute the following SQL command:
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'YourStrongPassword';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

Note: Replace 'YourStrongPassword' with a secure password.

Configure Firewall Rules

Ensure the replica server can connect to the source server. If your source server uses public access, you might need to add the replica server's public IP address or the Azure services IP range to the source server's firewall rules.

• In the Azure portal, navigate to your source server.
• Under Settings, select Connection security.
• Click Add to add a new firewall rule. Specify a rule name, and set the Start and End IP addresses appropriately. If the replica is in the same region and uses private endpoints, ensure VNet rules are configured correctly.

3. Configure the Replica Server

Set up Replication

Now, configure the replica server to connect to the source and start receiving changes.

-- Connect to your replica MySQL server
-- Then execute the following SQL command:
CALL mysql.az_replication_config_primary(
    'SourceServerName.mysql.database.azure.com', -- Primary server hostname
    'repl_user',                                -- Replication username
    'YourStrongPassword',                       -- Replication user password
    3306,                                       -- Primary server port (usually 3306)
    'PrimaryServerResourceGroup'                -- Primary server resource group
);

Important: Ensure you replace placeholders with your actual server names, username, password, and resource group. The mysql.az_replication_config_primary stored procedure simplifies this process in Azure Database for MySQL.

Start Replication

After configuring, you need to start the replication process.

-- Connect to your replica MySQL server
-- Then execute the following SQL command:
CALL mysql.az_replication_start;

4. Verify Replication Status

It's crucial to monitor the replication status to ensure it's working correctly.

-- Connect to your replica MySQL server
-- Then execute the following SQL command:
SHOW REPLICA STATUS\G;
-- or for older versions:
-- SHOW SLAVE STATUS\G;

Look for the following key fields in the output:

• Replica_IO_Running: Yes (or Slave_IO_Running: Yes)
• Replica_SQL_Running: Yes (or Slave_SQL_Running: Yes)
• Seconds_Behind_Source: 0 (or a low, stable number)

If any of these are 'No' or if Seconds_Behind_Source is continuously increasing, you need to troubleshoot.

5. Stopping and Resetting Replication

If you need to stop or reconfigure replication:

Stop Replication

-- Connect to your replica MySQL server
CALL mysql.az_replication_stop;

Reset Replication Configuration

To remove the existing replication configuration and start fresh:

-- Connect to your replica MySQL server
CALL mysql.az_replication_reset;