How to log your mysql queries in Ubuntu 16.04

At times, you need to log your mysql queries.

The first you need to locate your configuration file of mysql. You can use either locate command or the find command for it. The name of the configuration file for mysql is "my.cnf", and in ubuntu its usually present in your /etc directory.

$ find /etc -name "my.cnf"

or

$ locate my.cnf

In my system, it's located in /etc/mysql/my.cnf

Once the file is located, open it using your favorite text editor.
There are chances that the file is not writable by your user, so you need to use sudo to switch your user to root to edit the file.

Open the file and add the lines,

[mysqld]
general_log = on
general_log_file = /var/log/mysql/mysql_query.log

It will add the group as mysqld, and start the logging. The logs will be save in file:

/var/log/mysql/mysql_query.log

Save it and close it.

Then you need to restart your mysql server, to enable the option. Once the mysql service is started, the logging will start.

$ sudo service mysqld restart

Sample log file:

$ cat /var/log/mysql/mysql_query.log
/usr/sbin/mysqld, Version: 5.7.15-0ubuntu0.16.04.1-log ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                                                  Id Command    Argument
2016-10-13T14:02:57.300324Z    3 Connect myuser@localhost on  using Socket
2016-10-13T14:02:57.300563Z    3 Query select @@version_comment limit 1
2016-10-13T14:03:02.895987Z    3 Query SELECT DATABASE()
2016-10-13T14:03:02.896302Z    3 Init DB test
2016-10-13T14:03:02.898415Z    3 Query show databases
2016-10-13T14:03:02.902113Z    3 Query show tables