Quickstart: Create an Azure Database for MariaDB server using PowerShell

This quickstart describes how to use PowerShell to create an Azure Database for MariaDB server in an Azure resource group. You can use PowerShell to create and manage Azure resources interactively or in scripts.

Prerequisites

If you don't have an Azure subscription, create a trial account before you begin.

This article requires that you install the Az PowerShell module and connect to your Azure account using the Connect-AzAccount cmdlet. For more information about installing the Az PowerShell module, see Install Azure PowerShell.

If this is your first time using the Azure Database for MariaDB service, you must register the Microsoft.DBforMariaDB resource provider.

Register-AzResourceProvider -ProviderNamespace Microsoft.DBforMariaDB

If you have multiple Azure subscriptions, choose the appropriate subscription in which the resources should be billed. Select a specific subscription ID using the Set-AzContext cmdlet.

Set-AzContext -SubscriptionId aaaa0a0a-bb1b-cc2c-dd3d-eeeeee4e4e4e

Create a resource group

Create an Azure resource group using the New-AzResourceGroup cmdlet. A resource group is a logical container in which Azure resources are deployed and managed as a group.

The following example creates a resource group named myresourcegroup in the China East 2 region.

New-AzResourceGroup -Name myresourcegroup -Location chinaeast2

Create an Azure Database for MariaDB server

Create an Azure Database for MariaDB server with the New-AzMariaDbServer cmdlet. A server can manage multiple databases. Typically, a separate database is used for each project or for each user.

The following table contains a list of commonly used parameters and sample values for the New-AzMariaDbServer cmdlet.

Setting	Sample value	Description
Name	mydemoserver	Choose a globally unique name in Azure that identifies your Azure Database for MariaDB server. The server name can only contain letters, numbers, and the hyphen (-) character. Any uppercase characters that are specified are automatically converted to lowercase during the creation process. It must contain from 3 to 63 characters.
ResourceGroupName	myresourcegroup	Provide the name of the Azure resource group.
Sku	GP_Gen5_2	The name of the SKU. Follows the convention pricing-tier_compute-generation_vCores in shorthand. For more information about the Sku parameter, see the information following this table.
BackupRetentionDay	7	How long a backup should be retained. Unit is days. Range is 7-35.
GeoRedundantBackup	Enabled	Whether geo-redundant backups should be enabled for this server or not. This value cannot be enabled for servers in the basic pricing tier and it cannot be changed after the server is created. Allowed values: Enabled, Disabled.
Location	chinaeast2	The Azure region for the server.
SslEnforcement	Enabled	Whether SSL should be enabled or not for this server. Allowed values: Enabled, Disabled.
StorageInMb	51200	The storage capacity of the server (unit is megabytes). Valid StorageInMb is a minimum of 5120 MB and increases in 1024 MB increments. For more information about storage size limits, see Azure Database for MariaDB pricing tiers.
Version	5.7	The MariaDB major version.
AdministratorUserName	myadmin	The username for the administrator login. It cannot be azure_superuser, admin, administrator, root, guest, or public.
AdministratorLoginPassword	<securestring>	The password of the administrator user in the form of a secure string. It must contain between 8 and 128 characters. Your password must contain characters from three of the following categories: English uppercase letters, English lowercase letters, numbers, and non-alphanumeric characters.

The Sku parameter value follows the convention pricing-tier_compute-generation_vCores as shown in the following examples.

• -Sku B_Gen5_1 maps to Basic, Gen 5, and 1 vCore. This option is the smallest SKU available.
• -Sku GP_Gen5_32 maps to General Purpose, Gen 5, and 32 vCores.
• -Sku MO_Gen5_2 maps to Memory Optimized, Gen 5, and 2 vCores.

For information about valid Sku values by region and for tiers, see Azure Database for MariaDB pricing tiers.

The following example creates a MariaDB server in the China East 2 region named mydemoserver in the myresourcegroup resource group with a server admin login of myadmin. It is a Gen 5 server in the general-purpose pricing tier with 2 vCores and geo-redundant backups enabled. Document the password used in the first line of the example as this is the password for the MariaDB server admin account.

$Password = Read-Host -Prompt 'Please enter your password' -AsSecureString
New-AzMariaDbServer -Name mydemoserver -ResourceGroupName myresourcegroup -Sku GP_Gen5_2 -GeoRedundantBackup Enabled -Location chinaeast2 -AdministratorUsername myadmin -AdministratorLoginPassword $Password

Consider using the basic pricing tier if light compute and I/O are adequate for your workload.

Configure a firewall rule

Create an Azure Database for MariaDB server-level firewall rule using the New-AzMariaDbFirewallRule cmdlet. A server-level firewall rule allows an external application, such as the mysql command-line tool or MariaDB Workbench to connect to your server through the Azure Database for MariaDB service firewall.

The following example creates a firewall rule named AllowMyIP that allows connections from a specific IP address, 192.168.0.1. Substitute an IP address or range of IP addresses that correspond to the location that you are connecting from.

New-AzMariaDbFirewallRule -Name AllowMyIP -ResourceGroupName myresourcegroup -ServerName mydemoserver -StartIPAddress 192.168.0.1 -EndIPAddress 192.168.0.1

Configure SSL settings

By default, SSL connections between your server and client applications are enforced. This default ensures the security of in-motion data by encrypting the data stream over the Internet. For this quickstart, disable SSL connections for your server. For more information, see Configure SSL connectivity in your application to securely connect to Azure Database for MariaDB.

The following example disables SSL on your Azure Database for MariaDB server.

Update-AzMariaDbServer -Name mydemoserver -ResourceGroupName myresourcegroup -SslEnforcement Disabled

Get the connection information

To connect to your server, you need to provide host information and access credentials. Use the following example to determine the connection information. Make a note of the values for FullyQualifiedDomainName and AdministratorLogin.

Get-AzMariaDbServer -Name mydemoserver -ResourceGroupName myresourcegroup |
  Select-Object -Property FullyQualifiedDomainName, AdministratorLogin

FullyQualifiedDomainName                    AdministratorLogin
------------------------                    ------------------
mydemoserver.mariadb.database.chinacloudapi.cn       myadmin

Connect to the server using the mysql command-line tool

Connect to your server using the mysql command-line tool. To download and install the command-line tool, see MySQL Community Downloads.

1. Connect to the server using the mysql command-line tool.

   mysql -h <servername>.mariadb.database.chinacloudapi.cn -u myadmin@<servername> -p
   

2. View server status.

   mysql> status
   

   C:\Users\>mysql -h mydemoserver.mariadb.database.chinacloudapi.cn -u myadmin@mydemoserver -p
   Enter password: *************
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 64793
   Server version: 5.6.42.0 MariaDB Server
   
   Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql> status
   --------------
   /usr/bin/mysql  Ver 14.14 Distrib 5.7.29, for Linux (x86_64) using  EditLine wrapper
   
   Connection id:          64793
   Current database:
   Current user:           myadmin@myipaddress
   SSL:                    Cipher in use is ECDHE-RSA-AES256-GCM-SHA384
   Current pager:          stdout
   Using outfile:          ''
   Using delimiter:        ;
   Server version:         5.6.42.0 MariaDB Server
   Protocol version:       10
   Connection:             mydemoserver.mariadb.database.chinacloudapi.cn via TCP/IP
   Server characterset:    latin1
   Db     characterset:    latin1
   Client characterset:    utf8
   Conn.  characterset:    utf8
   TCP port:               3306
   Uptime:                 17 min 4 sec
   
   Threads: 19  Questions: 482  Slow queries: 0  Opens: 50  Flush tables: 3  Open tables: 12  Queries per second avg: 0.470
   --------------
   
   mysql>
   

For additional commands, see MySQL 5.7 Reference Manual - Chapter 4.5.1.

Connect to the server using MariaDB Workbench

1. Launch the MySQL Workbench application on your client computer. To download and install MySQL Workbench, see Download MySQL Workbench.

2. In the Setup New Connection dialog box, enter the following information on the Parameters tab:

   

   Setting	Suggested Value	Description
   Connection Name	My Connection	Specify a label for this connection
   Connection Method	Standard (TCP/IP)	Use TCP/IP protocol to connect to Azure Database for MariaDB
   Hostname	mydemoserver.mariadb.database.chinacloudapi.cn	Server name you previously noted
   Port	3306	The default port for MariaDB
   Username	myadmin@mydemoserver	The server admin login you previously noted
   Password	*************	Use the admin account password you configured earlier

3. To test if the parameters are configured correctly, select the Test Connection button.

4. Select the connection to connect to the server.

Clean up resources

If the resources created in this quickstart aren't needed for another quickstart or tutorial, you can delete them by running the following example.

Remove-AzResourceGroup -Name myresourcegroup

To delete only the server created in this quickstart without deleting the resource group, use the Remove-AzMariaDbServer cmdlet.

Remove-AzMariaDbServer -Name mydemoserver -ResourceGroupName myresourcegroup

Next steps