Logging MySQL interactive queries

Question

I have several people doing some database queries directly into some systems.

I would like to log to a secure syslog server all the queries done interactively/by hand, or failing that, all the queries done by using the mysql binary client.

I am using Debian Jessie, and Oracle MySQL 5.6.31

How could I achieve that?

Let's wait and see but I think you can't filter by client software and your only options are to log all queries or only slow queries. (Of course, when the server receives a connection it has no way to know whether you're typing in a keyboard or SQL was generated previously.) — Álvaro González
– Álvaro González, Commented Jun 15, 2016 at 12:02
Thanks @ÁlvaroGonzález ; I actually forgot that in the past I saw a possible solution, however at the time MySQL 5.7 was still in beta. I made it an answer. — Rui F Ribeiro
– Rui F Ribeiro, Commented Jun 15, 2016 at 17:21

Rui F Ribeiro · Accepted Answer · 2016-06-15 12:52:02Z

2

Starting in MySQL 5.7, the mysql command accepts the --syslog option. So I upgraded a test system to MySQL 5.7, and it works. I will make an alias from mysql to mysql --syslog

From MySQL 5.7 Reference Manual - mysql Logging

syslog Logging Characteristics

If the --syslog option is given, mysql writes interactive statements to the system logging facility. Message logging has the following characteristics.

Logging occurs at the “information” level. This corresponds to the LOG_INFO priority for syslog on Unix/Linux syslog capability and to EVENTLOG_INFORMATION_TYPE for the Windows Event Log. Consult your system documentation for configuration of your logging capability.

Message size is limited to 1024 bytes.

Messages consist of the identifier MysqlClient followed by these values:

SYSTEM_USER

The system user name (login name) or -- if the user is unknown.

MYSQL_USER

The MySQL user name (specified with the --user option) or -- if the user is unknown.

CONNECTION_ID:

The client connection identifier. This is the same as the CONNECTION_ID() function value within the session.

DB_SERVER

The server host or -- if the host is unknown.

DB

The default database or -- if no database has been selected.

QUERY

The text of the logged statement.

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 7 12:39:25 myhost MysqlClient[20824]: SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23, DB_SERVER:'127.0.0.1', DB:'--', QUERY:'USE test;' Mar 7 12:39:28 myhost MysqlClient[20824]: SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
DB_SERVER:'127.0.0.1', DB:'test', QUERY:'SHOW TABLES;'

edited Jun 15, 2016 at 12:52

answered Jun 15, 2016 at 12:46

Rui F Ribeiro

4271 gold badge12 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alasdair Over a year ago

Instead of creating an alias you could add syslog to a [mysql] section of either your /etc/mysql/my.cnf or a file in /etc/mysql/conf.d/

Rui F Ribeiro Over a year ago

@Alasdair Please add an answer and I will accept it.

Alasdair · Accepted Answer · 2017-10-11 13:58:19Z

1

As you correctly spotted from MySQL 5.7 there is now the option to log to syslog.

Instead of creating an alias to the command as you have above, you could instead add syslog to a [mysql] section of either your /etc/mysql/my.cnf or a file in /etc/mysql/conf.d/

As in

[mysql]
syslog

This also allows for handy integration into logwatch.

answered Oct 11, 2017 at 13:58

Alasdair

6061 gold badge5 silver badges16 bronze badges

Collectives™ on Stack Overflow

Logging MySQL interactive queries

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related