Configure and access slow query logs by using Azure CLI

Article
06/20/2022

APPLIES TO: Azure Database for MySQL - Single Server

Important

Azure Database for MySQL single server is on the retirement path. We strongly recommend that you upgrade to Azure Database for MySQL flexible server. For more information about migrating to Azure Database for MySQL flexible server, see What's happening to Azure Database for MySQL Single Server?

You can download the Azure Database for MySQL slow query logs by using Azure CLI, the Azure command-line utility.

Prerequisites

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

Azure Database for MySQL server
The Azure CLI or Azure Cloud Shell in the browser

Configure logging

You can configure the server to access the MySQL slow query log by taking the following steps:

Turn on slow query logging by setting the slow_query_log parameter to ON.
Select where to output the logs to using log_output. To send logs to both local storage and Azure Monitor Diagnostic Logs, select File. To send logs only to Azure Monitor Logs, select None
Adjust other parameters, such as long_query_time and log_slow_admin_statements.

To learn how to set the value of these parameters through Azure CLI, see How to configure server parameters.

For example, the following CLI command turns on the slow query log, sets the long query time to 10 seconds, and then turns off the logging of the slow admin statement. Finally, it lists the configuration options for your review.

az mysql server configuration set --name slow_query_log --resource-group myresourcegroup --server mydemoserver --value ON
az mysql server configuration set --name log_output --resource-group myresourcegroup --server mydemoserver --value FILE
az mysql server configuration set --name long_query_time --resource-group myresourcegroup --server mydemoserver --value 10
az mysql server configuration set --name log_slow_admin_statements --resource-group myresourcegroup --server mydemoserver --value OFF
az mysql server configuration list --resource-group myresourcegroup --server mydemoserver

List logs for Azure Database for MySQL server

If log_output is configured to "File", you can access logs directly from the server's local storage. To list the available slow query log files for your server, run the az mysql server-logs list command.

You can list the log files for server mydemoserver.mysql.database.azure.com under the resource group myresourcegroup. Then direct the list of log files to a text file called log_files_list.txt.

az mysql server-logs list --resource-group myresourcegroup --server mydemoserver > log_files_list.txt

Download logs from the server

If log_output is configured to "File", you can download individual log files from your server with the az mysql server-logs download command.

Use the following example to download the specific log file for the server mydemoserver.mysql.database.azure.com under the resource group myresourcegroup to your local environment.

az mysql server-logs download --name 20170414-mydemoserver-mysql.log --resource-group myresourcegroup --server mydemoserver

Next steps

Learn about slow query logs in Azure Database for MySQL.

Share via