​​Если вам нужно точно знать, кто и что делает в базе данных 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

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

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

> SET GLOBAL general_log_file = '/var/log/mysql/mysql.log';
> SET GLOBAL general_log = 'ON';

В файле mysql.log будет фиксироваться время запроса и сам запрос. Адрес клиента не увидим. Для того, чтобы фиксировать адрес клиента, хранить лог запросов нужно будет в в отдельной таблице mysql.general_log. Это уже будет немного другая настройка, для которой понадобится перезапуск. Но заметку я хотел написать не об этом.

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

Запускаем на любой машине, через которую проходит трафик к MySQL серверу. Можно прямо на нём:

#  tcpdump -i ens18 port 3306 -s 1500 -w tcpdump.out

Ждём, когда посыпятся запросы. Можно с соседней машины подключиться и позадавать их:

# mysql -h 10.20.1.36 -u user01 -p
> use database;
> select * from users;

В файле tcpdump.out будет собран raw трафик в формате pcap. Распарсить его можно тут же в консоли с помощью tshark:

# apt install tshark
# tshark -r tcpdump.out -d tcp.port==3306,mysql -T fields -e mysql.query > query_log.out

Получится файл, где всё содержимое будет удалено, кроме mysql запросов. Но при этом останутся пустые строки. Чистим их:

# sed -i '/^$/d' query_log.out

Можно поступить немного проще, преобразовав трафик сразу в обычные строки с помощью stdbuf и strings, а потом уже грепнуть по нужным запросам:

# tcpdump -i ens18 -s 0 -U -w - dst port 3306 | stdbuf -i0 -o0 -e0 strings | grep -iE --line-buffered "(SELECT|UPDATE|INSERT|DELETE|SET|SHOW|COMMIT|ROLLBACK|CREATE|DROP|ALTER)"

Если нужны метки времени, то добавляем с помощью awk:

# tcpdump -i ens18 -s 0 -U -w - dst port 3306 | stdbuf -i0 -o0 -e0 strings | grep -iE --line-buffered "(SELECT|UPDATE|INSERT|DELETE|SET|SHOW|COMMIT|ROLLBACK|CREATE|DROP|ALTER)" | awk -W interactive '{print strftime("%F %T")" "$0}'

Если хочется привязать запросы к IP адресам клиентов, то задача усложняется. Можно вывести содержимое пакетов вместе с остальной информацией, в том числе и об адресате пакета:

# tcpdump -i ens18 -s 0 -A -Q in port 3306

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

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

#mysql #tcpdump

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

❓У вас непрогнозируемо и очень быстро растёт размер базы данных. Что вы будете экстренно предпринимать в связи с этим?

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

1️⃣ Первым делом я бы зашёл на сервере в консоль, открыл mysql клиент и запустил там:

> SHOW FULL PROCESSLIST;
или так, чтобы было удобнее смотреть:
# mysql -e "SHOW FULL PROCESSLIST;" > ~/processlist.txt

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

Так как у нас стоит вопрос о росте базы данных, имеет смысл отфильтровать запросы по типу, оставив только UPDATE и INSERT. По идее именно они загружают данные в таблицы:

> SELECT * FROM information_schema.processlist WHERE `INFO` LIKE 'UPDATE %';
> SELECT * FROM information_schema.processlist WHERE `INFO` LIKE 'INSERT %';

2️⃣ Для более детального просмотра активности в режиме реального времени можно запустить Mytop и посмотреть информацию там. Можно быстро фильтрануть по клиентам и базам данных, чтобы быстрее определить аномалии.

3️⃣ Параллельно я бы посмотрел размер таблиц. Можно просто отсортировать файлы по размеру в директории /var/lib/mysql/database_name. В MySQL чаще всего каждая таблица в отдельном файле. Либо можно зайти клиентом и посмотреть там:

> SELECT table_schema as `Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = "database_name" ORDER BY (data_length + index_length) DESC;

У меня есть отдельная заметка по таким прикладным запросам.

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

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

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

В памяти всплыло ещё то, что размер базы раздувают индексы. Возможно кто-то их лепит на автомате, что приводит к росту базы. По идее это всё тоже будет видно в списке процессов как запросы с CREATE INDEX. Проблемного клиента можно отключить, индексы удалить, базу оптимизировать. Но тут я уже темой не владею. Не знаю, сколько и как этих индексов можно насоздавать и реально ли это может привести к лавинообразному росту объема занимаемого пространства на диске.

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

#mysql