Monitoring and creating alerts for Data-in replication with Azure Database for MySQL-Flexible Server
Published Feb 08 2022 09:46 AM 2,260 Views
Microsoft

You can use Data-in replication allows to synchronize data from an external MySQL server with data in an Azure Database for MySQL flexible server. The external server can be hosted on-premises, in virtual machines, in an Azure Database for MySQL single server, or in a database service offered other cloud providers.

 

Data-in replication is based on the binary log (binlog) file position-based method. For more information, see Binary Log File Position Based Replication Configuration Overview.

 

This post provides details about how to monitor and create alerts for Data-in replication when the replication lag is high.

 

Prerequisites

Before you begin to work through the process outlined in this post, be sure that you have:

 

Monitor Data-in replication

The value of the Seconds_Behind_Master parameter, as displayed by the SHOW SLAVE STATUS command, is commonly used as an indication of the current replication lag of the replica. In Azure Database for MySQL - Flexible Server, the Replication Lag in Seconds metric tracks this value.

 

To monitor the replication lag, perform the following steps:

  1. In the Azure portal, select the replica Azure Database for MySQL flexible server that you want to monitor.
  2. In the resource menu on the left, under Monitoring, select Metrics.
  3. From the Metric drop-down list, select Replication Lag in Seconds, and then, in the Aggregation drop-down list, ensure that Max is selected.

 

1 - Monitor.png

 

A graph showing the replication lag over time appears.

 

2 Monitor_rep_lag.png

 

You can use this information to monitor the replication lag between your primary and replica servers.

 

Create an alert for when replication lag is high

If you notice that the replication lag is substantial, you can create an alert to ensure that you’re notified if the replication lag approaches a threshold that you set.

 

  1. On the Metrics blade, select New alert rule.

    3_New_Alert.png

     

  2. On the Create an alert rule page, on the Condition tab, under Condition name, select Whenever the maximum replication_lag is greater than <logic undefined>.

    4_Create_rule.png
  3. In the Configure signal logic dialog box, under Alert logic, in the Threshold value text box, enter 60.
  4. To refine the condition, in the Aggregation granularity (Period) drop-down list, select the interval over which data points are grouped using the aggregation type function (for example, 5 minutes).
  5. In the Frequency of evaluation drop-down list, select how frequently you want the status evaluated (for example, Every 5 Minutes), and then select Done.

    5_Condition.png
  6. Select Next: Action, and then on the Actions tab, select Add action group to add an existing action group, or select Create action group to create a new one.

    If you create a new action group, on the Create an action group page, under Instance details, specify the action group name and the display name. The display name is used in place of a full action group name when notifications are sent using this group.

    6_Create Action Group.png

     

  7. Select Next: Notifications, and then, on Notifications tab, define a list of notifications to send when an alert is triggered. For each notification, specify the:

    Notification type: The type of notification that you want to send. Use Email/SMS message/Push/Voice to send these types of notifications to specific recipients.
    Name: A unique name for the notification.
    Email: The email address to which you want the alert sent (specify in the Email/SMS message/Push/Voice dialog box).

    7_Create Action Group Actions.png

     

  8. Select OK, and then select Review + create.
  9. After the action group is created, on the Create an alert rule page, select Next: Details, and then specify the:

    Severity: Critical
    Alert Rule Name: Data-in-replication-Lag
    Alert rule description: The replication lag is high.

  10. Select Review + create.

 

Now, when the replication lag exceeds the threshold you specified, you’ll see alerts in portal under Monitoring > Alerts

 

8_alertview.png

 

You’ll also receive an email notification at the email address you configured in action croup. When the replication lag falls below the threshold value, you’ll receive another email, as the alert will have been resolved. These emails will appear similar to the following:

 

9_Emails.png

 

Conclusion

It’s important to monitor the replication lag and take necessary actions if the lag exceeds the threshold to ensure your replica is in close sync with your primary. The replication lag impacting replica servers depends on several factors, including but not limited to:

  • Network latency.
  • Transaction volume on the source server.
  • Compute tier of the source server and replica server.
  • Queries running on the source server and secondary server.

Monitoring and taking timely action to reduce the replication lag is necessary to ensure that the applications that are connected to a read replica server do not get inconsistent data.

 

Note: To troubleshoot and resolve replication lag issues, see the following resources:


If you have any feedback or questions, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com.

 

Thank you!

Co-Authors
Version history
Last update:
‎Feb 08 2022 10:00 AM
Updated by: