​​Делюсь с вами простым и удобным скриптом для автоматического бэкапа mysql баз с помощью mysqldump - AutoMySQLBackup. Это обычный bash скрипт, который упрощает рутинные задачи по бэкапу, предлагая встроенный функционал.

https://github.com/sixhop/AutoMySQLBackup

Основные возможности скрипта:
▪ Сжимает бэкапы баз и раскладывает их по отдельным директориям.
▪ Поддерживает многопоточные архиваторы типа pigz, позволяет управлять количеством потоков.
▪ Автоматически ротирует дневные, недельные, месячные архивы по заданным параметрам.
▪ Позволяет использовать ключи mysqldump.
▪ Умеет хранить настройки в отдельном конфигурационном файле.
▪ Можно гибко управлять набором баз для бэкапа, вручную указывая их или управляя списком с помощью шаблонов.
▪ Умеет отправлять результаты своей работы по почте.
▪ Может шифровать дампы указанным ключом.
▪ Запуск своего скрипта до или после выполнения бэкапа.

Вот простой конфиг для ежедневного бэкапа баз mysql за исключением performance_schema и information_schema. Ежедневные бэкапы хранятся 7 дней, недельные 31 день, месячные год.

CONFIG_mysql_dump_username='root'
CONFIG_mysql_dump_password='parol'
CONFIG_mysql_dump_host='localhost'
CONFIG_backup_dir='/mnt/backup'
CONFIG_multicore='no'
CONFIG_db_names=()
CONFIG_db_exclude=( 'performance_schema' 'information_schema' )
CONFIG_db_exclude_pattern=()
CONFIG_do_monthly='01'
CONFIG_do_weekly='7'
CONFIG_rotation_daily=6
CONFIG_rotation_weekly=31
CONFIG_rotation_monthly=360

После работы сккрипта в директории /mnt/backup будет создана структура директорий:

◽ daily - ежедневные бэкапы, где каждая база будет в своей директории;
◽ fullschema - только структура всех баз данных;
◽ latest  - если указать соответствующую настройку, то в этой директории всегда будут лежать бэкапы от последнего запуска. Удобно отсюда их забирать куда-то в другое место сразу после окончания бэкапа.
◽ monthly - месячные бэкапы;
◽ status - список баз каждого запущенного бэкапа;
◽ tmp - для временных файлов;
◽ weekly - недельные бэкапы;

Рекомендую так же прочитать мою заметку про выбор параметров для mysqldump. Там есть важные моменты, которые влияют на успешность создания дампа.

#bash #mysql #backup

​​На неделе прослушал интересный доклад с HighLoad ++ 2021 - MySQL orchestrator, ProxySQL от Ситимобил. Тема актуальна только для очень нагруженных сервисов, где много MySQL серверов в кластере, но тем не менее мне понравилось, хоть и вряд ли когда-то пригодится. Качественный рассказ и построение повествования.

Авторы рассказали всю историю поддержки MySQL сервера с одиночного инстанса на старте проекта до довольно сложной структуры. Особенность их схемы в том, что они очень хотели оставить только один Master сервер, не выстраивая Master-Master репликацию. В итоге у них всё получилось. Есть один Master сервер, очень много Slave серверов. А у различных веб приложений свои персональные ProxySQL, которые сглаживают пики запросов, кэшируют и всячески оберегают Master от большой нагрузки.

Видео рекомендую для общего развития. Прошу заметить, что никакого Кубера, хотя им в конце прямо задали вопрос, как вы запускаете свои ProxySQL инстансы, в отдельных подах? Но нет, у них они на железе работают.

Видео - https://www.youtube.com/watch?v=YvbELUvqLm8
Презентация - https://drive.google.com/file/d/1zyC9JLiiRGHuKHmMzPAQu52B0XwIw4o7/view

#видео #mysql

​​Я давно уже привык использовать proxy nginx для http подключений. С ней удобно управлять запросами, распределять их на разные сервисы, кэшировать, логировать и мониторить в одном месте. Недавно познакомился с подобным инструментом, только для mysql подключений - ProxySQL.

Одной из полезных возможностей этой программы, помимо распределения подключений, является кэширования запросов. У Mysql серверов есть дефолтная возможность кэширования, но насколько я знаю, работает она либо неэффективно, либо никак. А в последних версиях её вроде бы вообще убрали. Специально не уточнял, но где-то видел в обзорах изменений.

Настроить и запустить в работу ProxySQL достаточно просто.

Сайт - https://proxysql.com/
Исходники - https://github.com/sysown/proxysql/

▶️ Интересные и полезные видео по теме, которые посмотрел сам и могу рекомендовать:
ProxySQL: быстрый кэш запросов в MySQL 8.0 и не только (тут прям конкретная настройка и примеры работы):
https://www.youtube.com/watch?v=QCylrIyiHwg
MySQL orchestrator, ProxySQL – это продукты, которые вам нужны (HighLoad ++, доклад):
https://www.youtube.com/watch?v=YvbELUvqLm8

#mysql

​​Посмотрел на днях очередной доклад с HighLoad ++ 2021, который показался полезным - Как правильно и надежно убить MySQL. Выступление построено в необычной манере, где рассказывают, как гарантированно сделать плохую конфигурацию сервера. Чтобы сделать нормально, надо делать наоборот.

Не могу сказать, что мне понравилось подобное повествование. Авторы явно хотели сделать оригинально, но лично мне воспринималось не очень. Приходилось на ходу в голове прикидывать постоянно, как делать правильно.

Тем не менее, выступление полезное, так как там конкретно указаны параметры, на которые надо обращать внимание при настройке Mysql сервера. Большую часть того, что они рассказывали я и так знал, поэтому и пишу эту рекомендацию.

Это одно из немногих выступлений, которые полезно не только тем, кто работает с Highload. Список параметров, на которые надо обращать внимание в первую очередь пригодится и для обычных баз данных. Например, того же Zabbix, если он у вас с Mysql работает по старой дружбе. Хотя сейчас уже пора переехать на PostgreSQL.

https://www.youtube.com/watch?v=CpBXc5995eg
Презентация

#mysql #видео

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

#remote - все, что касается удалённого управления компьютерами
#helpdesk - обзор helpdesk систем
#backup - софт для бэкапа и некоторые мои заметки по теме
#zabbix - всё, что касается системы мониторинга Zabbix
#мониторинг - в этот тэг иногда попадает Zabbix, но помимо него перечислено много различных систем мониторинга
#управление #ITSM - инструменты для управления инфраструктурой
#devops - в основном софт, который так или иначе связан с методологией devops
#kuber - небольшой цикл постов про работу с kubernetes
#chat - мои обзоры на популярные чат платформы, которые можно развернуть у себя
#бесплатно - в основном подборка всяких бесплатностей, немного бесплатных курсов
#сервис - сервисы, которые мне показались интересными и полезными
#security - заметки, так или иначе связанные с безопасностью
#webserver - всё, что касается веб серверов
#gateway - заметки на тему шлюзов
#mailserver - всё, что касается почтовых серверов
#elk - заметки по ELK Stack
#mikrotik - очень много заметок про Mikrotik
#proxmox - заметки о популярном гипервизоре Proxmox
#terminal - всё, что связано с работой в терминале
#bash - заметки с примерами полезных и не очень bash скриптов или каких-то команд. По просмотрам, комментариям, сохранениям самая популярная тематика канала.
#windows - всё, что касается системы Windows
#хостинг - немного информации и хостерах, в том числе о тех, кого использую сам
#vpn - заметки на тему VPN
#perfomance - анализ производительности сервера и профилирование нагрузки
#курсы - под этим тэгом заметки на тему курсов, которые я сам проходил, которые могу порекомендовать, а также некоторые бесплатные курсы
#игра - игры исключительно IT тематики, за редким исключением
#совет - мои советы на различные темы, в основном IT
#подборка - посты с компиляцией нескольких продуктов, объединённых одной тематикой
#отечественное - обзор софта из реестра отечественного ПО
#юмор - большое количество каких-то смешных вещей на тему IT, которые я скрупулезно выбирал, чтобы показать вам самое интересное. В самом начале есть шутки, которые придумывал сам, проводил конкурсы.
#мысли - мои рассуждения на различные темы, не только IT
#разное - этим тэгом маркирую то, что не подошло ни под какие другие, но при этом не хочется, чтобы материал терялся, так как я посчитал его полезным
#дети - информация на тему обучения и вовлечения в IT детей
#развитие_канала - серия постов на тему развития данного telegram канала

Остальные тэги публикую общим списком без комментариев, так как они про конкретный софт, понятный из названия тэга:
#docker #nginx #mysql #postgresql #gitlab #asterisk #openvpn #lxc #postfix #bitrix #икс #debian #hyperv #rsync #wordpress #zfs #grafana #iptables #prometheus #1с #waf #logs #netflow

​​Иногда возникает задача быстро удалить все таблицы в базе данных mysql, не удаляя саму базу данных. Доступ к базе через консоль. В общем случае удалить конкретную таблицу в базе db можно следующей командой:
> use db;
> drop table00 table01, table02;

Если таблиц немного, то можно поступить и так. Но если их много, то нужен какой-то другой способ. Можно изобрести или поискать какой-то готовый велосипед на bash. Я предлагаю вам свой 😁

Берём mysqldump и делаем дамп только структуры, добавляя информацию об удалении таблиц перед их созданием:
# mysqldump --add-drop-table --no-data db

Теперь получить консольные команды на удаление всех таблиц базы данных проще простого. Грепаем получившийся дамп:
# mysqldump --add-drop-table --no-data db | grep ^DROP
То есть выводим все строки, которые начинаются с DROP. Это как раз то, что нам нужно.

Далее можно либо взять только нужные строки с определёнными таблицами для удаления, либо сразу весь вывод отправить в консоль mysql и удалить все таблицы, оставив саму базу данных:
# mysqldump --add-drop-table --no-data db | grep ^DROP | mysql db

Не забудьте добавить авторизацию, если у вас она не настроена каким-то другим способом:
# mysqldump -uuser -ppassword --add-drop-table --no-data db \
| grep ^DROP | mysql -uuser -ppassword db

Таким простым способом, без скриптов, можно прямо в консоли сервера удалить все таблицы из базы данных mysql, не удаляя саму базу.

❓Может возникнуть вопрос, а почему не удалить всё же базу и не создать заново. Причин может быть несколько:

1️⃣ У вас нет прав на создание и удаление баз данных (наиболее частый случай).
2️⃣ Не помните точно параметры базы данных, не хочется вспоминать, искать, как создать новую базу данных с теми же параметрами, что стоят у текущей (мой случай).

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

#terminal #mysql

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

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

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

Потом заглянул в конфиги Mariadb и сначала не понял, а где вообще основная конфигурация. Всё пересмотрел и тут до меня дошло. Я забыл подтюнить СУБД под ресурсы сервера. В итоге MariaDB работала с дефолтными настройками. Если не ошибаюсь, там innodb_buffer_pool_size установлен в 128M. Это очень сильно влияет на производительность.

Скачал mysqltuner и выставил основные параметры в соответствии с объёмом оперативной памяти сервера. Этого оказалось достаточно, чтобы нормализовалась работа. Я этот тюнер использую как калькулятор. С его помощью удобно подобрать параметры innodb_buffer_pool_size и все сопутствующие настройки, а также буферы. И чтобы всё это не потребляло ресурсов больше, чем есть оперативной памяти под нужды СУБД. Если ошибиться, то можно словить OOM Killer, который будет прибивать базу данных.

Полезные ссылки по теме:
- Сравнение mysql vs mariadb
- На что обращать внимание при настройке Mysql
- Производительности Mysql сервера на файловой системе zfs
- индексы в Mysql
- Размещение субд в контейнерах

#mysql #website

​​Хочу поделиться с вами очень приятной находкой в виде бесплатной программы для бэкапа SQL баз и обычных файлов - SQLBackupAndFTP. Программа работает под Windows и на самом деле платная, но есть функциональная бесплатная версия с некоторыми ограничениями.

SQLBackupAndFTP умеет:
◽ бэкапить вручную и по расписанию базы данных MSSQL, MySQL, PostgreSQL
◽ складывать бэкапы на FTP, SFTP, FTPS, локальную или сетевую папку, Yandex.Disk
◽ отправлять уведомления на почту
◽ писать лог выполняемых действий
◽ автоматически удалять старые бэкапы
◽ работать через прокси
◽ подключаться к sql серверу и выполнять там sql команды

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

Я попробовал программу. Скачивается с сайта без всяких регистраций и других ужимок. Настройки очень простые и наглядные. Сама программа выглядит современно и удобно. Порадовала возможность снять бэкап mysql базы, подключившись к веб интерфейсу phpmyadmin. То есть не нужен прямой доступ к базе. Раньше нигде не видел такого функционала.

Программа оставила приятное впечатление. Если вам некритичны ограничения, то можно пользоваться. Уточню ещё раз, что вручную запускать бэкап можно неограниченного количества баз. Вы можете их добавить в интерфейс и запускать время от времени вручную по мере необходимости. Знаю людей, которые базы 1С вручную раз в неделю копируют на Яндекс.Диск. Эта программа может существенно упростить задачу, особенно людям, далёким от IT.

Сайт - https://sqlbackupandftp.com/

#backup #mysql #windows

Решил упростить себе задачу и подготовить список настроек, которые надо обязательно проверить при настройке одиночного mysql/mariadb сервера. Я не буду давать описания настроек и советовать какие-то значения, потому что это очень большой объём информации. Вы сами сможете её найти в интернете и подогнать под свою ситуацию.

Первое, что надо сделать - сбалансировать потребление памяти сервером. Не обязательно делать это вручную. Можно воспользоваться скриптом mysqltuner. Перечислю параметры Global + Thread, из которых складывается потребление памяти:

Global:
innodb_buffer_pool_size
innodb_log_file_size
key_buffer_size
innodb_log_buffer_size
query_cache_size
aria_pagecache_buffer_size
Эти значения просто суммируются.

Thread:
sort_buffer_size
join_buffer_size
read_buffer_size
read_rnd_buffer_size
max_allowed_packet
thread_stack
Эти значения суммируются и умножаются на max_connections.

Как я уже сказал, не обязательно их все править. Можно воспользоваться mysqltuner или оставить дефолтные значения, а вручную указать наиболее критичные - innodb_buffer_pool_size, max_connections. Остальные параметры mysqltuner подскажет, как подогнать под основные. Innodb_buffer_pool_size подбирают таким образом, чтобы с учётом всех остальных параметров суммарное потребление оперативной памяти не выходило за отведённые для Mysql Server пределы.

Обязательно проверяю:
bind-address = 127.0.0.1
Если не требуются подключения извне, привязываю к localhost.

Проверяю расположение логов и чаще всего сразу добавляю лог медленных запросов:
log_error = /var/log/mysql/error.log
slow_query_log
slow_query_log_file  = /var/log/mysql/slow.log
long_query_time = 2.0

Указываю нужную кодировку. Сейчас вроде бы везде utf8mb4 по умолчанию стоит, раньше utf8 ставили.
character-set-server = utf8mb4
collation-server   = utf8mb4_general_ci

Важные параметры, которые заметно влияют на производительность:
open_files_limit
table_open_cache 
Они привязаны к количеству соединений и таблиц в базе. Для того же Bitrix эти параметры имеют высокие значения и часто упираются в системные лимиты ОС для отдельного процесса. Их нужно тоже увеличить. Например вот так:
# mkdir /etc/systemd/system/mysqld.service.d
# touch limit.conf
Содержимое файла:
[Service]
LimitNOFILE=65535

Ещё один параметр, на который стоит обратить внимание:
innodb_temp_data_file_path
Он регулирует размер и рост файла с временным табличным пространством. Этот файл иногда может вырастать до огромных размеров и вызывать нехватку свободного места. Имеет смысл его сразу ограничить до разумных пределов. Вот пример ограничения размера в 2 ГБ и роста частями по 12 Мб
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2G

Это основное, на что я обращаю внимание. Более детальные настройки делаются, если возникают какие-то проблемы.

#mysql

​​Существуют разные подходы к мониторингу. Можно настраивать одну универсальную систему и всё замыкать на неё. Это удобно в том плане, что всё в одном месте. Но мониторинг каждого отдельного элемента будет не самым лучшим.

Другой подход - для каждого продукта использовать тот мониторинг, который заточен именно под него. Например, такой мониторинг есть для Nginx - NGINX Amplify. Или мониторинг для баз данных от Percona - Percona Monitoring and Management (PMM). Про последний я и хочу сегодня рассказать.

Percona Monitoring and Management - open source мониторинг для баз данных MySQL, PostgreSQL и MongoDB. Построен на базе своего PMM Server и PMM Agent. Визуализация метрик реализована через Grafana.

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

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

В принципе, если для вас важны базы данных, подобные мониторинги можно и отдельно разворачивать рядом с основным, а потом события со всех мониторингов собирать в одном месте. Например, с помощью OnCall.

Как я уже сказал, установить PMM очень просто. Можете сами оценить сложность и трудозатраты - Install Percona Monitoring and Management. Буквально 10 минут копипасты: ставим сервер, ставим клиент, соединяем клиента с сервером, добавляем в базу пользователя для сбора метрик. Если я правильно понял, то PMM построен на базе prometheus и использует метрики с его exporters.

Как всё это выглядит, можете посмотреть в публичном Demo. Там даже аутентификация не нужна. Помимо метрик баз данных PMM может собирать типовые метрики сервера Linux, HAProxy, выполнять внешние проверки tcp портов или забирать метрики по http.

Проект относительно свежий (2019 год) и очень активно развивается.

⇨ Сайт / Установка / Demo / Исходники

#мониторинг #mysql #postgresql

​​Решил для себя проработать ещё один документ от CIS на тему MySQL сервера. Взял версию 5.7, как наиболее универсальный вариант. Честно сказать, документ вообще не впечатлил. Показался набором очевидных банальностей. Ничего для себя оттуда не вынес, но так как потратил довольно много времени на прочтение и разбор, решил всё же сделать небольшую подборку для вас о тех вещах, что мне показались сколько-нибудь полезными.

📌 Mysql сервер должен запускать от непривилегированного пользователя без shell доступа к серверу. Все файлы и директории, с которыми работает сервер, должны принадлежать этому пользователю без доступа посторонних. Это же касается и лог файлов, особенно с ошибками.

📌 Если есть возможность использовать аутентификацию пользователей через unix сокет, оставьте только её, а остальные виды отключите. Речь идёт про плагин auth_socket для mysql или unix_socket для mariadb. Удалённые подключения к mysql серверу для безопасности стоит настроить по tls с помощью сертификатов.

📌 Обязательно ограничьте запуск сервера конкретным IP адресом, на котором он должен быть доступен. Например так:
bind_address=192.168.11.3
Если используется внешний IP адрес, необходимо ограничить к нему доступ на уровне firewall.

📌 Если не используете символьные ссылки в файлах, с которыми работает сервер, то отключите их поддержку в my.cnf:
skip-symbolic-links = YES

📌 Настройте ведение лога ошибок:
log-error = /var/log/mysql/error.log

📌 При работе с mysql сервером через консольный клиент следует избегать ситуаций, когда пароль пользователя используется непосредственно в команде и остаётся в системной истории команд. Я, кстати, и сам так попадал, и на других серверах видел пароли mysql пользователей в истории bash. Стоит избегать вот таких конструкций:
# mysql -u admin -p password
Либо интерактивно вводите пароль, либо храните в отдельном файле .my.cnf с ограничением доступа.

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

💡Необходимо устанавливать все обновления, удалить все тестовые данные, если они есть. Проверить всех пользователей и их права. Особенно обратить внимание на права SUPER, PROCESS, CREATE USER, GRANT OPTION. Они должны быть только у административных пользователей.

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

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

💡По возможности сервер должен быть изолирован от остальных служб на отдельной VPS или сервере.

#cis #mysql

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

Я покажу на примере системы Debian и СУБД MariaDB, как настроить аудит всех действий с сервером. Это будет полная инструкция, по которой копированием команд можно всё настроить.

Ставим сервер и выполняем начальную настройку:
# apt install mariadb-server
# mysql_secure_installation

Заходим в консоль Mysql:
# mysql -u root -p

Устанавливаем плагин:
> install plugin server_audit soname 'server_audit.so';
В Debian этот плагин присутствует по умолчанию. Проверить можно в директории /usr/lib/mysql/plugin наличие файла server_audit.so.

Смотрим информацию о плагине и его настройки:
> select * from information_schema.plugins
where plugin_name='SERVER_AUDIT'\G
> show global variables like 'server_audit%';

По умолчанию аудит в СУБД отключён. За это отвечает параметр server_audit_logging. Активируем его:
> set global server_audit_logging=on;

Теперь все действия на сервере СУБД будут записываться в лог файл, который по умолчанию будет создан в директории /var/lib/mysql в файле server_audit.log.

Записываться будут все действия для всех пользователей. Это можно изменить, задав явно параметр server_audit_events, который будет указывать, какие события записываем. Например так:
> set global server_audit_events='QUERY;
В этом параметре поддерживаются следующие значения: CONNECT, QUERY, TABLE, QUERY_DDL, QUERY_DML, QUERY_DCL, QUERY_DML_NO_SELECT.

Для пользователей можно настроить исключения. То есть указать, для какого пользователя мы не будем вести аудит. Список этих пользователей хранится в параметре server_audit_excl_users. Тут, в отличие от предыдущего параметра, мы указываем исключение. Управляется примерно так:
> set global server_audit_excl_users='user01';

Все подробности по настройке плагина есть в официальной документации.

Дальше этот лог можно передать куда-то на хранение. Например в elasticsearch. Для него есть готовые grok фильтры для разбора лога аудита в Ingest ноде или Logstash. Вот пример подобного фильтра. После этого можно будет настроить какие-то оповещения в зависимости от ваших потребностей. Например, при превышении каким-то пользователем числа подключений или слишком больше количество каких-то операций в единицу времени.

Максимально просто и быстро мониторинг на события можно настроить и в Zabbix. К примеру, сделать отдельные айтемы для строк только с конкретными запросами, например UPDATE или DELETE, или записывать только коды выхода, отличные от 0. Далее для них настроить триггеры на превышение средних значений в интервал времени на какую-то величину. Если происходят всплески по этим строкам, получите уведомление. Но сразу предупрежу, что Zabbix не очень для этого подходит. Только если событий немного. Иначе лог аудита будет занимать очень много места в базе данных Zabbix. Это плохая практика.

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

#mysql #security

​​Рекомендую очень полезный скрипт для Mysql, который помогает настраивать параметры сервера в зависимости от имеющейся памяти. Я уже неоднократно писал в заметках примерный алгоритм действий для этого. Подробности можно посмотреть в статье про настройку сервера под Битрикс в разделе про Mysql. Можно вот эту заметку посмотреть, где я частично эту же тему поднимаю.

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

Причём автор поддерживает этот скрипт. Я в апреле на одном из серверов заметил, что он даёт ошибку деления на ноль. Не стал разбираться, в чём там проблема. А сейчас зашёл и вижу, что автор внёс исправление как раз по этой части. Похоже, какое-то обновление Mysql сломало работу.

Вот прямая ссылка на код: mysql-stat.sh. Результат работы на картинке ниже. Добавить к нему нечего. Использовать так:

# ./mysql-stat.sh --user root --password "superpass"

Не забудьте поставить пробел перед командой, чтобы она вместе с паролем не залетела в history. Либо почистите её после работы скрипта, так как пробел не всегда работает. Зависит от настроек. Это при условии, что у вас парольное подключение к MySQL.

#bash #script #mysql

​​Вчера слушал вебинар Rebrain по поводу бэкапов MySQL. Мне казалось, что я в целом всё знаю по этой теме. Это так и оказалось, кроме одного маленького момента. Существует более продвинутый аналог mysqldump для создания логических бэкапов — mydumper. Решил сразу сделать про него заметку, чтобы не забыть и самому запомнить инструмент.

Как вы, наверное, знаете, существуют два типа бэкапов MySQL:

1️⃣ Логические, или дампы, как их ещё называют. Это текстовые данные в виде sql команд, которые при восстановлении просто выполняются на сервере. Их обычно делают с помощью утилиты Mysqldump, которая идёт в комплекте с MySQL сервером. Их отличает простота создания и проверки. Из минусов — бэкапы только полные, долго создаются и долго восстанавливаются. В какой-то момент, в зависимости от размера базы и возможностей железа, их делать становится практически невозможно. (моя статья по теме)

2️⃣ Бинарные бэкапы на уровне непосредственно файлов базы данных. Наиболее популярное бесплатное средство для создания таких бэкапов — Percona XtraBackup. Позволяет делать полные и инкрементные бэкапы баз данных и всего сервера СУБД в целом. (моя статья по теме)

Mysqldump делает бэкапы в один поток и на выходе создаёт один файл дампа. Можно это обойти, делая скриптом бэкап таблиц по отдельности, но при таком подходе могут быть проблемы с целостностью итоговой базы. Отдельно таблицу из полного дампа можно вытянуть с помощью sed или awk. Пример потабличного бэкапа базы или вытаскивания отдельной таблицы из полного дампа я делал в заметке.

Так вот, с чего я начал. Есть утилита MyDumper, которая работает чуть лучше Mysqldump. Она делает логический дамп базы данных в несколько потоков и сразу складывает его в отдельные файлы по таблицам. И при этом контролирует целостность итогового бэкапа базы данных. Восстановление такого бэкапа выполняется с помощью MyLoader и тоже в несколько потоков.

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

Я не знал про существовании этой утилиты, хотя почти все свои сервера MySQL бэкаплю в виде логических бэкапов Mysqldump. Надо будет переходить на MyDumper.

#mysql #backup

​​Вчера смотрел очень информативный вебинар Rebrain про оптимизацию запросов MySQL. Планирую сделать по нему заметку, когда запись появится в личном кабинете. Когда автор упомянул свой клиент MySQL Workbench, в чате начали присылать другие варианты клиентов. Я вспомнил, что несколько лет назад делал серию заметок по этой теме. Решил сформировать их в единый список. Думаю, будет полезно. С MySQL постоянно приходится работать в различных приложениях и сайтах.

🟢 MySQL клиенты в виде приложений:

▪ MySQL Workbench - самый навороченный, функциональный и тормозной клиент от авторов непосредственно СУБД MySQL. Если нужен максимум возможностей, то это про него. В большинстве случаев всё это не надо, если вы не разработчик.

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

▪ Dbeaver - отличается в первую очередь хорошей поддержкой PostgreSQL и часто используется именно для неё. Если нужно только MySQL, то лучше взять HeidiSQL.

🟡 MySQL клиенты в виде скриптов:

▪ PhpMyAdmin - думаю, не нуждается в представлении. Я почти всегда использую именно его. Его же обычно просят и разработчики. В публичный доступ не рекомендую его выставлять. Если хочется разово запустить, но не хочется настраивать веб сервер, то можно быстро запустить через встроенный в php веб сервер.

▪ Adminer - простенький php скрипт из одного файла. Очень удобно, если не хочется ничего настраивать, а нужно быстро зайти в базу и что-то там сделать. Как закончишь, скрипт можно просто удалить.

Тру админы наверняка начнут сейчас писать в комментариях, что не надо никаких внешних клиентов. Заходим консольным клиентом и делаем всё, что надо. Иногда я так делаю, но приходится вспоминать синтаксис, так как нужен он редко. Время тратится больше. Тот же adminer быстрее закинуть, чем вспомнить, как правильно написать запрос на создание пользователя и выставление для него прав на базу. Другое дело, если копипастишь откуда-то команды. Тогда можно и в консольный клиент.

#mysql #подборка

​​Я посмотрел два больших информативных вебинара про оптимизацию запросов в MySQL. Понятно, что в формате заметки невозможно раскрыть тему, поэтому я сделаю выжимку основных этапов и инструментов, которые используются. Кому нужна будет эта тема, сможет раскрутить её на основе этих вводных.

1️⃣ Включаем логирование запросов, всех или только медленных в зависимости от задач. В общем случае это делается примерно так:
log_error = /var/log/mysql/error.log
slow_query_log
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2.0
Для детального разбора нужны будут и более тонкие настройки.

2️⃣ Организуется, если нет, хотя бы базовый мониторинг MySQL, чтобы можно было как-то оценить результат и состояние сервера. Можно взять Zabbix, Percona Monitoring and Management, LPAR2RRD или что-то ещё.

3️⃣ Начинаем анализировать slow_query_log с помощью pt-query-digest из состава Percona Toolkit. Она выведет статистику по всем запросам, из которых один или несколько будут занимать большую часть времени работы СУБД. Возможно это будет вообще один единственный запрос, из-за которого тормозит весь сервер. Выбираем запросы и работаем дальше с ними. Уже здесь можно увидеть запрос от какого-то ненужного модуля, или какой-то забытой системы по собору статистики и т.д.

4️⃣ Если есть возможность, показываем запрос разработчикам или кому-то ещё, чтобы выполнили оптимизацию схемы БД: поработали с типами данных, индексами, внешними ключами, нормализацией и т.д. Если такой возможности нет, работаем с запросом дальше сами.

5️⃣ Смотрим план выполнения проблемного запроса, добавляя к нему в начало EXPLAIN и EXPLAIN ANALYZE. Можно воспользоваться визуализацией плана в MySQL Workbench. Если нет специальных знаний по анализу запросов, то кроме добавления индекса в какое-то место вряд ли что-то получится сделать. Если знания есть, то скорее всего и этот материал вам не нужен. Про индексы у меня была отдельная заметка. Понимая, как работают индексы, и глядя на медленные места запроса, где нет индекса, можно попробовать добавить туда индекс и оценить результат. Отдельно отмечу, что если у вас в запросе есть где-то полное сканирование большой таблицы, то это плохо. Этого нужно стараться избегать в том числе с помощью индексов.

6️⃣ После того, как закончите с запросами, проанализируйте в целом индексы в базе с помощью pt-duplicate-key-checker. Она покажет дубликаты индексов и внешних ключей. Если база большая и имеет много составных индексов, то вероятность появления дубликатов индексов немалая. А лишние индексы увеличивают количество записей на диск и снижают в целом производительность СУБД.

7️⃣ Оцените результат своей работы в мониторинге. Соберите ещё раз лог медленных запросов и оцените изменения, если они есть.

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

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

Я лично не раз с этим сталкивался. Где-то даже команду поменяли, потому что они не могли обеспечить нормальную производительность сайта. Другие пришли и всё сделали быстро, потому что банально разбирались, как это делается. А если разработчик не может, то ничего не поделать. И все будут думать, что это сервер тормозит, если вы не докажете обратное.

#mysql #perfomance

​​Для мониторинга загрузки сервера MySQL в режиме реального времени есть старый и известный инструмент - Mytop. Из названия понятно, что это топоподобная консольная программа. С её помощью можно смотреть какие пользователи и какие запросы отправляют к СУБД. Как минимум, это удобнее и нагляднее, чем смотреть show full processlist;. Я обычно туда первым делом заглядываю, если на сервере намечаются какие-то проблемы.

Когда попытался установить mytop на Debian 11, удивился, не обнаружив её в репозиториях. Обычно жила там. Немного погуглил и понял, что отдельный пакет mytop убрали, потому что теперь он входит в состав mariadb-client. Если вы используете mariadb, то mytop у вас скорее всего уже установлен. Если у вас другая система, то попробуйте установить mytop через пакетный менеджер. В rpm дистрибутивах она точно была раньше в репах. В Freebsd, кстати, тоже есть.

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

Mytop можно запустить, передав все параметры через ключи, либо создать файл конфигурации ~/.mytop (не забудьте ограничить к нему доступ):

user=root
pass=password
host=127.0.0.1
port=3306
color=1
delay=3
idle=0

Обращаю внимание, что если укажите localhost вместо 127.0.0.1, то скорее всего не подключитесь. Вообще, это распространённая проблема, так что я стараюсь всегда и везде писать именно адрес, а не имя хоста. Локалхост часто отвечает по ipv6, и не весь софт корректно это отрабатывает. Лучше явно прописывать 127.0.0.1.

Теперь можно запускать mytop и смотреть информацию по всем базам. Если набрать ?, то откроется справка. Там можно увидеть все возможности программы. Наиболее актуальны:
◽d - выбрать конкретную базу данных
◽p - остановить обновление информации на экране
◽i - отобразить соединения в статусе Sleep
◽V - посмотреть параметры СУБД

Хорошая программа в копилку тех, кто работает с MySQL, наряду с:
🔹 mysqltuner
🔹 MyDumper

Ещё полезные ссылки по теме:
▪ Оптимизация запросов в MySQL
▪ MySQL клиенты
▪ Скрипт для оптимизации потребления памяти
▪ Аудит запросов и прочих действия в СУБД
▪ Рекомендации CIS по настройке MySQL
▪ Список настроек, обязательных к проверке
▪ Сравнение mysql vs mariadb
▪ Индексы в Mysql
▪ Полный и инкрементный бэкап MySQL
▪ Мониторинг MySQL с помощью Zabbix

#mysql

​​Несколько полезных команд для консоли клиента mysql из практики, которыми пользуюсь сам.

📌 Посмотреть размер баз данных на сервере с сортировкой по размеру в мегабайтах:

SELECT table_schema "DB Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "Size in MB" FROM information_schema.tables GROUP BY table_schema ORDER BY `Size in MB` DESC;

📌 Посмотреть размер конкретной базы данных:

SELECT table_schema `Database`, round(Sum(data_length + index_length) / 1024 / 1024, 1) `Size in MB` FROM information_schema.TABLES WHERE table_schema = "имя базы";

📌 Посмотреть размер самых больших таблиц в базе данных:

SELECT table_name `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = "имя базы" ORDER BY `Size in MB` DESC LIMIT 10;

📌 Удалить все таблицы в базе данных, не удаляя саму базу. То есть полная очистка базы без удаления:

SELECT CONCAT ('DROP TABLE ',GROUP_CONCAT(TABLE_NAME),';') INTO @qery FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = "имя базы"; PREPARE drop_tables FROM @qery; EXECUTE drop_tables;

Последнюю команду посоветовали в комментариях к заметке, где я то же самое делаю своими костылями на bash. На самом деле эта, казалось бы простая задача, на деле не такая уж и простая. Я себе сохранил предложенный вариант. Он удобнее и проще моего.

Сами понимаете, что это идеальная заметка для того, чтобы её сохранить и использовать, когда будете работать с mysql.

#mysql