​​Я давно уже привык использовать 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