Official Mafiree Blogs

Keeping you informed about Databases

Scaling Databases Horizontally: A Solution for High-Concurrency Systems
By Sukan   |   September 20, 2023   |   Posted in : TiDB
Blog post on how TiDB handles the OLAP requests without compromising OLTP

6 Interesting Features In MongoDB 6.0
By Thivakar   |   June 19, 2023   |   Posted in : MongoDB
Upgrade to MongoDB 6.0 for improved index management, error handling, powerful aggregation, replicaset enhancements, time series collection improvements, and embedded field exclusion. Experience efficient database management.

Access Control & Account Management In MySQL 8
By Sukan   |   June 08, 2023   |   Posted in : MySQL
Discover how MySQL 8 enhances security through features like password expiry, password validation, account lock/unlock, privilege management, and dual passwords. Safeguard your valuable data and streamline access control in your MySQL environment.

Significance of using Invisible Primary key (GIPK) with MySQL 8.0
By Sukan   |   February 21, 2023   |   Posted in : MySQL
How Generated Invisible Primary Keys (GIPK) Can Boost Your Database Efficiency

Achieving High Availability Using Log Shipping
By Sujith   |   February 21, 2018   |   Posted in : SQL Server
Here we will get the detailed explanation of how we can achieve HA using Log Shipping.

 Home  /  MySQL  
Best practices on enabling Audit log on MySQL, MariaDB and ProxySQL
By Sukan   |   August 13, 2020   |   Posted in : MySQL

Audit logs are crucial when it comes to production database instances, there must be logging available every time when someone gets into the database. Audits logs are almost on top of the checklist if you have security policies and compliances. Yet many clients are either not aware nor interested to enable audit logs. Audit logs play a vital role in security practices recommended by data security and privacy certifications like PCI DSS, GDPR, SOX etc

Why we need an audit log?

  • Who accessed the database?

  • When they accessed it?

  • Why they accessed it? What are the changes done?

  • Was any data or schema modified without proper knowledge?

  • Which user or host was the reason for bad queries that caused a downtime?

To be honest, these are fairly very simple reasons why we need to enable the audit log. Now let’s see what are the options to enable audit log.

Audit logging On MySQL

MySQL audit plugin comes with enterprise edition only, it can be loaded in runtime.

Formats - new/old/json

Simple query, select * from roles is logged as,

XML

  1. 2020-01-02T10:23:17 UTC
  2. 12_2020-01-02T10:23:03
  3. Query
  4. 53
  5. 0
  6. 0
  7. root[root] @ localhost []
  8. localhost
  9. select
  10. select * from roles

Old Format

  1. TIMESTAMP="2020-01-02T10:22:27 UTC" RECORD_ID="12_2020-01-02T10:22:01" NAME="Query" CONNECTION_ID="31" STATUS="0" STATUS_CODE="0" USER="root[root] @ localhost []" OS_LOGIN="" HOST="localhost" IP="" COMMAND_CLASS="select" SQLTEXT="select * from roles"/>

JSON

  1. { "timestamp": "2020-01-02 10:18:19", "id": 0, "class": "general", "event": "status", "connection_id": 28, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "select", "query": "select * from roles", "status": 0 } }

Features

Rotate size Threshold - It will rotate the audit log once the size is reached

Events logging - Controls what are the elements those are to be logged (All, None, Logins, Queries)

Audit logging on MariaDB

Unlike MySQL, the audit log comes with all versions of MariaDB even on community editions.

  1. 20200102 10:40:31,mariadblab,root,localhost,49,15,QUERY,foo,'select * from roles',0

Features

Events logging - Connect,query, table, query_ddl,query_dml,query_dcl,query_dml_no_select (latest audit version)

User-based logging - It is used to exclude/include certain users from logging.

Rotate size Threshold - Can describe the size to rotate the audit log

NOTE: Apart from MySQL and MariaDB, we can enable an audit log on the load balancer such as ProxySQL

Audit logging on ProxySQL

Currently ProxySQL supports JSON and traditional format

  1. {"client_addr":"127.0.0.1:50100","creation_time":"2020-01-02 11:05:08.363","duration":"13.355ms","event":"MySQL_Client_Close","extra_info":"MySQL_Thread.cpp:4398:process_all_sessions()",
  2. "proxy_addr":"0.0.0.0:6033","schemaname":"information_schema","ssl":false,"thread_id":7,"time":"2020-01-02 11:05:08.376","timestamp":1577963108376,"username":"appuser"}

Separate query logging is available which can be configured as per business needs

  • Client host level
  • Certain table / database [based on regex]
  • Certain user.

Summary




0 Comments


Leave a Comment

Name *

Email *

Comment *



Search All Blogs



Need Support?


Solutions      Services      Resources      About Mafiree

More than 3000 servers monitored

Certified database Administrators

24X7X365 Support

Happy Clients


ENQUIRE NOW
For Sales Related Queries, Please Call Our Sales Experts at

 +91-80-41155993


Meet Mafiree on social networks!

     

PCI Certificate

Copyright © 2019 - All Rights Reserved - Mafiree