Custom auto processlist logging (MySQL):

As a DBA, you should be capture logs to track all SQL activities executed by all users on the database server. For this you have to enable the general log.

Enabling the general log is a great logging process for debugging and monitoring activities, including identifying slow or problematic queries. However, it can create significant performance overhead on production servers.

To tackle this, we can create a custom event that executes every 30 seconds (or your preferred interval) to track all SQL statements, including connection attempts, disconnections, queries, and commands.

Here’s how you can set it up:

-- create database (optional)
CREATE DATABASE log;
-- use created database
USE log;        

-- create logging table

CREATE TABLE log.processlist_logs (
  ID bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key of processlist_logs table',
  PID bigint unsigned NOT NULL DEFAULT '0' COMMENT 'ID of Processlist Thread (ID of information_schema.processlist table)',
  USER varchar(50) NOT NULL DEFAULT '' COMMENT 'User currently active with running thread.',
  HOST varchar(100) NOT NULL DEFAULT '' COMMENT 'Host details from which request initiated.',
  DB varchar(50) DEFAULT NULL COMMENT 'Database name on which the running thread is performing.',
  COMMAND varchar(50) NOT NULL DEFAULT '' COMMENT 'Command mode, e.g., sleep, query, etc.',
  TIME int NOT NULL DEFAULT '0' COMMENT 'What is the exact time for executing that query currently?',
  STATE varchar(100) DEFAULT NULL COMMENT 'Command state.',
  INFO longtext COMMENT 'Full details about running query',
  C_TIME timestamp DEFAULT CURRENT_TIMESTAMP COMMENT 'Current time (C_TIME) when current thread is running',
  PRIMARY KEY (ID),
  UNIQUE KEY PID_UNIQUE (PID),
  KEY idx_c_time (C_TIME)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT ='This table holds the records of the running process list to track activities on the database.';        

-- create event for auto logging running processlist/thread

DELIMITER $$

CREATE DEFINER=`root`@`localhost` 
EVENT `processlist_log` 
ON SCHEDULE EVERY 30 second 
STARTS '2024-10-03 13:23:00' 
ON COMPLETION PRESERVE 
ENABLE 
COMMENT 'Running current processlist auto logging event'
DO
BEGIN
    -- Insert new processlist logs or update if duplicate key
    INSERT INTO log.processlist_logs(PID, USER, HOST, DB, COMMAND, TIME, STATE, INFO, C_TIME)
    SELECT p.ID, p.USER, p.HOST, p.DB, p.COMMAND, p.TIME, p.STATE, p.INFO, CURRENT_TIMESTAMP()
    FROM information_schema.processlist p
    LEFT JOIN log.processlist_logs pl ON p.ID = pl.ID
    WHERE p.COMMAND <> 'Sleep' AND p.INFO IS NOT NULL
    ON DUPLICATE KEY UPDATE TIME = p.TIME, C_TIME = CURRENT_TIMESTAMP();
    
    -- Delete old processlist logs if after 23:30
    IF CURRENT_TIME() > '23:30:00' THEN
        DELETE FROM log.processlist_logs 
        WHERE C_TIME < NOW() - INTERVAL 7 DAY;
    END IF;
END $$

DELIMITER ;        

-- check logs

select * from log.processlist_logs order by id desc;        

?? This setup will help you efficiently track database activities while minimizing performance overhead.