Use Azure Resource Manager and PowerShell to deploy and use MySQL Database on Azure

  • Article

Azure Resource Manager is a new management framework for Azure services. Now you can use APIs and tools based on Resource Manager to manage MySQL Database on Azure. MySQL Database on Azure supports the use of Azure PowerShell scripts to query, create, manage, and delete MySQL servers, databases, firewall rules, and users. It also supports the use of scripts to change parameters and settings. This article explains how to use PowerShell scripts to quickly set up and use MySQL services. For more information about creating, viewing, removing and modifying operations, see Use PowerShell to manage MySQL Database on Azure.

Contents

Step 1: Install Azure PowerShell

You must install and run PowerShell before you run scripts. To download and install the latest version of Powershell, run the Microsoft web platform installation program. For more information, see How to install and configure Azure PowerShell. The cmdlet that’s used to create and manage MySQL Database on Azure databases is located in the Resource Manager module. Starting PowerShell imports the cmdlet from the Azure module by default. First confirm the version of PowerShell, and then run the following command from the console:

(Get-Module Azure).Version

Azure PowerShell version 0.9*:

Switch to the Resource Manager module. Use the following command to switch:

Switch-AzureMode -Name AzureResourceManager

Azure PowerShell version 1.0.0+:

In this version, the original Switch-AzureMode instruction is removed. For more information, see Precautions for using Azure PowerShell version 1.0.0 and above in China

Step 2: Configure account details

You must configure the Azure account before running PowerShell for Azure subscriptions. Run the following commands, and enter the same email address and password you used on the Azure portal sign-in page to verify your identity.

Azure PowerShell version 0.9*:

Add-AzureAccount -Environment AzureChinaCloud

Azure PowerShell version 1.0.0+:

Login-AzureRmAccount -EnvironmentName AzureChinaCloud

If you have multiple SubIDs to choose from, you can make your selection by running the Select-AzureSubscription command.

Step 3: Subscribe to MySQL Database on Azure service

Run the following command to subscribe to MySQL services.

Azure PowerShell version 0.9*:

Register-AzureProvider -ProviderNamespace "Microsoft.MySql"

Azure PowerShell version 1.0.0+:

Register-AzureRmResourceProvider -ProviderNamespace "Microsoft.MySql"

Step 4: Create resource groups

If you already have resource groups, you can create a server directly. You also can edit and run the following commands to create a new resource group.

Azure PowerShell version 0.9*:

New-AzureResourceGroup -Name "resourcegroupChinaEast" -Location "chinaeast"

Azure PowerShell version 1.0.0+:

New-AzureRmResourceGroup -Name "resourcegroupChinaEast" -Location "chinaeast"

Note

** The default location option is “chinanorth.” For performance and security, choose services that are in the same region as your resource group. **

Step 5: Create servers

Edit and run the following commands to set information, including your server name, location, version, and other details, to finish creating the server.

Azure PowerShell version 0.9*:

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

Azure PowerShell version 1.0.0+:

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

Note

** “-ApiVersion 2015-09-01” specifies the API version and is essential. Running the previous commands finishes creating the MySQL server, but not the user. You must create user settings and privileges in the subsequent steps. This process is different from the one used by the Azure portal.**

Step 6: Create server firewall rules

Edit and run the following commands to set information, including your firewall rule names and safe IP range (start and end IP addresses), to finish creating the firewall rules.

Azure PowerShell version 0.9*:

New-AzureResource -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

Azure PowerShell version 1.0.0+:

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

Step 7: Create databases

Edit and run the following commands to set information, including your database name and character sets, to finish creating the database.

Azure PowerShell version 0.9*:

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

Azure PowerShell version 1.0.0+:

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

Step 8: Create users

Edit and run the following commands to set information, including your username and password, to finish creating users.

Azure PowerShell version 0.9*:

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

Azure PowerShell version 1.0.0+:

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

Step 9: Add user privileges

Edit and run the following commands to assign database read/write privileges to users. Permissions can be either “Read” or “ReadWrite.”

Azure PowerShell version 0.9*:

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

Azure PowerShell version 1.0.0+:

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

Now you have finished creating the services, databases, users, and firewall rules. You are ready to begin using MySQL Database on Azure database services. For more information about creating, viewing, removing and reading operations while using database services, see Use PowerShell to manage MySQL Database on Azure.