Инструкция по установке MariaDB на CentOS

В этой статье я рассмотрю особенности установки, базовой настройки и оптимизации сервера баз данных MariaDB. Передо мной стояла задача установитьc MariaDB и провести базовую настройку на Linux CentOS 7. Так как это актуальный вопрос, я надеюсь статья будет вам полезна. В конце статьи я приведу несколько примеров конфигурационных файлов, а вы с помощью их сможете подобрать параметры под свой сервер.

В этой инструкции:

• Установка MariaDB в CentOS
• Защита и безопасность MariaDB
• Проверка соединения с сервером MariaDB
• Настройка конфигурационного файла MariaDB
• Оптимизация и тюнинг производительности MariaDB
• Основные команды для работы в консоли MariaDB

Установка MariaDB на CentOS

С недавнего времени MariaDB входит в стандартный для CentOS 7 репозиторий base, но в данном репозитории содержится версия 5.5. Эта версия уже не актуальна, в ней есть проблемы с производительностью и нет полнотекстового поиска в InnoDB. На момент написания статьи актуальная версия MariaDB — 10.4, поэтому подключившись к нашему Linux серверу по ssh мы для начала подключим репозиторий разработчика mariadb.org и после запустим установку сервера БД.

Я привык работать с файлами через редактор nano, устанавливаем его через yum:

yum install nano -y

И открываем файл репозитория для редактирования:

nano /etc/yum.repos.d/mariadb.repo

И добавляем туда следующую информацию:

Сохраняем файл и запускаем установку:

yum install MariaDB-server MariaDB-client -y

Установка окончена, теперь нужно добавить сервис в автозагрузку и запустить:

systemctl start mariadb
systemctl enable mariadb

Проверяем статус сервиса:

systemctl status mariadb

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

Защита и безопасность MariaDB

После того, как мы установили и запустили MariaDB, можем перейти к настройкам безопасности. Запускаем встроенный сценарий:

/usr/bin/mysql_secure_installation

Данный сценарий подробно описывает каждый шаг и подробно останавливаться на каждом из них мы не будем. Сначала он запросит root-пароль, но после начальной установки его нет и поэтому мы нажимаем enter и на следующем этапе задаем сложный пароль (от этого зависит насколько безопасным, будет ваш сервер). После можно до конца выполнения сценария нажимать «enter», в процессе будут удалены анонимные пользователи, удаленный root-логин, а также тестовые таблицы и будут перезагружены привилегии.

Для подключения к mariadb серверу нужно создать правила в Linux файерволе с помощью iptables:

iptables -I INPUT -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables-save > /etc/sysconfig/iptables

Опционально, можно разрешить исходящие подключения к другим базам MariaDB.

iptables -I OUTPUT -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT

Проверка соединения с сервером MariaDB

Нужно убедиться, что установка MariaDB выполнена успешно.

Подключимся к серверу БД с помощью встроенного инструмента mysqladmin:

mysqladmin version

Команда выдаст результат:

Это означает, что установка MariaDB выполнена успешно, база данных работает и доступна.

Можно подключиться к консоли сервера maridb для интерактивного выполнения sql команд:

mysql -u root -p

Настройка конфигурационного файла MariaDB

Обычно после установки MariaDB я добавляю в конфигурационный файл /etc/my.cnf свою стандартную конфигурацию, которая работает на большем количестве серверов и пока проблем с базами не возникало. Очистим файл my.cnf и добавим в него следующее:

Для общего понимания разберем основные параметры конфигурационного файла my.cnf:

• datadir — каталог для хранения файлов БД;
• tmpdir – каталог для хранения временных файлов;
• skip-name-resolve – отключает DNS резолвинг;
• max_allowed_packet — максимальный размер пакета. Если в БД используются поля blob, нужно выставлять значение не менее чем самое большое поле;
• max_connections — максимальное количество открытых соединений, параметр определяет, сколько клиентов одновременно могут работать с сервером БД;
• Секция # Cache parameters — все что связано с кешем запросов, устанавливать слишком высокие значения не рекомендуется, так как потребление ресурсов сервером БД станет расти;
• Секция # InnoDB parameters — все что связано с таблицами innodb;
• innodb_buffer_pool_size — буфер кеша для данных и индексов, если на сервере размещено 1-2 проекта, выставляйте значение равное 70-80% доступной оперативной памяти;
• innodb_flush_method — для Linux ставим значение O_DIRECT , это отключит кеширование на уровне ОС;
• innodb_flush_log_at_trx_commit — этот параметр влияет на скорость записи innoDB таблиц. Отнеситесь серьезно к данному параметру, выставляя значение 0, вы получаете большую производительность, но риск потери данных возрастает. Я предпочитаю устанавливать значение 2, так как большой прирост в работе сервера БД я не замечал, а безопасность превыше всего.

Оптимизация и тюнинг производительности MariaDB

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

Установим скрипт Tuning-Primer.sh:

yum install bc net-tools -y

Скачиваем скрипт:

wget https://launchpadlibrarian.net/78745738/tuning-primer.sh

Даем права:

chmod +x tuning-primer.sh

Запускаем:

./tuning-primer.sh

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

Например, у меня скрипт сразу же выдал, что используемое количество коннектов гораздо меньше, чем я выставил:

Данный параметр можно изменить, как и в файле my.cnf так и через консоль, я убавил до 10 и скрипт меня похвалил:

С помощью подобных манипуляций можно привести к идеалу работу вашего сервера БД.

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

К моему конфигурационному файлу, можно добавить секцию для активации журнала медленных запросов, это поможет вам анализировать работу ваших проектов. В секцию «mysqld» добавляем:

slow_query_log = 1 # включаем лог медленных запросов
long_query_time = 5 # устанавливаем время в секундах
slow_query_log_file = /var/log/slow-query.log # имя и путь к лог файлу медленных запросов
log_queries_not_using_indexes # записывать ли в лог файл запросы, которые не используют индексы

Так же нужно создать лог файлы, для ошибок работы MariaDB и журнала медленных запросов:

touch /var/log/mysql-error.log
touch /var/log/slow-query.log

Делаем рестарт сервиса:

systemctl restart mariadb

Просмотреть журнал медленных запросов можно открыв файл /var/log/slow-query.log или же проверять его в режиме реального времени:

tail -f /var/log/slow-query.log

Таким образом вы можете анализировать запросы к БД вашего проекта и на основе этого проводить аудит.

После любых изменений настроек БД нужно проверять конфигурацию на наличие ошибок:

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

systemctl status mariadb -l

Основные команды для работы в консоли MariaDB

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

Для локального входа в консоль MariaDB, выполните:

mysql -u root -p

Для удаленного подключения к серверу БД MariaDB:

mysql -u root -p -h 10.1.1.20

Полезные команды:

create database db1; — создать БД с именем db1

show databases; — вывести список созданных БД

use db1; — войти в БД с именем db1

show tables; — вывести таблицы БД в которой мы находимся

create user 'test'@'localhost' identified by '123456'; — создать пользователя test и задать пароль 123456(используйте более сложные пароли)

grant all privileges on database_name.* to 'test'@'localhost'; — даем полные права пользователю test

flush privileges; — обновляем все привилегии

show processlist; — посмотреть активные соединения в БД или же можно использовать команду:

show status where `variable_name` = 'Threads_connected';

Так же из консоли mysql можно просматривать или изменять параметры, например:

SHOW VARIABLES LIKE 'max_error_count';

Изменить:

SET max_error_count=256;

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