Включение журнала для показа медленных запросов в mysql / mariadb

Получить двоичный журнал с удаленного сервера

С вашего локального компьютера вы также можете прочитать бинарные журналы mysql, расположенные на удаленном сервере.

Для этого вам необходимо указать IP-адрес, имя пользователя и пароль для удаленного сервера, как описано ниже.

Используйте для этого вариант -R. Параметр -R аналогичен -read-from-remote-server.

mysqlbinlog -R -h 192.168.101.2 -p mysqld-bin.000001

В приведенном выше:

• Параметр -R указывает команде mysqlbinlog считывать файл журнала с удаленного сервера
• -h указать ip-адрес удаленного сервера
• -p даст вам пароль. По умолчанию он будет использовать «root» в качестве имени пользователя. Вы также можете указать имя пользователя, используя опцию -u.
• mysqld-bin.000001 Это имя бинарного файла журнала с удаленного сервера, который мы здесь читаем.

Следующая команда точно такая же, как приведенная выше команда:

mysqlbinlog --read-from-remote-server --host = 192.168.101.2 -p mysqld-bin.000001

Ниже приводится частичный вывод указанной выше команды:

/*!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
#170726 13:57:37 server id 1  end_log_pos 106   Start: binlog v 4, server v 5.1.73-log created 170726 13:57:37 at startup
ROLLBACK/*!*/;
BINLOG '
IeZ4WQ8BAAAAZgAAAGoAAAAAAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#170726 13:59:31 server id 1  end_log_pos 182   Query   thread_id=2     exec_time=0     error_code=0
..
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Если вы укажете только параметр -h, вы получите следующее сообщение об ошибке.

# mysqlbinlog -h 192.168.101.2 mysqld-bin.000001
mysqlbinlog: File 'mysqld-bin.000001' not found (Errcode: 2)

Если у вас недостаточно прав для удаленной базы данных, вы получите сообщение об ошибке “is not allowed to connect”. В этом случае убедитесь, что вы предоставили правильные привилегии в удаленной базе данных для своего локального клиента (т. е. когда запущена команда mysqlbinlog)

# mysqlbinlog -R --host=192.168.101.2 mysqld-bin.000001
ERROR: Failed on connect: Host '216.172.166.27' is not allowed to connect to this MySQL server

Если вы не укажете правильный пароль с помощью параметра -p, вы получите следующее сообщение об ошибке «access denied»

# mysqlbinlog -R --host=192.168.101.2 mysqld-bin.000001
ERROR: Failed on connect: Access denied for user 'root'@'216.172.166.27' (using password: YES)

В следующем примере показано, что вы также можете использовать опцию -u для указания имени пользователя, которое должен использовать mysqlbinlog для подключения к удаленной базе данных MySQL. Обратите внимание, что этот пользователь является пользователем mysql (а не пользователем Linux-сервера)

mysqlbinlog -R --host = 192.168.101.2 -u root -p mysqld-bin.000001

Пропустить первые N количество записей

Вместо чтения всего бинарного файла mysql, вы также можете прочитать только определенную его часть, указав смещение.

Для этого используйте опцию -o. -o означает смещение.

Ниже перечислены первые 10 записей в указанном журнале bin mysql.

mysqlbinlog -o 10 mysqld-bin.000001

Чтобы убедиться, что это работает должным образом, дайте номер события для смещения, и вы не увидите никаких записей. Следующий пример пропустит первые 10 000 записей (событий) из журнала.

В этом примере, поскольку этот конкретный файл журнала, нет 10 000 записей, он не указывает на какие-либо события базы данных на выходе.

# mysqlbinlog -o 10000 mysqld-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
..
..
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Лог ошибок

Этот журнал содержит все ошибки, которые произошли во время работы сервера, включая критические ошибки, а также остановки, включения сервера и предупреждения (warnings). С него нужно начать в случае сбоя системы. По умолчанию все ошибки выводятся в консоль (stderr), также можно записывать ошибки в syslog (по умолчанию в Debian) или отдельный лог-файл:

Log_error=/var/log/mysql/mysql_error.log

# Ошибки будут писаться в 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

# Объясняет значения кодов ошибок

Настройка

Перед тем как начать, убедитесь, что PhpMyAdmin запущен и работает

Если вы залогинитесь, то, возможно, обратите внимание на сообщение следующего вида:. The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated

To find out why click here. (Хранилище конфигураций phpMyAdmin не настроено должным образом, некоторые возможности отключены. Чтобы узнать почему, нажмите здесь)

The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. To find out why click here. (Хранилище конфигураций phpMyAdmin не настроено должным образом, некоторые возможности отключены. Чтобы узнать почему, нажмите здесь)

$cfg = «pma»;
$cfg = «pmapass»;
$cfg = «phpmyadmin»;
$cfg = «pma__bookmark»;
$cfg = «pma__relation»;
$cfg = «pma__table_info»;
$cfg = «pma__pdf_pages»;
$cfg = «pma__table_coords»;
$cfg = «pma__column_info»;
$cfg = «pma__history»;
$cfg = «pma__recent»;
$cfg = «pma__table_uiprefs»;
$cfg = «pma__users»;
$cfg = «pma__usergroups»;
$cfg = «pma__navigationhiding»;
$cfg = «pma__tracking»;
$cfg = «pma__userconfig»;
$cfg = «pma__designer_coords»;
$cfg = ‘pma__favorite’;
$cfg = «pma__savedsearches»;

Смените имя пользователя, пароль и имя базы данных по вашему собственному усмотрению. Остальные конфигурационные значения — это имена таблиц. Если оставить их пустыми, то вы отключите соответствующие им возможности. Но вообще, я бы рекомендовал оставить эти названия как есть. В зависимости от версии PhpMyAdmin не все значения будут доступны по умолчанию. В данном случае я использовал версию PhpMyAdmin 4.2.x.

Когда вы закончите с конфигом, нужно будет создать базу данных. Создайте базу данных с именем, которое вы указали в вашем конфигурационном файле. Далее поищите файл с названием create_tables.sql на вашей машине. Скорее всего, данный файл будет находиться в корневой директории установленного PhpMyAdmin, или в поддиректории scripts. Если у вас Linux, то расположение будет следущим /usr/share/phpMyAdmin/examples/ или /usr/share/doc/phpmyadmin/scripts/ . Запустите этот файл в рамках созданной базы данных. Если вы меняли имена таблиц в вышеприведенном конфиге, не забудьте поменять их также и в базе данных.

GRANT USAGE ON mysql.* TO «pma»@»localhost» IDENTIFIED BY «pmapass»;
GRANT SELECT (Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
Execute_priv, Repl_slave_priv, Repl_client_priv) ON mysql.user TO «pma»@»localhost»;
GRANT SELECT ON mysql.db TO «pma»@»localhost»;
GRANT SELECT ON mysql.host TO «pma»@»localhost»;
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
ON mysql.tables_priv TO «pma»@»localhost»;
GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO «pma»@»localhost»;

Давайте рассмотрим дополнительные возможности.

Отключение sync_binlog

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

Значение отключит синхронизацию из Mysql, и база данных будет полагаться на ОС в вопросе записи лога на диск. Такое значение может увеличить производительность мастера в несколько раз.

Проверить текущее значение можно так:

mysql -e "show variables like 'sync_binlog'"

# Проверка режима синхронизации бинлога

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+

# Синхронизацию лучше отключить

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

Однако не забудьте исправить этот параметр и в my.cnf, чтобы он сохранился после перезагрузки:

...

sync_binlog = 0
...

Генерирование запроса для профилирования

Теперь вы знакомы с настройками логов медленных запросов. Попробуйте сгенерировать данные запроса для профилирования.

Примечание
: Приведенный здесь пример был выполнен на запущенном экземпляре MySQL без настроенных логов медленных запросов. Эти тестовые запросы можно выполнять через графический интерфейс или командную строку MySQL.

При мониторинге лога медленных запросов полезно открыть два окна терминала: одно соединение для отправки операторов MySQL, а второе – для просмотра лога запросов.

Зайдите на сервер MySQL с помощью консоли как пользователь с привилегиями SUPER ADMIN. Для начала создайте тестовую базу данных и таблицу, добавьте в нее фиктивные данные и включите лог медленных запросов.

Примечание
: В идеале этот пример лучше выполнять в среде без каких-либо других приложений, использующих MySQL, чтобы избежать загромождения лога запросов.

$> mysql -u -pmysql> CREATE DATABASE profile_sampling;

mysql> USE profile_sampling;

mysql> CREATE TABLE users (id TINYINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255));

mysql> INSERT INTO users (name) VALUES («Walter»),(«Skyler»),(«Jesse»),(«Hank»),(«Walter Jr.»),(«Marie»),(«Saul»),(«Gustavo»),(«Hector»),(«Mike»);

mysql> SET GLOBAL slow_query_log = 1;

mysql> SET GLOBAL slow_query_log_file = «/var/log/mysql/localhost-slow.log»;

mysql> SET GLOBAL log_queries_not_using_indexes = 1;

mysql> SET long_query_time = 10;

mysql> SET min_examined_row_limit = 0;

Теперь у вас есть тестовая база данных и таблица с небольшим количеством данных. Лог медленных запросов включен. Мы нарочно установили высокое время обработки запроса и отключили проверку количества строк. Чтобы просмотреть лог, введите:

cd /var/log/mysql
ls -l

Пока что в папке не должно быть лога медленных запросов, так как на данный момент запросов не было. Если же такой лог уже есть, это значит, что БД уже сталкивалась с медленными запросами с тех пор, как вы включили поддержку лога медленных запросов. Это может исказить результаты этого примера. Вернитесь во вкладку MySQL и запустите:

mysql> USE profile_sampling;
mysql> SELECT * FROM users WHERE id = 1;

Выполненный запрос просто извлекает данные и использует индекс первого ключа из таблицы. Этот запрос был быстрым и использовал индекс, поэтому в логе медленных запросов он не регистрируется. Вернитесь в каталог и убедитесь, что лог запросов не был создан. Теперь вернитесь в окно MySQL и запустите:

mysql>

В этом запросе не используется индекс. Теперь в логе /var/log/mysql/localhost-slow.log должна появиться примерно такая запись:

# Time: 140322 13:54:58
use profile_sampling;
SET timestamp=1395521698;

Еще один пример. Увеличьте минимальное количество строк для анализа и отправьте такой запрос:

mysql> SET min_examined_row_limit = 100;
mysql> SELECT * FROM users WHERE name = «Walter»;

Данные не будут добавлены в лог, поскольку при запросе было проанализировано меньше 100 строк.

Примечание
: Если данные не были добавлены в лог, нужно проверить несколько факторов. Сначала проверьте права каталога, в котором создается лог. Он должен принадлежать пользователю/группе mysqld и иметь привилегии chmod 755. Затем следует проверить, нет ли на сервере других настроек медленных запросов, которые переопределяют ваши параметры. Сбросьте значения по умолчанию, чтобы удалить все переменные медленных запросов из конфигурационного файла, и перезагрузите сервер. Также можно динамически установить глобальным переменным их стандартные значения. Если вы вносите изменения динамически, выйдите из системы и войдите в MySQL, чтобы обновить параметры.

Просмотр записей, начиная с определенного времени

Ниже перечислены только те записи, которые начинаются с указанного времени. Любые записи до этого времени будут проигнорированы.

mysqlbinlog --start-datetime = "2017-08-16 15:00:00" mysqld-bin.000001 

Это очень полезно, если вы хотите извлечь данные из двоичного файла только из определенного временного интервала, который вы хотите использовать для восстановления или восстановления определенных действий базы данных, которые произошли за этот период времени.

Формат метки времени может быть любым, что понимается типами DATETIME и TIMESTAMP сервера MYSQL. Итак, у вас здесь много гибкости.

Мониторинг

После настройки репликации следует постоянно следить за несколькими параметрами на слейве:

# Запрос вернет статистику и настройки слейва

Стоит обратить внимание на такие показатели:

Slave_IO_State: Waiting for master to send event
                 ...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                 ...
                   Last_Errno: 0
                   Last_Error: 
                 ...
        Seconds_Behind_Master: 0
                 ...

# Статус работы реплики

Показатели и отражают нормальную работу реплики. Оба должны иметь значение Yes. Когда один из этих показателей равен No, в будет виден текст ошибки репликации.

Параметр отражает количество секунд, на которое слейв отстает от мастера. Этот показатель должен быть равен нулю (иногда может вырастать до нескольких секунд).

Мониторинг бэкапов mysql

Для того, чтобы нам не тащить на бэкап сервер битые дампы, будем проверять их сразу же на месте. Сразу поясню, что это не отменяет дальнейшие проверки этих дампов на возможность реального восстановления из них. Эту процедуру надо делать на отдельном сервере. В рамках данной заметки я не буду это рассматривать. Сейчас мы просто будем следить за тем, что дамп базы данных mysql выполнен корректно.

Мониторинг за валидацией бэкапов будем осуществлять с помощью Zabbix.

Если у вас еще нет своего сервера для мониторинга, то рекомендую материалы на эту тему. Для тех, кто предпочитает систему CentOS:

1. Установка CentOS 8.
2. Настройка CentOS 8.
3. Установка и настройка zabbix сервера.

То же самое на Debian 10, если предпочитаете его:

1. Установка Debian 10.
2. Базовая настройка Debian.
3. Установка и настройка zabbix на debian.

Мы настроили вывод результатов проверки архивов в лог файл /var/log/mysql/backup.log. Теперь сделаем так, чтобы Zabbix анализировал содержимое файла и слал оповещение, если там появится слово corrupted, что будет означать проблему с созданием дампа.

Для этого создаем новый шаблон и добавляем туда элемент данных.

Тут всё очень просто и стандартно. Далее делаем триггер.

Выражение проблемы:

{Backup mysql status:log[/var/log/mysql/backup.log].str(corrupted)}=1

Выражение восстановления:

{Backup mysql status:log[/var/log/mysql/backup.log].str(OK)}=1

Прикрепляем шаблон к хосту, где делаем бэкап. Не забудьте убедиться, что у zabbix-agent на хосте есть доступ на чтение этого лог файла. Таким образом, если проверка дампа не будет завершена успешно, сработает триггер. Он будет висеть активным до тех пор, пока не будет создан корректный бэкап базы mysql.

Вот так достаточно просто и быстро я решаю вопрос создания, проверки и оповещения о проблемах при создании дампов и бэкапов mysql баз.

Лог ошибок

Этот жур­нал содер­жит все ошиб­ки, кото­рые про­изо­шли во вре­мя рабо­ты сер­ве­ра, вклю­чая кри­ти­че­ские ошиб­ки, а так­же оста­нов­ки, вклю­че­ния сер­ве­ра и пре­ду­пре­жде­ния (warnings). С него нуж­но начать в слу­чае сбоя систе­мы. По умол­ча­нию все ошиб­ки выво­дят­ся в кон­соль (stderr), так­же мож­но запи­сы­вать ошиб­ки в syslog (по умол­ча­нию в Debian) или отдель­ный лог-файл:

YAML

log_error=/var/log/mysql/mysql_error.log

1	log_error=/var/log/mysql/mysql_error.log

Реко­мен­ду­ем дер­жать этот жур­нал вклю­чен­ным для быст­ро­го опре­де­ле­ния оши­бок. А для пони­ма­ния, что зна­чит та или иная ошиб­ка, в MySQL при­сут­ству­ет ути­ли­та perror:

YAML

shell> perror 13 64
OS error code 13: Permission denied
OS error code 64: Machine is not on the network

1 2 3

shell>perror1364 OSerrorcode13:  Permission denied OSerrorcode64:  Machine is not on the network

Многопоточная репликация

До недавнего времени Mysql реплика работала всего в один поток. Тогда, даже если мастер и слейв идентичны по характеристикам, слейв все равно может отставать от мастера.

...

slave-parallel-workers = 2
...

# Включение репликации в 2 потока

Число задает количество потоков и может принимать значения от до . Значение отключит многопоточную обработку бинлога.

При включении этой настройки Mysql будет распределять обработку бинлога разных баз данных между разными потоками. Это даст возможность Mysql не ждать завершения операций в разных базах, а выполнять их параллельно. На мастере для этого ничего настраивать не нужно.

Понятно, что если у вас всего одна база данных, вы не получите прирост в производительности. Зато в версии Mysql 5.7 можно изменить тип распределения операций с помощью настройки в my.cnf:

...

slave-parallel-workers = 2
...

# Изменения типа параллелизации обработки бинлога

В таком случае, уже все операции (точнее закомиченные транзакции) из бинлога будут обрабатываться параллельно.

Заключение

Решение задачи по бэкапу mysql баз, что я описал, не претендует на уникальность и 100% правильность. Это просто мой личный опыт. Никаких особых изысканий и поиска наилучшего решения не проводил. Просто сделал, как сделал, чем с вами и поделился. В моих задачах такой подход достаточен.

Еще раз напоминаю, что этот способ актуален для относительно небольших баз. Дампить объемные базы плохая идея, так как будет сильно проседать i/o дисков. Плюс тут нет возможности делать инкрементные бэкпы. Только полные, что, очевидно, не всегда удобно.

Для мониторинга бэкапов в целом, можете воспользоваться моей объемной статьей по теме — Мониторинг бэкапов с помощью zabbix.

Онлайн курс по Linux

Если у вас есть желание научиться строить и поддерживать высокодоступные и надежные системы, рекомендую познакомиться с онлайн-курсом «Administrator Linux. Professional» в OTUS. Курс не для новичков, для поступления нужны базовые знания по сетям и установке Linux на виртуалку. Обучение длится 5 месяцев, после чего успешные выпускники курса смогут пройти собеседования у партнеров.

Что даст вам этот курс:

• Знание архитектуры Linux.
• Освоение современных методов и инструментов анализа и обработки данных.
• Умение подбирать конфигурацию под необходимые задачи, управлять процессами и обеспечивать безопасность системы.
• Владение основными рабочими инструментами системного администратора.
• Понимание особенностей развертывания, настройки и обслуживания сетей, построенных на базе Linux.
• Способность быстро решать возникающие проблемы и обеспечивать стабильную и бесперебойную работу системы.

Проверьте себя на вступительном тесте и смотрите подробнее программу по .

Управление base-64 в выводе BINLOG

Используя опцию base64-output, вы можете управлять поведением, когда оператором вывода должны быть операторы BINLOG, закодированные в base64.

Ниже приведены возможные значения для base64-output:

• never
• always
• decode-rows
• auto (this is default)

never: Когда вы укажете «never», как показано ниже, будут выводиться операторы BINLOG с кодировкой base64 в выходном файле.

mysqlbinlog --base64-output = never mysqld-bin.000001

т.е. когда вы используете «never», в выводе команды mysqlbinlog вы не будете использовать строки, похожие на следующие, которые имеют BINLOG с кодировкой base64.

BINLOG '
IeZ4WQ8BAAAAZgAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC

Обратите внимание, что вышеупомянутая опция «never» для вывода base64 будет работать в двоичных файлах журнала, если они не содержат события на основе строк. always: когда вы укажете опцию «always», отображать только записи BINLOG, когда это возможно. Таким образом, используйте это только тогда, когда вы специально отлаживаете некоторые проблемы

always: когда вы укажете опцию «always», отображать только записи BINLOG, когда это возможно. Таким образом, используйте это только тогда, когда вы специально отлаживаете некоторые проблемы.

mysqlbinlog --base64-output = всегда mysqld-bin.000001

Ниже приведен вывод выше с помощью «always», который показывает только записи BINLOG.

BINLOG '
IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
«/ * * /!;
# при 106
# 170726 14:59:31 server id 1 end_log_pos 182
BINLOG '
к + Z4WQIBAAAATAAAALYAAAAIAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI
AHRoZWdlZWtzdHVmZgBCRUdJTg ==
«/ * * /!;
# at 182
# 170726 14:59:30 server id 1 end_log_pos 291
BINLOG '
kuZ4WQIBAAAAbQAAACMBAAAAAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI
AHRoZWdlZWtzdHVmZgBJTlNFUlQgSU5UTyB0IFZBTFVFUygxLCAnYXBwbGUnLCBOVUxMKQ ==
«/ * * /!;
# 291
# 170726 14:59:30 server id 1 end_log_pos 422
BINLOG '
kuZ4WQIBAAAAgwAAAKYBAAAAAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI
AHRoZWdlZWtzdHVmZgBVUERBVEUgdCBTRVQgbmFtZSA9ICdwZWFyJywgZGF0ZSA9ICcyMDA5LTAx
LTAxJyBXSEVSRSBpZCA9IDE =

decode-rows: эта опция будет декодировать события на основе строк в комментированные инструкции SQL, особенно если вы укажете опцию -verbose также вместе с ней, как показано ниже.

mysqlbinlog --base64-output = decode-rows --verbose mysqld-bin.000001

auto: Это опция по умолчанию. Если вы не укажете опцию base64-decode, она будет использовать auto. В этом случае mysqlbinlog будет печатать записи BINLOG только для определенных типов событий, таких как события на основе строк и события описания формата.

Оба следующих утверждения точно совпадают.

mysqlbinlog --base64-output = auto mysqld-bin.000001

mysqlbinlog mysqld-bin.000001