Sjoukje Zaal

MariaDB started as a fork of MySQL, after it was acquired by Oracle, to create a new fully open source database. Over time, MariaDB has replaced MySQL in many places, because—besides the fact that MariaDB is being developed fully in the open—it offers more cutting-edge features then MySQL does.

With Azure Database for MariaDB, Microsoft further extends its commitment to the open source community. By hosting your MariaDB in Azure, you can use the high availability and scaling features that are part of the underlying foundation of Azure for your data. It is designed to deliver high availability with a service-level agreement (SLA) of 99.99 percent. You can also replicate your data from an on-premises MariaDB instance or virtual machine to an Azure Database for MariaDB in Azure.

In this article, we are going to create a MariaDB on Azure and look at how to scale with built-in high availability. To get started with Azure Database for MariaDB, you’ll need an Azure subscription. If you don’t already have one, you can create a free Azure account before you begin.

 

Create a new server

The first step is to create a new server. We are going to use the Azure Cloud Shell and Azure CLI for this, which can be executed directly in the Azure portal. Common Azure tools are preinstalled and configured in Cloud Shell for you to use.

1. Open a web browser and navigate to: https://ms.portal.azure.com
2. Open Cloud Shell in the top-right menu:

   

3. Make sure that Bash is selected in Cloud Shell and add the following line of code to create a new resource group. A resource group is the container in which we are going to create the database:

   az group create --name demoresourcegroup --location westus

4. The following example creates a server named demoserver in the West US region. The server is also created in the resource group demoresourcegroup and has the server admin login myadmin. The server is a Gen 5 server in the General Purpose pricing tier, and it has two vCores. (For more information about pricing tiers, you can refer to the useful links section at the end of this article.) Replace <server_admin_password> with your own server admin password. The name for the server must be unique, or you will get an error creating the database.

   az mariadb server create --resource-group demoresourcegroup --name demoserver459  --location eastus --admin-user myadmin --admin-password <server_admin_password> --sku-name GP_Gen5_2 --version 10.2

    

Configure firewall

Now that we have created the database, we are going to configure the firewall. A server-level firewall rule allows an external application like MySQL Shell or MySQL Workbench to connect to your server through the Azure Database for MariaDB service firewall.

1. Add the following line of code to create a rule called AllowIP that allows connections from a specific IP address, 192.168.0.1. Replace the IP address or range of IP addresses with an address corresponding to the location you connect from.

   az mariadb server firewall-rule create --resource-group demoresourcegroup --server demoserver459 --name AllowMyIP --start-ip-address 192.168.0.1 --end-ip-address 192.168.0.1

    

Configure SSL settings

By default, SSL connections between your server and client applications are enforced. This default setting ensures security of “in motion” data by encrypting the data stream over the Internet.

1. For this demo, disable SSL (this is not recommended for production servers) by adding the following line of code:

   az mariadb server update --resource-group demoresourcegroup --name demoserver459 --ssl-enforcement Disabled

    

Get connection information

We need to retrieve the host information and access credentials to connect to the database using MySQL Shell. The result will be in JSON. Make a note of the values for fullyQualifiedDomainName and administratorLogin.

az mariadb server show --resource-group demoresourcegroup --name demoserver459

 

Connect to the server using mysql

You can connect to the server using MySQL Shell or MySQL Workbench. You can also connect to it from the Azure Cloud Shell. I’ve downloaded the MySQL Shell and connected from my local system. I’ve added my IP address coming from my Internet provider as the allowed IP address in the previous step. Install and open MySQL and add the following line of code to connect to your database:

mysql -h demoserver459.mariadb.database.azure.com -u myadmin@demoserver459 -p

 

Create a database, tables and add some data

1. Once you’re connected to the server, create a blank database by adding the following line of code:

   mysql> CREATE DATABASE sampledb;
   
   Add the following to change the connection to the newly created database:
   
   mysql> USE sampledb;
   
   To create a table, add the following:
   
   CREATE TABLE inventory (
   
           id serial PRIMARY KEY,
   
           name VARCHAR(50),
   
           quantity INTEGER
   
   );

    

2. To add some data to the table:

   INSERT INTO inventory (id, name, quantity) VALUES (1, 'banana', 150); 
   INSERT INTO inventory (id, name, quantity) VALUES (2, 'orange', 154);

    

Configure vCores and storage

Now that we have a database, table, and some data added to it, we can look into the different scaling options for your database. Therefore, we need to go to the Azure portal and select the database that we created using CLI. In the database properties blade, under Settings, select Pricing tiers. Here, you can adjust the scaling of your database:

You can switch the pricing tier, scale the amount of vCores, increase the storage capacity, and adjust the backup retention period.

 

Configure and manage security

To configure and manage the security of your MariaDB on Azure, in the Azure Portal again, under Settings, select Connection security. Here you can add additional IP addresses that can connect to the database; you can enable or disable Enforce SSL Connection; and you can add VNet rules (see the useful links for more information about VNets).

 

As an addition to the above security features, the Azure Database for MariaDB service uses storage encryption for data at rest and is FIPS 140-2–compliant. Data, including backup data, is encrypted on disk. (Temporary files that are created by the engine when it runs queries are not encrypted on disk.) The service uses AES 256-bit cipher, which is included in Azure Storage encryption. The keys are system-managed. Storage encryption is always on and can’t be disabled.