sudo apt-get install mariadb-plugin-audit
[mysqld]
plugin-load-add=audit_log.so
audit_log_file=/var/log/mysql/mariadb-audit.log
audit_log_policy=ALL
SHOW VARIABLES LIKE 'audit%';
sudo tail -f /var/log/mariadb/audit.log
20250101 12:34:56,server1,root,localhost,1,1,QUERY_DML,'UPDATE mydb.mytable SET mycolumn = 'new_value' WHERE id = 1',0
2023-10-10T12:34:56.789012Z 1 Connect root@localhost on mydb
2023-10-10T12:34:57.123456Z 1 Query SELECT * FROM mytable
2023-10-10T12:35:01.234567Z 1 Query UPDATE mytable SET column1 = 'value' WHERE id = 1
2023-10-10T12:35:05.345678Z 1 Quit
[mysqld]
general_log=ON
general_log_file=/var/log/mysql/mariadb-general.log
SHOW VARIABLES LIKE 'general_log';
general_log_output=TABLE
grep "SELECT" /var/log/mysql/mariadb-general.log
2023-10-10 12:36:00 123456789 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
2023-10-10 12:36:01 123456789 [Warning] Too many connections
2023-10-10 12:36:02 123456789 [Warning] Aborted connection 123 to db: 'mydb' user: 'user1' host: '192.168.1.100' (Got timeout reading communication packets)
[mysqld]
log_warnings=2
tail -f /var/log/mysql/error.log
SHOW BINARY LOGS;
mysqlbinlog /var/log/mysql/mariadb-bin.000001
PURGE BINARY LOGS TO 'mariadb-bin.000005';
SHOW VARIABLES LIKE 'binlog_format';
# Time: 2023-10-10T12:43:00.123456Z
# User@Host: user8[user8] @ 192.168.1.180 []
# Thread_id: 802
# Query_time: 22.123456 Lock_time: 0.009012 Rows_sent: 100 Rows_examined: 1000000
SET timestamp=1696941780;
SELECT * FROM mytable WHERE indexed_column LIKE '%value%';
[mysqld]
slow_query_log=ON
slow_query_log_file=/var/log/mysql/mariadb-slow.log
long_query_time=2
[mysqld]
log_output=TABLE
max_binlog_size = 100M
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
SELECT event_time, user_host, argument
FROM mysql.general_log
WHERE argument LIKE '%Access denied%';
mysqlbinlog --start-datetime="2024-01-01 00:00:00" mariadb-bin.000001
mysqldumpslow -s at -t 10 /path/to/slow_query.log