Azure Database for MySQL

Azure Database for MySQL provides fully managed, enterprise-ready community MySQL database as a service.

Like with any PaaS solution you may find that the ways you used to manage and troubleshoot services within IaaS world may be no longer applicable. While it may sound as a challenge,  don’t be afraid to much – Azure has everything you need.

Azure Diagnostics

To collect logs from the Azure Database for MySQL  – we need to enable logging itself as well as configure the feed to Azure Diagnostics

Concept is described in the following doc: https://docs.microsoft.com/en-us/azure/mysql/concepts-audit-logs

Configuration process is very straightforward and very well outlined  here: https://docs.microsoft.com/en-us/azure/mysql/howto-configure-audit-logs-portal

Log Analytics

Azure Log Analytics is an Azure service that would let us query the Azure Diagnostics data within the Azure Monitor

Such queries, especially initially is a challenge for me. Mostly because I don’t use  Azure Log Analytics on daily basis, so I decided to share few examples:

Show all connections logs
AzureDiagnostics
where TimeGenerated > ago(10d)
where ResourceProvider == "MICROSOFT.DBFORMYSQL"
where Category == "MySqlAuditLogs" and event_class_s == "connection_log"
Summarize login ( Connect and Disconnect events) , user, IP and database
AzureDiagnostics
where TimeGenerated > ago(10d)
where ResourceProvider == "MICROSOFT.DBFORMYSQL"
where Category == "MySqlAuditLogs" and event_class_s == "connection_log"
| project TimeGenerated, event_subclass_s , ip_s, user_s , db_s
List Source IP’s for connections
AzureDiagnostics
where TimeGenerated > ago(10d)
where ResourceProvider == "MICROSOFT.DBFORMYSQL"
where Category == "MySqlAuditLogs" and event_class_s == "connection_log"
| distinct ip_s
List all connections for specific IP
AzureDiagnostics
where TimeGenerated > ago(10d)
where ResourceProvider == "MICROSOFT.DBFORMYSQL"
where Category == "MySqlAuditLogs" and event_class_s == "connection_log"
| project TimeGenerated, event_subclass_s , ip_s, user_s , db_s | where ip_s == "8.8.8.8"

For more information about MYSQL diagnostics settings table schema please refer to https://docs.microsoft.com/en-us/azure/mysql/concepts-audit-logs#diagnostic-logs-schemas

For other events type (GENERAL, DML_SELECT, DML_NONSELECT, DML, DDL, DCL, and ADMIN event types.) , please refer to https://docs.microsoft.com/en-us/azure/mysql/concepts-audit-logs#general

Other events type
AzureDiagnostics
where TimeGenerated > ago(2d)
where ResourceProvider == "MICROSOFT.DBFORMYSQL"
where Category == "MySqlAuditLogs"
where event_class_s =="general_log"