​​Хочу поделиться с вами очень приятной находкой в виде бесплатной программы для бэкапа 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

На днях столкнулся с ошибкой в эксплуатации и настройке 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

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

Сразу покажу на примере, как это работает. Существует популярная open source утилита для этих целей - migrate. Она поддерживает все наиболее распространённые СУБД. Миграции можно выполнять с помощью готовой библиотеки на Go, либо в консоли через CLI. Я буду использовать CLI, СУБД PostgreSQL и ОС Debian 12.

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

# wget https://github.com/golang-migrate/migrate/releases/download/v4.18.1/migrate.linux-amd64.deb
# dpkg -i migrate.linux-amd64.deb

Для удобства добавим строку на подключение к локальной базе данных в переменную:

# export POSTGRESQL_URL='postgres://postgres:pass123@localhost:5432/test_migrations?sslmode=disable'

Я подключаюсь без ssl к базе данных test_migrations под учёткой postgres с паролем pass123. С рабочей базой так делать не надо, чтобы пароль не улетел в history.

Принцип работы migrate в том, что создаются 2 файла с sql командами. Первый файл выполняется, когда мы применяем обновление, а второй - когда мы откатываем. В своём примере я добавлю таблицу test01 с простой структурой, а потом удалю её в случае отката.

# cd ~ && mkdir migrations
# migrate create -ext sql -dir migrations -seq create_test01_table
~/migrations/000001_create_test01_table.up.sql
~/migrations/000001_create_test01_table.down.sql

В директории были созданы 2 файла. В первый файл с up в имени добавим sql код создания таблицы test01:

CREATE TABLE IF NOT EXISTS test01(
  user_id serial PRIMARY KEY,
  username VARCHAR (50) UNIQUE NOT NULL,
  password VARCHAR (50) NOT NULL,
  email VARCHAR (300) UNIQUE NOT NULL
);

А во второй с down - удаление:

DROP TABLE IF EXISTS test01;

Проверим, как это работает:

# migrate -database ${POSTGRESQL_URL} -path migrations up
1/u create_test01_table (24.160815ms)

Смотрим, появилась ли таблица:

# psql ${POSTGRESQL_URL} -c "\d test01"

Вы должны увидеть структуру таблицы test01. Теперь откатим наше изменение:

# migrate -database ${POSTGRESQL_URL} -path migrations down
Are you sure you want to apply all down migrations? [y/N]
y
Applying all down migrations
1/d create_test01_table (15.851045ms)

Проверяем:

# psql ${POSTGRESQL_URL} -c "\d test01"
Did not find any relation named "test01".

Таблицы нет. Принцип тут простой - пишем SQL код, который исполняем. На деле, конечно, изменения бывают сложные, особенно когда не добавляются или удаляются таблицы, а меняется структура существующих таблиц с данными. Инструменты типа migrate позволяют описать все изменения и проработать процесс обновления/отката в тестовых средах. В простых случаях можно обойтись и своими bash скриптами, но migrate упрощает эту задачу, так как, во-первых поддерживает множество СУБД. Во-вторых, автоматически нумерует миграции и исполняет последовательно. В-третьих, может, к примеру, забирать миграции напрямую из git репозитория.

Для каждой СУБД в репозитории Migrate есть примеры настройки миграций.

❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.

⇨ Исходники

#postgresql #mysql

Провозился вчера некоторое время над банальной задачей. Нужно было добавить нового пользователя в mysql базу через консольный клиент. Задача простая, но я реально запутался в версиях mysql и используемых плагинах аутентификации. У меня была версия Mariadb 10.11, нужна была аутентификация по сети по паролю, а не через сокет.

Открыл свою шпаргалку, нашёл команду:

> ALTER USER 'zbx_monitor'@'10.30.52.9' IDENTIFIED WITH mysql_native_password BY 'XkRy1bRRgLIB';
ERROR 1064 (42000): You have an error in your SQL syntax;

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

> CREATE USER 'zbx_monitor'@'10.30.52.9' IDENTIFIED VIA mysql_native_password;
Query OK, 0 rows affected (0.075 sec)

Нормально, пользователя создал. Пробую ещё раз первую команду, чтобы задать пароль:

> ALTER USER 'zbx_monitor'@'10.30.52.9' IDENTIFIED WITH mysql_native_password BY 'XkRy1bRRgLIB';
ERROR 1064 (42000): You have an error in your SQL syntax;

Опять не работает. Перебираю разные варианты и нахожу рабочий:

> ALTER USER 'zbx_monitor'@'10.30.52.9' IDENTIFIED BY 'XkRy1bRRgLIB';
Query OK, 0 rows affected (0.114 sec)

Честно говоря не понял, почему мой исходный вариант не сработал. Раньше точно работал, сейчас перестал. С MySQL в этом плане сейчас геморно стало. Проще сразу поставить adminer или phpmyadmin. Из-за того, что у mysql появилось несколько форков, плюс разные плагины аутентификации: unix_socket и mysql_native_password, комбинации команд по управлению пользователями стали отличаться в разных версиях. Это создаёт неудобства и только тратит время на поиск работающего варианта.

Я в основном с MariaDB работаю. Вот небольшая шпаргалка для консольного клиента этой СУБД.

📌 Первичная настройка сразу после установки, где можно выбрать тип плагин для аутентификации и задать пароль root:

# mysql_secure_installation

📌 Создание базы данных:

> CREATE DATABASE zabbix CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

📌 Создание пользователя с паролем:

> CREATE USER 'zabbix'@'10.30.52.9' IDENTIFIED VIA mysql_native_password;
> ALTER USER 'zabbix'@'10.30.52.9' IDENTIFIED BY 'PassWORD123';

📌 Назначаем пользователю права на базу данных (полные и выборочные):

> GRANT ALL PRIVILEGES on zabbix.* to 'zabbix'@'10.30.52.9';
> GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'10.30.52.9';

📌 Список пользователей и их прав:

> SELECT user,authentication_string,plugin,host FROM mysql.user;

📌 Загрузить дамп в базу zabbix:

> USE zabbix;
> SOURCE my_dump.sql;
или
> \. my_dump.sql;

📌 Удалить базу:

> DROP DATABASE zabbix;

📌 Список активных соединений:

> SHOW FULL PROCESSLIST;

📌 Посмотреть значение конкретного параметра (innodb_buffer_pool_size):

> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

📌 Изменение параметра (max_connections):

> SET GLOBAL max_connections = 200;

Эти команды покрывают 95% моих задач, которые приходится выполнять в консольном клиенте mysql. Есть ещё несколько, касающихся репликации, но это отдельная тема.

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

❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.

#mysql