Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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

share|improve this answer

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

share|improve this answer
    
How Audit Plugin will help me here except security concern? –  ursitesion Apr 4 at 14:07
    
Audit logs records everything executed on a server a much more detailed report than general log. –  Mahesh Patil Apr 7 at 10:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.