MySQL Shell : send SQL statements to syslog

From MySQL Shell 8.0.24, it’s possible to log all the SQL statements issued in MySQL Shell.

Everything is well documented in the dedicated manual section: System Logging for SQL Statements.

Let’s see it in action. We will first start MySQL Shell with --syslog option:

$ mysqlsh --syslog --sql root@localhost

We enter a statement:

MySQL  localhost:33060+   2021-04-30 09:18:39 
SQL  show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| bookstore                     |
| clusterdemo                   |
...

And we can verify in syslog if something has been logged. I use Systemd and Journald therefor I will use journaclt to query syslog:

$ journalctl $(which mysqlsh)
-- Journal begins at Wed 2021-04-28 18:34:16 CEST, ends at Fri 2021-04-30 09:20:35 CEST. --
Apr 30 09:18:43 imac mysqlsh[256214]: SYSTEM_USER=fred MYSQL_USER=root 
                                      CONNECTION_ID=15 DB_SERVER=localhost DB='--' 
                                      QUERY='show databases;'

Excellent this is what we were looking for. We have some important information like the system user and the MySQL user, and of course the query.

The manual states that SQL statements that would be excluded from the MySQL Shell code history are also excluded from the system logging facility. What are those statements ?

In JS mode, get all the options like this:

JS  shell.options

And the entry we are looking for is "history.sql.ignorePattern":

"history.sql.ignorePattern": "*IDENTIFIED*:*PASSWORD*",

So those SQL statement having INDENTIFIED or PASSWORD in them won’t be saved in the history neither sent to syslog.

Will it be possible to enable this feature each time I start MySQL Shell… and as I am very use to it, I’m pretty sure I will forget to add --syslog each time.

The answer is… YES of course 😉

Like other options, you can configure it and persist it like this:

JS  shell.options.setPersist("history.sql.syslog", 1)

This means that if you have started mysqlsh with --syslog, it’s also possible to disable it for the current session and re-enable it like this:

JS  shell.options.set("history.sql.syslog", 0)

JS  shell.options.set("history.sql.syslog", 1)

This feature is very useful to track who did what on the system. I think it would be a great idea to extend it to the other modes (JavaScript & Python) and also non-interactive commands from a script or from command line.