There are two main options for logging in MySQL, the general query log and the slow query log.
The General Query Log
The general query log is a facility used to log all client connections and queries to the server. It can be used to troubleshoot problems with incorrect queries or to monitor your database performance.
To enable the general query log, edit your my.cnf or my.ini configuration file and add the following parameter:
log-output=TABLE
general_log=1
general_log_file=<path to log file>
Where <path to log file> is the path to the log file you want to use.
You can also set the verbosity and log format of the general query log:
log_output=TABLE
general_log=1
log_queries=1
log_query_time=1
general_log_file=<path to log file>
log_queries_not_using_indexes=1
log_slow_filter=min_statement_time=<time in milliseconds>
Where is <time in milliseconds> the minimum execution time, in milliseconds, that an SQL statement needs to execute in order for it to be logged.
The Slow Query Log
The slow query log is a facility used to log queries that take a long time to execute. This can be used to help you identify queries that are taking longer than usual to complete, or queries that may cause potential bottle necks or performance issues.
To enable the slow query log, edit your my.cnf or my.ini configuration file and add the following parameter:
log_output=TABLE
slow_query_log=1
slow_query_log_file=<path to log file>
Where <path to log file> is the path to the log file you want to use.
You can also set the verbosity and log format of the slow query log:
log_output=TABLE
slow_query_log=1
log_queries=1
log_query_time=1
slow_query_log_file=<path to log file>
log_long_format=1
log_slow_filter=min_statement_time=<time in milliseconds>
Where <time in milliseconds> is the minimum execution time, in milliseconds, that an SQL statement needs to execute in order for it to be logged.
Once the parameters have been correctly configured, restart your MySQL server for the changes to take effect. You can then begin to review your logs in order to identify potential areas of improvement or query optimization.
