What Is TiDB Audit Trail?

Introduction

In database management, a reliable audit trail is crucial for securing data, improving performance, and ensuring compliance.

TiDB, a distributed SQL database known for its scalability and high availability, provides native audit tools to track data access and modifications. While these built-in features offer a foundational level of auditing, they may not cover all the advanced needs of organizations. This article will explore how TiDB’s audit trails work and how DataSunrise can take your auditing to the next level with advanced capabilities and real-time monitoring.

Understanding TiDB Audit Trail

TiDB's audit trail system captures detailed logs of database operations, leveraging its built-in mechanisms to track a variety of events. These include user authentications, data modifications, and other critical actions within the distributed SQL environment. The audit trail serves as an essential resource for TiDB administrators, security teams, and compliance officers, offering a comprehensive view of database activity in real-time. By capturing these events, TiDB helps ensure transparency and security across large-scale, high-performance databases.

TiDB Audit Trail: Built-In Logging and Monitoring Mechanisms

1. TiDB Dashboard

A web-based interface for cluster monitoring and management. While primarily graphical, it offers deep insights into cluster performance.

Key Features:

Performance analysis
Slow query visualization
Cluster diagnostics

2. Metrics Schema

The Metrics Schema provides SQL-based access to Prometheus performance metrics, offering a unique way to query system performance data directly through SQL.

Example queries:

-- View TiDB server uptime
SELECT * FROM metrics_schema.uptime;

-- Check TiDB query duration
SELECT * FROM metrics_schema.tidb_query_duration 
WHERE value IS NOT NULL 
LIMIT 10;

The Metrics Schema provides performance-oriented insights, offering aggregated query and system metrics. However, it lacks the detailed user activity tracking needed for comprehensive audit logging, making it best suited for high-level performance troubleshooting rather than security monitoring.

3. Information Schema

TiDB's Information Schema offers real-time, system-wide insights into the database, allowing administrators to monitor query performance, view schema information, and track system status.

a. Statement Summary Table

Aggregates SQL statement performance statistics.

Example queries:

-- Top 5 slowest statements
SELECT * FROM information_schema.statements_summary 
ORDER BY avg_latency DESC 
LIMIT 5;

-- Statements with high total execution time
SELECT digest, 
       query, 
       sum_latency, 
       exec_count
FROM information_schema.statements_summary
WHERE sum_latency > 1000000  -- 1 second total
ORDER BY sum_latency DESC;

b. Cluster Log Table

Query logs across different TiDB cluster components.

Example queries:

-- Find DDL-related logs in the last hour
SELECT * FROM information_schema.cluster_log
WHERE time > '2024-02-18 00:00:00'
AND time < '2025-02-18 23:59:59'
AND message LIKE 'ddl%';

-- Filter logs by specific component
SELECT * FROM information_schema.cluster_log
WHERE time > '2024-02-18 00:00:00'
AND time < '2025-02-18 23:59:59'
AND level = 'ERROR';

c. Slow Query Log

Captures and records slow-running SQL statements.

Example queries:

-- View slow queries taking more than 1 second
SELECT * FROM information_schema.slow_query
WHERE query_time > 1
ORDER BY query_time DESC;

-- Analyze slow queries by user
SELECT user, 
       COUNT(*) as slow_query_count, 
       AVG(query_time) as avg_query_time
FROM information_schema.slow_query
GROUP BY user
ORDER BY slow_query_count DESC;

d. Expensive Query Log

Identifies and logs resource-intensive queries.

Example queries:

-- Find queries consuming high memory
SELECT * FROM information_schema.cluster_slow_query
WHERE mem_max > 1024 * 1024 * 100  -- 100 MB
ORDER BY mem_max DESC;

In summary, TiDB's Information Schema provides essential tools for real-time monitoring and performance analysis, including statement summaries, cluster logs, and slow/expensive query logs.

Additional Monitoring Tips

Enable verbose logging: “`sql — Enable detailed slow query logging SET GLOBAL tidb_slow_log_threshold = 300; — Log queries over 300ms

— Enable expensive query tracking SET GLOBAL tidb_expensive_query_time_threshold = 60;


2. Monitoring Configuration:
```sql
-- Check current variables for log settings
SHOW VARIABLES LIKE 'tidb%log%';
-- Check current log settings
SELECT *
FROM INFORMATION_SCHEMA.CLUSTER_CONFIG
WHERE `KEY` LIKE '%log%';

By utilizing these logging and monitoring mechanisms, you can gain deep insights into your TiDB cluster's performance, diagnose issues, and optimize database operations.

Enhancing TiDB Audit Trails with DataSunrise

Activity Monitoring for TiDB

DataSunrise amplifies TiDB's audit features with advanced database monitoring and security. Seamlessly integrating with TiDB, it enhances audit trails and enforces robust security protocols tailored for regulatory compliance and data protection.

Real-Time Insights and Monitoring

DataSunrise provides a centralized dashboard for real-time visibility into database activities, enabling administrators to track key events, such as:

Query executions and data modifications
User authentication and session activity
Behavioral anomalies and access patterns
Security breaches and policy violations
Comprehensive database activity

Intelligent Analytics & Regulatory Compliance

DataSunrise simplifies compliance with an integrated suite of tools, including:

For detailed implementation guidance, check out the DataSunrise Documentation.

Conclusion

TiDB offers essential audit capabilities for tracking data operations, but as security challenges evolve, organizations often need more sophisticated solutions.

DataSunrise enhances TiDB’s native features, providing deeper audit control, real-time monitoring, and stronger security measures. By integrating DataSunrise with TiDB, you can build a more resilient security framework that simplifies compliance, strengthens data protection, and delivers critical intelligence.

Ready to strengthen your TiDB audit capabilities? Schedule a demo to see how DataSunrise can transform your security and compliance efforts.