How to Log All mysql queries into log file?

If you are a web developer, you need to refer to various log files, in order to debug your application or improve its performance. Logs is the best place to start troubleshooting. Concerning the famous MySQL database server (or MariaDB server), you need to refer to the following log files:

The Error Log. It contains information about errors that occur while the server is running (also server start and stop)
The General Query Log. This is a general record of what mysqld is doing (connect, disconnect, queries)
The Slow Query Log. ?t consists of “slow” SQL statements (as indicated by its name).
Logging parameters are located under [mysqld] section.
Note: enabling general_log on a production server has overhead you should avoid it. You can check problematic queries from slow log.
How to enable General Query Log in MySQL under Linux

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld. More about General query log

So if you have some bug or problem with database, one of the way to trace the problem in turning on this log, and check what is happening.

General query log is disabled by default, and to enable it, in Linux, you should do this:
=============================1st Method======================================
Edit mysql configuration file:

vi /etc/my.cnf

Enable logging file, under [mysqld] section:

log=/var/log/mysql.general.log

Save the file. Then create log file and set mysql ownership:

touch /var/log/mysql.general.log
chown mysql.mysql /var/log/mysql.general.log

Now, restart the mysql service:

/etc/init.d/mysql restart

You can now use your applications/scripts, run queries, etc. and everything will be logged. To see real time logging, run:

tail -f /var/log/mysql.general.log

Just be careful with this, log file can become big pretty fast.

When you want to disable General query log, just delete the line you have added in my.cnf, and restart mysql server.
=============================2nd Method======================================

Since MySQL 5.1 you can enable and disable logs at runtime.

To enable logs at runtime, login to mysql client (mysql -u root -p ) and give:

SET GLOBAL general_log = ‘ON’;
SET GLOBAL slow_query_log = ‘ON’;

To disable logs at runtime, login to mysql client (mysql -u root -p ) and give:

SET GLOBAL general_log = ‘OFF’;
SET GLOBAL slow_query_log = ‘OFF’;
TIP: Use show variables like ‘%log%’; to examine your server variables related to log files
mysql> show variables like ‘%log%’;

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: