How to View MySQL Server Log Files

January 29th, 2021

Sooner or later, every developer and DBA faces the necessity to find out the reasons for slow database performance. This article will take a detailed look at how to set up MySQL Server logging, what types of MySQL Server logs exist, and how to work with them.

Contents

MySQL Log Types
Getting Started With MySQL logs
Working With the Error Log
Working With the Binary Log
Working With the General Log
Working With the Slow query log

MySQL Log Types

The log is the first place to look to determine your system status and identify errors. MySQL has several different log files that can help you with that.

Error log

The error log contains a record of errors that occur when the server is running (including when starting and stopping).

Binary Log

The binary log records statements that change the database data. It is used for backups and replication.

General Query Log

The general query log contains the established client connections and statements received from clients.

Slow Query Log

The slow query log records all the queries that take longer to run than the time interval you’ve assigned.

Getting Started with MySQL Logs

First, you need to get to know if logging is enabled and identify the MySQL log location. Using dbForge Studio for MySQL, you can find this information in seconds.

To view and modify MySQL system and status variables, go to the Database menu and select the Server Variables command from it. Alternatively, you can see the main configuration file my.cnf.

Define MySQL log location - MySQL log directory

Working With the Error Log

The error log contains all errors that occurred while the server was running, including critical errors and errors encountered when stopping and/or starting the server, as well as some warnings. In case of a system failure, it’s the first place to look for the reason. It is also useful to check the MySQL error log when debugging MySQL query performance. By default, all errors are sent to the console (stderr), you can also select to write errors to syslog (default in Debian), or a separate log file. You can find out its location by checking the value of the log_error system variable:

log_error=/var/log/mysql/error.log

Errors are written to error.log

We highly recommend keeping this log enabled for quick identification of errors.

Check MySQL error log

Working with the Binary Log

All statements that change the database data are written to the binary log. It is useful for database replication and recovery.

Options used with binary logging

The binary log is resource-intensive and slows down system performance. We recommend enabling it only if you are going to scale the system and implement fault tolerance.

Binary MySQL log file

Working with the General Log

The general query log contains all received SQL queries and information about client connections. It can be useful for index analysis and optimization, as well as for detecting erroneous queries.

Options used with general query logging

The peculiarity of this type of logging is that it can be enabled or disabled while the MySQL server is running. You do not need to restart the MySQL Server after running the following statements.

SET GLOBAL general_log = 'ON'; 

SET GLOBAL general_log = 'OFF'; 

The general query log records all SQL queries to all databases on the server. Therefore, it can be very voluminous. We recommend you use it carefully and make sure that you do not run out of free space on the server.

MySQL general log file location

Working With the Slow Query Log

The Slow query log contains all SQL queries to all databases on the server that have been running for longer than long_query_time.

To enable slow query logging, you need to change the values of the system variables in MySQL and restart the MySQL instance for the changes to take effect.

To start logging, you need to set the value

slow_query_log ON

After that, all slow queries that need to be analyzed and optimized if necessary will be recorded in the file /var/log/mysql/test-VB-slow.log.

Log all slow MySQL queries

Conclusion

MySQL is a popular database management system. It often happens that incorrect configuration of the MySQL server or non-optimized SQL queries lead to unstable operation of the whole site or application. In this article, we showed how to get information about the state of the MySQL server, databases, tables, and queries from the logs created by the server.

MySQL logs are highly useful for optimizing MySQL performance as well as finding out the reasons for the failure of the database server. We recommend you always enable the error log. It is also a good idea to use the query log to check the connection of your application to the database and to analyze queries.

With dbForge Studio for MySQL, working with MySQL logs is significantly easier and smoother. Try this universal multifunctional IDE for MySQL administration, development, and management, and you will for sure fall in love with it.

Leave a Comment