Mysql – How to trace a MySQL session like in Oracle


I have an application running in MySQL 5.1.58 community edition.

My application is working fine, but suddenly any query stuck and then after all queries is getting logged in slow query log. I am curious to know whether which query is the real culprit.

In Oracle, we can easily trace the session to find all queries executed in a user session. Is there feature in MySQL through which I can find the list of all queries during a single session.

Best Answer

MySQL has "General Query Log". This logs everything that is going on MySQL server: users connecting, disconnecting, queries etc.

This query log is a file on your filesystem or (from 5.1.6 versions) table

Control the general query log at server startup as follows:

  • Before 5.1.6, the general query log destination is always a file. To enable the log, start mysqld with the --log[=file_name] or -l [file_name] option.

  • As of MySQL 5.1.6, the destination can be a file or a table, or both. Start mysqld with the --log[=file_name] or -l [file_name] option to enable the general query log, and optionally use --log-output to specify the log destination

  • As of MySQL 5.1.12, as an alternative to --log or -l, use --general_log[={0|1}] to specify the initial general query log state. In this case, the default general query log file name is used. With no argument or an argument of 1, --general_log enables the log. With an argument of 0, this option disables the log.

  • As of MySQL 5.1.29, use --general_log[={0|1}] to enable or disable the general query log, and optionally --general_log_file=file_name to specify a log file name. The --log and -l options are deprecated.

After starting query log, investigate the file (or table) for further information.

MySQL Query Log Documentation: