MySQL Log all queries to file and table

We have run into a situation that we generate queries to MySQL programmatically and some of the queries are not returning the expected result, we have the application logs from there we can get the generated queries, but the query and the parameter value are logged in a different line, some of the queries have multiple parameters its very hard to debug

So I enabled the log queries in MySQL to get all the queries executed. In this article, we will see how to Log all queries. There are two ways you can log, one is to Table and another is to file. Based on your requirement you can enable the log accordingly

Log all queries to File

Run the below query in your MySQL Console

SET global general_log = 1;
SET global log_output = '/var/log/queries.log';

Run the below command to get the latest queries

tail -f /var/log/queries.log

Log all queries to MySQL Table

Run the below query to create tables

use mysql;

 CREATE TABLE `slow_log` (
   `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `query_time` time NOT NULL,
   `lock_time` time NOT NULL,
   `rows_sent` int(11) NOT NULL,
   `rows_examined` int(11) NOT NULL,
   `db` varchar(512) NOT NULL,
   `last_insert_id` int(11) NOT NULL,
   `insert_id` int(11) NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `sql_text` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

  CREATE TABLE `general_log` (
   `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `command_type` varchar(64) NOT NULL,
   `argument` mediumtext NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

Run the below query in your MySQL Console

SET global general_log = 1;
SET global log_output = 'table';

To view the logs

select * from mysql.general_log

To Disable the Logs

Run the below query in your MySQL console

SET global general_log = 0;

Also published on Medium.