A log file is a recording of everything that goes in and out of a particular server. It is a concept much like the black box of an airplane that records everything going on with the plane in the event of a problem. If you are a web developer you need to know various log files in order to debug or improve the performace of your application.
MySQL Server has several logs that can help you find out what activity is taking place.
1. Error log : Problems encountered starting, running, or stopping Mysql Server 2. General query log : This contains general record of what mysqld is doing. (connect,disconnect, queries) 3. Slow query log : Queries that took more than long_query_time seconds to execute.
Enabling MySQL logs from configuration file
Logging parameters are located under [mysqld] section.
Edit MySQL configuration file: nano /etc/mysql/my.cnf
This is the default location for my.cnf. But the location may vary. In that case try
Sample my.cnf file
# The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /Applications/MAMP/tmp/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 # log files can be added here. socket = /Applications/MAMP/tmp/mysql/mysql.sock skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M
Error log contains information about when a mysqld was started,stopped and any critical errors that occur while server is running.
To enable error logs add the following line in [mysqld]
Once the changes are done the server has to be restarted.
service mysql restart
To make sure if log file is working perform any database operation and open the log file.
General Query log:
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.
mysqld writes statements to the query log in the order that it receives them, which might differ from the order in which they are executed.
To enable error logs you have to set general_log variable to 1 add specify the location of log file in [mysqld]
general_log_file = /var/log/mysql/mysql.log general_log = 1
Slow Query Log
One of the main requirements for a fast web server is to has efficient and effective SQL statements or queries that are optimized.
Any non-optimal SQL (Structured Query Language) commands or statements that take too long or lengthy time to execute will use up a lot of system resources, causing MySQL database to run slower, and then more and more queries backlogs queuing up, and when connection limit is reached, visitors are been denied or refused connection. In worst case scenario, your web server will go down as well, or continuously underperform.
MySQL has built-in functionality to capture slow query log or identify queries that are not optimal and take a long time to finish.
To enable slow query log, simply add the following lines to MySQL configuration file (my.cnf) and then restart the MySQL server:
log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 slow_query_log = 1 log-queries-not-using-indexes
After enabling slow query log, MySQL will create, capture and log to the log file with all SQL statements that took more than long_query_time seconds to execute, which is by default set to 10 seconds.(In our case 2 seconds)
long_query_time is the threshold for query execution time beyond which it is logged. Any queries taking longer than the threshold are logged, regardless of whether they use an index or not.
log_queries_not_using_indexes tells mysql to additionally log all queries that do not use an index to limit the number of rows returned.
Enabling MySQL logs on the fly:
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';
This doesn’t require a server restart.
View Error logs:
#tail -f path-of-log-file tail -f mysql-general.log
From MySQL 5.1, they have introduced one more parameter called “log_output” for server log tables where you can set the output fomat. For the log_output parameter value can be a TABLE (log to tables), FILE (log to files), or NONE (do not log to tables or files).
You can also set multiple values with comma means if you want to log statements in both TABLE and FILE than you can set variable like
mysql> SET GLOBAL log_output = 'TABLE,FILE';
To verify your log_output
mysql> show global variables like '%output%';
To view queries that are captured by slow log run the below command if you had set log_output to TABLE else you can view the log file.
mysql> select * from mysql.slow_log;
For getting queries, that takes more than 5 seconds to execute
mysqld> SELECT query_time, lock_time, rows_sent, rows_examined, sql_text FROM mysql.slow_log WHERE query_time > '00:00:05'
For getting queries from slow log between time interval
SELECT query_time, lock_time, rows_sent, rows_examined, sql_text FROM mysql.slow_log WHERE start_time BETWEEN 'START TIMESTAMP' AND 'END TIMESTAMP'