Use Windows PowerShell to manage MySQL Database on Azure
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
- Understand Azure Resource Manager templates and resource groups
- Operations: Create
- Operations: View
- Operations: Modify
- Operations: Delete
- Other operations
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指南。