One of the new features introduced in MySQL 8.0.24 was the ability to log all SQL statements that are issued in the MySQL Shell to the system log. This is a useful feature that can greatly assist in tracking who did what on the system.
Usage
The simplest way to utilize the new Shell logging feature is to simply start the MySQL Shell with the syslog option enabled like so:
1 | $> mysqlsh --syslog --sql root@localhost |
From this point forward all SQL entered in the MySQL Shell will be logged to the system log. For example, the following SQL is entered into the Shell:
1 2 3 4 5 6 7 8 9 10 | MySQL localhost:33060+ ssl SQL > show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ |
We can now check the system log and verify that the statement above was indeed logged as expected, along with the system user and MySQL user that was used:
1 2 | $> journalctl $(which mysqlsh) Jun 28 10:01:11 localhost mysqlsh[9558]: SYSTEM_USER=brian MYSQL_USER=root CONNECTION_ID=16 DB_SERVER=localhost DB='--' QUERY='show databases;' |
Exclusions
As per the MySQL user manual, not all statements will be logged to the system log. Basically, any statements that would normally be excluded from the MySQL Shell code history for security reasons will also be excluded from the system log. We can verify what will be excluded by checking the MySQL Shell options in JS mode:
1 | MySQL localhost:33060+ ssl JS > shell.options |
This will list all current options for the shell, and the key one here is the history.sql.ignorePattern option. In our case, it is set as follows:
1 | "history.sql.ignorePattern": "*IDENTIFIED*:*PASSWORD*" |
So any SQL statement containing the words IDENTIFIED or PASSWORD will be ignored, and will not be logged into the MySQL Shell history, nor the system log even with logging enabled. This alleviates any security concerns about passwords being set in plain text in the system log.
Logging By Default
To automatically enable system logging from the MySQL Shell without having to specify it at each runtime, it is possible to set the option to persist. From the MySQL Shell, enter JS mode and set the following option:
1 | MySQL localhost:33060+ ssl JS > shell.options.setPersist("history.sql.syslog",1) |
From this point forward all SQL that is entered in the MySQL shell will be logged to the system log by default.
Wrapping Up
Knowing who executed what in the MySQL Shell is a very useful administrative tool, and can help when tracking down issues or finding out who issued a particular statement at a given time. While not an entirely foolproof method of auditing (logging can still be disabled on a per-session basis), it is just another item for the DBA toolbox that can ease the administrative burden of today’s large database installations.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure, open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Download Percona Distribution for MySQL Today
