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 Availability
section of the Azure Database for MySQL Flexible Server and click on Forced Failover
:
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:
Comments