Database Auditing: Concepts, Methods, and TXSQL Implementation

In recent years, incidents caused by improper database operations have highlighted the importance of protecting critical data. Database auditing is an effective solution that records user actions, helps locate illegal operations, and enables post‑incident forensics.

1. What is database auditing? Similar to installing surveillance at a warehouse entrance, database auditing monitors all connection entry points, logs every action, and allows administrators to query historical events to identify suspicious behavior.

2. Common auditing approaches

(1) Application‑layer auditing : Audits statements before they reach the database engine. It is database‑agnostic but adds overhead to the application.

(2) Transport‑layer auditing : Captures packets between client and server. It works for both encrypted and unencrypted traffic but requires packet parsing.

(3) Kernel‑level auditing : Implements auditing directly in the database kernel, achieving minimal performance impact but demanding high stability and development effort.

(4) Plugin auditing : Extends open‑source databases via plugins. Plugins embed in the kernel, offering rich runtime information with moderate performance impact. Examples for MySQL include the McAfee plugin, the official audit plugin, MariaDB audit plugin, and Percona audit plugin.

Overall, audit performance varies with workload; the overhead is roughly proportional to QPS and the complexity of audit rules.

3. MySQL official audit plugins MySQL provides more than ten plugin types (e.g., MYSQL_AUDIT_GENERAL_ALL, MYSQL_AUDIT_CONNECTION_ALL) implemented in sql_audit.h and sql_audit.cc . These plugins define registration points that trigger audit processing when relevant events occur.

4. TXSQL audit implementation

TXSQL is Tencent Cloud’s MySQL‑compatible kernel branch. It offers both synchronous and asynchronous audit architectures while reusing the MySQL plugin interface.

4.1 Synchronous audit mode The workflow: a worker thread generates an audit event for each query, evaluates audit rules, converts the event to JSON, and copies it into a shared Audit buffer. A flush thread asynchronously writes the buffer to disk, and an audit agent pushes logs to CTSDB for storage and query.

4.2 Asynchronous audit mode To avoid performance degradation under heavy regex‑based rule sets, the worker thread quickly enqueues audit events. A dedicated write thread consumes the queue, performs rule evaluation, length calculation, locking, and buffer copying. This reduces contention on malloc and improves scalability.

5. Audit rule configuration TXSQL supports rules based on client IP, account, and database name, with match types such as include, exclude, equal, not equal, and regex. Rules are stored as linked‑list structures in memory and reloaded from configuration files on change. Rule lists represent OR relationships, while rule contents (username, host, database) use AND relationships.

6. Performance impact Enabling TXSQL audit incurs a modest overhead—typically around 6 % for synchronous mode and less than 3 % for asynchronous mode—far lower than many third‑party solutions. The design keeps critical sections (memory reservation) short, allowing high QPS workloads to maintain excellent throughput.

7. Best practices • Merge rules with the same type to reduce duplication. • Prioritize high‑selectivity rules to short‑circuit evaluation. • Place regex matches last, after exact and range checks.

8. Conclusion TXSQL provides a low‑overhead, feature‑rich auditing solution with both sync and async modes, flexible rule definitions, and strong security benefits. DBAs can use it to monitor activity, generate compliance reports, and quickly respond to suspicious behavior.