Configure server parameters in Azure Database for MariaDB using the Azure CLI

  • Article

Important

Azure Database for MariaDB is on the retirement path. We strongly recommend that you migrate to Azure Database for MySQL. For more information about migrating to Azure Database for MySQL, see What's happening to Azure Database for MariaDB?.

You can list, show, and update configuration parameters for an Azure Database for MariaDB server by using Azure CLI, the Azure command-line utility. A subset of engine configurations is exposed at the server-level and can be modified.

Note

Server parameters can be updated globally at the server-level, use the Azure CLI, PowerShell, or Azure portal.

Prerequisites

To step through this how-to guide, you need:

List server configuration parameters for Azure Database for MariaDB server

To list all modifiable parameters in a server and their values, run the az mariadb server configuration list command.

You can list the server configuration parameters for the server mydemoserver.mariadb.database.chinacloudapi.cn under resource group myresourcegroup.

az mariadb server configuration list --resource-group myresourcegroup --server mydemoserver

For the definition of each of the listed parameters, see the MariaDB reference section on Server System Variables.

Show server configuration parameter details

To show details about a particular configuration parameter for a server, run the az mariadb server configuration show command.

This example shows details of the slow_query_log server configuration parameter for server mydemoserver.mariadb.database.chinacloudapi.cn under resource group myresourcegroup.

az mariadb server configuration show --name slow_query_log --resource-group myresourcegroup --server mydemoserver

Modify a server configuration parameter value

You can also modify the value of a certain server configuration parameter, which updates the underlying configuration value for the MariaDB server engine. To update the configuration, use the az mariadb server configuration set command.

To update the slow_query_log server configuration parameter of server mydemoserver.mariadb.database.chinacloudapi.cn under resource group myresourcegroup.

az mariadb server configuration set --name slow_query_log --resource-group myresourcegroup --server mydemoserver --value ON

If you want to reset the value of a configuration parameter, omit the optional --value parameter, and the service applies the default value. For the example above, it would look like:

az mariadb server configuration set --name slow_query_log --resource-group myresourcegroup --server mydemoserver

This code resets the slow_query_log configuration to the default value OFF.

Setting parameters not listed

If the server parameter you want to update is not listed in the Azure portal, you can optionally set the parameter at the connection level using init_connect. This sets the server parameters for each client connecting to the server.

Update the init_connect server configuration parameter of server mydemoserver.mariadb.database.chinacloudapi.cn under resource group myresourcegroup to set values such as character set.

az mariadb server configuration set --name init_connect --resource-group myresourcegroup --server mydemoserver --value "SET character_set_client=utf8;SET character_set_database=utf8mb4;SET character_set_connection=latin1;SET character_set_results=latin1;"

Working with the time zone parameter

Populating the time zone tables

The time zone tables on your server can be populated by calling the mysql.az_load_timezone stored procedure from a tool like the MariaDB command line or MariaDB Workbench.

Note

If you are running the mysql.az_load_timezone command from MariaDB Workbench, you may need to turn off safe update mode first using SET SQL_SAFE_UPDATES=0;.

CALL mysql.az_load_timezone();

Important

You should restart the server to ensure the time zone tables are properly populated. To restart the server, use the Azure portal or CLI.

To view available time zone values, run the following command:

SELECT name FROM mysql.time_zone_name;

Setting the global level time zone

The global level time zone can be set using the az mariadb server configuration set command.

The following command updates the time_zone server configuration parameter of server mydemoserver.mariadb.database.chinacloudapi.cn under resource group myresourcegroup to ** +08:00**.

az mariadb server configuration set --name time_zone --resource-group myresourcegroup --server mydemoserver --value " +08:00"

Setting the session level time zone

The session level time zone can be set by running the SET time_zone command from a tool like the MariaDB command line or MariaDB Workbench. The example below sets the time zone to the ** +08:00** time zone.

SET time_zone = ' +08:00';

Refer to the MariaDB documentation for Date and Time Functions.

Next steps