Introducing the MySQL Flexible Server PowerShell Module

This article is contributed. See the original author and article here.

Late last year, the Open-Source Databases on Azure Team announced the Preview release of Azure Database for MySQL Flexible Server to provide customers with greater control and manageability. Today, we’re pleased to let you know that the PowerShell module for MySQL Flexible Server is now available. Developers can use now use PowerShell to manage MySQL Flexible Servers and dependent resources. Detailed development documentation and examples are available in the topic Az.MySql.

Provision a server quickly and easily

To provision an instance of MySQL Flexible Server, developers can run the following command:

$server = New-AzMySqlFlexibleServer

Creating Resource Group group28382412... 
Creating new vnet vnet-server2867384 in resource group group28382412... 
Creating new subnet subnet- server2867384 in resource group group28382412 and delegating it to Microsoft.DBforMySQL/flexibleServers... 
Creating MySQL server server2867384... 
Your server is using sku Standard_D2ds_V4 (Paid Tier). Please refer to https://aka.ms/mysql-pricing for pricing details... 
Creating database flexibleserverdb...

The example above shows that the module creates the associated resource group, default database, and network resource – the default network option is to create a server within a VNET and subnet.

Server properties (e.g., location, SKU, storage size etc.) are set to default values. To see all the properties in the $server object, run to following command:

Write-Host ($server | Format-List | Out-String)

For example, the generated password is saved as a SecureString in SecuredPassword.

Provision a server with private or public access

To set up an instance of MySQL Flexible Server more quickly, the New-AzMySqlFlexibleServer cmdlet provides options for private and public accessibility. As a result, you can create a server with a private network or a network open to public access. The detailed scenarios are below.

Private access

To set up a network with private access, use the following commands.

Scenario	Command
Existing VNET and Subnet (name or resource Id)	`New-AzMySqlFlexibleServer –Vnet <vnet name/Id> -Subnet <subnet name/Id>`
Existing VNET (name or resource Id)	`New-AzMySqlFlexibleServer -Vnet <vnet name/Id>`
New VNET and Subnet – provide name	`New-AzMySqlFlexibleServer -Vnet <vnet name> -Subnet <subnet name> -VnetPrefix 10.0.0.0/16 -SubnetPrefix 10.0.0.0/24`

Public access

To set up a network with public access, use the following commands.

Scenario	Command
Allow all IPs from 0.0.0.0-255.255.255.255	`New-AzMySqlFlexibleServer -PublicAccess All`
Allow access to your client IP only	`New-AzMySqlFlexibleServer -PublicAccess <Client IP>`
Allow all IPs within a range	`New-AzMySqlFlexibleServer -PublicAccess <Start IP>-<End IP>`
Allow access to all Azure Services	`New-AzMySqlFlexibleServer -PublicAccess 0.0.0.0`
No public access, but add allowed IPs later*	`New-AzMySqlFlexibleServer -PublicAccess none`

*You need to add allowed IPs using the `New-AzMySqlFlexibleServerFirewallRule` command.

Connect to your server easily

After provisioning your instance of MySQL Flexible Server, you can easily test the connection and obtain the connection string in the programming language you are using.

To test the connection to your server and try out a simple query, use the Test-AzMySqlFlexibleServerConnect cmdlet

PS C:> Get-AzMySqlFlexibleServerConnect -ResourceGroupName PowershellMySqlTest 
-Name mysql-test -AdministratorLoginPassword $password 

The connection testing to mysql-test.database.azure.com was successful!

PS C:> Get-AzMySqlFlexibleServerConnect -ResourceGroupName PowershellMySqlTest 
-Name mysql-test -AdministratorLoginPassword $password -Query "SELECT * FROM test"  

col  
-----  
1  
2

To obtain the connection string for programming language in which you are developing, use the Get-AzMySqlFlexibleServerConnectionString cmdlet.

PS C:> Get-AzMySqlFlexibleServerConnectionString -Client Python 
-ResourceGroupName PowershellMySqlTest -Name mysql-test  

cnx = mysql.connector.connect(user=mysql_user, password="{your_password}", host="mysql-test.mysql.database.azure.com", port=3306, database="{your_database}", ssl_ca="{ca-cert filename}", ssl_disabled=False)

PS C:> Get-AzMySqlFlexibleServer -ResourceGroupName PowershellMySqlTest 
-ServerName mysql-test | Get-AzMySqlFlexibleServerConnectionString -Client PHP  

$con=mysqli_init(); mysqli_real_connect($con, "mysql-test.mysql.database.azure.com", "mysql_test", {your_password}, {your_database}, 3306);

Additional resources

For more information, consult the following resources.

To try out the new module, use Azure Cloud Shell (https://shell.azure.com) in the Azure portal or download the Az.MySQL module (PowerShell Gallery | Az 5.5.0).

To learn more about MySQL Flexible Server, see the blog post What is Flexible Server in Azure Database for MySQL? or refer to the Azure Database for MySQL Flexible Server documentation.

You can also manage MySQL Flexible Servers by using the Azure CLI or SDKs at your convenience. To develop on other platforms, check out the following resources.