Importance of Logging in MySQL

Enable logging in MySql is necessary to manage your server and helps in analyzing performance and investigating problems.

Most of the time, when we find any issues in our web application or when user complains about the performance, then only we print our query and run that query in MySql server to analyse what is wrong.

Prevention is better than cure.

Important Log Types In MySql

• The error log
• The query log
• The slow query log

The Error Log

If you enable error log in MySql server, it keeps record of each error that occurs in the MySql server. You can check MySql configuration file (Example: my.cnf in /etc/mysql in Ubuntu) whether error log is enabled or not.

# Error log - should be very few entries.
log_error = /var/log/mysql/error.log

Once you change the configuration, restart MySql server to reflect the changes made for error log and then find your MySql errors in var/log/mysql/error.log.

The Query Log

The query log is a very important log also as it tracks every query executed in your MySql Server for your application. It also displays details about which clients are connected to the server and what these clients are doing.

We always emphasise on slow queries but redundant/multiple queries are worse than slow queries.

If you have slow query running for your application, then it needs to be refactored for your application for better performance. But if you are not aware of how many queries are running for the feature for your application or there might be chance that duplicate queries are executing through different functions for the same feature, but you have never got a chance to check.

Then query log helps to find out all queries are running for your feature so you can identify redundant query or multiple queries which can be optimised.

Enable query log (Example: my.cnf in /etc/mysql in Ubuntu):

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

After enable, the query log makes sure to restart your MySql server to reflect the changes. Once you are done, then you can just read /var/log/mysql/mysql.log or use the command tail -f /var/log/mysql/mysql.log and browse your feature in application to see how many queries are getting executed for that feature.

The Slow Query Log

The slow query log lists all queries that take more than the amount of time mentioned in long_query_time variable in configuration file. You can modify long_query_time according to your requirement. Please check the configuration for this setting:

# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes

Any query that takes longer than long_query_time will be listed in mysql-slow.log file.

Conclusion

Use the MySql logs to make your application efficient and robust.

Stay tuned for more type of logs in MySql.

Thanks for your valuable time in reading the blog. If you liked it, don’t forget to like and share the blog. Comments are always welcome!

CodeProject