MySQL Shell 101 - System Log - Percona Database Performance Blog

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:

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:

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:

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:

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:

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:

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.