У цій статті я розгляну особливості установки, базової налаштування і оптимізації сервера баз даних 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
І додаємо туди наступну інформацію:
[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 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Server version 10.4.7-MariaDB Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 11 min 35 sec 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] local-infile = 0 datadir = / var / lib / mysql socket = / var / lib / mysql / mysql.sock symbolic-links = 0 max_allowed_packet = 128M sql_mode = "" log-error = / var / log / mysql -error.log # Cache parameters query_cache_size = 16M table_open_cache = 4096 thread_cache_size = 16 key_buffer_size = 8M thread_stack = 256K join_buffer_size = 2M sort_buffer_size = 2M # Parameters for temporary tables tmpdir = / tmp max_heap_table_size = 32M tmp_table_size = 32M # InnoDB parameters innodb_file_per_table innodb_buffer_pool_size = 32M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_use_native_aio = 0 transaction-isolation = READ-COMMITTED character-set-server = utf8 collation-server = utf8_unicode_ci init-connect = "SET NAMES utf8 COLLATE utf8_unicode_ci" skip-name-resolve [mysqldump] quick quote- names max_allowed_packet = 128M default-character-set = utf8 [mysql] [isamchk] key_buffer = 16M [mysqld_safe] log-error = / var / log / mariadb / mariadb.log pid-file = / var / run / mariadb / mariadb. pid
Для загального розуміння розберемо основні параметри конфігураційного файлу 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;
У висновку хотілося б сказати, що при налаштуванні сервера БД потрібно спиратися на проекти які будуть розміщуватися на даному сервері. Те, що може бути застосовано для дрібних проектів, в корені може не підійти для великого проекту. Пробуйте, експериментуйте і найважливіше завжди заглядайте в логи. У наступній статті ми покажемо як організувати високу доступність і збільшити продуктивність додатків за рахунок ісопльзованія реплікації між серверами в MariDB.
