Use Windows PowerShell to manage MySQL Database on Azure

  • Article
  • 7 minutes to read

Use PowerShell to do more with MySQL Database on Azure, including create, view, delete, and modify operations. For helpful background information, first read Using Azure Resource Manager and PowerShell to deploy and use MySQL Database on Azure. This document explains how to download and use Azure PowerShell, and how to use PowerShell to quickly create MySQL Database on Azure data services.

Before you begin, ensure that you have properly prepared PowerShell.

Note: This article applies to Azure PowerShell 0.9*. If the version you use is 1.0.0 or later, see Notes for using Azure in China for Azure PowerShell 1.0.0 and above. Change AzureResource in the command below to AzureRmResource for running.

Contents

1. Understanding Azure Resource Manager templates and resource groups

Most apps that are deployed and run on Azure are built from combinations of various types of cloud resources. With Azure Resource Manager templates, you can to centralize the deployment and management of these different resources, so that all you need to do is complete a JavaScript Object Notation (JSON) description of the resources and the associated configuration and deployment parameters.

1.1 Understand resource-type parameter information in MySQL Database on Azure

Six resource types are currently defined in MySQL Database on Azure JSON files: servers, databases, users, privileges, firewall rules, and backups. Users can perform view, create, modify, and delete operations on these six resource types by using the Get, New, Set, and Remove commands. To find our more about API parameter definitions for MySQL Database on Azure services, downlload Json template files.

2. Operations: Create

The New command allows you to create MySQL servers, databases, users, user permissions, backups, and firewall rules.

2.1 Create servers

To finish creating the server, set the information, including your server name, location and version, by editing and running the following commands:

New-AzureRmResource -ResourceType "Microsoft.MySql/servers" -ResourceName testPSH -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -Location chinaeast -SkuObject @{name='MS4'} -PropertyObject @{version = '5.5'}

2.2 Create server firewall rules

To finish creating the firewall rules, set the information, including your firewall rule names and safe IP range (start IP address and end IP address), by editing and running the following commands:

New-AzureRmResource -ResourceType "Microsoft.MySql/servers/firewallRules" -ResourceName testPSH/rule1 -ApiVersion 2015-09-01 -PropertyObject @{startIpAddress="0.0.0.0"; endIpAddress="255.255.255.255"} -ResourceGroupName resourcegroupChinaEast

2.3 Create databases

To finish creating the database, set the information, including your database name and character sets, by editing and running the following commands:

New-AzureRmResource -ResourceType "Microsoft.MySql/servers/databases" -ResourceName testPSH/demodb -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -PropertyObject @{collation='utf8_general_ci'; charset='utf8'}

2.4 Create users

To finish creating users, set the information, including your username and password, by editing and running the following commands:

New-AzureRmResource -ResourceType "Microsoft.MySql/servers/users" -ResourceName testPSH/admin -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -PropertyObject @{password='abc123'}

2.5 Add user permissions

To assign database read/write permissions to users (permissions can be either Read or ReadWrite, edit and run the following commands: Permissions can be either “Read” or “ReadWrite.”

New-AzureRmResource -ResourceType "Microsoft.MySql/servers/databases/privileges" -ResourceName testPSH/demodb/admin -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -PropertyObject @{level='ReadWrite'}

2.6 Create on-demand backup files

To set backup file names and create on-demand backup files, edit and run the following commands:

New-AzureRmResource -ResourceType "Microsoft.MySql/servers/backups" -ResourceName testPSH/back1 -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -PropertyObject @{}

2.7 Backup and recovery of the server (snapshot-based recovery)

In this operation, server and region are mandatory fields. If the backup is not specified, recovery is performed by using the latest snapshot copy by default. 

New-AzureRmResource -ResourceType "Microsoft.MySql/servers" -ResourceName testrestore -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -Location ChinaEast -Properties @{creationSource=@{server='testPSH';region='chinaEast' ; backup = 'testpsh1b0a9038-6953-42ad-ac8e-42f73180825b'};version = '5.5'}

3. Operations: View

Use the Get command to view lists such as current MySQL servers, databases, users, user permissions, backups, and firewall rules. You can also view detailed parameters and configurations.

3.1 View server lists

Note

Note: The examples that are created in the Azure management portal are distributed to the following resource groups based on the area where the example is located: Default-MySql-ChinaEast and Default-MySql-ChinaNorth

To view all the current server lists, edit and run the following commands:

Get-AzureRmResource -ResourceType "Microsoft.MySql/servers"  -ApiVersion 2015-01-01 -ResourceGroupName resourcegroupchinaeast

Note

** This command checks that the “-ApiVersion 2015-01-01” in the server is directed at the Azure Resource Manager API. In all other commands, this is “-ApiVersion 2015-09-01” and directed at the MySQL API. **

3.2 View database lists and parameters

To view all database lists for a specific server in the current resource group, edit and run the following commands:

 Get-AzureRmResource -ResourceType "Microsoft.MySql/servers/databases" -Name testPSH -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast

3.3 View user lists and parameters

To view all user lists for a specific server in the current resource group, edit and run the following commands:

 Get-AzureRmResource -ResourceType "Microsoft.MySql/servers/users" -Name testPSH -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast

3.4 View user permissions lists and parameters

To view all user permissions for a specific database in the current resource group, edit and run the following commands:

 Get-AzureRmResource -ResourceType "Microsoft.MySql/servers/databases/privileges" -Name testPSH/demodb -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast

3.5 View backup lists and parameters

To view all backup files for a specific server in the current resource group, edit and run the following commands:

 Get-AzureRmResource -ResourceType "Microsoft.MySql/servers/backups" -Name testPSH -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast

3.6 View firewall rule lists and parameters

To view all firewall rules for a specific server in the current resource group, edit and run the following commands:

 Get-AzureRmResource -ResourceType "Microsoft.MySql/servers/firewallRules" -Name testPSH -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast

3.7 View slow query logs

To view all slow query logs for a specific server in the current resource group, edit and run the commands:

Get-AzureRmResource -ResourceType "Microsoft.MySql/servers/slowlogs" -Name testPSH -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast

4. Operations: Modify

Use the Set command to carry out configuration tasks, such as changing account passwords, modifying permissions, and altering certain server parameters.

4.1 Modify account passwords

To change the password for a specific account, edit and run the following commands:

Set-AzureRmResource -ResourceType "Microsoft.MySql/servers/users" -ResourceName testPSH/admin -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -PropertyObject @{password='abc1234'} -UsePatchSemantics

4.2 Modify access permissions for a particular user

To modify the access permissions for a specific user, edit and run the following commands:

Set-AzureRmResource -ResourceType "Microsoft.MySql/servers/databases/privileges"        
-ResourceName testPSH/demodb/admin -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -PropertyObject @{level='Read'} -UsePatchSemantics

4.3 Allow access for all Azure services

To allow all Azure services to access specific servers, edit and run the following commands:

Set-AzureRmResource -ResourceType "Microsoft.MySql/servers" -ResourceName testPSH -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -PropertyObject @{allowAzureServices='true'} -UsePatchSemantics

4.4 Modify the default time for daily backups

To change the default daily backup start time for a specific server, edit and run the following commands. The time can be set from 0 to 24, and means Beijing time UTC+8 in this location.

Set-AzureRmResource -ResourceType "Microsoft.MySql/servers" -ResourceName testPSH -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -PropertyObject @{dailyBackupTimeInHour='5'} -UsePatchSemantics

4.5 Turn on slow query logs

To turn on slow query logs, edit and run the following commands:

Set-AzureRmResource -ResourceType "Microsoft.MySql/servers" -ResourceName testPSH -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -PropertyObject @{options=@{slow_query_log='ON'}} -UsePatchSemantics

4.6 Modify firewall rules

To alter the existing firewall rules, edit and run the following commands:

Set-AzureRmResource -ResourceType "Microsoft.MySql/servers/firewallRules" -ResourceName testPSH/rule1 -ApiVersion 2015-09-01 -PropertyObject @{startIpAddress="1.1.1.1"} -ResourceGroupName resourcegroupChinaEast -UsePatchSemantics

4.7 Modify parts of the MySQL server settings

To change the default value for wait_timeout (using the wait_timeout parameter as an example), edit and run the following commands:

Set-AzureRmResource -ResourceType "Microsoft.MySql/servers" -ResourceName testPSH -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -PropertyObject @{options=@{wait_timeout=70}} -UsePatchSemantics

For modifications to other parameters, refer to the definitions in the following JSON file. For valid ranges for the parameters, see Customize MySQL Database on Azure server parameters(./mysql-database-advanced-settings.md).

    "options": {
          "type": "object",
          "properties": {
            "div_precision_increment": {
              "type": "integer"
            },
            "event_scheduler": {
              "type": "string"
            },
            "group_concat_max_len": {
              "type": "integer"
            },
            "innodb_adaptive_hash_index": {
              "type": "string"
            },
            "innodb_lock_wait_timeout": {
              "type": "integer"
            },
            "interactive_timeout": {
              "type": "integer"
            },
            "log_bin_trust_function_creators": {
              "type": "string"
            },
            "log_queries_not_using_indexes": {
              "type": "string"
            },
            "long_query_time": {
              "type": "number"
            },
            "max_allowed_packet": {
              "type": "integer"
            },
            "server-id": {
              "type": "integer"
            },
            "slow_query_log": {
              "type": "string"
            },
            "sql_mode": {
              "type": "string"
            },
            "wait_timeout": {
              "type": "integer"
            }
          }
        }

4.8 Modify the version of MySQL server performance

Set-AzureRmResource -ResourceType "Microsoft.MySql/servers" -ResourceName testPSH -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -SkuObject @{name="MS4"} -UsePatchSemantics

5. Operations: Delete

Use the Remove command to delete MySQL servers, databases, users, backups, and firewall rules.

5.1 Delete servers

To delete a specific server, edit and run the following commands:

Remove-AzureRmResource -ResourceType "Microsoft.MySql/servers" -ResourceName testPSH -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast

5.2 Delete server firewall rules

To delete a specific firewall, edit and run the following commands:

Remove-AzureRmResource -ResourceType "Microsoft.MySql/servers/firewallRules" -ResourceName testPSH/rule1 -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast

5.3 Delete databases

To delete a specific database, edit and run the following commands:

Remove-AzureRmResource -ResourceType "Microsoft.MySql/servers/databases" -ResourceName testPSH/demodb -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast

5.4 Delete users

To delete a specific user, edit and run the following commands:

Remove-AzureRmResource -ResourceType "Microsoft.MySql/servers/users" -ResourceName testPSH/admin -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast

5.5 Delete backup files

To delete a specific backup file, edit and run the following commands:

Remove-AzureRmResource -ResourceType "Microsoft.MySql/servers/backups" -ResourceName testPSH/back1 -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast

6 Other operations

6.1 Restart a server

To restart a server in the current resource group, edit and run the following commands:

Set-AzureRmResource -ResourceType "Microsoft.MySql/servers" -ResourceName testPSH -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -PropertyObject @{setRestart='true'} -UsePatchSemantics

6.2 Download slowlogs

To download the appointed slow query log from a specific server in the current resource group, edit and run the following commands:

Set-AzureRmResource -ResourceType "Microsoft.MySql/servers/slowlogs" -ResourceName testPSH/testslowlogname -ApiVersion 2015-09-01 -ResourceGroupName resourcegroupChinaEast -PropertyObject @{copyDestinationContainerUri=”your container uri";CopyDestinationSasToken="your container sasToken"} -UsePatchSemantics

A sample code for downloading the slow query log with parameters is provided here:

随着MySQL Database on Azure提供的功能增多，我们也会陆续更新相应的PowerShell指南。