Junior: питання на співбесіді з теми «Реплікація, бекапи й експлуатація»

mysqldump робить логічний бекап: SQL-файл з CREATE TABLE і INSERT, з якого базу можна відтворити на будь-якому сервері MySQL.

mysqldump --single-transaction --routines --events \
    --user=backup --password app > app-2026-10-04.sql

# відновлення
mysql --user=root --password app < app-2026-10-04.sql

Чому --single-transaction обов'язковий для InnoDB. Без нього дамп читає таблиці по черзі в різні моменти часу. Якщо між читанням orders і order_items хтось створив замовлення, у дампі будуть позиції без замовлення - неузгоджений бекап. Тому за замовчуванням mysqldump блокує таблиці (--lock-tables), і застосунок на час дампу не може писати.

З --single-transaction дамп відкриває транзакцію з узгодженим знімком (REPEATABLE READ) і читає всі таблиці на одну мить, нікого не блокуючи. Працює лише для InnoDB: MyISAM-таблиці знімка не підтримують.

Корисні параметри:

• --routines, --events - збережені процедури й події (тригери включено за замовчуванням);
• --no-data - лише схема; --no-create-info - лише дані;
• --source-data=2 - записати в дамп коментар з позицією бінарного журналу (потрібно для відновлення на момент у часі чи налаштування репліки);
• --quick (увімкнено за замовчуванням) - читати рядки потоком, а не завантажувати таблицю в пам'ять;
• стиснення на льоту: mysqldump ... | gzip > app.sql.gz.

Обмеження й застереження:

• під час дампу не можна змінювати схему (ALTER, TRUNCATE, RENAME) - вони порушують узгодженість знімка, а дамп може впасти чи отримати неповні дані;
• довга транзакція дампу заважає очищенню старих версій рядків (purge) - на великій навантаженій базі дамп краще знімати з репліки;
• відновлення повільне: SQL виконується рядок за рядком і перебудовує індекси. Для бази на сотні гігабайтів це години. Для великих баз використовують фізичні бекапи (Percona XtraBackup, MySQL Enterprise Backup) або паралельні утиліти MySQL Shell (util.dumpInstance()).

Бекап, який не перевіряли, - не бекап. Регулярно відновлюйте дамп на окремому сервері й запускайте хоча б базові перевірки: кількість рядків у ключових таблицях, свіжість останніх записів.

Безпека: пароль у командному рядку видно в списку процесів. Краще використовувати файл опцій (--defaults-extra-file) чи mysql_config_editor, а бекапи шифрувати й зберігати поза сервером бази.

Докладніше в документації: Бекапи за допомогою mysqldump

Журнал повільних запитів записує запити, що виконувалися довше заданого порогу. Це найпростіший спосіб дізнатися, що саме гальмує базу на продакшені.

Увімкнення без перезапуску:

SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 1;      -- секунди, можна дробові: 0.5
SET PERSIST slow_query_log_file = '/var/log/mysql/slow.log';

За замовчуванням long_query_time - 10 секунд, а для вебзастосунку повільним є вже запит на 200-500 мс.

Додаткові налаштування:

• log_queries_not_using_indexes = ON - записувати й запити без індексів, навіть швидкі. Корисно на розробці; на продакшені шумно (обмежує log_throttle_queries_not_using_indexes);
• min_examined_row_limit - не писати запити, що прочитали менше N рядків;
• log_slow_admin_statements - включити ALTER TABLE, OPTIMIZE TABLE тощо;
• log_output = 'TABLE' - писати в таблицю mysql.slow_log замість файлу (зручно для керованих хмарних баз).

Що є в записі:

# Query_time: 2.415  Lock_time: 0.000  Rows_sent: 20  Rows_examined: 1850234
SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;

Співвідношення Rows_examined до Rows_sent - головна підказка: прочитано майже два мільйони рядків, щоб повернути 20. Тут явно бракує індексу.

Як аналізувати журнал. Окремі записи читати марно - важливо, які запити сумарно забирають найбільше часу. Інструменти групують однакові запити з різними параметрами:

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log   # топ-10 за сумарним часом
pt-query-digest /var/log/mysql/slow.log            # детальніший звіт (Percona Toolkit)

Часто винуватцем виявляється не найповільніший запит, а запит на 50 мс, що виконується 100 000 разів на годину.

Альтернатива без журналу - Performance Schema вже збирає статистику за нормалізованими запитами:

SELECT query, exec_count, total_latency, rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC LIMIT 10;

У Laravel допоміжно: DB::whenQueryingForLongerThan() для сповіщень про сумарний час запитів у межах запиту, Telescope чи Pulse (картка повільних запитів) на рівні застосунку. Але журнал MySQL бачить і запити від черг, крон-задач і сторонніх клієнтів.

Докладніше в документації: Журнал повільних запитів

SHOW PROCESSLIST показує всі з'єднання з сервером і що кожне робить зараз:

SHOW FULL PROCESSLIST;

Без привілею PROCESS користувач бачить лише власні з'єднання.

Зручніше фільтрувати через таблиці:

SELECT id, user, host, time, state, LEFT(info, 120) AS query
FROM performance_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC;

-- з додатковою інформацією про транзакції й блокування
SELECT * FROM sys.session WHERE command <> 'Sleep' ORDER BY time DESC;

Зупинити запит:

KILL QUERY 12345;   -- перервати лише поточний запит, з'єднання лишається
KILL 12345;         -- закрити з'єднання повністю

Що варто знати про KILL:

• відкіт може тривати довго. Якщо вбити UPDATE, що змінював рядки 20 хвилин, InnoDB відкочуватиме зміни приблизно стільки ж. Стан Killed у процес-листі - це відкіт, і повторний KILL його не прискорить. Перезапуск сервера теж не допоможе: відкіт продовжиться після старту;
• з'єднання в стані Sleep з великим Time - це не завислі запити, а простоюючі з'єднання (пул, постійні з'єднання). Небезпечні вони лише якщо тримають відкриту транзакцію - це видно в information_schema.innodb_trx;
• убитий запит застосунок отримає як помилку - Laravel-джоба впаде і, можливо, буде повторена.

Запобіжники, щоб не доводилося вбивати вручну:

• max_execution_time (мілісекунди) - ліміт часу для SELECT, глобально чи на сесію, або підказкою /*+ MAX_EXECUTION_TIME(5000) */ в конкретному запиті;
• wait_timeout - закривати неактивні з'єднання;
• тайм-аути в застосунку й важкі звіти на репліці.

Типова картина аварії: десятки запитів у стані Waiting for table metadata lock - шукати не їх, а довгу транзакцію чи ALTER TABLE, що стоїть у черзі першим.

Докладніше в документації: SHOW PROCESSLIST

Застосунок не повинен ходити в базу під root. Якщо через SQL-ін'єкцію чи вкрадений .env хтось отримає доступ, права користувача визначають, скільки шкоди він зробить.

Користувач для застосунку:

CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'довгий-випадковий-пароль';

GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app'@'10.0.0.%';

Частина @'host' - невід'ємна частина облікового запису. 'app'@'10.0.0.%' - підключення лише з внутрішньої мережі; 'app'@'%' - звідусіль. 'app'@'localhost' і 'app'@'%' - два різні облікові записи з різними паролями й правами, що часто плутає.

Окремий користувач для міграцій:

CREATE USER 'migrator'@'10.0.0.%' IDENTIFIED BY '...';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, REFERENCES
    ON app.* TO 'migrator'@'10.0.0.%';

Застосунок працює з обмеженими правами, а при деплої міграції запускаються з окремими обліковими даними. Тоді ін'єкція не зможе виконати DROP TABLE.

Інші облікові записи за призначенням:

• backup - SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS (для дампів);
• readonly для аналітики й BI - лише SELECT, бажано на репліці;
• моніторинг - PROCESS, REPLICATION CLIENT і SELECT на performance_schema.

Чого не давати застосунку:

• ALL PRIVILEGES чи права на *.*;
• SUPER та адміністративні динамічні привілеї;
• FILE - дає змогу читати й писати файли на сервері (LOAD DATA INFILE, SELECT ... INTO OUTFILE);
• GRANT OPTION.

Перевірити права:

SHOW GRANTS FOR 'app'@'10.0.0.%';

Що змінилося в MySQL 8:

• GRANT більше не створює користувача неявно - спершу CREATE USER;
• зміна пароля - ALTER USER 'app'@'10.0.0.%' IDENTIFIED BY '...';
• плагін автентифікації за замовчуванням - caching_sha2_password;
• для груп прав є ролі (CREATE ROLE).

Пароль застосунку зберігається в .env (DB_USERNAME, DB_PASSWORD), а не в коді чи репозиторії, і змінюється, якщо .env міг потрапити в чужі руки.

Докладніше в документації: Оператор GRANT

Бінарний журнал (binary log, binlog) - послідовний запис усіх змін даних і схеми: INSERT, UPDATE, DELETE, CREATE, ALTER тощо. Запити SELECT туди не потрапляють. Записується лише закомічене, у порядку комітів.

Від MySQL 8.0 журнал увімкнено за замовчуванням.

Навіщо він:

1. Реплікація. Репліки читають бінарний журнал джерела й застосовують ті самі зміни у себе.
2. Відновлення на момент у часі (PITR). Відновлюємо нічний бекап, потім «програємо» бінарний журнал до хвилини перед аварією (наприклад, до випадкового DELETE без WHERE).
3. Аудит і захоплення змін (CDC). Інструменти на кшталт Debezium читають журнал і передають зміни в Kafka, пошукові індекси, сховища даних.

Не плутати з журналом повтору InnoDB (redo log). Redo log - внутрішній механізм InnoDB для відновлення після збою, у фізичних термінах сторінок. Бінарний журнал - логічний, на рівні сервера, для всіх рушіїв, і його читають зовнішні споживачі.

Основні команди:

SHOW BINARY LOGS;                 -- список файлів журналу і їхні розміри
SHOW BINARY LOG STATUS;           -- поточний файл і позиція (раніше SHOW MASTER STATUS)
SHOW BINLOG EVENTS IN 'binlog.000042' LIMIT 10;

Вміст у читабельному вигляді:

mysqlbinlog --base64-output=DECODE-ROWS --verbose binlog.000042 | less

Скільки зберігати: binlog_expire_logs_seconds - за замовчуванням 30 днів. Журнал займає місце пропорційно інтенсивності запису, і на навантаженій базі може зайняти більше, ніж самі дані. Але термін має покривати проміжок між бекапами, інакше відновлення на момент у часі неможливе. Видалити старі файли вручну:

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;

Ніколи не видаляйте файли журналу командою rm - сервер веде індекс файлів і почне скаржитися, а репліки, що ще не прочитали їх, зламаються.

Вплив на продуктивність: запис журналу й sync_binlog = 1 (fsync на кожен коміт) - помітна, але необхідна ціна стійкості. Групування комітів зменшує її під паралельним навантаженням.

Докладніше в документації: Бінарний журнал

Репліка - сервер MySQL, що отримує зміни від джерела (source, раніше «master») через бінарний журнал і застосовує їх у себе. У типовому вебзастосунку читань у десятки разів більше, ніж записів, тож читання можна розподілити між кількома репліками, а всі записи лишити на джерелі.

          записи                 читання
застосунок ──────► джерело ───► репліка 1 ◄──┐
                       │                      ├── застосунок
                       └──────► репліка 2 ◄──┘

Налаштування в Laravel (config/database.php):

'mysql' => [
    'driver' => 'mysql',
    'read' => [
        'host' => ['10.0.0.11', '10.0.0.12'],   // випадкова репліка на запит
    ],
    'write' => [
        'host' => ['10.0.0.10'],
    ],
    'sticky' => true,
    'database' => env('DB_DATABASE'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    // ...
],

SELECT ідуть на репліки, усе інше - на джерело. Транзакції й lockForUpdate() теж виконуються на джерелі.

Головна пастка - затримка реплікації. Репліка застосовує зміни з запізненням (зазвичай мілісекунди, під навантаженням - секунди й більше). Класичний баг:

$post = Post::create($data);          // запис на джерело
return redirect()->route('posts.show', $post);
// наступний запит читає з репліки - поста там ще немає, 404

Як Laravel це пом'якшує: 'sticky' => true - якщо в поточному запиті вже був запис, наступні читання цього ж запиту йдуть на джерело. Але наступний HTTP-запит (після редиректу) знову піде на репліку.

Інші способи:

• явно читати з джерела: Post::query()->useWritePdo()->find($id), DB::connection('mysql')->...;
• для критичних сторінок після запису (профіль після редагування, замовлення після оплати) - читати з джерела кілька секунд після запису (через сесію чи кеш);
• черги: джоба, поставлена в черзі одразу після запису, може не знайти рядок на репліці - afterCommit не допомагає від затримки репліки, лише від незакоміченої транзакції.

Що ще дають репліки:

• важкі звіти й аналітика без навантаження на джерело;
• бекапи з репліки;
• резерв на випадок відмови джерела (але перемикання - окрема задача).

Чого репліки не дають: масштабування записів. Усі записи все одно йдуть через одне джерело. Для цього - шардування чи інші архітектурні рішення.

Докладніше в документації: Реплікація для масштабування

Event Scheduler - вбудований у MySQL планувальник: SQL-код виконується за розкладом прямо на сервері бази, без cron і без застосунку.

CREATE EVENT purge_old_sessions
ON SCHEDULE EVERY 1 HOUR
DO
    DELETE FROM sessions WHERE last_activity < UNIX_TIMESTAMP() - 86400;

CREATE EVENT close_promo
ON SCHEDULE AT '2026-12-31 23:59:59'
DO
    UPDATE promotions SET active = 0 WHERE code = 'NY2027';

SHOW EVENTS;
ALTER EVENT purge_old_sessions DISABLE;

У MySQL 8 планувальник увімкнено за замовчуванням (event_scheduler = ON), і в SHOW PROCESSLIST видно його окремий потік.

Що вміє: разові (AT) й періодичні (EVERY) події, час початку й завершення, автоматичне видалення одноразової події після виконання.

Чому в Laravel-застосунку зазвичай обирають планувальник Laravel:

Типові пастки Event Scheduler:

• події не переносяться дампом за замовчуванням: mysqldump без --events їх не зберігає - після відновлення з бекапу задачі мовчки зникають;
• репліки: на репліці події, створені на джерелі, мають статус REPLICA_SIDE_DISABLED і не виконуються - інакше вони спрацювали б двічі. Після перемикання репліки на роль джерела їх треба ввімкнути вручну;
• керовані бази: деякі хмарні сервіси вимикають планувальник або обмежують права на EVENT;
• невидимість: нова людина в команді не знає, що база щогодини щось видаляє.

Коли Event Scheduler доречний: суто базове обслуговування, яке має працювати незалежно від застосунку (очищення технічних таблиць, оновлення зведених таблиць), особливо коли базою користуються кілька застосунків.

Laravel-аналог для очищення старих записів - моделі з Prunable і Schedule::command('model:prune')->daily().

Докладніше в документації: MySQL: Event Scheduler