Enable a Secondary Instance

  • Article
  • 2 minutes to read

The Enable a Secondary Instance function helps you to further improve the availability of a MySQL Database on Azure instance. After you have activated the Secondary Instance function for a MySQL Database on Azure instance, Azure automatically creates a Secondary Instance. Data is synchronized between the primary and Secondary Instance through replication technology. To meet the high level of read demand on the app, you can use the Secondary Instance as a read-only instance. If the master instance fails, Azure automatically upgrades the Secondary Instance to become the new master instance and create a new Secondary Instance. After the Secondary Instance function is enabled, the failure recovery time is generally in the minute range (less than 60 seconds). There is no data loss after a failover.

Limitations on the Enable a Secondary Instance function

This function currently has the following limitations:

  • The Enable a Secondary Instance function is not supported on read-only instances or external subordinate instances.
  • Restoring to the current server is not supported.
  • Directly creating a Secondary Instance is not supported, because you can enable or disable the Secondary Instance function only for existing instances.

Note

If you want to upgrade the MS instance (MS1-MS6) of an open readable backup database function to the MP service layer (MP1, MP2), first close the MS instance’s readable backup database function, wait until the upgrade is complete, and then reopen it. Closing a readable backup database before upgrading might cause the upgrade to fail. See the following for details of how to open and close readable backup database functions.

Enable the Secondary Instance function by using PowerShell

To enable the Secondary Instance function, enter the following commands in PowerShell:

Set-AzureRMResource -ResourceType "Microsoft.MySql/servers" -ResourceName mpswitch00 -ApiVersion 2015-09-01 -ResourceGroupName Default-MySql-ChinaEast -PropertyObject @{enableSecondary=$true} -UsePatchSemantics

Disable the Secondary Instance function by using PowerShell

Enter the following commands in PowerShell to disable the Secondary Instance function:

Set-AzureRMResource -ResourceType "Microsoft.MySql/servers" -ResourceName mpswitch00 -ApiVersion 2015-09-01 -ResourceGroupName Default-MySql-ChinaEast -PropertyObject @{enableSecondary=$false} -UsePatchSemantics

View the Secondary Instance by using PowerShell

To view the enabled Secondary Instance function and the corresponding outputs, enter the following command:

Get-AzureRMResource -ResourceType "Microsoft.MySql/servers"   -ResourceName mpswitch00 -ApiVersion 2015-09-01 -ResourceGroupName Default-MySql-ChinaEast

Here is a list of the outputs:

Name : mpswitch00

ResourceId : /…/mpswitch00

ResourceName : mpswitch00

ResourceType : Microsoft.MySql/servers

ResourceGroupName : Default-MySql-ChinaEast

Location : ChinaEast

SubscriptionId : …

Properties : @{AllowAzureServices=True; Version=5.7; ProvisioningState=Succeeded; EnableSecondary=True; ServiceEndPoint=mpswitch00.mysqldb.chinacloudapi.cn:3306; RunningState=Running}

ETag : W/"datetime'2017-03-22T14%3A27%3A14.8784432Z'"

Sku : @{Name=MP1}

Connect to the Secondary Instance

The name of the Secondary Instance is [Master Instance name]-sec. Change the master instance name in the database account to the name of the Secondary Instance to connect to the Secondary Instance. Master and subordinate instances on the same database account have the same password.

Let’s assume that you have the database instance mpswitch. The server address for this instance is mpswitch.mysqldb.chinacloudapi.cn. It has the database account mpswitch%user. If you enable the Secondary Instance function for this instance, the server address of the instance will be mpswitch-sec.mysqldb.chinacloudapi.cn and the database account for the Secondary Instance will be mpswitch-sec%user.

Test instance crashes

You can use a select crash() fault injection query to force the master instance to crash, thereby triggering failover, so you can test fault recovery.