В 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
Лог репликаций
Здесь логируются изменения, выполненные по инициации сервера репликаций. Как и бинарный лог, состоит из файлов, каждый из которых пронумерован.
Основные настройки в my.cnf
Местоположение лога:
relay-log = /var/log/mysql/mysql-relay-bin.log
Максимальный размер:
max_relay_log_size = 500М
Наиболее часто использующиеся команды
Начать новый файл лога можно только при остановленном дополнительном (slave) сервере:
shell> cat new_relay_log_name.index >> old_relay_log_name.index
shell> mv old_relay_log_name.index new_relay_log_name.index
Команда fluch logs инициирует ротацию лога.
Пароль к серверу баз данных утерян
В случае утери пароля создайте новый, выполнив следующие команды.
Остановите MySQL командой:
# service mysql stop - ОС Centos 6/Debian/Ubuntu # systemctl stop mariadb - ОС Centos 7
либо
# systemctl stop mysqld
Выполните запуск MySQL без учета прав доступа командой:
# mysqld_safe --skip-grant-tables &
Зайдите пользователем root командой:
# mysql -uroot
Измените пароль:
# use mysql; # UPDATE user SET Password=PASSWORD("mypassword") WHERE User='root'; # FLUSH PRIVILEGES;
Перезагрузите сервер баз данных с учетом прав доступа командой:
# service mysqld restart - ОС Centos 6/Debian/Ubuntu # systemctl restart mariadb - ОС Centos 7
либо
# systemctl restart mysqld
Выполните вход на MySQL сервер с новым паролем:
# mysql -uroot -p mypassword
Как просмотреть перечень ошибок MySQL сервера?
Для получения списка ошибок сервера баз данных просмотрите его лог-файлы. Для каждой ОС и файловой системы они располагаются в разных местах. Чтобы определить, где находятся лог-файлы MySQL на вашем сервере, подключитесь к серверу через консоль (например, Putty) и выполните команду, которая найдет файл my.cnf:
find / -name ‘my.cnf’
Результатом выполнения этой команды будут пути, по которым находится файл с этим именем.
Откройте любым редактором, например vi, найденный файл и найдите строки, начинающиеся с “log” или “log-error”
vi /etc/my.cnf
Откройте редактором лог-файл по найденному пути и просмотрите ошибки.
Если в файле my.cnf нет строк, указывающих на лог-файлы, это значит, что контроль ошибок (логирование) не включен. Включите эту функцию, добавив в my.cnf строку:
[mysqld] log-error=/var/log/mysql.log где mysql.log – новый файл, куда будут записываться ошибки.
Создайте его и наделите привилегиями командами:
touch /var/log/mysql.log chown mysql:mysql /var/log/mysql* chmod 640 /var/log/mysql*
При необходимости просмотр лог-файла можно запустить в фоновом режиме, чтобы параллельно запускать другие директивы. Для этого выполните команду:
tail –f /var/log/mysql.log &
Возможные ошибки в лог-файле и их решение
В лог-файле или в браузере выдается ошибка:
Сообщение говорит о том, что в одной из баз данных появилась поврежденная таблица, которую можно восстановить. Для этого подключитесь через консоль к серверу и выполните команду проверки всех таблиц на целостность:
mysqlcheck --repair --analyze --optimize --all-databases -u<USER> –p<PASSWORD>
В случае ошибки запустите несколько команд:
mysqlcheck --repair --all-databases -u<USER> –p<PASSWORD> mysqlcheck --analyze --all-databases -u<USER> –p<PASSWORD> mysqlcheck --optimize --all-databases -u<USER> –p<PASSWORD> где <USER> - пользователь базы данных, <PASSWORD> - пароль.
Если вы знаете, какая именно база данных повреждена, выполните команду:
mysqlcheck --repair --analyze --optimize <DB> -u<USER> -p<PASSWORD> где <USER> - пользователь базы данных, <PASSWORD> - пароль, <BD> - имя поврежденной базы.
Возникает ошибка вида:
Сообщение говорит о том, что запрещен доступ для пользователя user_xxx к базе данных или какой-то ее таблице.
Зайдите в ISPmanager, перейдите в раздел «Базы данных» — нажмите кнопку «Управление серверами БД», двойным кликом на имени сервера баз данных откройте его настройки.
Проверьте, что указанные данные в полях «Имя пользователя» и «Пароль» соответствуют тем, которые находятся в настройках сайта для подключения к этой БД.
На сайте возникает ошибка вида «Не удалось подключиться к базе данных»
Варианты сообщения об ошибке в случае неудачи при подключении к базе данных могут быть следующими:
Убедитесь, что сервер баз данных MySQL запущен. Зайдите в ISPmanager а раздел «Настройки» -> «Конфигурация ПО» и проверьте, что в списке возможностей присутствует строка «Сервер СУБД MySQL» и лампочка в этой строке зелёного цвета. Если лампочка выключена, то выделите строку и нажмите «Установить» на панели инструментов.
Если проблема не исчезла, то подключитесь к серверу через консоль и перезапустите MySQL командой:
/etc/init.d/mysqld restart - ОС Centos 6, Debian systemctl restart mysqld - ОС Centos 7
Проверьте, что сервер корректно запустился, выполнив команду, которая выводит список процессов MySQL:
ps axuw | grep mysql
Если в результате не вывелось ни одного процесса, то MySQL не запустился.
Не удается запустить MySQL
Попробуйте запустить MySQL через панель управления ISPmanager. Если не получилось, то подключитесь к серверу по SSH и попробуйте запустить MySQL через консоль командой:
/etc/init.d/mysqld restart - ОС Centos 6, Debian systemctl restart mariadb - ОС Centos 7
Если MySQL не запускается через консоль, вы получите сообщение об ошибке вида:
Проверьте свободное место на диске командой
df -h
Команда
du –hs /*
выведет, сколько места занимает каждая директория.
Если свободного места осталось мало, освободите его, очистив в первую очередь лог-файлы MySQL и других служб.
Перезапустите MySQL через консоль командами, приведенными выше.
Если проблема сохранилась, внимательно изучите записи в лог-файле MySQL, начинающиеся с [ERROR]. Например, запись Error while setting value ‘—read_buffer_size=256K’ to ‘sort_buffer_size’ означает, что директива sort_buffer_size в конфигурационном файле MySQL, прописана не верно.
26 марта, 2019 12:22 пп
935 views
| Комментариев нет
MariaDB, mySQL
Эта серия статей научит вас устранять неполадки и диагностировать ваш экземпляр MySQL. Мы рассмотрим базовые проблемы, с которыми сталкиваются многие пользователи MySQL, а также предоставим вам инструкции по их устранению. Также вы найдете здесь ссылки на другие полезные статьи и мануалы.
Очень часто главную причину замедления, сбоев или другого непредсказуемого поведения MySQL можно найти, проанализировав лог ошибок. В системах Ubuntu этот файл MySQL по умолчанию расположен в /var/log/mysql/error.log. В большинстве случаев для чтения логов рекомендуется использовать команду less (это утилита командной строки, которая позволяет просматривать файлы, но не редактировать их). Чтобы просмотреть лог ошибок, введите:
sudo less /var/log/mysql/error.log
Если MySQL ведет себя странно, вы можете получить больше информации об источнике проблемы, запустив эту команду. В логе вы найдете информацию, необходимую для диагностики проблем и их устранения.
Читайте также: Устранение неполадок в запросах MySQL
Tags: MySQL
Summary: in this tutorial, you will learn how to use the MySQL SHOW ERRORS
statement to display error information generated by a query.
Introduction to MySQL SHOW ERRORS
statement
The SHOW ERRORS
is a diagnostic statement that displays information for errors.
The SHOW ERRORS
is similar to the SHOW WARNINGS
except that it shows only errors, not warnings and notes.
The following SHOW ERRORS
displays all errors:
SHOW ERRORS;
Code language: SQL (Structured Query Language) (sql)
To limit the number of errors to return, you use the SHOW ERRORS LIMIT
statement:
SHOW ERRORS [LIMIT [offset,] row_count];
Code language: SQL (Structured Query Language) (sql)
The LIMIT
clause has the same meaning as for the SELECT
statement.
To get the total number of errors, you use the following form of the SHOW ERRORS
statement:
SHOW COUNT(*) ERRORS;
Code language: SQL (Structured Query Language) (sql)
Or you can select it from the system variable @@error_count
:
SELECT @@error_count;
Code language: SQL (Structured Query Language) (sql)
MySQL SHOW ERRORS
statement example
This statement returns id from the products
table in the sample database:
SELECT id FROM products;
Code language: SQL (Structured Query Language) (sql)
However, the products
table does not have the id
column.
To show the error, you use the SHOW ERRORS
statement:
SHOW ERRORS;
Code language: SQL (Structured Query Language) (sql)
To get the total number of errors, you use the error_count
variable:
SELECT @@error_count;
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you will have learned how to use the MySQL SHOW ERRORS
statement to display error information.
Was this tutorial helpful?
Thank you for reading this post, don’t forget to subscribe!
Журналы событий — первый и самый простой инструмент для определения статуса системы и выявления ошибок. Основных логов в MySQL четыре:
- Error Log — стандартный лог ошибок, которые собираются во время работы сервера (в том числе start и stop);
- Binary Log — лог всех команд изменения БД, нужен для репликации и бэкапов;
- General Query Log — основной лог запросов;
- Slow Query Log — лог медленных запросов.
Лог ошибок
Этот журнал содержит все ошибки, которые произошли во время работы сервера, включая критические ошибки, а также остановки, включения сервера и предупреждения (warnings). С него нужно начать в случае сбоя системы. По умолчанию все ошибки выводятся в консоль (stderr), также можно записывать ошибки в syslog (по умолчанию в Debian) или отдельный лог-файл:
log_error=/var/log/mysql/mysql_error.log |
Рекомендуем держать этот журнал включенным для быстрого определения ошибок. А для понимания, что значит та или иная ошибка, в MySQL присутствует утилита perror:
shell> perror 13 64 OS error code 13: Permission denied OS error code 64: Machine is not on the network |
Бинарный (он же двоичный) лог
В бинарный лог записываются все команды изменения базы данных, пригодится для репликации и восстановления.
Включается так:
log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 5 max_binlog_size = 500M |
Учтите, что если вы не собираетесь масштабировать систему и реализовывать отказоустойчивость, то бинарный лог лучше не включать. Он требователен к ресурсам и снижает производительность системы.
Лог запросов
В этом журнале содержатся все полученные SQL-запросы, информация о подключениях клиентов. Может пригодиться для анализа индексов и оптимизации, а также выявления ошибочных запросов:
general_log_file = /var/log/mysql/mysql.log <b>general_log = 1</b> |
Также его можно включить/отключить во время работы сервера MySQL:
SET GLOBAL general_log = ‘ON‘; SET GLOBAL general_log = ‘OFF‘; |
Лог медленных запросов
Журнал пригодится для определения медленных, то есть неэффективных запросов. Подробнее читайте в этой статье.
Просмотр логов
Для просмотра логов на Debian (Ubuntu) нужно выполнить:
# Лог ошибок tail -f /var/log/syslog <span class=«comment»> #Лог запросов </span>tail -f /var/log/mysql/mysql.log <span class=«comment»> # Лог медленных запросов </span>tail -f /var/log/mysql/mysql-slow.log |
Ротация логов
Не забывайте сжимать (архивировать, ротировать) файлы логов, чтобы они занимали меньше места на сервере. Для этого используйте утилиту logrotate, отредактировав файл конфигурации /etc/logrotate.d/mysql-server:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
# — I put everything in one block and added sharedscripts, so that mysql gets <span class=«comment»> # flush-logs’d only once. </span># Else the binary logs would automatically increase by n times every day. <span class=«comment»> # — The error log is obsolete, messages go to syslog now. </span><b>/var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log</b> { daily rotate 7 missingok create 640 mysql adm compress sharedscripts postrotate test -x /usr/bin/mysqladmin || exit 0 <span class=«comment»> # If this fails, check debian.conf! </span> MYADMIN=«/usr/bin/mysqladmin —defaults-file=/etc/mysql/debian.cnf» if [ -z «`$MYADMIN ping 2>/dev/null`» ]; then <span class=«comment»> # Really no mysqld or rather a missing debian-sys-maint user? </span> <span class=«comment»> # If this occurs and is not an error please report a bug. </span> <span class=«comment»> #if ps cax | grep -q mysqld; then </span> if killall -q -s0 -umysql mysqld; then exit 1 fi else $MYADMIN flush-logs fi endscript } |
DDL Log
MySQL также ведет лог языка описания данных. В него собираются данные операций типа DROP_TABLE and ALTER_TABLE. Лог используется для восстановления после сбоев, которые произошли во время выполнения таких операций. DDL Log — бинарный файл, не предназначенный для чтения пользователем, поэтому не модифицируйте и не удаляйте его.
Самое главное
Всегда включайте лог ошибок, используйте лог запросов для проверки соединения приложения с базой данных, проверки запросов и работы memcached. Лог медленных запросов пригодится для оптимизации работы MySQL.