Provisioning and connecting to an Azure MySQL database using the Azure CLI

Azure Database for MySQL is a Database-as-a-Service offering for the MySQL database. In this recipe, we'll learn how to provision an Azure database for MySQL and connect to it.

Getting ready

We'll be using the Azure CLI for this recipe. Open a new Command Prompt or PowerShell window, and run az login to log in to the Azure CLI.

How it works…

Let's see how to provision the Azure MySQL server.

Provisioning the Azure MySQL server

The steps are as follows:

1. Execute the following command to create a new resource group:

   az group create --name rgmysql --location eastus

2. Execute the following command to provision a new Azure MySQL server:

   az mysql server create --resource-group rgmysql --name ademysqlserver  --location eastus --admin-user dbadmin --admin-password mySQL@1234 --sku-name B_Gen5_1

   You should get an output as shown in the following screenshot:

Figure 2.6 – Creating an Azure MySQL server

Connecting to Azure MySQL Server

The steps are as follows:

1. Execute the following command to whitelist your public IP in the Azure MySQL Server firewall:

   $clientip = (Invoke-RestMethod -Uri https://ipinfo.io/json).ip
   az mysql server firewall-rule create --resource-group rgmysql --server ademysqlserver --name clientIP --start-ip-address $clientip --end-ip-address $clientip

   You should get an output as shown in the following screenshot:

   

   Figure 2.7 – Creating a firewall rule for the Azure MySQL Server

2. We can connect to the Azure MySQL server using the MySQL shell or the MySQL workbench, or from any programming language. To connect from the MySQL shell, execute the following command in a PowerShell window:

   .\mysqlsh.exe -h ademysqlserver.mysql.database.azure.com -u dbadmin@ademysqlserver -p

   Here's the output:

Figure 2.8 – Connecting to the Azure MySQL server

How it works…

To provision a new Azure MySQL server, execute the following Azure CLI command – az mysql server create. We need to specify the server name, resource group, administrator username and password, location, and SKU name parameters. As of now, there are three different SKUs:

• B_Gen5_1 is the basic and smallest SKU, up to 2 vCores.
• GP_Gen5_32 is the general-purpose SKU, up to 64 vCores.
• MO_Gen5_2 is the memory-optimized SKU, with 32 memory-optimized vCores.

To connect to the MySQL server, we first need to whitelist the IP in the server firewall. To do that, we run the az mysql server firewall-rule create Azure CLI command.

We need to provide the firewall rule name, server name, resource group, and start and end IPs.

Once the firewall rule is created, the MySQL server can be accessed by any of the utilities, such as the MySQL command line or the MySQL workbench, or from a programming language. To connect to the server, provide the host or server name as <mysql server name>.mysql.database.azure.com. We also need to provide the username and password.