Audit Logs in Azure Database for MySQL
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?
In Azure Database for MySQL, the audit log is available to users. The audit log can be used to track database-level activity and is commonly used for compliance.
Configure audit logging
Important
It is recommended to only log the event types and users required for your auditing purposes to ensure your server's performance is not heavily impacted and minimum amount of data is collected.
By default the audit log is disabled. To enable it, set audit_log_enabled to ON.
Other parameters you can adjust include:
audit_log_events: controls the events to be logged. See below table for specific audit events.audit_log_include_users: MySQL users to be included for logging. The default value for this parameter is empty, which will include all the users for logging. This has higher priority overaudit_log_exclude_users. Max length of the parameter is 512 characters.audit_log_exclude_users: MySQL users to be excluded from logging. Max length of the parameter is 512 characters.
Note
audit_log_include_users has higher priority over audit_log_exclude_users. For example, if audit_log_include_users = demouser and audit_log_exclude_users = demouser, the user will be included in the audit logs because audit_log_include_users has higher priority.
| Event | Description |
|---|---|
CONNECTION |
- Connection initiation (successful or unsuccessful) - User reauthentication with different user/password during session - Connection termination |
DML_SELECT |
SELECT queries |
DML_NONSELECT |
INSERT/DELETE/UPDATE queries |
DML |
DML = DML_SELECT + DML_NONSELECT |
DDL |
Queries like "DROP DATABASE" |
DCL |
Queries like "GRANT PERMISSION" |
ADMIN |
Queries like "SHOW STATUS" |
GENERAL |
All in DML_SELECT, DML_NONSELECT, DML, DDL, DCL, and ADMIN |
TABLE_ACCESS |
- Available for MySQL 5.7 and MySQL 8.0 - Table read statements, such as SELECT or INSERT INTO ... SELECT - Table delete statements, such as DELETE or TRUNCATE TABLE - Table insert statements, such as INSERT or REPLACE - Table update statements, such as UPDATE |
Access audit logs
Audit logs are integrated with Azure Monitor Diagnostic Logs. Once you've enabled audit logs on your MySQL server, you can emit them to Azure Monitor logs, Event Hubs, or Azure Storage. To learn more about how to enable diagnostic logs in the Azure portal, see the audit log portal article.
Note
Premium Storage accounts are not supported if you sending the logs to Azure storage via diagnostics and settings
Diagnostic Logs Schemas
The following sections describe what's output by MySQL audit logs based on the event type. Depending on the output method, the fields included and the order in which they appear may vary.
Connection
| Property | Description |
|---|---|
TenantId |
Your tenant ID |
SourceSystem |
Azure |
TimeGenerated [UTC] |
Time stamp when the log was recorded in UTC |
Type |
Type of the log. Always AzureDiagnostics |
SubscriptionId |
GUID for the subscription that the server belongs to |
ResourceGroup |
Name of the resource group the server belongs to |
ResourceProvider |
Name of the resource provider. Always MICROSOFT.DBFORMYSQL |
ResourceType |
Servers |
ResourceId |
Resource URI |
Resource |
Name of the server |
Category |
MySqlAuditLogs |
OperationName |
LogEvent |
LogicalServerName_s |
Name of the server |
event_class_s |
connection_log |
event_subclass_s |
CONNECT, DISCONNECT, CHANGE USER (only available for MySQL 5.7) |
connection_id_d |
Unique connection ID generated by MySQL |
host_s |
Blank |
ip_s |
IP address of client connecting to MySQL |
user_s |
Name of user executing the query |
db_s |
Name of database connected to |
\_ResourceId |
Resource URI |
General
Schema below applies to GENERAL, DML_SELECT, DML_NONSELECT, DML, DDL, DCL, and ADMIN event types.
Note
For sql_text, log will be truncated if it exceeds 2048 characters.
| Property | Description |
|---|---|
TenantId |
Your tenant ID |
SourceSystem |
Azure |
TimeGenerated [UTC] |
Time stamp when the log was recorded in UTC |
Type |
Type of the log. Always AzureDiagnostics |
SubscriptionId |
GUID for the subscription that the server belongs to |
ResourceGroup |
Name of the resource group the server belongs to |
ResourceProvider |
Name of the resource provider. Always MICROSOFT.DBFORMYSQL |
ResourceType |
Servers |
ResourceId |
Resource URI |
Resource |
Name of the server |
Category |
MySqlAuditLogs |
OperationName |
LogEvent |
LogicalServerName_s |
Name of the server |
event_class_s |
general_log |
event_subclass_s |
LOG, ERROR, RESULT (only available for MySQL 5.6) |
event_time |
Query start time in UTC timestamp |
error_code_d |
Error code if query failed. 0 means no error |
thread_id_d |
ID of thread that executed the query |
host_s |
Blank |
ip_s |
IP address of client connecting to MySQL |
user_s |
Name of user executing the query |
sql_text_s |
Full query text |
\_ResourceId |
Resource URI |
Table access
Note
Table access logs are only output for MySQL 5.7.
For sql_text, log will be truncated if it exceeds 2048 characters.
| Property | Description |
|---|---|
TenantId |
Your tenant ID |
SourceSystem |
Azure |
TimeGenerated [UTC] |
Time stamp when the log was recorded in UTC |
Type |
Type of the log. Always AzureDiagnostics |
SubscriptionId |
GUID for the subscription that the server belongs to |
ResourceGroup |
Name of the resource group the server belongs to |
ResourceProvider |
Name of the resource provider. Always MICROSOFT.DBFORMYSQL |
ResourceType |
Servers |
ResourceId |
Resource URI |
Resource |
Name of the server |
Category |
MySqlAuditLogs |
OperationName |
LogEvent |
LogicalServerName_s |
Name of the server |
event_class_s |
table_access_log |
event_subclass_s |
READ, INSERT, UPDATE, or DELETE |
connection_id_d |
Unique connection ID generated by MySQL |
db_s |
Name of database accessed |
table_s |
Name of table accessed |
sql_text_s |
Full query text |
\_ResourceId |
Resource URI |
Analyze logs in Azure Monitor Logs
Once your audit logs are piped to Azure Monitor Logs through Diagnostic Logs, you can perform further analysis of your audited events. Below are some sample queries to help you get started. Make sure to update the below with your server name.
List GENERAL events on a particular server
AzureDiagnostics | where LogicalServerName_s == '<your server name>' | where Category == 'MySqlAuditLogs' and event_class_s == "general_log" | project TimeGenerated, LogicalServerName_s, event_class_s, event_subclass_s, event_time_t, user_s , ip_s , sql_text_s | order by TimeGenerated asc nulls lastList CONNECTION events on a particular server
AzureDiagnostics | where LogicalServerName_s == '<your server name>' | where Category == 'MySqlAuditLogs' and event_class_s == "connection_log" | project TimeGenerated, LogicalServerName_s, event_class_s, event_subclass_s, event_time_t, user_s , ip_s , sql_text_s | order by TimeGenerated asc nulls lastSummarize audited events on a particular server
AzureDiagnostics | where LogicalServerName_s == '<your server name>' | where Category == 'MySqlAuditLogs' | project TimeGenerated, LogicalServerName_s, event_class_s, event_subclass_s, event_time_t, user_s , ip_s , sql_text_s | summarize count() by event_class_s, event_subclass_s, user_s, ip_sGraph the audit event type distribution on a particular server
AzureDiagnostics | where LogicalServerName_s == '<your server name>' | where Category == 'MySqlAuditLogs' | project TimeGenerated, LogicalServerName_s, event_class_s, event_subclass_s, event_time_t, user_s , ip_s , sql_text_s | summarize count() by LogicalServerName_s, bin(TimeGenerated, 5m) | render timechartList audited events across all MySQL servers with Diagnostic Logs enabled for audit logs
AzureDiagnostics | where Category == 'MySqlAuditLogs' | project TimeGenerated, LogicalServerName_s, event_class_s, event_subclass_s, event_time_t, user_s , ip_s , sql_text_s | order by TimeGenerated asc nulls last