MySQL Server has several logs that can help you find out what activity is taking place. By default, no logs are enabled (except the error log on Windows). The following log-specific sections provide information about the server options that enable logging.
By default, the server writes files for all enabled logs in the data directory. You can force the server to close and reopen the log files (or in some cases switch to a new log file) by flushing the logs.
We need to enable the following logs
The Error Log – Problems encountered starting, running, or stopping mysqld
The General Query Log – Established client connections and statements received from clients
The Slow Query Log – Queries that took more than long_query_time seconds to execute
Procedure to follow
Logging parameters are located under [mysqld] section in /etc/mysql/my.cnf file
Edit /etc/mysql/my.cnf file
#vi /etc/mysql/my.cnf
Error Log
Error Log goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf, which contains the following:
[mysqld_safe]
syslog
This is the recommended method. If, for some reason, you do not want Error log to go to syslog, comment the above lines in /etc/mysql/conf.d/mysqld_safe_syslog.cnf or completely remove this file. Then, add in /etc/mysql/my.cnf the following lines:
[mysqld_safe]
log_error=/var/log/mysql/mysql_error.log[mysqld]
log_error=/var/log/mysql/mysql_error.log
General Query Log
To enable General Query Log, uncomment (or add) the relevant lines
general_log_file = /var/log/mysql/mysql.log
general_log = 1
Slow Query Log
To enable Slow Query Log, uncomment (or add) the relevant lines
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
Restart MySQL server after changes using the following command
#service mysql restart