Using Azure Automation PowerShell runbooks with MySQL flexible servers

You can use Azure Automation PowerShell runbooks to automate several operations on Azure Database for MySQL flexible servers, which can help to optimize costs and streamline resource management and data management. By leveraging runbooks, you can schedule these operations to occur during off-peak hours or trigger them based on events. Using automation eliminates the need for manual intervention and reduces the risk of errors while integrating seamlessly with Azure services. With built-in logging and monitoring, runbooks provide for reliable and efficient management of your MySQL flexible servers.

This article explains how to use Azure Automation PowerShell runbooks on an Azure Database for MySQL flexible server for three different types of operations.

• Starting/Stopping (using the Az.MySQL module)
• Performing an on-demand full backup (using the REST API)
• Connecting to and querying data (using the SimplySql module)

Prerequisites

Before proceeding, it’s important to verify that you have the following prerequisites in place.

• An Azure subscription. If you don't have one, you can activate your MSDN subscriber benefits or sign up for a free account.
• An Azure Database for MySQL flexible server. If you don't have one, you can Create an instance of Azure Database for MySQL with the Azure portal.

Stopping/Starting a MySQL flexible server

Let’s start with the instructions for stopping and starting an Azure Database for MySQL flexible server using and Azure Automation PowerShell runbook on a schedule.

Step 1: Create a new Azure Automation account with a System Assigned Managed Identity: https://portal.azure.com/#create/Microsoft.AutomationAccount. The system assigned managed identity will have the same name as the automation account.

Step 2: Assign permissions to the system-assigned managed identity to allow for stopping and starting the MySQL flexible server.

a) In the Azure portal, navigate to your MySQL flexible server, and then select Access Control (IAM).
b) Select Add --> Add Role Assignment.
c) Select Privileged Administrator Roles, select Contributor, and then select Next.
d) Select Managed Identity, and then select +Select Members.
e) Select the subscription in which the system-assigned managed identity was created in Step 1.
f) In the Managed Identity dropdown, under System-assigned managed identity, select Automation Account, select the system-assigned managed identity created in Step 1, and then select Review+Assign.

NOTE: To provide the system-assigned managed identity with more granular permissions that those of the Contributor Role, create a custom role within an assignable scope (such as a management group, subscriptions, or resource groups) and grant it the Microsoft.DBforMySQL/flexibleServers/start/action and Microsoft.DBforMySQL/flexibleServers/stop/action permissions.

Step 3: Next, create a PowerShell runbook to stop the MySQL flexible server.

a) In the Azure portal, navigate to your Automation account.
b) Under Process Automation, select Runbooks. Select Create a runbook and then name the runbook StopMySQLFlexibleServer.
c) In the Runbook type drop-down, select PowerShell., and then in the Runtime version drop-down, select 2 (recommended). Enter an applicable Description. Select Review+Create to create the runbook.
d) In the runbook editor, paste the following PowerShell code:

Connect-AzAccount -Identity
Import-Module Az.MySql
Stop-AzMySqlFlexibleServer -SubscriptionId "<SubscriptionId>" -ResourceGroupName "<ResourceGroupName>" -Name "<MySQLFlexibleServerName>"

e) Modify SubscriptionId, ResourceGroup and MySQLFlexibleServerName with appropriate values, and then select Save.
f) To test the runbook, on the Test Pane, select Start. After you've ensured that the runbook works as intended, select Publish.
g) In the runbook pane, select Link To Schedule and then select Add a schedule,.

h) On the New schedule pane, enter a name, description, and other scheduling parameters, and then select Create.

Step 4: Repeat Step 3 to create another PowerShell runbook named StartMySQLFlexibleServer to start the MySQL flexible server using the following PowerShell code:

Connect-AzAccount -Identity
Import-Module Az.MySql
Start-AzMySqlFlexibleServer -SubscriptionId "<SubscriptionId>" -ResourceGroupName "<ResourceGroupName>" -Name "<MySQLFlexibleServerName>"

a) Modify SubscriptionId, ResourceGroupName, and MySQLFlexibleServerName with appropriate values.

b) Select an appropriate schedule.

You can configure these runbooks to stop the MySQL flexible server on Fridays and start it on Mondays if you don’t need the server over the weekend. The runbooks can also leverage webhooks to trigger automations based on external events. 

Performing an on-demand full backup

To perform an on-demand backup of a MySQL flexible server, you can use the following PowerShell script, and you can automate it to run on a schedule by using an Azure Automation PowerShell Runbook.

$url = $env:IDENTITY_ENDPOINT  
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" 
$headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) 
$headers.Add("Metadata", "True") 
$body = @{resource='https://management.azure.com/' } 
$accessToken = Invoke-RestMethod $url -Method 'POST' -Headers $headers -ContentType 'application/x-www-form-urlencoded' -Body $body 
$token = $accessToken.access_token
$headers = @{
    "Authorization" = "Bearer $token"
    "Content-Type"  = "application/json"

$subscriptionId = "<SubscriptionId>"
$resourceGroup = "<ResourceGroupName>" 
$mysqlFlexibleServerName = "<MySQLFlexibleServerName>"
$mysqlBackupName = "<MySQLFlexibleServerBackupName>"
$uri = "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.DBforMySQL/flexibleServers/$mysqlFlexibleServerName/backups/$mysqlBackupName`?api-version=2023-10-01-preview"
$response = Invoke-RestMethod -Uri $uri -Headers $headers -Method Put
$response | ConvertTo-Json -Depth 10

Modify SubscriptionId, ResourceGroupName, MySQLFlexibleServerName, and MySQLFlexibleServerBackupName with appropriate values.

NOTE: To provide the system-assigned managed identity with more granular permissions that those of the Contributor Role, create a custom role within an assignable scope (such as a management group, subscriptions, or resource groups) and grant it the Microsoft.DBforMySQL/flexibleServers/backups/write permission.

Connecting to and querying data

To connect to and query data using an Azure Automation PowerShell runbook, 

Step 1: Install the SimplySql module for the Automation account.

a) In the Azure portal, navigate to your Automation account. Under Shared Resources, select Module, and then select Add a Module.
b) In the Gallery, select Browse, type SimplySql, select it, and then select Select. In the Runtime version drop-down, select 7.2 (recommended), and then select Import.

Important: If the MySQL flexible server is configured with Public Access, on the Networking tab, verify that Allow public access from any Azure service within Azure to this server is selected. If the MySQL flexible server is configured with Private Access, verify that the Automation Account and MySQL flexible server are on the same VNET.

Step 2: Create a new Credential Asset in the Automation account

a) From your Automation account, on the left-hand pane under Shared Resources, select Credentials.
b) On the Credentials page, select Add a credential.
c) On the New Credential pane, enter an appropriate credential name following your naming standards, for example MySqlFlexCredential. In the User name field, type the name of your MySQL flexible server user account, and then, for both password fields, specify the password for your MySQL flexible server user account.

Step 3: Create a PowerShell runbook using the following code

Import-Module SimplySql
$MySqlFlexCredential = Get-AutomationPSCredential -Name 'MySqlFlexCredential'
Open-MySqlConnection -server '<MySQLFlexibleServerName>.mysql.database.azure.com' -database 'mysql' -Credential $MySqlFlexCredential
$data = Invoke-SqlQuery -query "SELECT VERSION()"
$data
Close-SqlConnection

Modify MySQLFlexibleServerName, database name, and query with appropriate values. 

Conclusion

In conclusion, leveraging Azure Automation PowerShell runbooks to manage Azure Database for MySQL flexible servers offers significant benefits in terms of cost optimization and streamlined resource management. By automating operations such as starting and stopping servers, performing on-demand backups, and querying data, you can reduce manual intervention and minimize the risk of errors. The integration with Azure services and the built-in logging and monitoring capabilities ensures reliable and efficient management of your MySQL flexible servers.

If you have any queries or suggestions, please let us know by leaving a comment below or by contacting directly us at AskAzureDBforMySQL@service.microsoft.com. Thank you!