How to create and manage read replicas in Azure Database for MySQL using PowerShell
APPLIES TO: 
Azure Database for MySQL - Single Server
Important
Azure Database for MySQL single server is on the retirement path. We strongly recommend that you upgrade to Azure Database for MySQL flexible server. For more information about migrating to Azure Database for MySQL flexible server, see What's happening to Azure Database for MySQL Single Server?
In this article, you learn how to create and manage read replicas in the Azure Database for MySQL service using PowerShell. To learn more about read replicas, see the overview.
Azure PowerShell
You can create and manage read replicas using PowerShell.
Prerequisites
To complete this how-to guide, you need:
- The Az PowerShell module installed locally or Azure Cloud Shell in the browser
- An Azure Database for MySQL server
Important
While the Az.MySql PowerShell module is in preview, you must install it separately from the Az
PowerShell module using the following command: Install-Module -Name Az.MySql -AllowPrerelease.
Once the Az.MySql PowerShell module is generally available, it becomes part of future Az
PowerShell module releases and available natively from within Azure Cloud Shell.
If you choose to use PowerShell locally, connect to your Azure account using the Connect-AzAccount cmdlet.
Azure Cloud Shell
Azure hosts Azure Cloud Shell, an interactive shell environment that you can use through your browser. You can use either Bash or PowerShell with Cloud Shell to work with Azure services. You can use the Cloud Shell preinstalled commands to run the code in this article, without having to install anything on your local environment.
To start Azure Cloud Shell:
| Option | Example/Link |
|---|---|
| Select Try It in the upper-right corner of a code or command block. Selecting Try It doesn't automatically copy the code or command to Cloud Shell. |  |
| Go to https://shell.azure.com, or select the Launch Cloud Shell button to open Cloud Shell in your browser. |  |
| Select the Cloud Shell button on the menu bar at the upper right in the Azure portal. |  |
To use Azure Cloud Shell:
Start Cloud Shell.
Select the Copy button on a code block (or command block) to copy the code or command.
Paste the code or command into the Cloud Shell session by selecting Ctrl+Shift+V on Windows and Linux, or by selecting Cmd+Shift+V on macOS.
Select Enter to run the code or command.
Important
The read replica feature is only available for Azure Database for MySQL servers in the General Purpose or Memory Optimized pricing tiers. Ensure the source server is in one of these pricing tiers.
If GTID is enabled on a primary server (gtid_mode = ON), newly created replicas will also have GTID enabled and use GTID based replication. To learn more refer to Global transaction identifier (GTID)
Create a read replica
Important
If your source server has no existing replica servers, source server might need a restart to prepare itself for replication depending upon the storage used (v1/v2). Please consider server restart and perform this operation during off-peak hours. See Source Server restart for more details.
A read replica server can be created using the following command:
Get-AzMySqlServer -Name mydemoserver -ResourceGroupName myresourcegroup |
New-AzMySqlReplica -Name mydemoreplicaserver -ResourceGroupName myresourcegroup
The New-AzMySqlReplica command requires the following parameters:
| Setting | Example value | Description |
|---|---|---|
| ResourceGroupName | myresourcegroup | The resource group where the replica server is created. |
| Name | mydemoreplicaserver | The name of the new replica server that is created. |
To create a cross region read replica, use the Location parameter. The following example creates a replica in the West US region.
Get-AzMySqlServer -Name mrdemoserver -ResourceGroupName myresourcegroup |
New-AzMySqlReplica -Name mydemoreplicaserver -ResourceGroupName myresourcegroup -Location westus
Note
To learn more about which regions you can create a replica in, visit the read replica concepts article.
By default, read replicas are created with the same server configuration as the source unless the Sku parameter is specified.
Note
It is recommended that the replica server's configuration should be kept at equal or greater values than the source to ensure the replica is able to keep up with the master.
List replicas for a source server
To view all replicas for a given source server, run the following command:
Get-AzMySqlReplica -ResourceGroupName myresourcegroup -ServerName mydemoserver
The Get-AzMySqlReplica command requires the following parameters:
| Setting | Example value | Description |
|---|---|---|
| ResourceGroupName | myresourcegroup | The resource group where the replica server will be created to. |
| ServerName | mydemoserver | The name or ID of the source server. |
Delete a replica server
Deleting a read replica server can be done by running the Remove-AzMySqlServer cmdlet.
Remove-AzMySqlServer -Name mydemoreplicaserver -ResourceGroupName myresourcegroup
Delete a source server
Important
Deleting a source server stops replication to all replica servers and deletes the source server itself. Replica servers become standalone servers that now support both read and writes.
To delete a source server, you can run the Remove-AzMySqlServer cmdlet.
Remove-AzMySqlServer -Name mydemoserver -ResourceGroupName myresourcegroup
Known Issue
There are two generations of storage which the servers in General Purpose and Memory Optimized tier use, General purpose storage v1 (Supports up to 4-TB) & General purpose storage v2 (Supports up to 16-TB storage). Source server and the replica server should have same storage type. As General purpose storage v2 is not available in all regions, please make sure you choose the correct replica region while you use location with the PowerShell for read replica creation. On how to identify the storage type of your source server refer to link How can I determine which storage type my server is running on.
If you choose a region where you cannot create a read replica for your source server, you will encounter the issue where the deployment will keep running as shown in the figure below and then will timeout with the error “The resource provision operation did not complete within the allowed timeout period.”
Get-AzMySqlServer -Name <source-servername> -ResourceGroupName <RG-Name> | New-AzMySqlReplica -Name <replica-servername> -ResourceGroupName <RG-Name> -Location <region-name>