In software applications, log files keep a record of what actions were performed in the system and perhaps who performed them. Should something unexpected occur, whether it be a security breach, system crash, or just sluggish performance, the log file(s) can be an administrator's best friend. As it happens, MySQL has several different log files that can help you find out what's going on inside the MySQL server. Today's blog is a primer on MySQL logging - a topic that we'll be referencing later on when we talk about monitoring in Navicat Monitor for MySQL/MariaDB.
Log Types
MySQL can support several log types, but bear in mind that, by default, no logs are enabled except for the error log on Windows. Here's a list of types:
| Log file | Description |
|---|---|
| The error log | Problems encountered when starting, running, or stopping mysqld. |
| The isam log | Logs all changes to the ISAM tables. Used only for debugging the ISAM code. |
| The general query log | Established connections and executed queries. |
| The update log | Deprecated: stores all statements that change data. |
| The binary log | Stores all statements that change something. Used also for replication. |
| The slow log | Stores all queries that took more than long_query_time to execute or didn't use indexes. |
Out of these, the most important are the error, general, binary, and slow logs, so we'll focus on the first two today, and the last two next week.
The error log
Your first resource when troubleshooting server issues is the error log. MySQL server uses the error log to record information relevant to any issue which prevents the server from starting. You'll find the error log in the data directory specified in your my.ini file. The default data directory location in Windows is "C:\Program Files\MySQL\MySQL Server 5.7\data", or "C:\ProgramData\Mysql". Note that the "C:\ProgramData" directory is hidden by default, so you may need to change your folder options to see the directory and its contents.
Fig.1 - the MySQL Error log in Windows
For other platforms, it may be helpful to refer to the log_error config variable. If you use Navicat to manage your database(s), you can look up system variables using the Server Monitor tool. It's accessible via the Tools main menu command.
In the Server Monitor, click on the middle "Variables" tab and scroll down to log_error in the list:
Fig.2 - the log_error server variable in the Navicat Server Monitor tool
The General Query Log
As the name implies, the general query log provides a general record of what MySQL is doing. The server writes information to this log when clients connect or disconnect, as well as each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client application and want to know exactly what the client sent to the database.
By default, the general query log is disabled. To enable it, set the general_log variable to 1 (or ON in Navicat). Not assigning any value to general_log also enables it. Setting it back to 0 (or OFF in Navicat) disables the log. To specify a log file name, assign it to the general_log_file variable. To specify that the log outputs to a file, use the log_output system variable to assign the file name. MySQL can also send output to the slow_log tables in the mysql system database. In fact, file output, table output, or both can be selected. We'll talk about that in greater detail in the next blog.
Fig.3 - the general_log and general_log_file server variables in the Navicat Server Monitor tool
