Auditing MySQL Operations with init_connect and Binlog Analysis

In a testing environment a careless DELETE without a WHERE clause caused a table‑drop alert, highlighting the need for better operational visibility and user‑action tracking in MySQL.

The proposed solution leverages MySQL's init_connect variable to automatically record each connection, creates a dedicated audit database, and uses binary log (binlog) analysis to trace dangerous statements back to the originating session.

Step 1 – Enable init_connect

Set the global init_connect variable to insert a row into an audit table whenever a client connects:

mysql> show variables like 'init_connect';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| init_connect  |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global init_connect='insert into auditdb.accesslog(connectionID,ConnUser,MatchUser,LoginTime) values(connection_id(),user(),current_user(),now());';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'init_connect';
+---------------+---------------------------------------------------------------+
| Variable_name | Value                                                         |
+---------------+---------------------------------------------------------------+
| init_connect  | insert into auditdb.accesslog(connectionID,ConnUser,MatchUser,LoginTime) values(connection_id(),user(),current_user(),now()); |
+---------------+---------------------------------------------------------------+
1 row in set (0.00 sec)

Note: The setting does not apply to users with the SUPER privilege.

Step 2 – Create the audit log table and grant permissions

mysql> create database auditdb charset utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> create table auditdb.accesslog (
    id int(10) unsigned not null primary key auto_increment,
    Connectionid int(10) unsigned,
    ConnUser varchar(30) not null default '',
    MatchUser varchar(30) not null default '',
    Logintime datetime
);
Query OK, 0 rows affected (0.02 sec)

mysql> grant insert on auditdb.accesslog to mindoc@'%';
Query OK, 0 rows affected (0.00 sec)

All users that will log connections need INSERT rights on the audit table, but should not be granted UPDATE or DELETE to avoid tampering.

Step 3 – Simulate an accidental delete and capture the binlog

[root@db ~]# mysql -u mindoc -p -h 172.18.1.76
Enter password:

mysql> create table temp(id int, name varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into temp values(1,'aa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into temp values(2,'aa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into temp values(3,'aa');
Query OK, 1 row affected (0.01 sec)

mysql> delete from temp;
Query OK, 3 rows affected (0.01 sec)

The delete triggers an alert; the exact time and thread ID can be retrieved from the binlog.

Step 4 – Extract and analyze the binlog

# mysqlbinlog -v --base64-output=decode-rows /usr/local/mysql-5.7.20/binlog/mysql-bin.000002 > audit.log

# tail -35 audit.log
# at 49003
#191120 13:02:18 server id 76  end_log_pos 49080 CRC32 0x73dc1dda     Query    thread_id=130    exec_time=0    error_code=0
SET TIMESTAMP=1574226138/*!*/;
BEGIN;
# at 49080
#191120 13:02:18 server id 76  end_log_pos 49135 CRC32 0x360e7fe4     Table_map: `mindoc_db`.`temp` mapped to number 249
# at 49135
#191120 13:02:18 server id 76  end_log_pos 49194 CRC32 0xbbf0d78f     Delete_rows: table id 249 flags: STMT_END_F
### DELETE FROM `mindoc_db`.`temp`
### WHERE @1=1 @2='aa'
### DELETE FROM `mindoc_db`.`temp`
### WHERE @1=2 @2='aa'
### DELETE FROM `mindoc_db`.`temp`
### WHERE @1=3 @2='aa'
# at 49194
#191120 13:02:18 server id 76  end_log_pos 49225 CRC32 0x277ece0b     Xid = 23721
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT='AUTOMATIC'/* added by mysqlbinlog */;

From the binlog we see thread_id=130 executing the DELETE at 2019‑11‑20 13:02:18 . Using the audit table we can retrieve the connection details:

mysql> select * from auditdb.accesslog where Connectionid=130;
+----+--------------+--------------------+-----------+---------------------+
| id | Connectionid | ConnUser           | MatchUser | Logintime           |
+----+--------------+--------------------+-----------+---------------------+
| 1  | 130          | [email protected] | mindoc@%  | 2019-11-20 12:59:21 |
+----+--------------+--------------------+-----------+---------------------+
1 row in set (0.00 sec)

The offending session originated from IP 172.18.1.99 using the mindoc@'%' account, allowing the team to pinpoint the responsible user and remediate the privilege configuration.

By combining init_connect logging, a lightweight audit table, and binlog inspection, MySQL administrators can quickly trace destructive statements back to the exact user and host, improving operational security and reducing downtime.