Установка и базовая оптимизация MariaDB в CentOS

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

Установка 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

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

[mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.4/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 

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

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

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

mysqladmin Ver 9.1 Distrib 10.4.7-MariaDB, for Linux on x86_64_x000D_Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others._x000D_Server version 10.4.7-MariaDB_x000D_Protocol version 10_x000D_Connection Localhost via UNIX socket_x000D_UNIX socket /var/lib/mysql/mysql.sock_x000D_Uptime: 11 min 35 sec_x000D_Threads: 7 Questions: 26 Slow queries: 0 Opens: 20 Flush tables: 1 Open tab

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

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

mysql -u root -p

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

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

[mysqld]_x000D_local-infile=0_x000D_datadir=/var/lib/mysql_x000D_socket=/var/lib/mysql/mysql.sock_x000D_ symbolic-links=0_x000D_max_allowed_packet = 128M_x000D_sql_mode = ""_x000D_log-error = /var/log/mysql-error.log_x000D__x000D_# Cache parameters_x000D_query_cache_size = 16M_x000D_table_open_cache = 4096_x000D_thread_cache_size = 16_x000D_key_buffer_size = 8M_x000D_thread_stack = 256K_x000D_join_buffer_size = 2M_x000D_sort_buffer_size = 2M_x000D__x000D_# Parameters for temporary tables_x000D_tmpdir = /tmp_x000D_max_heap_table_size = 32M_x000D_tmp_table_size = 32M_x000D__x000D_# InnoDB parameters_x000D_innodb_file_per_table_x000D_innodb_buffer_pool_size = 32M_x000D_innodb_flush_log_at_trx_commit = 2_x000D_innodb_flush_method = O_DIRECT_x000D_innodb_use_native_aio = 0_x000D_transaction-isolation = READ-COMMITTED_x000D_character-set-server = utf8_x000D_collation-server = utf8_unicode_ci_x000D_init-connect = "SET NAMES utf8 COLLATE utf8_unicode_ci"_x000D_skip-name-resolve_x000D__x000D_[mysqldump]_x000D_quick_x000D_quote-names_x000D_max_allowed_packet = 128M_x000D_default-character-set = utf8_x000D__x000D_[mysql]_x000D__x000D_[isamchk]_x000D_key_buffer = 16M_x000D__x000D_[mysqld_safe]_x000D_log-error=/var/log/mariadb/mariadb.log_x000D_pid-file=/var/run/mariadb/mariadb.pid_x000D_

Для общего понимания разберем основные параметры конфигурационного файла 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 # включаем лог медленных запросов_x000D_long_query_time = 5 # устанавливаем время в секундах_x000D_slow_query_log_file = /var/log/slow-query.log # имя и путь к лог файлу медленных запросов_x000D_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;

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