Повний цикл адміністрування MySQL та MariaDB для веб-проектів
Уявіть: сайт на WordPress падає при 1000 одночасних відвідувачів, хоча сервер потужний. Швидше за все, проблема в MySQL — не налаштований InnoDB buffer pool, немає індексів на часті запити, binary log забив диск. Такі сценарії зустрічаються повсюдно: типовий проект втрачає до 30% продуктивності через неоптимальну конфігурацію. Ми вирішуємо ці завдання щодня, маючи за плечима понад 50 успішних проектів з адміністрування MySQL та MariaDB. Гарантуємо стабільну роботу та приріст продуктивності на 20-50% після налаштування.
Проблеми, які ми вирішуємо
Фрагментація таблиць. MyISAM без регулярного OPTIMIZE гальмує вибірки. InnoDB теж фрагментується після частих UPDATE/DELETE. Розмір бази зростає, а продуктивність падає. Повільні запити. Відсутність індексів, N+1 запитів у додатку, неоптимальний JOIN без ключів. Реплікація. Налаштування master-replica без контролю лагу веде до розсинхронізації. Переповнення binary log без ротації займає весь диск — сервер зупиняється. Балансування навантаження. Без ProxySQL читання та запис йдуть на один сервер, створюючи вузьке місце.
Як ми це робимо
Початковий аудит
-- Оцінка стану: розмір баз, фрагментація, повільні запити SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size_mb FROM information_schema.tables GROUP BY table_schema ORDER BY size_mb DESC; Потім аналізуємо slow_query_log та config-файли. На основі даних підбираємо параметри. Наприклад, для бази розміром 50 ГБ з високим навантаженням на запис збільшуємо innodb_log_file_size до 1 ГБ, що скорочує час checkpoint на 40%.
Оптимізація InnoDB
Ключовий параметр — innodb_buffer_pool_size. Для виділеного сервера виділяємо 70-80% RAM. Розмір лог-файлів збільшуємо до 512 МБ — це зменшує частоту checkpoint і прискорює запис. Файл на таблицю (innodb_file_per_table = ON) спрощує бекап та оптимізацію. В результаті типова база на 100 ГБ отримує приріст швидкості запитів на 30-60%.
[mysqld] innodb_buffer_pool_size = 4G innodb_buffer_pool_instances = 4 innodb_log_file_size = 512M innodb_log_buffer_size = 64M innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = ON innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_io_capacity = 2000 innodb_io_capacity_max = 4000 Як ми оптимізуємо базу даних: крок за кроком
- Аудит поточної конфігурації та продуктивності.
- Аналіз повільних запитів та індексів (виявляємо до 90% неоптимальних запитів).
- Налаштування параметрів InnoDB (buffer pool, log file, IO threads).
- Налаштування реплікації master-replica з контролем лагу (цільовий лаг < 1 сек).
- Налаштування автоматичних бекапів (mysqldump для малих баз, XtraBackup для великих).
- Впровадження моніторингу та алертів (Prometheus + Grafana, ProxySQL для балансування).
- Документування конфігурації та навчання вашого адміністратора.
Чому важлива реплікація?
Реплікація master-replica дає відмовостійкість: при відмові майстра перемикаємося на репліку. Налаштовуємо її так, щоб лаг не перевищував 1 секунди. Використовуємо ROW-формат binary log — він безпечніший за STATEMENT для додатків зі збереженими процедурами. Приклад налаштування:
-- На майстрі CREATE USER 'replication'@'%' IDENTIFIED BY 'strong_password'; GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%'; SHOW MASTER STATUS; -- На репліці CHANGE MASTER TO MASTER_HOST = '10.0.0.1', MASTER_USER = 'replication', MASTER_PASSWORD = 'strong_password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 12345; START SLAVE; Резервне копіювання: mysqldump vs XtraBackup
Для баз до 10 ГБ використовуємо mysqldump з --single-transaction — бекап без блокування InnoDB. Для великих баз застосовуємо Percona XtraBackup: він фізично копіює дані, працює до 3 разів швидше і майже не навантажує сервер. Обидва методи автоматизуємо з ротацією та перевіркою цілісності.
# mysqldump для невеликих баз mysqldump --single-transaction --quick --routines --triggers --flush-logs -u backup -p mydb | gzip > /backups/mydb_$(date +%Y%m%d).sql.gz # XtraBackup для великих баз xtrabackup --backup --user=backup --target-dir=/backups/full_$(date +%Y%m%d) Порівняння методів бекапу
| Параметр | mysqldump | XtraBackup |
|---|---|---|
| Розмір бази | < 10 ГБ | ≥ 10 ГБ |
| Швидкість | Повільно (логічний) | Швидко (фізичний) |
| Навантаження на сервер | Середнє | Низьке |
| Блокування | Немає (--single-transaction) | Немає |
| Відновлення | Повільно (SQL) | Швидко (файли) |
| Економія на ліцензіях | – | Безкоштовно, економія до $2000/рік |
Оптимізація та моніторинг
Регулярно виконуємо дефрагментацію таблиць за допомогою OPTIMIZE TABLE або pt-online-schema-change (без блокування). Моніторинг через performance_schema: відстежуємо топ повільних запитів та блокування. Налаштовуємо алерти при заповненні диска більше 80%.
| Задача | Частота |
|---|---|
| mysqldump бекап | Щодня |
| Перевірка лагу репліки | Щохвилини |
| Ротація binary log | По expire_logs_days |
| OPTIMIZE таблиць | Щотижня |
| Аналіз slow query log | Щотижня |
| Перевірка місця на диску | Постійно (алерт при >80%) |
Що робити при розростанні binary log?
Binary log розростається, якщо не налаштована ротація. Встановіть expire_logs_days = 7 в my.cnf, а також регулярно виконуйте PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY. Для реплікації використовуйте ROW-формат: він компактніший при змішаному навантаженні. Розмір логу можна контролювати алертами — при досягненні 80% диска спрацьовує сповіщення.
Приклад конфігурації my.cnf з ротацією
[mysqld] expire_logs_days = 7 max_binlog_size = 500M binlog_format = ROW Орієнтовні строки робіт
Строки залежать від складності: аудит займає 1-2 дні, налаштування конфігурації — 2-3 дні, впровадження реплікації — 1-2 дні, налаштування бекапів — 1 день. Повний цикл — від 5 до 10 робочих днів. Вартість розраховується індивідуально і окупається за рахунок зниження навантаження на сервер до 40%. Замовте первинний аудит — ми покажемо реальні точки зростання.
Що входить в роботу
- Аудит поточної конфігурації та продуктивності (з видачею звіту).
- Налаштування InnoDB під навантаження (оптимізація buffer pool, log file, IO).
- Налаштування реплікації master-replica з моніторингом лагу.
- Налаштування автоматичних бекапів (mysqldump/XtraBackup) з ротацією.
- Інтеграція моніторингу (performance_schema, алерти по диску та лагу).
- Документація з конфігурації та процедур відновлення.
- Навчання вашого адміністратора (2-3 години консультацій).
- Гарантійна підтримка 1 місяць після впровадження.
Зв'яжіться з нами для первинного аудиту вашої бази даних. Замовте налаштування MySQL/MariaDB і забудьте про проблеми з продуктивністю. Наша команда гарантує результат: стабільна робота без сюрпризів.
Для додаткової інформації зверніться до документації InnoDB та Wikipedia про реплікацію.