Several common implementation methods for MySQL recording operation logs

Preface

There are usually several ways to log operation logs in MySQL. The most common way is to enable MySQL's logging function, or use triggers, audit plug-ins and other means to record database operations. Here are some common methods for recording operation logs:

1. Enable MySQL General Query Log

MySQL provides the General Query Log function, which can record all executed SQL queries. Enabling this log can help you track operations in the database.

How to enable:

Edit MySQL configuration file(usuallyor, depending on your operating system and MySQL installation location):

exist[mysqld]Partially add the following configuration:
```
general_log = 1 
general_log_file = /path/to/your/
```
- general_log = 1: Enable universal query log.
- general_log_file: Specify the storage path of the log file.
Restart MySQL service: After modifying the configuration file, restart the MySQL service to take effect.
```
sudo systemctl restart mysql
```
Dynamically enable/disable universal query logs: You can also enable or disable common query logs dynamically through SQL commands:
```
SET GLOBAL general_log = 'ON'; -- Enable 
SET GLOBAL general_log = 'OFF'; -- Disabled
```

Notice:

The general query log records all SQL queries, including SELECT, INSERT, UPDATE, DELETE, etc.
Enabling query logging can cause performance degradation, especially in high load environments, as each query needs to be written to the log file.
Operation records can be analyzed by viewing the log files, but the log files will be very large and therefore need to be cleaned regularly.

2. Enable MySQL Binary Log

Binary logs are mainly used for replication and data recovery, but they can also be used to record more detailed operation history, especially operations involving changes to data. Unlike general query logs, binary logs do not record SELECT queries and only record operations that change data.

How to enable:

In MySQL configuration fileorEnable binary logging:
```
[mysqld] 
log_bin = /path/to/your/mysql-bin 
binlog_format = ROW
```
- log_bin: Enable binary logging.
- binlog_format = ROW: Set the binary log format to row level (ROW), so that data changes can be recorded more accurately.
Restart MySQL service：
```
sudo systemctl restart mysql
```
View binary log：

You can use the following command to view the contents in the binary log:
```
mysqlbinlog /path/to/your/mysql-bin.000001
```

Notice:

Binary log files will also grow over time and therefore need to be cleaned regularly.
Binary logs provide detailed records of data changes, suitable for data recovery and data auditing.

3. Use the MySQL Audit Plugin

MySQL also provides audit plug-ins (such as MySQL Enterprise Audit Plugin), which are specially used to record users' operation logs, including login, query, modification and other operations.

Enable MySQL Enterprise Audit Plugin:

Install and enable plug-ins：

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Configure audit log file path：

SET GLOBAL audit_log_file = '/path/to/audit_log.log';

View audit log：

The audit plug-in writes the recorded information to the specified log file, which can be viewed using the general text viewing tool.

Notice:

MySQL audit plug-ins are usually featured in the MySQL Enterprise Edition, but there are open source audit plug-ins available.
The audit plugin provides more meticulous logging, which can record every user's operation and allows you to set different logging levels.

4. Use Triggers to record operation logs

If you want to record operation logs for specific tables, it can be done with triggers. When an INSERT, UPDATE, or DELETE operation occurs in the table, the trigger can insert the operation log into a log table.

Example: Create log tables and triggers

Create a log table：

CREATE TABLE operation_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    action_type VARCHAR(20),
    table_name VARCHAR(50),
    old_data TEXT,
    new_data TEXT,
    user VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Create a trigger：

For example, recordusersAll INSERT operations of the table:

DELIMITER //

CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO operation_log (action_type, table_name, new_data, user)
    VALUES ('INSERT', 'users', CONCAT('id: ', , ', name: ', ), USER());
END //

DELIMITER ;

Other triggers：
- For UPDATE operations, you can recordold_dataandnew_data。
- For DELETE operations, only recordsold_data。

Notice:

Triggers are database-level solutions that accurately record every data operation, but may affect database performance, especially in the case of large amounts of data operations.
This method is more suitable for fine-grained audits of specific tables.

Summarize

General query logSuitable for logging all SQL queries, but may affect performance and is suitable for development and debugging environments.
Binary logSuitable for data recovery and replication, but only records operations that change data.
Audit plug-inSuitable for more detailed and standardized operational logging, usually used in enterprise environments.
triggerSuitable for logging for specific tables and specific operations, suitable for granular control.

Choose the appropriate method to record operation logs according to your needs to ensure balanced log details and system performance.

This is the article about several commonly used implementation methods for MySQL logging operation logs. For more related contents of MySQL operation logging, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!