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
$ 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: 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:
And the entry we are looking for is
So those SQL statement having
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
--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)