How to enable mysql general log

Document created by Hao Zhu Employee on Feb 18, 2016
Version 1Show Document
  • View in full screen mode

Author: Hao Zhu

Original Publication Date: March 12, 2015

 

Goal:

Many MapR components are using MySQL as the backend database by default, for example, MapR Metrics, Hive Metastore, etc.

To troubleshooting some query or connectivity issues between these components and MySQL, mysqld.log may not be enough.

This article shows how to enable MySQL general log to record every query executed in MySQL.

Env:

MySQL 5.1.29+

Solution:

Here are 2 options.

 

1. Using mysql table as the general log

a. Log on MySQL using superuser and execute below commands.

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

b. Query general log table.

For example:

select * from mysql.general_log 
where event_time between '2015-03-11 22:52:40' and '2015-03-13 22:52:40'
and argument not like '%mysql.general_log%' order by event_time;

2. Using a file as the general log:

a. Create an empty log file which can be written by mysql user.

touch /var/log/mysql_general_log 
chown mysql:mysql /var/log/mysql_general_log

b. Put below line into /etc/my.cnf to specify the log file path.

general_log_file=/var/log/mysql_general_log

c. Restart MySQL.

service mysqld restart

d. Log on MySQL using superuser and execute below command to enable general log.

SET global general_log = 1; 

e. Query general log file

tail -100f /var/log/mysql_general_log

 

Note: MySQL general log needs much more disk space, make sure the disk space is enough.

To disable general log, please execute below command in MySQL as superuser:

SET global general_log = 0;

Attachments

    Outcomes