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:
AzureDiagnostics | where TimeGenerated > ago(10d) | where ResourceProvider == "MICROSOFT.DBFORMYSQL" | where Category == "MySqlAuditLogs" and event_class_s == "connection_log" |
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 |
AzureDiagnostics | where TimeGenerated > ago(10d) | where ResourceProvider == "MICROSOFT.DBFORMYSQL" | where Category == "MySqlAuditLogs" and event_class_s == "connection_log" | distinct ip_s |
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
AzureDiagnostics | where TimeGenerated > ago(2d) | where ResourceProvider == "MICROSOFT.DBFORMYSQL" | where Category == "MySqlAuditLogs" | where event_class_s == "general_log" |
Leave a Reply