На днях столкнулся с ошибкой в эксплуатации и настройке MySQL сервера, о которой решил написать, чтобы самому не забыть решение, и с вами поделиться. Тема довольно распространённая, теория по которой будет полезна всем, кто работает с этой СУБД.

В MySQL есть настройка innodb_temp_data_file_path, которая отвечает за управление временным табличным пространством (temporary tablespace). Это место, где временно хранятся данные на протяжении сеанса пользователя, потом очищаются. В основном это пространство используется для операций сортировки, может чего-то ещё. Точно я не знаю.

По умолчанию этот параметр имеет значение autoextend и в некоторых случаях при такой настройке файл ibtmp1, в котором хранятся временные данные, может сожрать всё свободное место на сервере. У меня такое было не раз. Поэтому я всегда ограничиваю этот размер. Выглядит это примерно так:

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2G

Тут указано, что создаётся файл изначального размера 12 мегабайт с автоматическим увеличением до 2 гигабайт. Заранее предсказать, какого максимального объёма будет достаточно, трудно. Это зависит от многих факторов. Я такую настройку сделал несколько лет назад на одном сервере, который на днях засбоил.

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

[ERROR] /usr/sbin/mysqld: The table '/tmp/#sql_1c6f_1' is full

Меня они сначала сбили с толку, так как подумал, что на сервере не хватает места. Но нет, с местом всё в порядке. Стал разбираться дальше и понял, что подобная ошибка означает не только недостаток места на диске, (а при нём будет такая же ошибка) но и нехватку выделенного места под temporary tablespace. Увеличил значение в innodb_temp_data_file_path и перезапустил MySQL сервер. Ошибка ушла, как и повышенная нагрузка на диск.

Это один из тех параметров, на который нужно обращать внимание при настройке MySQL сервера, иначе он может в какой-то момент преподнести сюрприз. Можно этот файл вынести на отдельный диск, или вообще указать 2 разных файла на разных дисках. В процессе написания заметки возникла идея, что может его и в память можно перенести. Но это надо погружаться в тему, чтобы понять, к чему это в итоге приведёт и стоит ли так делать. Если кто-то пробовал, напишите о своём опыте.

#mysql

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

⇨ https://serveradmin.ru/nastrojka-mysqldump-proverka-i-monitoring-bekapov-mysql/

Она старя, что-то уже переделывалась, но общий смысл примерно тот же. К сожалению, нет времени обновлять и актуализировать статьи. Да и просмотров там не очень много. Тема узкая, не очень популярная. Хотя как по мне, без мониторинга этих дампов просто нельзя. Можно годами не знать, что у тебя дампы битые, если хотя бы не проверять их создание.

Описание ниже будет актуально для любых текстовых дампов sql серверов. Как минимум, один и тот же подход я применяю как к Mysql, так и Postgresql.

1️⃣ В любом дампе sql обычно есть служебные строки в начале и в конце. Для Mysql это обычно в начале -- MySQL dump и в конце -- Dump completed. После создания дампа я банальным grep проверяю, что там эти строки есть.

2️⃣ Если строки есть, пишу в отдельный лог файл что-то типа ${BASE01} backup is OK, если хоть одной строки нет, то ${BASE01} backup is corrupted.

3️⃣ В Zabbix настраиваю отдельный шаблон, где в айтем забираю этот лог. И делаю для него триггер, что если есть слово corrupted, то срабатывает триггер.

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

В логе увидел ошибку:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `b_stat_session_data` at row: 1479887

Не знаю, с чем она была связана. Проверил лог mysql в это время, там тоже ошибка:

Aborted connection 1290694 to db: 'db01' user: 'user01' host: 'localhost' (Got timeout writing communication packets)

Проверил таблицу, там всё в порядке:

> check table b_stat_session_data;

Следующий дамп прошёл уже без ошибок, так что я просто забил. Если повторится, буду разбираться детальнее.

Делать такую проверку можно как угодно. Я люблю всё замыкать на Zabbix, а он уже шлёт уведомления. Можно в скрипте с проверкой сразу отправлять информацию на почту, и, к примеру, мониторить почтовый ящик. Если кто-то тоже мониторит создание дампов, то расскажите, как это делаете вы.

Ну и не забываем, что это только мониторинг создания. Даже если не было ошибок, это ещё не гарантия того, что дамп реально рабочий, хотя лично я ни разу не сталкивался с тем, что корректно созданный дамп не восстанавливается.

Тем не менее, восстановление я тоже проверяю. Тут уже могут быть различные реализации в зависимости от инфраструктуры. Самый более ли менее приближённый к реальности вариант такой. Копируете этот дамп вместе с бэкапом исходников на запасной веб сервер, там скриптами разворачиваете и проверяете тем же Zabbix, что развёрнутый из бэкапов сайт работает и актуален по свежести. Пример, как это может выглядеть, я когда-то тоже описывал в старой статье.

#mysql #backup

​​На канале накопилось много полезных материалов по MySQL, которые я использую сам, особенно во время настройки нового сервера. Чтобы упростить себе и вам задачу, решил объединить всё наиболее полезное в одну публикацию для удобной навигации.

📌 Настройка:
▪️ Базовые настройки сервера, которые нужно проконтроллировать при установке
▪️ MySQLTuner - помощник по подбору оптимальных параметров под железо и профиль нагрузки
▪️ Скрипт mysql-stat для упрощения выбора параметров в зависимости от доступной оперативы на сервере
▪️ Рекомендации по безопасности от CIS
▪️ Разбор параметра innodb_temp_data_file_path
▪️ Настройка Audit Plugin для отслеживания действий на сервере
▪️ Настройка репликации

📌 Бэкап:
▪️ Полный и инкрементный backup Mysql с помощью Percona XtraBackup
▪️ Mydumper - многопоточное создание дампов
▪️ SQLBackupAndFTP - приложение под Windows для бэкапа
▪️ Скрипт AutoMySQLBackup для автоматического бэкапа баз
▪️ Восстановление отдельной таблицы из дампа всей базы

📌 Мониторинг:
▪️ Мониторинг MySQL с помощью Zabbix
▪️ Консольный Mytop для мониторинга в режиме реального времени
▪️ Проверка успешности создания дампа
▪️ Мониторинг с помощью Percona Monitoring and Management

📌 Эксплуатация:
▪️ Пошаговый план оптимизации запросов MySQL сервера
▪️ Полезные консольные команды mysql клиента
▪️ Подборка MySQL клиентов для работы с СУБД
▪️ Удаление всех таблиц в базе без удаления самой базы
▪️ ProxySQL для проксирования и кэша SQL запросов
▪️ Краткая информация о том, что такое индексы в MySQL

#mysql #подборка

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

#!/bin/bash

# Дамп базы с заменой общего комплексного параметра --opt, где используется ключ --lock-tables на набор отдельных ключей, где вместо lock-tables используется --single-transaction
/usr/bin/mysqldump --add-drop-database --add-locks --create-options --disable-keys --extended-insert --single-transaction --quick --set-charset --routines --events --triggers --comments --quote-names --order-by-primary --hex-blob --databases database01 -u'userdb' -p'password' > /mnt/backup/sql/"$(date +%Y-%m-%d)"-database01.sql

# Из общего дампа вырезаю дамп только данных таблицы table01. Общий дамп тоже оставляю, потому что он нужен для других задач
/usr/bin/cat /mnt/backup/sql/"$(date +%Y-%m-%d)"-database01.sql | /usr/bin/awk '/LOCK TABLES `table01`/,/UNLOCK TABLES/' > /mnt/backup/sql/"$(date +%Y-%m-%d)"-table01.sql

# Сжимаю оба дампа
/usr/bin/gzip /mnt/backup/sql/"$(date +%Y-%m-%d)"-database01.sql
/usr/bin/gzip /mnt/backup/sql/"$(date +%Y-%m-%d)"-table01.sql

# Копирую дамп таблицы на второй сервер, аутентификация по ключам
/usr/bin/scp /mnt/backup/sql/"$(date +%Y-%m-%d)"-table01.sql.gz sshuser@10.20.30.45:/tmp

# Выполняю на втором сервере ряд заданий в рамках ssh сессии: распаковываю дамп таблицы, очищаю таблицу на этом сервере, заливаю туда данные из дампа
/usr/bin/ssh sshuser@10.20.30.45 '/usr/bin/gunzip /tmp/"$(date +%Y-%m-%d)"-table01.sql.gz && /usr/bin/mysql -e "delete from database01.table01; use database01; source /tmp/"$(date +%Y-%m-%d)"-table01.sql;"'

# Удаляю дамп
/usr/bin/ssh sshuser@10.20.30.45 'rm /tmp/"$(date +%Y-%m-%d)"-table01.sql'

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

Отдельно отмечу для тех, кто не в курсе, что можно вот так запросто тут же после подключения по ssh выполнять какие-то команды в автоматическом режиме. Это удобно и часто пригождается.

#mysql #bash #script

​​🎓 У хостера Selectel есть небольшая "академия", где в открытом доступе есть набор курсов. Они неплохого качества. Где-то по верхам в основном теория, а где-то полезные практические вещи. Я бы обратил внимание на два курса, которые показались наиболее полезными:

⇨ PostgreSQL для новичков
⇨ MySQL для новичков

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

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

#обучение #mysql #postgresql

​​Для быстрого доступа к СУБД Mysql очень удобно использовать небольшой скрипт Adminer, который представляет из себя ровно один php файл и запускается на любом хостинге. Я бы не писал о нём в очередной раз, если бы не столкнулся на днях с некоторыми нюансами при его использовании, так что решил оформить в заметку, чтобы самому потом не забыть.

С помощью Adminer можно создать пользователя и базу данных, назначить или изменить права, посмотреть содержимое таблиц, выгрузить или загрузить дамп. Лично мне для административных целей больше ничего и не надо. Разработчики, работая над сайтом, обычно просят phpmyadmin. Я не очень его люблю, потому что он монструозный, для него надо ставить дополнительные расширения php, поддерживать это дело.

Если я пользуюсь сам, то обычно просто закидываю adminer на сайт, делаю, что мне надо и потом сразу удаляю. В этот раз решил оставить на некоторое время и закрыть через basic_auth в Nginx (на самом деле в Angie). Вроде бы нет ничего проще:

# wget https://github.com/vrana/adminer/releases/download/v4.8.1/adminer-4.8.1-mysql-en.php
# mv adminer-4.8.1-mysql-en.php /var/www/html/adminer.php

Закрываем паролем. Создаём файл с учёткой:

# htpasswd -c /etc/nginx/.htpasswd admineruser21

Добавляем в Nginx:

location /adminer.php {
auth_basic "Administrator’s Area";
  auth_basic_user_file /etc/nginx/.htpasswd;
}

Перезапускаю Nginx, проверяю. Пароль не спрашивает, доступ прямой. Всё внимательно проверяю, ошибок нет. Я 100 раз это проделывал. Тут нет никаких нюансов. Но не работает.

Быстро догадался, в чём тут дело. В Nginx есть определённые правила обработки locations. Я с этой темой когда-то разбирался подробно и кое-что в памяти осело. Ниже для php файлов уже есть location:

location ~ \.php$ {
....
}

Он с регулярным выражением, поэтому обрабатывается раньше и при первом же совпадении поиск прекращается. Так что до моего location с паролем очередь просто не доходит. Сделать надо так:

location ~ adminer.php {
  auth_basic "Administrator’s Area";
  auth_basic_user_file /etc/nginx/.htpasswd;
.......................  
}

Тут надо указать все те же настройки, что у вас стоят для всех остальных php файлов в location ~ \.php$. И расположить location с adminer выше location для остальных php файлов. Это важно, так как судя по документации:

Затем nginx проверяет location’ы, заданные регулярными выражениями, в порядке их следования в конфигурационном файле. При первом же совпадении поиск прекращается и nginx использует совпавший location.

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

#webserver #nginx #mysql