Set the audit log option in the configuration file as follows:
audit_log_format=CSV plugin-load="audit_log=audit_log.so" audit_log_policy=ALL audit_log_strategy=SYNCHRONOUS
Use the MySQL client to connect to the database (do not specify the default database when establishing the connection):
bin/mysql --defaults-file=./my.cnf -uroot
Run the following statement:
mysql> create database db1; Query OK, 1 row affected (0.04 sec) mysql> create database db2; Query OK, 1 row affected (0.01 sec) mysql> use db1; Database changed mysql> use db2; Database changed mysql> select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)
Check the audit log file:
"Connect","15892_2023-06-07T04:06:54","2024-03-14T11:17:40Z","11",0,"root","root","","","localhost","","" "Query","15893_2023-06-07T04:06:54","2024-03-14T11:17:40Z","select","11",0,"select @@version_comment limit 1","root[root] @ localhost []","localhost","","","" "Query","15894_2023-06-07T04:06:54","2024-03-14T11:17:59Z","create_db","11",0,"create database db1","root[root] @ localhost []","localhost","","","" "Query","15895_2023-06-07T04:06:54","2024-03-14T11:18:02Z","create_db","11",0,"create database db2","root[root] @ localhost []","localhost","","","" "Query","15896_2023-06-07T04:06:54","2024-03-14T11:18:07Z","select","11",0,"SELECT DATABASE()","root[root] @ localhost []","localhost","","","" "Init DB","15897_2023-06-07T04:06:54","2024-03-14T11:18:07Z","init db","11",0,"","root[root] @ localhost []","localhost","","","" "Query","15898_2023-06-07T04:06:54","2024-03-14T11:18:07Z","show_databases","11",0,"show databases","root[root] @ localhost []","localhost","","","" "Query","15899_2023-06-07T04:06:54","2024-03-14T11:18:07Z","show_tables","11",0,"show tables","root[root] @ localhost []","localhost","","","" "Query","15900_2023-06-07T04:06:54","2024-03-14T11:18:11Z","select","11",0,"SELECT DATABASE()","root[root] @ localhost []","localhost","","","" "Init DB","15901_2023-06-07T04:06:54","2024-03-14T11:18:11Z","init db","11",0,"","root[root] @ localhost []","localhost","","","" "Query","15902_2023-06-07T04:06:54","2024-03-14T11:18:11Z","show_databases","11",0,"show databases","root[root] @ localhost []","localhost","","","" "Query","15903_2023-06-07T04:06:54","2024-03-14T11:18:11Z","show_tables","11",0,"show tables","root[root] @ localhost []","localhost","","","" "Query","15904_2023-06-07T04:06:54","2024-03-14T11:20:22Z","select","11",0,"select 1","root[root] @ localhost []","localhost","","",""
The use db statement is not recorded, and the default db field is empty.
Disconnect and reconnect mysqld server (specify the default database db1):
bin/mysql --defaults-file=./my.cnf -uroot -Ddb1
mysql> select 2; +---+ | 2 | +---+ | 2 | +---+ 1 row in set (0.00 sec) mysql> use db2; Database changed mysql> select 3; +---+ | 3 | +---+ | 3 | +---+ 1 row in set (0.00 sec)
"Connect","15906_2023-06-07T04:06:54","2024-03-14T11:23:25Z","12",0,"root","root","","","localhost","","db1" "Query","15907_2023-06-07T04:06:54","2024-03-14T11:23:25Z","show_databases","12",0,"show databases","root[root] @ localhost []","localhost","","","db1" "Query","15908_2023-06-07T04:06:54","2024-03-14T11:23:25Z","show_tables","12",0,"show tables","root[root] @ localhost []","localhost","","","db1" "Query","15909_2023-06-07T04:06:54","2024-03-14T11:23:25Z","select","12",0,"select @@version_comment limit 1","root[root] @ localhost []","localhost","","","db1" "Query","15910_2023-06-07T04:06:54","2024-03-14T11:23:47Z","select","12",0,"select 2","root[root] @ localhost []","localhost","","","db1" "Query","15911_2023-06-07T04:06:54","2024-03-14T11:23:54Z","select","12",0,"SELECT DATABASE()","root[root] @ localhost []","localhost","","","db1" "Init DB","15912_2023-06-07T04:06:54","2024-03-14T11:23:54Z","init db","12",0,"","root[root] @ localhost []","localhost","","","db1" "Query","15913_2023-06-07T04:06:54","2024-03-14T11:23:54Z","show_databases","12",0,"show databases","root[root] @ localhost []","localhost","","","db1" "Query","15914_2023-06-07T04:06:54","2024-03-14T11:23:54Z","show_tables","12",0,"show tables","root[root] @ localhost []","localhost","","","db1" "Query","15915_2023-06-07T04:06:54","2024-03-14T11:23:57Z","select","12",0,"select 3","root[root] @ localhost []","localhost","","","db1"
The use db statement is not recorded, and the default db field is always db1.
Hi @Chi Zhang
Thank you for the report.Verified as described.
High
HighHigh
Set the audit log option in the configuration file as follows:
audit_log_format=CSV plugin-load="audit_log=audit_log.so" audit_log_policy=ALL audit_log_strategy=SYNCHRONOUSUse the MySQL client to connect to the database (do not specify the default database when establishing the connection):
bin/mysql --defaults-file=./my.cnf -urootRun the following statement:
mysql> create database db1; Query OK, 1 row affected (0.04 sec) mysql> create database db2; Query OK, 1 row affected (0.01 sec) mysql> use db1; Database changed mysql> use db2; Database changed mysql> select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)Check the audit log file:
"Connect","15892_2023-06-07T04:06:54","2024-03-14T11:17:40Z","11",0,"root","root","","","localhost","","" "Query","15893_2023-06-07T04:06:54","2024-03-14T11:17:40Z","select","11",0,"select @@version_comment limit 1","root[root] @ localhost []","localhost","","","" "Query","15894_2023-06-07T04:06:54","2024-03-14T11:17:59Z","create_db","11",0,"create database db1","root[root] @ localhost []","localhost","","","" "Query","15895_2023-06-07T04:06:54","2024-03-14T11:18:02Z","create_db","11",0,"create database db2","root[root] @ localhost []","localhost","","","" "Query","15896_2023-06-07T04:06:54","2024-03-14T11:18:07Z","select","11",0,"SELECT DATABASE()","root[root] @ localhost []","localhost","","","" "Init DB","15897_2023-06-07T04:06:54","2024-03-14T11:18:07Z","init db","11",0,"","root[root] @ localhost []","localhost","","","" "Query","15898_2023-06-07T04:06:54","2024-03-14T11:18:07Z","show_databases","11",0,"show databases","root[root] @ localhost []","localhost","","","" "Query","15899_2023-06-07T04:06:54","2024-03-14T11:18:07Z","show_tables","11",0,"show tables","root[root] @ localhost []","localhost","","","" "Query","15900_2023-06-07T04:06:54","2024-03-14T11:18:11Z","select","11",0,"SELECT DATABASE()","root[root] @ localhost []","localhost","","","" "Init DB","15901_2023-06-07T04:06:54","2024-03-14T11:18:11Z","init db","11",0,"","root[root] @ localhost []","localhost","","","" "Query","15902_2023-06-07T04:06:54","2024-03-14T11:18:11Z","show_databases","11",0,"show databases","root[root] @ localhost []","localhost","","","" "Query","15903_2023-06-07T04:06:54","2024-03-14T11:18:11Z","show_tables","11",0,"show tables","root[root] @ localhost []","localhost","","","" "Query","15904_2023-06-07T04:06:54","2024-03-14T11:20:22Z","select","11",0,"select 1","root[root] @ localhost []","localhost","","",""The use db statement is not recorded, and the default db field is empty.
Disconnect and reconnect mysqld server (specify the default database db1):
bin/mysql --defaults-file=./my.cnf -uroot -Ddb1Run the following statement:
mysql> select 2; +---+ | 2 | +---+ | 2 | +---+ 1 row in set (0.00 sec) mysql> use db2; Database changed mysql> select 3; +---+ | 3 | +---+ | 3 | +---+ 1 row in set (0.00 sec)Check the audit log file:
"Connect","15906_2023-06-07T04:06:54","2024-03-14T11:23:25Z","12",0,"root","root","","","localhost","","db1" "Query","15907_2023-06-07T04:06:54","2024-03-14T11:23:25Z","show_databases","12",0,"show databases","root[root] @ localhost []","localhost","","","db1" "Query","15908_2023-06-07T04:06:54","2024-03-14T11:23:25Z","show_tables","12",0,"show tables","root[root] @ localhost []","localhost","","","db1" "Query","15909_2023-06-07T04:06:54","2024-03-14T11:23:25Z","select","12",0,"select @@version_comment limit 1","root[root] @ localhost []","localhost","","","db1" "Query","15910_2023-06-07T04:06:54","2024-03-14T11:23:47Z","select","12",0,"select 2","root[root] @ localhost []","localhost","","","db1" "Query","15911_2023-06-07T04:06:54","2024-03-14T11:23:54Z","select","12",0,"SELECT DATABASE()","root[root] @ localhost []","localhost","","","db1" "Init DB","15912_2023-06-07T04:06:54","2024-03-14T11:23:54Z","init db","12",0,"","root[root] @ localhost []","localhost","","","db1" "Query","15913_2023-06-07T04:06:54","2024-03-14T11:23:54Z","show_databases","12",0,"show databases","root[root] @ localhost []","localhost","","","db1" "Query","15914_2023-06-07T04:06:54","2024-03-14T11:23:54Z","show_tables","12",0,"show tables","root[root] @ localhost []","localhost","","","db1" "Query","15915_2023-06-07T04:06:54","2024-03-14T11:23:57Z","select","12",0,"select 3","root[root] @ localhost []","localhost","","","db1"The use db statement is not recorded, and the default db field is always db1.