Azure Database for MySQL Flexible Server allows configuring high availability with automatic failover. With Zone-redundant HA your service has redundancy of infrastructure across multiple availability zones.

Zone-redundant HA is preferred when you want to achieve the highest level of availability against any infrastructure failure in the availability zone and when latency across the availability zone is acceptable.

Today I’ll show hot to test the failover of a zone-redundant MySQL Flexible Server instance.

Deploy Azure Database for MySQL Flexible Server with availability zones

Create a main.tf file with the following content:

  1terraform {
  2  required_version = "> 0.14"
  3  required_providers {
  4    azurerm = {
  5      version = ">= 2.87.0"
  6    }
  7    random = {
  8      version = "= 3.1.0"
  9    }
 10  }
 11}
 12
 13provider "azurerm" {
 14  features {}
 15}
 16
 17# Location of the services
 18variable "location" {
 19  default = "west europe"
 20}
 21
 22# Resource Group Name
 23variable "resource_group" {
 24  default = "mysql-failover"
 25}
 26
 27# Name of the mysql cluster
 28variable "mysql_name" {
 29  default = "mysql-failover"
 30}
 31
 32resource "random_id" "random" {
 33  byte_length = 8
 34}
 35
 36resource "azurerm_resource_group" "rg" {
 37  name     = var.resource_group
 38  location = var.location
 39}
 40
 41data "http" "current_public_ip" {
 42  url = "http://ipinfo.io/json"
 43  request_headers = {
 44    Accept = "application/json"
 45  }
 46}
 47
 48resource "azurerm_mysql_flexible_server" "flexible_server" {
 49  name                   = "${var.mysql_name}-${lower(random_id.random.hex)}"
 50  resource_group_name    = azurerm_resource_group.rg.name
 51  location               = azurerm_resource_group.rg.location
 52  administrator_login    = "psqladmin"
 53  administrator_password = "H@Sh1CoR3!"
 54  backup_retention_days  = 7
 55  sku_name               = "GP_Standard_D2ds_v4"
 56  high_availability {
 57    mode                      = "ZoneRedundant"
 58    standby_availability_zone = "2"
 59  }
 60  zone = "1"
 61}
 62
 63resource "azurerm_mysql_flexible_server_firewall_rule" "ip" {
 64  name                = "home"
 65  resource_group_name = azurerm_resource_group.rg.name
 66  server_name         = azurerm_mysql_flexible_server.flexible_server.name
 67  start_ip_address    = jsondecode(data.http.current_public_ip.body).ip
 68  end_ip_address      = jsondecode(data.http.current_public_ip.body).ip
 69}
 70
 71resource "azurerm_log_analytics_workspace" "logs" {
 72  name                = "mysql-logs"
 73  location            = azurerm_resource_group.rg.location
 74  resource_group_name = azurerm_resource_group.rg.name
 75  sku                 = "PerGB2018"
 76  retention_in_days   = 30
 77}
 78
 79resource "azurerm_monitor_diagnostic_setting" "monitor" {
 80  name                       = lower("extaudit-${var.mysql_name}-diag")
 81  target_resource_id         = azurerm_mysql_flexible_server.flexible_server.id
 82  log_analytics_workspace_id = azurerm_log_analytics_workspace.logs.id
 83
 84  metric {
 85    category = "AllMetrics"
 86
 87    retention_policy {
 88      enabled = false
 89    }
 90  }
 91
 92  log {
 93    category = "MySqlAuditLogs"
 94    enabled  = false
 95
 96    retention_policy {
 97      days    = 0
 98      enabled = false
 99    }
100  }
101  log {
102    category = "MySqlSlowLogs"
103    enabled  = false
104
105    retention_policy {
106      days    = 0
107      enabled = false
108    }
109  }
110
111  lifecycle {
112    ignore_changes = [metric]
113  }
114}
115
116output "resource_group" {
117  value = var.resource_group
118}
119
120output "mysql_name" {
121  value = azurerm_mysql_flexible_server.flexible_server.name
122}
123
124output "mysql_fqdn" {
125  value = azurerm_mysql_flexible_server.flexible_server.fqdn
126}
127
128output "administrator_login" {
129  value     = azurerm_mysql_flexible_server.flexible_server.administrator_login
130  sensitive = true
131}
132
133output "administrator_password" {
134  value     = azurerm_mysql_flexible_server.flexible_server.administrator_password
135  sensitive = true
136}

Note:

  • zone = "1" sets the primary availability zone.
  • high_availability.mode = "ZoneRedundant" enables availability zone redundancy.
  • high_availability.standby_availability_zone = "2" sets the stanby availability zone

For convinience, I added the password to the terraform configuration. Please don’t repeat this in your environment.

Deploy the Azure Database for MySQL Flexible Server with availability zones:

Run the following command to deploy the Azure Database for MySQL Flexible Server with availability zones:

1terraform init
2terraform apply -auto-approve

Test the Azure Database for MySQL Flexible Server failover

Create a Database:

1resource_group=$(terraform output -raw resource_group)
2mysql_name=$(terraform output -raw mysql_name)
3
4az mysql flexible-server db create --resource-group $resource_group --server-name $mysql_name --database-name failover_database

Check Database Connectivity:

1mysql_name=$(terraform output -raw mysql_name)
2mysql_user=$(terraform output -raw administrator_login)
3mysql_password=$(terraform output -raw administrator_password)
4
5az mysql flexible-server connect -n $mysql_name -u $mysql_user -p $mysql_password -d failover_database

Check the availability zone hosting the primary database:

1resource_group=$(terraform output -raw resource_group)
2mysql_name=$(terraform output -raw mysql_name)
3az mysql flexible-server show -n $mysql_name -g $resource_group --query "availabilityZone" -o tsv

you should get the following output:

11

Test the Azure Database for MySQL Flexible Server failover (Azure Portal):

To force the failover head to the Azure portal and use the Settings / High Availabilitysection of the Azure Database for MySQL Flexible Server and click on Forced Failover:

Forced Failover using the Azure Portal

Once failover start and the database becomes unavailable, the standby replica is activated as the primary database, DNS is updated, and clients must reconnect to the database server and resume their operations.

This process should take between 60 and 120 seconds. But, depending on the activity in the primary database server at the time of the failover, the failover might take longer.

Once the failover is complete, check again which availability zone hosts the primary database:

1resource_group=$(terraform output -raw resource_group)
2mysql_name=$(terraform output -raw mysql_name)
3az mysql flexible-server show -n $mysql_name -g $resource_group --query "availabilityZone" -o tsv

the output should be:

12

Hope it helps!!!

References: