How to trace a MySQL session like in Oracle

Question

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.

Answer 1

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: http://dev.mysql.com/doc/refman/5.1/en/query-log.html

Answer 2

Enable general log http://dev.mysql.com/doc/refman/5.1/en/query-log.html

Also You can refer this blog post: http://serge.frezefond.com/2013/04/how-can-we-audit-a-mysql-server/

There are other ways as well for a detailed logging information for each and every query auditing:

Using MySQL audit plugins

There are audit plugins released by MySQL and some of the forks, you can try them if you are concerned about security on your MySQL server

1) MySQL Enterprise Audit

Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity executed on specific MySQL servers.

2) McAfee Audit Plugin - Open source

designed with an emphasis on security and audit requirements. The plugin may be used as a standalone audit solution or configured to feed data to external monitoring tools.

3) MariaDB Audit plugin

Auditing regulations are used by many enterprises to ensure they comply with laws and industry standards. Such regulations often require processes for tracking user access to data in databases.

Refer my existing answer here MySQL query log

• ursitesion: How Audit Plugin will help me here except security concern?
• Mahesh Patil: Audit logs records everything executed on a server a much more detailed report than general log.