12.3 System Logging for User SQL Statements

From MySQL 8.0.24, SQL statements that you issue in MySQL Shell’s SQL mode can be sent to the operating system’s system logging facility. On Unix, this is syslog; on Windows, it is the Windows Event Log. The destination where logged messages appear is system dependent. On Linux, the destination is often the /var/log/messages file.

When you activate system logging for SQL statements, the following items are written to the system logging facility:

SQL statements that you issue interactively in MySQL Shell’s SQL mode.
Single SQL statements that you execute by entering them immediately after the \sql command while in MySQL Shell’s JavaScript or Python mode.
Instances of the \source command that you issue interactively in MySQL Shell’s SQL mode.

The following items are excluded and are not written to the system logging facility:

The contents of a script file that you execute using the \source command. Only the \source command itself is written to the system logging facility.
SQL statements that MySQL Shell executes itself in the course of AdminAPI operations. You can log these to the MySQL Shell application log file, as explained in Section 12.5, “Logging AdminAPI Operations”.
SQL statements that would be excluded from the MySQL Shell code history, as specified by the history.sql.ignorePattern MySQL Shell configuration option, or the --histignore command-line option (which sets the value of history.sql.ignorePattern for the current session only).

To send SQL statements that you issue in MySQL Shell’s SQL mode to the operating system’s system logging facility, choose one of these options:

Use the --syslog command-line option when starting MySQL Shell.
Use the MySQL Shell \option command to set the history.sql.syslog MySQL Shell configuration option. For instructions to use this command, see Section 13.4, “Configuring MySQL Shell Options”.
Use the shell.options object to set the history.sql.syslog MySQL Shell configuration option. For instructions to use this configuration interface, see Section 13.4, “Configuring MySQL Shell Options”.

System logging for SQL statements only takes place when MySQL Shell is started in interactive mode, so either a normal start or a start with the --interactive option. It does not take place if the --execute or --file options are used at startup to run mysqlsh in batch mode to process a command or file.

Log message format

The log message for an SQL statement is formatted as a series of key-value pairs separated by a space character. The key-value pairs are as follows:

SYSTEM_USER =: The login name of the operating system user, or -- if this user name is unknown.
MYSQL_USER =: The name of the MySQL user, or -- if this user name is unknown.
CONNECTION_ID =: The identifier for the MySQL Shell connection.
DB_SERVER =: The server’s host name, or -- if the host name is unknown.
DB =: The default database, or -- if no database has been selected.
QUERY =: The text of the logged SQL statement.

The log message is truncated to 1024 bytes if it exceeds that length.

Here is a sample of output generated on Linux by using --syslog. This output is formatted for readability; each logged message actually takes a single line.

Mar  1 17:35:33 myhost mysqlsh[33060]: 
SYSTEM_USER=hanna_j MYSQL_USER=hanna 
CONNECTION_ID=14 DB_SERVER=localhost DB='--' 
QUERY='create table test.test (c int, my_row_id BIGINT AUTO_INCREMENT INVISIBLE PRIMARY KEY);'