Как посмотреть ошибки mariadb

The error log contains a record of critical errors that occurred during the server’s operation, table corruption, start and stop information.

SQL errors can also be logged in a separate file using the SQL_ERROR_LOG plugin.

Configuring the Error Log Output Destination

MariaDB always writes its error log, but the destination is configurable.

Writing the Error Log to a File

To configure the error log to be written to a file, you can set the log_error system variable. You can configure a specific file name. However, if a specific file name is not configured, then the log will be written to the ${hostname}.err file in the datadir directory by default.

The log_error system variable can be set in a server option group in an option file prior to starting up the server. For example, to write the error log to the default ${hostname}.err file, you could configure the following:

[mariadb]
...
log_error

If you configure a specific file name as the log_error system variable, and if it is not an absolute path, then it will be relative to the datadir directory. For example, if you configured the following, then the error log would be written to mariadb.err in the datadir directory:

[mariadb]
...
log_error=mariadb.err

If it is a relative path, then the log_error is relative to the datadir directory.

However, the log_error system variable can also be an absolute path. For example:

[mariadb]
...
log_error=/var/log/mysql/mariadb.err

Another way to configure the error log file name is to set the log-basename option, which configures MariaDB to use a common prefix for all log files (e.g. general query log, slow query log, error log, binary logs, etc.). The error log file name will be built by adding a .err extension to this prefix. For example, if you configured the following, then the error log would still be written to mariadb.err in the datadir directory:

[mariadb]
...
log-basename=mariadb
log_error

The log-basename cannot be an absolute path. The log file name is relative to the datadir directory.

Writing the Error Log to Stderr on Unix

On Unix, if the log_error system variable is not set, then errors are written to stderr, which usually means that the log messages are output to the terminal that started mysqld.

If the log_error system variable was set in an option file or on the command-line, then it can still be unset by specifying --skip-log-error.

Writing the Error Log to Syslog on Unix

On Unix, the error log can also be redirected to the syslog. How this is done depends on how you start MariaDB.

Syslog with mysqld_safe

If you start MariaDB with mysqld_safe, then the error log can be redirected to the syslog. See mysqld_safe: Configuring MariaDB to Write the Error Log to Syslog for more information.

Syslog with Systemd

If you start MariaDB with systemd, then the error log can also be redirected to the syslog. See Systemd: Configuring MariaDB to Write the Error Log to Syslog for more information.

systemd also has its own logging system called the journal, and some errors may get logged there instead. See Systemd:Systemd Journal for more information.

Writing the Error Log to Console on Windows

On Windows, if the console option is specified, and if the log_error system variable is not used, then errors are written to the console. If both options are specified, then the last option takes precedence.

Writing the Error Log to the Windows Event Viewer

On Windows, error log messages are also written to the Windows Event Viewer. You can find MariaDB’s error log messages by browsing Windows Logs, and then selecting Application or Application Log, depending on the Windows version.

In MariaDB 10.3 and before, you can find MariaDB’s error log messages by searching for the Source MySQL.

In MariaDB 10.4 and later, you can find MariaDB’s error log messages by searching for the Source MariaDB.

Configuring the Error Log Verbosity

The default value of the log_warnings system variable is 2.

The log_warnings system variable can be used to configure the verbosity of the error log. It can be changed dynamically with SET GLOBAL. For example:

SET GLOBAL log_warnings=3;

It can also be set either on the command-line or in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
log_warnings=3

Some of the warnings included in each verbosity level are described below.

The log_warnings system variable only has an effect on some log messages. Some log messages are always written to the error log, regardless of the error log verbosity. For example, most warnings from the InnoDB storage engine are not affected by log_warnings. For a complete list of log messages affected by log_warnings, see the description of the log_warnings system variable.

Verbosity Level 0

If log_warnings is 0, then many optional warnings will not be logged. However, this does not prevent all warnings from being logged, because there are certain core warnings that will always be written to the error log. For example:

• If InnoDB strict mode is disabled, and if DDL is performed on a table that triggers a «Row size too large» error, then InnoDB will log a warning:

[Warning] InnoDB: Cannot add field col25 in table db1.tab because after 
  adding it, the row size is 8477 which is greater than maximum allowed 
  size (8126) for a record on index leaf page.

However, if InnoDB strict mode is enabled, then the same message will be logged as an error.

Verbosity Level 1

Default until MariaDB 10.2.3. If log_warnings is 1, then many types of warnings are logged. Some useful warnings are:

• Replication-related messages:

[Note] Error reading relay log event: slave SQL thread was killed
[Note] Slave SQL thread exiting, replication stopped in log 
  'dbserver-2-bin.000033'   at position 181420; 
  GTID position '0-263316466-368886'
[Note] Slave I/O thread exiting, read up to log 
  'dbserver-2-bin.000034', position 642; 
  GTID position 0-263316466-368887

• Messages related to DNS lookup failures:

[Warning] IP address '192.168.1.193' 
  could not be resolved: Name or service not known

• Messages related to the event scheduler:

[Note] Event Scheduler: Loaded 0 events

• Messages related to unsafe statements for statement-based replication:

[Warning] Unsafe statement written to the binary log using statement format since 
  BINLOG_FORMAT = STATEMENT. The statement is unsafe because 
  it uses a LIMIT clause. This 
  is unsafe because the set of rows included cannot be predicted.

Frequent warnings about unsafe statements for statement-based replication can cause the error log to grow very large. MariaDB will automatically detect frequent duplicate warnings about unsafe statements for statement-based replication. After 10 identical warnings are detected, MariaDB will prevent that same warning from being written to the error log again for the next 5 minutes.

Verbosity Level 2

Default from MariaDB 10.2.4. If log_warnings is 2, then a couple other different kinds of warnings are printed. For example:

• Messages related to access denied errors:

[Warning] Access denied for user 'root'@'localhost' (using password: YES)

• Messages related to connections that are aborted due to errors or timeouts:

[Warning] Aborted connection 35 to db: 'unconnected' user: 
  'user1@host1' host: '192.168.1.40' (Got an error writing communication packets)
[Warning] Aborted connection 36 to db: 'unconnected' user: 
  'user1@host2' host: '192.168.1.230' (Got an error writing communication packets)
[Warning] Aborted connection 38 to db: 'db1' user: 
  'user2' host: '192.168.1.60' (Unknown error) 
[Warning] Aborted connection 51 to db: 'db1' user: 
  'user2' host: '192.168.1.50' (Got an error reading communication packets)
[Warning] Aborted connection 52 to db: 'db1' user: 
  'user3' host: '192.168.1.53' (Got timeout reading communication packets)

• Messages related to table handler errors:

[Warning] Can't find record in 'tab1'.
[Warning] Can't write; duplicate key in table 'tab1'.
[Warning] Lock wait timeout exceeded; try restarting transaction.
[Warning] The number of locks exceeds the lock table size.
[Warning] Update locks cannot be acquired during a READ UNCOMMITTED transaction.

• Messages related to the files used to persist replication state:
  • Either the default master.info file or the file that is configured by the master_info_file option.
  • Either the default relay-log.info file or the file that is configured by the relay_log_info_file system variable.

[Note] Reading Master_info: '/mariadb/data/master.info'  
  Relay_info:'/mariadb/data/relay-log.info'
[Note] Initialized Master_info from '/mariadb/data/master.info'
[Note] Reading of all Master_info entries succeded
[Note] Deleted Master_info file '/mariadb/data/master.info'.
[Note] Deleted Master_info file '/mariadb/data/relay-log.info'.

• Messages about a master’s binary log dump thread:

[Note] Start binlog_dump to slave_server(263316466), pos(, 4)

Verbosity Level 3

If log_warnings is 3, then a couple other different kinds of warnings are printed. For example:

• Messages related to old-style language options:

[Warning] An old style --language value with language specific 
  part detected: /usr/local/mysql/data/
[Warning] Use --lc-messages-dir without language specific part instead.

• Messages related to progress of InnoDB online DDL:

[Note] InnoDB: Online DDL : Start
[Note] InnoDB: Online DDL : Start reading clustered index of the table and 
  create temporary files
[Note] InnoDB: Online DDL : End of reading clustered index of the table and 
  create temporary files
[Note] InnoDB: Online DDL : Start merge-sorting index PRIMARY (1 / 3), 
  estimated cost : 18.0263
[Note] InnoDB: Online DDL : merge-sorting has estimated 33 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 33 runs
[Note] InnoDB: Online DDL : merge-sorting current run 2 estimated 17 runs
[Note] InnoDB: Online DDL : merge-sorting current run 3 estimated 9 runs
[Note] InnoDB: Online DDL : merge-sorting current run 4 estimated 5 runs
[Note] InnoDB: Online DDL : merge-sorting current run 5 estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 6 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index PRIMARY (1 / 3)
[Note] InnoDB: Online DDL : Start building index PRIMARY (1 / 3), 
  estimated cost : 27.0395
[Note] InnoDB: Online DDL : End of building index PRIMARY (1 / 3)
[Note] InnoDB: Online DDL : Completed
[Note] InnoDB: Online DDL : Start merge-sorting index ux1 (2 / 3), 
  estimated cost : 5.7895
[Note] InnoDB: Online DDL : merge-sorting has estimated 2 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index ux1 (2 / 3)
[Note] InnoDB: Online DDL : Start building index ux1 (2 / 3), 
  estimated cost : 8.6842
[Note] InnoDB: Online DDL : End of building index ux1 (2 / 3)
[Note] InnoDB: Online DDL : Completed
[Note] InnoDB: Online DDL : Start merge-sorting index ix1 (3 / 3), 
  estimated cost : 6.1842
[Note] InnoDB: Online DDL : merge-sorting has estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 2 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index ix1 (3 / 3)
[Note] InnoDB: Online DDL : Start building index ix1 (3 / 3), 
  estimated cost : 9.2763
[Note] InnoDB: Online DDL : End of building index ix1 (3 / 3)
[Note] InnoDB: Online DDL : Completed

Verbosity Level 4

If log_warnings is 4, then a couple other different kinds of warnings are printed. For example:

• Messages related to killed connections:

[Warning] Aborted connection 53 to db: 'db1' user: 
  'user2' host: '192.168.1.50' (KILLED)

• Messages related to all closed connections:

[Warning] Aborted connection 56 to db: 'db1' user: 
  'user2' host: '192.168.1.50' (CLOSE_CONNECTION)

• Messages related to released connections, such as when a transaction is committed and completion_type is set to RELEASE:

[Warning] Aborted connection 58 to db: 'db1' user: 
  'user2' host: '192.168.1.50' (RELEASE)

Verbosity Level 9

If log_warnings is 9, then some very verbose warnings are printed. For example:

• Messages about initializing plugins:

[Note] Initializing built-in plugins
[Note] Initializing plugins specified on the command line
[Note] Initializing installed plugins

MySQL’s log_error_verbosity

MariaDB does not support the log_error_verbosity system variable added in MySQL 5.7.

Format

The format consists of the date (yyyy-mm-dd) and time, the thread ID, followed by the type of error (Note, Warning or Error) and the error message, for example:

2016-06-15 16:53:33 139651251140544 [Note] InnoDB: 
  The InnoDB memory heap is disabled

Until MariaDB 10.1.4, the format only consisted of the date (yymmdd) and time, followed by the type of error (Note, Warning or Error) and the error message, for example:

160615 16:53:08 [Note] InnoDB: The InnoDB memory heap is disabled

Rotating the Error Log on Unix and Linux

Unix and Linux distributions offer the logrotate utility, which makes it very easy to rotate log files. See Rotating Logs on Unix and Linux for more information on how to use this utility to rotate the error log.

Error Messages File

Many error messages are ready from an error messages file that contains localized error messages. If the server can’t find this file when it starts up, then you might see errors like the following:

[ERROR] Can't find messagefile '/usr/share/errmsg.sys'

If this error is occurring because the file is in a custom location, then you can configure this location by setting the lc_messages_dir system variable either on the command-line or in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
lc_messages_dir=/usr/share/mysql/

If you want to use a different locale for error messages, then you can also set the lc_messages system variable. For example:

[mariadb]
...
lc_messages_dir=/usr/share/mysql/
lc_messages=en_US

See Setting the Language for Error Messages for more information.

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

Ошибки SQL также можно регистрировать в отдельном файле с помощью подключаемого модуля SQL_ERROR_LOG .

Настройка назначения выхода журнала ошибок

MariaDB всегда записывает свой журнал ошибок,но место назначения настраивается.

Запись журнала ошибок в файл.

Чтобы настроить запись журнала ошибок в файл, вы можете установить системную переменную log_error . Вы можете настроить конкретное имя файла. Однако, если конкретное имя файла не настроено, журнал по умолчанию будет записываться в файл ${hostname}.err в каталоге datadir .

Системная переменная log_error может быть установлена ​​в группе опций сервера в файле опций перед запуском сервера. Например, чтобы записать журнал ошибок в файл ${hostname}.err по умолчанию , вы можете настроить следующее:

[mariadb]
...
log_error

Если вы настроите определенное имя файла в качестве системной переменной log_error и если это не абсолютный путь, то он будет относительным к каталогу datadir . Например, если вы настроили следующее, журнал ошибок будет записываться в mariadb.err в каталоге datadir :

[mariadb]
...
log_error=mariadb.err

Если это относительный путь, то log_error относится к каталогу datadir .

Однако системная переменная log_error также может быть абсолютным путем. Например:

[mariadb]
...
log_error=/var/log/mysql/mariadb.err

Другой способ настроить имя файла журнала ошибок — установить параметр log-basename , который настраивает MariaDB на использование общего префикса для всех файлов журнала (например , общий журнал запросов , журнал медленных запросов , журнал ошибок, двоичные журналы и т. д .). Имя файла журнала ошибок будет создано путем добавления расширения .err к этому префиксу. Например, если вы настроили следующее, журнал ошибок все равно будет записываться в mariadb.err в каталоге datadir :

[mariadb]
...
log-basename=mariadb
log_error

Лог-базовое имя не может быть абсолютным путем. Имя файла журнала относится к каталогу datadir .

Запись журнала ошибок в Stderr на Unix

В Unix, если системная переменная log_error не установлена, то ошибки записываются в stderr , что обычно означает, что сообщения журнала выводятся на терминал, запустивший mysqld .

Если системная переменная log_error была установлена ​​в файле опций или в командной строке, ее все равно можно отменить, указав --skip-log-error .

Запись журнала ошибок в Syslog на Unix

В Unix журнал ошибок также можно перенаправить в системный журнал . Как это будет сделано, зависит от того, как вы запускаете MariaDB.

Syslog с mysqld_safe

Если вы запускаете MariaDB с mysqld_safe , то журнал ошибок можно перенаправить в syslog. См. mysqld_safe: Настройка MariaDB для записи журнала ошибок в системный журнал для получения дополнительной информации.

Syslog с Systemd

Если вы запускаете MariaDB с помощью systemd , то журнал ошибок также может быть перенаправлен в syslog. См. Systemd: Настройка MariaDB для записи журнала ошибок в системный журнал для получения дополнительной информации.

systemd также имеет свою собственную систему журналирования, называемую journal , и вместо этого некоторые ошибки могут регистрироваться там. См. Systemd:Журнал Systemd для получения дополнительной информации.

Запись журнала ошибок в консоль на Windows

В Windows, если указана консольная опция и не используется системная переменная log_error , то ошибки записываются в консоль. Если указаны оба параметра, последний вариант имеет приоритет.

Запись журнала ошибок в программу просмотра событий Windows Event Viewer

В Windows сообщения журнала об ошибках также записываются в средство просмотра событий Windows. Вы можете найти сообщения журнала ошибок MariaDB, просмотрев журналы Windows и выбрав приложение или журнал приложений , в зависимости от версии Windows.

В MariaDB 10.3 и ранее вы можете найти сообщения журнала ошибок MariaDB, выполнив поиск по Source MySQL .

В MariaDB 10.4 и более поздних версиях вы можете найти сообщения журнала ошибок MariaDB, выполнив поиск по источнику MariaDB .

Настройка вербозиции журнала ошибок

Системная переменная log_warnings может использоваться для настройки детализации журнала ошибок. Его можно изменить динамически с помощью SET GLOBAL . Например:

SET GLOBAL log_warnings=3;

Его также можно установить либо в командной строке, либо в группе опций сервера в файле опций перед запуском сервера. Например:

[mariadb]
...
log_warnings=3

Некоторые из предупреждений,включенных в каждый уровень сложности,описаны ниже.

Системная переменная log_warnings влияет только на некоторые сообщения журнала . Некоторые сообщения журнала всегда записываются в журнал ошибок, независимо от детализации журнала ошибок. Например, log_warnings не влияет на большинство предупреждений механизма хранения InnoDB . Полный список сообщений журнала, на которые влияет log_warnings , см. в описании системной переменной log_warnings .

Уровень гербицидности 0

Если log_warnings равен 0 , то многие необязательные предупреждения не будут регистрироваться. Однако это не препятствует регистрации всех предупреждений, поскольку некоторые основные предупреждения всегда будут записываться в журнал ошибок. Например:

• Если строгий режим InnoDB отключен, и если DDL выполняется для таблицы, которая вызывает ошибку «Размер строки слишком большой» , то InnoDB регистрирует предупреждение:

[Warning] InnoDB: Cannot add field col25 in table db1.tab because after 
  adding it, the row size is 8477 which is greater than maximum allowed 
  size (8126) for a record on index leaf page.

Однако, если строгий режим InnoDB включен, то это же сообщение будет зарегистрировано как ошибка.

Уровень гербицидности 1

Если log_warnings равно 1 , то регистрируются многие типы предупреждений. Некоторые полезные предупреждения:

• Replication-related messages:

[Note] Error reading relay log event: slave SQL thread was killed
[Note] Slave SQL thread exiting, replication stopped in log 
  'dbserver-2-bin.000033'   at position 181420; 
  GTID position '0-263316466-368886'
[Note] Slave I/O thread exiting, read up to log 
  'dbserver-2-bin.000034', position 642; 
  GTID position 0-263316466-368887

• Сообщения,связанные с ошибками DNS поиска:

[Warning] IP address '192.168.1.193' 
  could not be resolved: Name or service not known

• Сообщения, относящиеся к планировщику событий :

[Note] Event Scheduler: Loaded 0 events

• Сообщения, связанные с небезопасными операторами для репликации на основе операторов :

[Warning] Unsafe statement written to the binary log using statement format since 
  BINLOG_FORMAT = STATEMENT. The statement is unsafe because 
  it uses a LIMIT clause. This 
  is unsafe because the set of rows included cannot be predicted.

Частые предупреждения о небезопасных операторах для репликации на основе операторов могут привести к тому, что журнал ошибок станет очень большим. MariaDB автоматически обнаружит частые повторяющиеся предупреждения о небезопасных операторах для репликации на основе операторов . После обнаружения 10 одинаковых предупреждений MariaDB предотвратит повторную запись того же предупреждения в журнал ошибок в течение следующих 5 минут.

Уровень гербицидности 2

Если log_warnings равно 2 , то выводится несколько других видов предупреждений. Например:

• Сообщения,связанные с ошибками отказа в доступе:

[Warning] Access denied for user 'root'@'localhost' (using password: YES)

• Сообщения,связанные с соединениями,которые прерываются из-за ошибок или таймаутов:

[Warning] Aborted connection 35 to db: 'unconnected' user: 
  'user1@host1' host: '192.168.1.40' (Got an error writing communication packets)
[Warning] Aborted connection 36 to db: 'unconnected' user: 
  'user1@host2' host: '192.168.1.230' (Got an error writing communication packets)
[Warning] Aborted connection 38 to db: 'db1' user: 
  'user2' host: '192.168.1.60' (Unknown error) 
[Warning] Aborted connection 51 to db: 'db1' user: 
  'user2' host: '192.168.1.50' (Got an error reading communication packets)
[Warning] Aborted connection 52 to db: 'db1' user: 
  'user3' host: '192.168.1.53' (Got timeout reading communication packets)

• Сообщения,связанные с ошибками в обработчике таблицы:

[Warning] Can't find record in 'tab1'.
[Warning] Can't write; duplicate key in table 'tab1'.
[Warning] Lock wait timeout exceeded; try restarting transaction.
[Warning] The number of locks exceeds the lock table size.
[Warning] Update locks cannot be acquired during a READ UNCOMMITTED transaction.

• Сообщения, относящиеся к файлам, используемым для сохранения состояния репликации :
  • Либо файл master.info по умолчанию , либо файл, настроенный опцией master_info_file .
  • Либо файл relay-log.info по умолчанию , либо файл, настроенный системной переменной relay_log_info_file .

[Note] Reading Master_info: '/mariadb/data/master.info'  
  Relay_info:'/mariadb/data/relay-log.info'
[Note] Initialized Master_info from '/mariadb/data/master.info'
[Note] Reading of all Master_info entries succeded
[Note] Deleted Master_info file '/mariadb/data/master.info'.
[Note] Deleted Master_info file '/mariadb/data/relay-log.info'.

• Сообщения о потоке дампа двоичного журнала мастера :

[Note] Start binlog_dump to slave_server(263316466), pos(, 4)

Уровень гербицидности 3

Если log_warnings равно 3 , то печатается пара других различных видов предупреждений. Например:

• Сообщения,связанные с вариантами языка старого образца:

[Warning] An old style 
  part detected: /usr/local/mysql/data/
[Warning] Use 

• Сообщения, относящиеся к прогрессу InnoDB online DDL :

[Note] InnoDB: Online DDL : Start
[Note] InnoDB: Online DDL : Start reading clustered index of the table and 
  create temporary files
[Note] InnoDB: Online DDL : End of reading clustered index of the table and 
  create temporary files
[Note] InnoDB: Online DDL : Start merge-sorting index PRIMARY (1 / 3), 
  estimated cost : 18.0263
[Note] InnoDB: Online DDL : merge-sorting has estimated 33 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 33 runs
[Note] InnoDB: Online DDL : merge-sorting current run 2 estimated 17 runs
[Note] InnoDB: Online DDL : merge-sorting current run 3 estimated 9 runs
[Note] InnoDB: Online DDL : merge-sorting current run 4 estimated 5 runs
[Note] InnoDB: Online DDL : merge-sorting current run 5 estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 6 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index PRIMARY (1 / 3)
[Note] InnoDB: Online DDL : Start building index PRIMARY (1 / 3), 
  estimated cost : 27.0395
[Note] InnoDB: Online DDL : End of building index PRIMARY (1 / 3)
[Note] InnoDB: Online DDL : Completed
[Note] InnoDB: Online DDL : Start merge-sorting index ux1 (2 / 3), 
  estimated cost : 5.7895
[Note] InnoDB: Online DDL : merge-sorting has estimated 2 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index ux1 (2 / 3)
[Note] InnoDB: Online DDL : Start building index ux1 (2 / 3), 
  estimated cost : 8.6842
[Note] InnoDB: Online DDL : End of building index ux1 (2 / 3)
[Note] InnoDB: Online DDL : Completed
[Note] InnoDB: Online DDL : Start merge-sorting index ix1 (3 / 3), 
  estimated cost : 6.1842
[Note] InnoDB: Online DDL : merge-sorting has estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 2 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index ix1 (3 / 3)
[Note] InnoDB: Online DDL : Start building index ix1 (3 / 3), 
  estimated cost : 9.2763
[Note] InnoDB: Online DDL : End of building index ix1 (3 / 3)
[Note] InnoDB: Online DDL : Completed

The error log contains a record of critical errors that occurred during the server’s operation, table corruption, start and stop information.

SQL errors can also be logged in a separate file using the SQL_ERROR_LOG plugin.

Configuring the Error Log Output Destination

MariaDB always writes its error log, but the destination is configurable.

Writing the Error Log to a File

To configure the error log to be written to a file, you can set the log_error system variable. You can configure a specific file name. However, if a specific file name is not configured, then the log will be written to the ${hostname}.err file in the datadir directory by default.

The log_error system variable can be set in a server option group in an option file prior to starting up the server. For example, to write the error log to the default ${hostname}.err file, you could configure the following:

[mariadb]
...
log_error

If you configure a specific file name as the log_error system variable, and if it is not an absolute path, then it will be relative to the datadir directory. For example, if you configured the following, then the error log would be written to mariadb.err in the datadir directory:

[mariadb]
...
log_error=mariadb.err

If it is a relative path, then the log_error is relative to the datadir directory.

However, the log_error system variable can also be an absolute path. For example:

[mariadb]
...
log_error=/var/log/mysql/mariadb.err

Another way to configure the error log file name is to set the log-basename option, which configures MariaDB to use a common prefix for all log files (e.g. general query log, slow query log, error log, binary logs, etc.). The error log file name will be built by adding a .err extension to this prefix. For example, if you configured the following, then the error log would still be written to mariadb.err in the datadir directory:

[mariadb]
...
log-basename=mariadb
log_error

The log-basename cannot be an absolute path. The log file name is relative to the datadir directory.

Writing the Error Log to Stderr on Unix

On Unix, if the log_error system variable is not set, then errors are written to stderr, which usually means that the log messages are output to the terminal that started mysqld.

If the log_error system variable was set in an option file or on the command-line, then it can still be unset by specifying --skip-log-error.

Writing the Error Log to Syslog on Unix

On Unix, the error log can also be redirected to the syslog. How this is done depends on how you start MariaDB.

Syslog with mysqld_safe

If you start MariaDB with mysqld_safe, then the error log can be redirected to the syslog. See mysqld_safe: Configuring MariaDB to Write the Error Log to Syslog for more information.

Syslog with Systemd

If you start MariaDB with systemd, then the error log can also be redirected to the syslog. See Systemd: Configuring MariaDB to Write the Error Log to Syslog for more information.

systemd also has its own logging system called the journal, and some errors may get logged there instead. See Systemd:Systemd Journal for more information.

Writing the Error Log to Console on Windows

On Windows, if the console option is specified, and if the log_error system variable is not used, then errors are written to the console. If both options are specified, then the last option takes precedence.

Writing the Error Log to the Windows Event Viewer

On Windows, error log messages are also written to the Windows Event Viewer. You can find MariaDB’s error log messages by browsing Windows Logs, and then selecting Application or Application Log, depending on the Windows version.

In MariaDB 10.3 and before, you can find MariaDB’s error log messages by searching for the Source MySQL.

In MariaDB 10.4 and later, you can find MariaDB’s error log messages by searching for the Source MariaDB.

Configuring the Error Log Verbosity

The log_warnings system variable can be used to configure the verbosity of the error log. It can be changed dynamically with SET GLOBAL. For example:

SET GLOBAL log_warnings=3;

It can also be set either on the command-line or in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
log_warnings=3

Some of the warnings included in each verbosity level are described below.

The log_warnings system variable only has an effect on some log messages. Some log messages are always written to the error log, regardless of the error log verbosity. For example, most warnings from the InnoDB storage engine are not affected by log_warnings. For a complete list of log messages affected by log_warnings, see the description of the log_warnings system variable.

Verbosity Level 0

If log_warnings is 0, then many optional warnings will not be logged. However, this does not prevent all warnings from being logged, because there are certain core warnings that will always be written to the error log. For example:

• If InnoDB strict mode is disabled, and if DDL is performed on a table that triggers a «Row size too large» error, then InnoDB will log a warning:

[Warning] InnoDB: Cannot add field col25 in table db1.tab because after adding it, the row size is 8477 which is greater than maximum allowed size (8126) for a record on index leaf page.

However, if InnoDB strict mode is enabled, then the same message will be logged as an error.

Verbosity Level 1

If log_warnings is 1, then many types of warnings are logged. Some useful warnings are:

• Replication-related messages:

[Note] Error reading relay log event: slave SQL thread was killed
[Note] Slave SQL thread exiting, replication stopped in log 'dbserver-2-bin.000033' at position 181420; GTID position '0-263316466-368886'
[Note] Slave I/O thread exiting, read up to log 'dbserver-2-bin.000034', position 642; GTID position 0-263316466-368887

• Messages related to DNS lookup failures:

[Warning] IP address '192.168.1.193' could not be resolved: Name or service not known

• Messages related to the event scheduler:

[Note] Event Scheduler: Loaded 0 events

• Messages related to unsafe statements for statement-based replication:

[Warning] Unsafe statement written to the binary log using statement format since 
  BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This 
  is unsafe because the set of rows included cannot be predicted.

Verbosity Level 2

If log_warnings is 2, then a couple other different kinds of warnings are printed. For example:

• Messages related to access denied errors:

[Warning] Access denied for user 'root'@'localhost' (using password: YES)

• Messages related to connections that are aborted due to errors or timeouts:

[Warning] Aborted connection 35 to db: 'unconnected' user: '[email protected]' host: '192.168.1.40' (Got an error writing communication packets)
[Warning] Aborted connection 36 to db: 'unconnected' user: '[email protected]' host: '192.168.1.230' (Got an error writing communication packets)
[Warning] Aborted connection 38 to db: 'db1' user: 'user2' host: '192.168.1.60' (Unknown error) 
[Warning] Aborted connection 51 to db: 'db1' user: 'user2' host: '192.168.1.50' (Got an error reading communication packets)
[Warning] Aborted connection 52 to db: 'db1' user: 'user3' host: '192.168.1.53' (Got timeout reading communication packets)

• Messages related to table handler errors:

[Warning] Can't find record in 'tab1'.
[Warning] Can't write; duplicate key in table 'tab1'.
[Warning] Lock wait timeout exceeded; try restarting transaction.
[Warning] The number of locks exceeds the lock table size.
[Warning] Update locks cannot be acquired during a READ UNCOMMITTED transaction.

• Messages related to the files used to persist replication state:
  • Either the default master.info file or the file that is configured by the master_info_file option.
  • Either the default relay-log.info file or the file that is configured by the relay_log_info_file system variable.

[Note] Reading Master_info: '/mariadb/data/master.info'  Relay_info:'/mariadb/data/relay-log.info'
[Note] Initialized Master_info from '/mariadb/data/master.info'
[Note] Reading of all Master_info entries succeded
[Note] Deleted Master_info file '/mariadb/data/master.info'.
[Note] Deleted Master_info file '/mariadb/data/relay-log.info'.

• Messages about a master’s binary log dump thread:

[Note] Start binlog_dump to slave_server(263316466), pos(, 4)

Verbosity Level 3

If log_warnings is 3, then a couple other different kinds of warnings are printed. For example:

• Messages related to old-style language options:

[Warning] An old style --language value with language specific part detected: /usr/local/mysql/data/
[Warning] Use --lc-messages-dir without language specific part instead.

• Messages related to progress of InnoDB online DDL:

[Note] InnoDB: Online DDL : Start
[Note] InnoDB: Online DDL : Start reading clustered index of the table and create temporary files
[Note] InnoDB: Online DDL : End of reading clustered index of the table and create temporary files
[Note] InnoDB: Online DDL : Start merge-sorting index PRIMARY (1 / 3), estimated cost : 18.0263
[Note] InnoDB: Online DDL : merge-sorting has estimated 33 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 33 runs
[Note] InnoDB: Online DDL : merge-sorting current run 2 estimated 17 runs
[Note] InnoDB: Online DDL : merge-sorting current run 3 estimated 9 runs
[Note] InnoDB: Online DDL : merge-sorting current run 4 estimated 5 runs
[Note] InnoDB: Online DDL : merge-sorting current run 5 estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 6 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index PRIMARY (1 / 3)
[Note] InnoDB: Online DDL : Start building index PRIMARY (1 / 3), estimated cost : 27.0395
[Note] InnoDB: Online DDL : End of building index PRIMARY (1 / 3)
[Note] InnoDB: Online DDL : Completed
[Note] InnoDB: Online DDL : Start merge-sorting index ux1 (2 / 3), estimated cost : 5.7895
[Note] InnoDB: Online DDL : merge-sorting has estimated 2 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index ux1 (2 / 3)
[Note] InnoDB: Online DDL : Start building index ux1 (2 / 3), estimated cost : 8.6842
[Note] InnoDB: Online DDL : End of building index ux1 (2 / 3)
[Note] InnoDB: Online DDL : Completed
[Note] InnoDB: Online DDL : Start merge-sorting index ix1 (3 / 3), estimated cost : 6.1842
[Note] InnoDB: Online DDL : merge-sorting has estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 2 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index ix1 (3 / 3)
[Note] InnoDB: Online DDL : Start building index ix1 (3 / 3), estimated cost : 9.2763
[Note] InnoDB: Online DDL : End of building index ix1 (3 / 3)
[Note] InnoDB: Online DDL : Completed

Verbosity Level 4

If log_warnings is 4, then a couple other different kinds of warnings are printed. For example:

• Messages related to killed connections:

[Warning] Aborted connection 53 to db: 'db1' user: 'user2' host: '192.168.1.50' (KILLED)

• Messages related to all closed connections:

[Warning] Aborted connection 56 to db: 'db1' user: 'user2' host: '192.168.1.50' (CLOSE_CONNECTION)

• Messages related to released connections, such as when a transaction is committed and completion_type is set to RELEASE:

[Warning] Aborted connection 58 to db: 'db1' user: 'user2' host: '192.168.1.50' (RELEASE)

Verbosity Level 9

If log_warnings is 9, then some very verbose warnings are printed. For example:

• Messages about initializing plugins:

[Note] Initializing built-in plugins
[Note] Initializing plugins specified on the command line
[Note] Initializing installed plugins

MySQL’s log_error_verbosity

MariaDB does not support the log_error_verbosity system variable added in MySQL 5.7.

Format

Until MariaDB 10.1.4, the format consisted of the date (yymmdd) and time, followed by the type of error (Note, Warning or Error) and the error message, for example:

160615 16:53:08 [Note] InnoDB: The InnoDB memory heap is disabled

From MariaDB 10.1.5, the date format has been extended to yyyy-mm-dd, and the thread ID has been added, for example:

2016-06-15 16:53:33 139651251140544 [Note] InnoDB: The InnoDB memory heap is disabled

Rotating the Error Log on Unix and Linux

Unix and Linux distributions offer the logrotate utility, which makes it very easy to rotate log files. See Rotating Logs on Unix and Linux for more information on how to use this utility to rotate the error log.

Error Messages File

Many error messages are ready from an error messages file that contains localized error messages. If the server can’t find this file when it starts up, then you might see errors like the following:

[ERROR] Can't find messagefile '/usr/share/errmsg.sys'

If this error is occurring because the file is in a custom location, then you can configure this location by setting the lc_messages_dir system variable either on the command-line or in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
lc_messages_dir=/usr/share/mysql/

If you want to use a different locale for error messages, then you can also set the lc_messages system variable. For example:

[mariadb]
...
lc_messages_dir=/usr/share/mysql/
lc_messages=en_US

See Setting the Language for Error Messages for more information.

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

MariaDB has four main logs with different purpose:

• Error log: Problems encountered during starting, running, or stopping the
  server. The database always generates records for this log, but the
  destination is configurable. It is useful when you want to analyze the server
  itself.
• General query logs: Records every connection established with each client.
  This log records every query that the client sent to the server. This log is
  useful to determine client problems. However, the log can grow large quite
  quickly.
• Binary logs: Record each event that manipulates data in a database. It
  records operations such as 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 following actions:

• You will install the MariaDB server, configure and view default error log.
• You will connect to the MariaDB server, view metadata about general query
  logs, enable it and view these logs.
• You will enable the MariaDB binary logs and list them.
• You will enable and configure a slow query log, simulate some slow query
  and check this incident in the new log.

Prerequisites

You will need:

• Ubuntu 20.04 distribution including the non-root user with sudo access.
• The server shouldn’t include the installed MySQL server because there is a
  problem with coexistence with the MariaDB configuration. You can learn how to
  completely remove MySQL from the machine in the following
  article.
• Basic knowledge of SQL languages (understanding of simple select query
  statement).

Step 1 — Viewing and Configuring Error Log

The MariaDB server is maintained by the command-line program mariadb. This
program manages access to the MariaDB data directory that contains databases and
tables. The problems encountered during mariadb starting, running, or stopping
are recorded as an error log. This log doesn’t include any information about SQL
queries. It is useful for the analysis of the MariaDB server.

Installing MariaDB server

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

$ sudo apt update
$ sudo apt install mariadb-server

The first command will update Ubuntu repositories, and the second will download
and install required packages for the MariaDB server. Now, the server is
installed.

Connecting to Server and Viewing the Default Configuration

The error log is always enabled, but the destination of this log is
configurable. Let’s view the default configuration.

You can connect to MariaDB server as a root client:

You will be redirected to MariaDB command-line.

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

MariaDB> show variables like '%log_error%';

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

Output
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_error     |       |
+---------------+-------+
1 row in set (0.002 sec)

The output shows the global variable log_error. This variable holds the
absolute path to file where are stored error log records. As you can see, it is
by default set to null. As a result, the error log output goes to syslog.

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

You will be redirected back to the terminal.

Viewing Error Log With Journal

Now, you can view the syslog records related to the MariaDB service with
journalctl:

$ journalctl -u mariadb.service

The option -u with the name of the service MariaDB displays only records
related to this service:

Output
May 19 08:54:02 alice systemd[1]: Started MariaDB 10.5.10 database server.
May 19 08:54:02 alice /etc/mysql/debian-start[17467]: Upgrading MySQL tables if necessary.
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Looking for 'mysql' as: /usr/bin/mysql
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Phase 1/7: Checking and upgrading mysql database
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Processing databases
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.column_stats                                 OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.columns_priv                                 OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.db                                           OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.event                                        OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.func                                         OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.global_priv                                  OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.gtid_slave_pos                               OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.help_category                                OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.help_keyword                                 OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.help_relation                                OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.help_topic                                   OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.index_stats                                  OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.innodb_index_stats                           OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.innodb_table_stats                           OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.plugin                                       OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.proc                                         OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.procs_priv                                   OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.proxies_priv                                 OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.roles_mapping                                OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.servers                                      OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.table_stats                                  OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.tables_priv                                  OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.time_zone                                    OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.time_zone_leap_second                        OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.time_zone_name                               OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.time_zone_transition                         OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.time_zone_transition_type                    OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.transaction_registry                         OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Phase 2/7: Installing used storage engines... Skipped
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Phase 3/7: Fixing views
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Processing databases
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: information_schema
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql.user                                         OK
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: performance_schema
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Phase 4/7: Running 'mysql_fix_privilege_tables'
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Phase 5/7: Fixing table and database names
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Processing databases
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: information_schema
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: mysql
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: performance_schema
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Phase 6/7: Checking and upgrading tables
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Processing databases
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: information_schema
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: performance_schema
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: Phase 7/7: Running 'FLUSH PRIVILEGES'
May 19 08:54:05 alice /etc/mysql/debian-start[17470]: OK

The output shows that the syslog stores records about the mariadb server
initialisation, and running.

Configuring Custom Error Log File

If you don’t want to include MariaDB error log into syslog, you can set up a
custom log file. You can configure custom error log file by editing MariaDB
configuration file /etc/mysql/mariadb.conf.d/50-server.cnf (sudo required):

$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

The file contains the following lines that hold configuration variable
log_error (by default commented out):

# Enable this if you want to have error logging into a separate file
#log_error = /var/log/mysql/error.log

You can configure a custom error log file by uncommenting the line with the
variable log_error, and set up the absolute path to the log file:

# Enable this if you want to have error logging into a separate file
log_error = /var/log/mariadb/error.log

In our example, we change the default log_error value to the directory
/var/log/mariadb (the Linux default directory for logs). Now, you can save the
file.

Now, let’s create a subdirectory mariadb in the /var/log. You can create the
subdirectory with the command mkdir (sudo required):

$ sudo mkdir /var/log/mariadb

Next, you must set proper access right to this directory for the MariaDB daemon.
You can set up user and group owner of this subdirectory by command chown
(sudo required):

$ sudo chown mysql:mysql /var/log/mariadb

The command chown set directory user to mysql and group to mysql, which is
the proper access rights for the MariaDB daemon.

At last, if you want immediately apply the new configuration rules then you must
restart the MariaDB server with systemctl (sudo required):

$ sudo systemctl restart mariadb.service

Now, the MariaDB server record error log to custom file.

Viewing Custom Error Log

You can view the new file with cat (sudo required because file is maintained
by the system):

$ cat /var/log/mariadb/error.log

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

Output
2021-05-19 11:17:25 0 [Note] InnoDB: Uses event mutexes
2021-05-19 11:17:25 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-05-19 11:17:25 0 [Note] InnoDB: Number of pools: 1
2021-05-19 11:17:25 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2021-05-19 11:17:25 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
2021-05-19 11:17:25 0 [Note] InnoDB: Using Linux native AIO
2021-05-19 11:17:25 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
2021-05-19 11:17:25 0 [Note] InnoDB: Completed initialization of buffer pool
2021-05-19 11:17:25 0 [Note] InnoDB: 128 rollback segments are active.
2021-05-19 11:17:25 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-05-19 11:17:25 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-05-19 11:17:25 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-05-19 11:17:25 0 [Note] InnoDB: 10.5.10 started; log sequence number 45214; transaction id 20
2021-05-19 11:17:25 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-05-19 11:17:25 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2021-05-19 11:17:25 0 [Note] InnoDB: Buffer pool(s) load completed at 210519 11:17:25
2021-05-19 11:17:25 0 [Note] Server socket created on IP: '127.0.0.1'.
2021-05-19 11:17:25 0 [Note] Reading of all Master_info entries succeeded
2021-05-19 11:17:25 0 [Note] Added new Master_info '' to hash table
2021-05-19 11:17:25 0 [Note] /usr/sbin/mariadbd: ready for connections.
Version: '10.5.10-MariaDB-1:10.5.10+maria~focal'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution

The output shows records similar to syslog records.

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 happened. This log is useful when the administrators want to know what
clients exactly execute. However, keep in mind that the general query log slows
down performance, and administrators usually turn it on only for a short time
for debugging.

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 is usually turned off.

You can connect to MariaDB server as a root client:

You will be redirected to MariaDB command-line.

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

MariaDB> show variables like '%general%';

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:

Output
+------------------+--------------+
| Variable_name    | Value        |
+------------------+--------------+
| general_log      | OFF          |
| general_log_file | alice.log    |
+------------------+--------------+
2 rows in set (0.002 sec)

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 disabled. We can disconnect
from the server by executing the exit command:

You will be redirected back to the terminal.

Enabling the General Query Log

You can enable general query log by editing MariaDB configuration file
/etc/mysql/mariadb.conf.d/50-server.cnf (sudo required):

$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

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

# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# Recommend only changing this at runtime for short testing periods if needed!
#general_log_file       = /var/log/mysql/mysql.log
#general_log            = 1

You can configure a general query log file by uncommenting the lines with these
variables and set up the general_log to 1 (enabled) and general_log_file
to the absolute path to the log file:

# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# Recommend only changing this at runtime for short testing periods if needed!
general_log_file       = /var/log/mariadb/general-query.log
general_log            = 1

In our example, we change the directory to the /var/log/mariadb, same as for
the error log. This directory has already proper access right, as we set in the
previous step.  Now, you can save the file.

Viewing General Query Log

Next, let’s view the content of the new log with a cat (the sudo is required
because this file is maintained by the system):

$ sudo cat /var/log/mariadb/general-query.log

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

Output
/usr/sbin/mariadbd, Version: 10.5.10-MariaDB-1:10.5.10+maria~focal-log (mariadb.org binary distribution). started with:
Tcp port: 0  Unix socket: /run/mysqld/mysqld.sock
Time                Id Command  Argument
210519 11:58:23      3 Connect  root@localhost on mysql using Socket
                     3 Query    SET SQL_LOG_BIN=0, WSREP_ON=OFF
                     3 Query    show variables like 'datadir'
                     3 Quit
                     4 Connect  root@localhost on  using Socket
                     4 Query    SELECT count(*) FROM mysql.user WHERE user='root' and password='' and plugin in ('', 'mysql_native_password', 'mysql_old_password')
                     4 Quit
                     5 Connect  root@localhost on  using Socket
                     5 Query    select concat('select count(*) into @discard from `',
                    TABLE_SCHEMA, '`.`', TABLE_NAME, '`')
      from information_schema.TABLES where TABLE_SCHEMA<>'INFORMATION_SCHEMA' and TABLE_SCHEMA<>'PERFORMANCE_SCHEMA' and ( ENGINE='MyISAM' or ENGINE='Aria' )
                     5 Quit
                     6 Connect  root@localhost on  using Socket
                     6 Query    select count(*) into @discard from `mysql`.`procs_priv`
                     6 Quit
...

The output shows all statement executed at each session. You can see the
timestamp of the session beginning and the list of SQL queries. There are
already a lot of queries because the system executes them at the server starting
(it retrieves the metadata about the environment).

Step 3 — Enabling and Listing Binary Logs

The binary log contains events that manipulate 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.

Enabling Binary Logs

By default, the binary logs are disabled. You can enable binary logs by editing
MariaDB configuration file /etc/mysql/mariadb.conf.d/50-server.cnf (sudo
required):

$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

The file contains the following lines that hold configuration variable log_bin
(by default commented out):

#log_bin                = /var/log/mysql/mysql-bin.log

You can enable binary logging by uncommenting the line with this variable and
set up the log_bin to the absolute path to the directory where you want to
store them:

log_bin                = /var/log/mariadb/binary.log

In our example, we change the directory to the /var/log/mariadb, same as for
the error and general query logs. Now, you can save the file.

Listing Binary Logs

At last, let’s check the binary log configuration in the MariaDB server. Let’s
connect to the MariaDB server as a root client:

You will be redirected to MariaDB prompt.

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

MariaDB> show binary logs;

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

Output
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binary.000001 |       325 |
+---------------+-----------+
1 row in set (0.000 sec)

The output shows all binary logs. We enable this service just a minute ago, soo
there is just a single file. However, there will be more binary logs indexed
with increasing suffix (for example, binary.000002, binary.000003) Now, we
can find out where are these logs stored.

We can show logs location by executing command show variables:

MariaDB> show variables like '%log_bin%';

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:

Output
+---------------------------------+-------------------------------+
| Variable_name                   | Value                         |
+---------------------------------+-------------------------------+
| log_bin                         | ON                            |
| log_bin_basename                | /var/log/mariadb/binary       |
| log_bin_compress                | OFF                           |
| log_bin_compress_min_len        | 256                           |
| log_bin_index                   | /var/log/mariadb/binary.index |
| log_bin_trust_function_creators | OFF                           |
| sql_log_bin                     | ON                            |
+---------------------------------+-------------------------------+
7 rows in set (0.002 sec)

The output shows that the binary logs are stored in directory
/var/log/mariadb, as we set up in the configuration file.

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

You will be redirected back to the terminal.

Viewing the Binary Log

The binary logs are not plain text files and you can not read them with the text
editor. However, the MariaDB includes command-line utility mysqlbinlog.

You can view the binary log binary.000001 with this utility by executing the
following command (sudo required because the file binary.000001 is
maintained by the system):

$ sudo mysqlbinlog /var/log/mariadb/binary.000001

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

Output
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210519 12:10:58 server id 1  end_log_pos 256 CRC32 0xb7f7dbc5  Start: binlog v 4, server v 10.5.10-MariaDB-1:10.5.10+maria~focal-log created 210519 12:10:58 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
MuSkYA8BAAAA/AAAAAABAAABAAQAMTAuNS4xMC1NYXJpYURCLTE6MTAuNS4xMCttYXJpYX5mb2Nh
bC1sb2cAAAAAAAAAAAAy5KRgEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgHF2/e3
'/*!*/;
# at 256
#210519 12:10:58 server id 1  end_log_pos 285 CRC32 0x218f4e3e  Gtid list []
# at 285
#210519 12:10:58 server id 1  end_log_pos 325 CRC32 0x346ae813  Binlog checkpoint binary.000001
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

The output shows an encoded binary log. You can see the warning, which told us
that the log is still in use. You can read about the format and meaning of each
record in this log in the manual pages (man mysqlbinlog). The purpose of the
binary log is to allow replication, as
well as assisting in backup operations.

Step 4 — Configuring Slow Query Log

MariaDB allows to log queries, which took too much time. This mechanism is
called a slow query log. Once again, it is a heavy offload to database
performance, and you should use it just for a short time for database
performance optimisation.

Enabling Slow Query Logging

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

$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

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

# Enable the slow query log to see queries with especially long duration
#slow_query_log_file    = /var/log/mysql/mariadb-slow.log
#long_query_time        = 10

The slow query log is configured by following three variables:

• slow_query_log: The slow query logging is disabled (value 0) or enabled
  (value 1).
• slow_query_log_file: The absolute path to the slow query 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 a slow query log by inserting the new line into the configuration
file with the variable slow_query_log with value 1 (the variable is not
included by default). Also, you can set up your own values to the variables
slow_query_log_file and long_query_time when you uncomment the lines with
these variables:

# Enable the slow query log to see queries with especially long duration
slow_query_log         = 1
slow_query_log_file    = /var/log/mariadb/slow-query.log
long_query_time        = 5

In our example, we change the default long_query_time value to 5 seconds and
set the log file to /var/log/mariadb/slow-query (same directory as for error,
general and binary logs). Now, you can save the file.

Once again, if you want immediately apply the new configuration rules then you
must restart the MariaDB server with systemctl (sudo required):

$ sudo systemctl restart mariadb.service

Now, the MariaDB server enables slow query log.

Checking Slow Query Log Status

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

You will be redirected to MariaDB prompt.

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

MariaDB> show variables like '%slow_query_log%';

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:

Output
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /var/log/mariadb/slow-query.log |
+---------------------+---------------------------------+
2 rows in set (0.002 sec)

The output shows that the slow query log is enabled (the variable
slow_query_log holds the value ON).

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

MariaDB> show variables like '%long_query_time%';

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

Output
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time |  5.000000 |
+-----------------+-----------+
1 row in set (0.002 sec)

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 MariaDB records slow queries to the new log. You
can execute the following select query that takes 6 seconds:

MariaDB> select sleep(6);

The select will wait 6 seconds and then return 0:

Output
+----------+
| sleep(6) |
+----------+
|        0 |
+----------+
1 row in set (6.001 sec)

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 the content of the slow query log
/var/log/mariadb/slow-query.log (the sudo is required because the file is
maintained by the system):

$ sudo cat /var/log/mariadb/slow-query.log

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

/usr/sbin/mariadbd, Version: 10.5.10-MariaDB-1:10.5.10+maria~focal-log (mariadb.org binary distribution). started with:
Tcp port: 0  Unix socket: /run/mysqld/mysqld.sock
Time            Id Command  Argument
# Time: 210519 14:09:32
# User@Host: root[root] @ localhost []
# Thread_id: 31  Schema:   QC_hit: No
# Query_time: 6.000286  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 64
SET timestamp=1621426172;
select sleep(6);

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

Conclusion

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

Next article

→