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.
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.logErrors are written to error.log
We highly recommend keeping this log enabled for quick identification of errors.
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.
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.
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.
Conclusion
MySQL is a popular database management system. It often happens that incorrect configuration of the MySQL server or non-optimized SQL queries leads 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.
