В этой статье я рассмотрю особенности установки, базовой настройки и оптимизации сервера баз данных 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 .
