Где хранятся логи mysql

# Log files

The Slow Query Log consists of log events for queries taking up to long_query_time seconds to finish. For instance, up to 10 seconds to complete. To see the time threshold currently set, issue the following:

It can be set as a GLOBAL variable, in my.cnf or my.ini file. Or it can be set by the connection, though this is unusual. The value can be set between 0 to 10 (seconds). What value to use?

• 10 is so high as to be almost useless;
• 2 is a compromise;
• 0.5 and other fractions are possible;
• 0 captures everything; this could fill up disk dangerously fast, but can be very useful.

The capturing of slow queries is either turned on or off. And the file logged to is also specified. The below captures these concepts:

For more information, please see the MySQL Manual Page The Slow Query Log

Note: The above information on turning on/off the slowlog was changed in 5.6(?); older version had another mechanism.

The "best" way to see what is slowing down your system:

# A List

• General log — all queries — see VARIABLE general_log
• Slow log — queries slower than long_query_time — slow_query_log_file
• Binlog — for replication and backup — log_bin_basename
• Relay log — also for replication
• general errors — mysqld.err
• start/stop — mysql.log (not very interesting) — log_error
• InnoDB redo log — iblog*

See the variables basedir and datadir for default location for many logs

Some logs are turned on/off by other VARIABLES. Some are either written to a file or to a table.

(Note to reviewers: This needs more details and more explanation.)

Documenters: please include the default location and name for each log type, for both Windows and *nix. (Or at least as much as you can.)

# General Query Log

The General Query Log contains a listing of general information from client connects, disconnects, and queries. It is invaluable for debugging, yet it poses as a hindrance to performance (citation?).

An example view of a General Query Log is seen below:

To determine if the General Log is currently being captured:

To determine the filename of the capture file:

If the fullpath to the file is not shown, the file exists in the datadir .

When changes are made to the general_log_file GLOBAL variable, the new log is saved in the datadir . However, the fullpath may no longer be reflected by examining the variable.

In the case of no entry for general_log_file in the configuration file, it will default to @@hostname .log in the datadir .

Best practices are to turn OFF capture. Save the log file to a backup directory with a filename reflecting the begin/end datetime of the capture. Deleting the prior file if a filesystem move did not occur of that file. Establish a new filename for the log file and turn capture ON (all show below). Best practices also include a careful determination if you even want to capture at the moment. Typically, capture is ON for debugging purposes only.

A typical filesystem filename for a backed-up log might be:

where the date and time are part to the filename as a range.

For Windows note the following sequence with setting changes.

Linux is similar. These would represent dynamic changes. Any restart of the server would pick up configuration file settings.

As for the configuration file, consider the following relevant variable settings:

In addition, the variable log_output can be configured for TABLE output, not just FILE . For that, please see Destinations

Please see the MySQL Manual Page The General Query Log

# Error Log

The Error Log is populated with start and stop information, and critical events encountered by the server.

The following is an example of its contents:

The variable log_error holds the path to the log file for error logging.

In the absence of a configuration file entry for log_error , the system will default its values to @@hostname .err in the datadir . Note that log_error is not a dynamic variable. As such, changes are done through a cnf or ini file changes and a server restart (or by seeing "Flushing and Renaming the Error Log File" in the Manual Page link at the bottom here).

Logging cannot be disabled for errors. They are important for system health while troubleshooting problems. Also, entries are infrequent compared to the General Query Log.

The GLOBAL variable log_warnings sets the level for verbosity which varies by server version. The following snippet illustrates:

log_warnings as seen above is a dynamic variable.

Configuration file changes in cnf and ini files might look like the following.

MySQL 5.7.2 expanded the warning level verbosity to 3 and added the GLOBAL log_error_verbosity . Again, it was introduced

(opens new window) in 5.7.2. It can be set dynamically and checked as a variable or set via cnf or ini configuration file settings.

As of MySQL 5.7.2:

Please see the MySQL Manual Page entitled The Error Log

(opens new window) especially for Flushing and Renaming the Error Log file, and its Error Log Verbosity section with versions related to log_warnings and error_log_verbosity .

How To Start Logging With MySQL

This tutorial shows you how to configure and view different MySQL logs. MySQL is an open-source relational database based on SQL (Structured Query Language). MySQL offers various built-in logs. In general, a database is the cornerstone of almost every backend, and because of that administrators want to log this service.

MySQL has multiple logs with different purpose. We will focus on following four logs:

• Error log: Records problems encountered starting, running, or stopping mysqld . This log is stored by default in the /var/log directory. It could be useful if you want to analyze the server itself.
• General query logs: Records every connection established with each client. This log records everything that client sent to the server. This log is useful to determine client problems.
• Binary logs: Record each event that manipulates data in the database. These log records operations such as a table creating, modification of schema, inserting new values, or querying tables. These logs are used to backup and recover the database.
• Slow query log: Record of each query, which execution took too much time. This log could be useful for the optimisation of slow SQL queries.

In this tutorial, you will do the following actions:

• You will install the MySQL server and view default error log.
• You will connect to MySQL server, view metadata about general query logs and view these logs.
• You will understand the concept of the MySQL binary logs and where to find them.
• You will enable and configure a slow query log, simulate some slow query and check this incident in the new log.

�� Want to centralize and monitor your MySQL logs?

Head over to Logtail and start ingesting your logs in 5 minutes.

Prerequisites

• Ubuntu 20.04 distribution including the non-root user with sudo access.
• Basic knowledge of SQL languages (understanding of simple select query statement).

Step 1 — Viewing Error Log

The MySQL server is maintained by the command-line program mysqld . This program manages access to the MySQL data directory that contains databases and tables. The problems encountered during mysqld starting, running, or stopping are stored as a custom log in the directory /var/log/mysql . This log doesn't include any information about SQL queries. It is useful for the analysis of the MySQL server.

First of all, let's install the MySQL server. Ubuntu 20.04 allows to install the MySQL from default packages with the apt install (installation requires sudo privilege):

The first command will update Ubuntu repositories, and the second will download and install required packages for the MySQL server.

Now, the server is installed. The new service already creates a default error log. You can list the directory /var/log and find a new subdirectory mysql with ls :

You'll see the program's output appear on the screen:

The output shows also directory mysql . This directory contains by default single log error.log . Let's view the content of file error.log with cat :

You'll see the program's output appear on the screen:

The output shows that the file stores plain text records about the mysqld server initialisation, and running.

Step 2 — Viewing General Query Logs

The server writes records about each client event during connection to the general query log. Basically, it keeps the information about all SQL statements that happens. This log is useful when the administrators want to know what clients exactly execute.

Connecting to Server and Checking General Query Log Status

First of all, let's check the status of the general query log because this logging feature can be turned off.

You can connect to MySQL server as a root client:

You will be redirected to MySQL command-line.

Now, you can view system variables related to the general query log by executing command show variables :

The clause specifies a pattern that should match the variable. In our case, the pattern '%general%' specifies to show variables that contain the string general . You'll see the program's output appear on the screen:

The output shows two variables:

• general_log : the variable holds value ON (general log enable), or OFF (general log disabled).
• general_log_file : the variable defines where is the log stored in the file system.

As you can see, the general query log is by default enabled. We can disconnect from the server by executing the exit command:

You will be redirected back to the terminal.

Viewing General Query Log

Now, you can view the content of this log with a cat (the sudo is required because this file is maintained by the system):

You'll see the program's output appear on the screen:

The output shows all statement executed on the server. You can see the time stamp and the specific command that was executed. There is also the executed command show variables like '%general%' .

Step 3 — Listing Binary Logs

The binary log contains events that manipulated the database. If you want to recover the database, you need a backup and a binary log relevant to this backup. There are multiple binary logs because they are versioned.

By default, the binary logs are enabled. You can check where are they stored. Let's connect to the MySQL server as a root client:

You will be redirected to MySQL prompt.

Now, you can check the binary logs status by executing show binary logs :

The command will list the binary log files on the server:

The output shows all binary logs. Now, we can find out where are this logs stored.

We can show logs location by executing command show variables :

We already use this show clause in the previous step. This time, the clause shows variables that contain the string log_bin . You'll see the program's output appear on the screen:

The output shows that the binary logs are stored in directory /var/lib/mysql , and they are labelled as binlog.index (for example binlog.000001 ).

We can disconnect from the server by executing the exit command:

You will be redirected back to the terminal.

Now, let's list the directory /var/lib/mysql but only as a root because it is owned and maintained by the system:

You'll see the program's output appear on the screen:

The output shows that the directory /var/lib/mysql contains the binary log files.

Step 4 — Configuring Slow Query Log

MySQL allows to log queries, which took too much time. This mechanism is called a slow query log.

Enabling Slow Query Logging

By default, the slow query log is disabled. You can enable it by editing MySQL configuration file /etc/mysql/mysql.conf.d/mysqld.cnf ( sudo required):

The file contains following lines that holds configuration variables (by default commented out):

These three lines holds following three configuration variables:

• slow_query_log : the slow query logging is disable (value 0 ) or enabled (value 1 ).
• slow_query_log_file : the slow query log is stored in the file /var/log/mysql/mysql-slow.log . You can specify your own file.
• long_query_time : by default, the slow query logs record each SQL query that takes more than 10 seconds. You can change this minimal time interval to another value. The value can be specified as a floating-point number where the value 1.0 refers to 1 second.

You can enable slow query log by uncommenting this three lines. Also, you can set your own long_query_time value:

In our example, we change the default long_query_time value to 5 seconds. Now, you can save the file.

If you want immediately apply the new configuration rules then you must restart the MySQL server with systemctl ( sudo required):

Now, the MySQL server enables slow query log.

Checking Slow Query Log Status

You can check that the log is enabled if you login into the MySQL server as a root client:

You will be redirected to MySQL prompt.

Let's check the slow query log status by executing command show variables :

Once again, we use the show clause. This time, the clause shows variables that contain the string slow_query_log . You'll see the program's output appear on the screen:

The output shows that the slow query log is enabled (the variable slow_query_log holds the value ON ). The log is stored in the file /var/log/mysql/mysql-slow.log . You can see that it is the same file as the file specified in the configuration script.

Let's view actual slow query time interval by executing the command show variables :

You'll see the program's output appear on the screen:

The output shows that the variable long_query_time holds the value 5 seconds (as we define in the configuration script).

Viewing Slow Query Log

At last, we can check that the MySQL records slow queries to the new log. You can execute the following select query that takes 6 seconds:

The select will wait 6 seconds and then return 0:

The output shows that this query takes 6 seconds. As a result, it should be recorded in a slow query log.

We can disconnect from the server by executing the exit command:

You will be redirected back to the terminal.

At last, we can print content of the slow query log /var/log/mysql/mysql-slow.log (the sudo is required because the file is maintained by system):

You'll see the program's output appear on the screen:

You can see that the output shows record about execution query select sleep(6) .

Conclusion

In this tutorial, you configured and viewed different MySQL logs. You installed the MySQL server and viewed the error log. You connected to the server, viewed the general query logs and their configuration. You listed binary logs. At last, you enabled, configured and viewed a slow query log.

Журналы (logs) в MySQL

В MySQL на данный момент существуют 4 вида журнала (лога) и при достаточно серьёзной работе с базами на MySQL необходимо за ними следить. Например, бинарный лог у нас за сутки набирает около гигабайта, а размер жёсткого диска на сервере ограничен и за ними надо следить. Однако следить следует не только за бинарным логом, так как логи (журналы) в MySQL могут принести немалую пользу.

Итак, какие логи ведёт MySQL? Это:
1. бинарный лог (binary log)
2. лог ошибок (error log)
3. лог медленный запросов (slow query log)
4. лог запросов (general query log)
5. лог репликаций (relay log)

Каждый из них по-своему полезен.

Бинарный лог

В первую очередь полезен с точки зрения репликаций. Можно его бэкапить, можно использовать для восстановления данных на более точное время при использовании бэкапов. Лог содержит все команды изменений базы данных, выборки (select, show) не сохраняет, для таблиц, поддерживающих транзакции (BDB, InnoDB) запись в лог выполняется только после выполнения команды COMMIT . Для лога можно указывать список баз данных, которые надо логировать и список баз данных, которые не надо логировать. В более ранних версиях вместо бинарного лога использовался лог обновлений. Использование бинарного лога снижает производительность базы данных, однако его польза настолько велика, что крайне не рекомендуется его отключать. Рекомендуется защищать бинарный лог паролем, так как он может данные также о паролях пользователей. При достижении максимально разрешённого размера (1 гиг по умолчанию) создаётся следующий файл. Каждый новый файл имеет порядковый номер после имени.

Содержание бинарного лога можно посмотреть с помощью утилиты mysqlbinlog.

Основные настройки в my.cnf

Местоположение лога:
log_bin = /var/log/mysql/mysql-bin.log

Максимальный размер, минимум 4096 байт, по умолчанию 1073741824 байт (1 гигабайт):
max_binlog_size= 500M

Сколько дней хранится:
expire_logs_days = 3

Наиболее часто использующиеся команды

Повторение действий после операции восстановления:
shell> mysqlbinlog log_file | mysql -h server_name

Удаление логов до определённого файла:
PURGE BINARY LOGS TO ‘mysql-bin.000’;

Удаление логов до определённой даты:
PURGE BINARY LOGS BEFORE ‘YYYY-MM-DD hh:mm:ss’;

Лог ошибок

Особенно полезен в случаях сбоев. Лог содержит информацию об остановках, запусках сервера, а также сообщения о критических ошибках. Может содержать сообщения с предупреждениями (warnings).

Основные настройки в my.cnf

Местоположение лога:
log_error = /var/log/mysql/mysql.err

Флаг, указывающий стоит ли записывать в лог в том числе предупреждения (записываются, если значение больше нуля):
log_warnings = 1

Наиболее часто использующиеся команды

Переход к новому файл лога:
shell> mysqladmin flush-logs

Копирование старой части лога (необходимо, так как в случае повторного выполнения fluch он будет удалён):
shell> mv host_name.err-old backup-directory

Лог медленных запросов

Если есть подозрение, что приложение работает медленно из-за неэффективных запросов к базе, то в первую очередь следует проверить лог медленных запросов. В случае оптимизации запросов этот лог поможет выяснить, что необходимо оптимизировать в первую очередь.

Основные настройки в my.cnf

Местоположение лога:
log_slow_queries = /var/log/mysql/mysql_slow.log

Со скольки секунд выполнения запрос считается медленным, минимальное значений — 1 секунда, по умолчанию 10 секунд:
long_query_time = 10

Если надо логировать запросы, которые не используют индексы, надо добавить строку:
log-queries-not-using-indexes

Если надо вести лог медленных команд, таких как OPTIMIZE TABLE , ANALYZE TABLE и ALTER TABLE :
log-slow-admin-statements

Лог запросов

Лог содержит информацию о подключениях и отключениях клиентов, а также все SQL запросы, которые были получены. Фактически, это временный лог. Обычно лог удаляется автоматически сразу после выполнения всех команд (т.е. как только он стал ненужным). Лог ведётся в соответствии с очередность поступления запросов. Этот лог содержит все запросы к базе данных (независимо от приложений и пользователей). Так что если есть желание (или необходимость) проанализировать, какие необходимы индексы, какие запросы могли бы оптимизированы, то этот лог как раз может помочь в таких целях. Лог полезен не только для случаев, когда необходимо знать, какие запросы выполняются с базой данных, но и в случаях, когда ясно, что возникла ошибка с базой данных, но неизвестно, какой запрос был отправлен к базе данных (например, в случае генерации динамического SQL-а). Рекомендуется защищать лог запросов паролем, так как он может данные также о паролях пользователей.

Основные настройки в my.cnf

Местоположение лога:
log = /var/log/mysql/mysql.log

Наиболее часто использующиеся команды

В отличии от других логов, перезагрузка сервера и команда fluch не инициирует создание нового лога. Но это можно сделать вручную:
shell> mv host_name.log host_name-old.log
shell> mysqladmin flush-logs
shell> mv host_name-old.log backup-directory

Лог репликаций

Здесь логируются изменения, выполненные по инициации сервера репликаций. Как и бинарный лог, состоит из файлов, каждый из которых пронумерован.

MySQL Log File Location

Logs are valuable. Logs generated by a major backend resource that provides clients with access to crucial data are more than just valuable; knowing where they are and being able to manage and understand the information that they contain can mean the difference between smooth, secure operation and degraded performance or even catastrophic failure for your application.

MySQL Server produces a handful of basic logs. We’ll look at which ones are important (and why), where they are, and what you can do to get the most out of them.

MySQL Logs

The three most important logs in terms of day-to-day IT operations are the error log, the slow query log, and (to a lesser degree) the general query log. Their default format is text, and they are useful for detecting and diagnosing functional problems and security issues, for improving performance, and for tracing the history of server operations and client access to the server.

The binary, relay, and DDL logs are all binary in format, and they are designed for use primarily by MySQL itself, specifically for tasks such as server replication and data recovery.

The Question of How and Where with MySQL Log Files

First, let’s take a quick look at how and under what circumstances the various MySQL distributions set default log file locations.

There are three basic types of MySQL distribution: Windows, platform-specific UNIX/Linux, and generic UNIX/Linux. In general, the platform-specific distributions have default settings for placing and enabling logs, while the generic UNIX/Linux distributions assume that logs will be managed via manual settings.

Windows

Official MySQL Windows distributions use an MSI installer with user-selectable options at various stages of the installation. The Logging Options page displays the log enabling and location defaults and also allows you to make adjustments as required.

The error, slow query, and binary logs are enabled by default, but the general query log is not enabled. The default location for each of the logs is the MySQL Data directory (C:\ProgramData\MySQL\MySQL Server [version number]\Data\), and the default log names are based on the computer’s device name.

You can manually enable/disable the general query, slow query, and binary logs via the installer GUI, but not the error log. You can also manually set the names and paths for each of the logs.

After installation, the log settings are managed via the user-editable C:\ProgramData\MySQL\MySQL Server [version number]\my.ini file. These settings include log names and paths as well as enable/disable switches.

For more information on collecting Microsoft MySQL logs, see our dedicated page to collecting and understanding MySQL Logs on windows.

Platform-Specific UNIX/Linux

The official distributions for individual UNIX/Linux platforms are typically script-based, with little or no interactive configuration during installation. Some installation packages (including Yum and APT) create the error log in /var/log/ or var/log/mysql/ with a name like error.log or mysqld.log. The data directory will typically be /var/lib/mysql/ or something similar, and it will serve as the default destination for any logs that are enabled without an alternate path.

The log settings are managed via a user-editable configuration file such as /etc/mysql/mysql.conf.d/mysqld.cnf. These settings include log names and paths as well as enable/disable switches. Startup and shutdown are typically managed by mysqld_safe (or with some distributions, systemd), which should find and apply log configuration options.

Generic UNIX/Linux

Generic installation is largely manual. During the installation process, you can enable and configure logs via the command line, by running scripts, or by editing the appropriate configuration file. The MySQL online reference manual (https://dev.mysql.com/doc/refman/5.7/en/server-logs.html) covers these options in detail.

The MySQL Error Log

The error log includes error messages, warnings, and notes generated during server operations as well as during the startup and shutdown phases; it also records startup and shutdown times.

The basic error log format is:

Error logging is always enabled, and the available options allow you to set the destination, verbosity level, and time zone.

Possible error log destinations are a file or the console. On Windows, if no destination option is specified, the error log is written to host_name.err (where host_name is the host system name) in the data directory. On UNIX/Linux systems, the default destination when no option is specified is the console.

For both UNIX/Linux and Windows-based MySQL installations, the —log-error option by itself (with no file name or path) sends the error log to host_name.err in the Data directory. If you specify the name and path (i.e., —log-error=»G:/TMP/mysql_logs/mysql_error.err» or —log-error=/var/log/mysql/error.log), the error log will be written to the specified file. In order to send the error log to the console on Windows, you must use the —console option; it overrides the —log-error option if both are present.

For more information regarding error logs and collecting them, see our page on Collecting logs in MySQL.

The General and Slow Query Logs

The general query and slow query logs both record user queries using a similar format: Time, ID, Command, and Argument (where Argument includes both the SQL commands and the data making up the query).

The general query log, however, records all client SQL statements along with connect and disconnect times, while the slow query log only records queries that take longer than the time specified by the long-query-time system variable. The slow query log also includes a set of fields containing the execution time, lock time, rows sent, and rows examined for each logged query.

A typical general query log entry might look like this:

The slow query log entry for the same event might look like this:

The slow query log allows you to identify queries that require an unusually long time to execute; it may also be of use in uncovering system or database issues that result in slow execution. The general query log allows you to track all client SQL statements, which can be useful both for tracing errors and for identifying potential security problems.

The query logs tend to accumulate data rapidly, which may impact system performance in addition to taking up disk space. The general query log in particular can grow very quickly; both it and the slow query log are disabled by default in most installation packages. (The Windows MySQL installer, however, is an exception, as described above.)

The general query and slow query logs are enabled separately, using the —general-log and —slow-query-log options. The default destination is the data directory, with (host name].log and [host name]-slow.log as the file names. To set the log names and paths, use the —general-log-file and —slow-log-file options.

The format of both logs is controlled by a single option, —log-output, which takes the following values: FILE, TABLE, or NONE. FILE is the default value. TABLE stores both logs as tables, which can be read and managed via SQL queries. Both FILE and TABLE can be used together, separated by commas. NONE disables the output of both logs; if it is present, it overrides the other values.

Binary, Relay, and DDL Logs

The binary and relay logs are necessary for server replication, and the DDL log is used by the system to manage metadata during mysqld operation. These logs are generally of limited diagnostic use, although you may need to access binary and relay logs using the mysqlbinlog utility as part of the data recovery process.

Simplifying MySQL Log File location with Sumo Logic

The truth is that even with a relatively small number of server logs, MySQL can generate a lot of log data. At the same time, elements of that data may be important in terms of error-tracking, performance, and security. What’s the best way to organize and sort through MySQL server log data in order to find the things that you need to know?

The Sumo Logic App for MYSQL automatically picks out key metrics and data items from the error and slow query logs and presents them in easy-to-read dashboards. Sumo Logic makes it easy to identify performance issues, unusual behavior and activity patterns, and critical errors. You can check system health, replication status, and server performance at a glance, drilling down to detailed real-time information on slow queries (including origin by individual host, IP, and user), failed logins (by user, host, and location), and replication and server problems.

Don’t spend hours digging through log files — let Sumo Logic do the work for you so that you have time to get down to the business of serving your customers!