Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

Питання на співбесіді: Реплікація, бекапи й експлуатація

Питання з реальних співбесід з відповідями: Laravel і PHP, бази даних, JavaScript і фронтенд, Git, Docker, API, безпека й архітектура. Тими самими темами, що й тести.

23 питань

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;
Колонка Значення
Id ідентифікатор з'єднання (для KILL)
User, Host, db хто й звідки
Command Query - виконує запит, Sleep - чекає на наступний
Time скільки секунд у поточному стані
State що саме робить: Sending data, Waiting for table metadata lock, Creating sort index...
Info текст запиту (FULL - без обрізання до 100 символів)

Без привілею 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 Планувальник Laravel
де живе код у базі, створюється SQL у routes/console.php, у Git
що може робити лише SQL будь-який PHP: листи, черги, API, файли
тести складно звичайні тести Pest
видимість SHOW EVENTS, легко забути php artisan schedule:list
моніторинг і помилки помилки - у лозі помилок MySQL логи застосунку, onFailure, сповіщення
кілька серверів одна база - одне виконання потрібен onOneServer()

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

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

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

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

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

Бінарний журнал може записувати зміни трьома способами, що задається binlog_format.

STATEMENT - записується текст запиту:

UPDATE products SET price = price * 1.1 WHERE category_id = 5;
-- у журналі - один цей рядок, хоч змінилося 100 000 рядків
  • компактно для масових змін;
  • небезпечно для недетермінованих запитів: UUID(), NOW() в певних контекстах, RAND(), LIMIT без ORDER BY, UPDATE ... ORDER BY з різним порядком на репліці, тригери й функції. Репліка може отримати інші дані, і це ніхто не помітить одразу;
  • несумісний з READ COMMITTED для InnoDB.

ROW (за замовчуванням з MySQL 5.7.7) - записуються самі змінені рядки: значення до і після:

  • детерміновано: репліка отримує рівно ті самі дані, незалежно від того, як їх обчислено;
  • безпечно з будь-яким рівнем ізоляції й будь-якими функціями;
  • великий обсяг для масових змін: UPDATE 100 000 рядків - 100 000 записів у журналі;
  • зручний для захоплення змін (CDC): Debezium та подібні інструменти вимагають саме ROW.

MIXED - за замовчуванням STATEMENT, але для небезпечних запитів автоматично перемикається на ROW. Компроміс, що втрачає популярність: складно передбачити, що саме потрапить у журнал.

Налаштування ROW-формату:

  • binlog_row_image:
    • FULL (за замовчуванням) - усі колонки до і після;
    • MINIMAL - лише колонки, що змінилися, плюс ті, що ідентифікують рядок. Помітно зменшує журнал для широких таблиць із TEXT/JSON, але дехто зі споживачів CDC вимагає FULL;
    • NOBLOB - усі, крім незмінених BLOB/TEXT.
  • binlog_row_value_options = PARTIAL_JSON - для JSON_SET/JSON_REPLACE писати лише змінену частину документа.

Важливо для реплікації в ROW: на репліці рядки знаходяться за первинним ключем. Таблиця без первинного ключа змушує репліку для кожного зміненого рядка сканувати всю таблицю - класична причина багатогодинного відставання після одного масового UPDATE.

Як подивитися ROW-події:

mysqlbinlog --base64-output=DECODE-ROWS --verbose binlog.000042
# ### UPDATE `app`.`products`
# ### WHERE
# ###   @1=17 ...
# ### SET
# ###   @1=17 ...

Практична відповідь на співбесіді: використовувати ROW (типове значення), за потреби з binlog_row_image = MINIMAL, і мати первинні ключі в усіх таблицях.

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

GTID (global transaction identifier) - глобально унікальний ідентифікатор кожної закоміченої транзакції:

3e11fa47-71ca-11e1-9e33-c80aa9429562:23
└──────── server_uuid джерела ───────┘ └ номер транзакції

Класична реплікація без GTID спирається на файл і позицію в бінарному журналі: «репліка прочитала binlog.000042 до позиції 1234567». Проблема - позиції існують лише в журналі конкретного сервера. Коли джерело падає й треба перемкнути репліки на нове джерело, доводиться вручну вираховувати, якій позиції в його журналі відповідає стан кожної репліки. Помилка - втрачені чи двічі застосовані транзакції.

З GTID кожен сервер знає множину транзакцій, які він уже застосував (gtid_executed). Перемикання зводиться до «підключись до нового джерела й забери все, чого в тебе немає»:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '10.0.0.11',
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = '...',
    SOURCE_AUTO_POSITION = 1;
START REPLICA;

Увімкнення (на всіх серверах, послідовно, можна без простою через проміжні режими gtid_mode):

gtid_mode = ON
enforce_gtid_consistency = ON

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

Що ще дає GTID:

  • перевірка стану репліки: SHOW REPLICA STATUS показує Retrieved_Gtid_Set і Executed_Gtid_Set, легко порівняти з джерелом;
  • очікування реплікації конкретної транзакції: SELECT WAIT_FOR_EXECUTED_GTID_SET('...', 5) - корисно для читання власних записів з репліки;
  • ідемпотентність: транзакція з уже застосованим GTID пропускається, тож повторне застосування журналу не задвоює дані;
  • основа для Group Replication і InnoDB Cluster.

Типова проблема - «чужі» транзакції на репліці (errant transactions). Хтось виконав INSERT прямо на репліці - у неї з'явився GTID з її власним server_uuid, якого немає на джерелі. При перемиканні, коли ця репліка стане джерелом, інші репліки отримають цю транзакцію. Тому репліки варто тримати в super_read_only = ON.

Відновлення з дампу в GTID-середовищі вимагає уваги до --set-gtid-purged у mysqldump: він записує в дамп множину вже виконаних транзакцій, щоб нова репліка не намагалася отримати їх повторно.

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

Відновлення на момент у часі (point-in-time recovery, PITR) = останній повний бекап + бінарний журнал від моменту бекапу до потрібної точки.

Що потрібно мати заздалегідь:

  • регулярний повний бекап з відомою позицією бінарного журналу (mysqldump --single-transaction --source-data=2 записує її в дамп коментарем; фізичні інструменти - у свої метадані);
  • бінарні журнали, що зберігаються довше за інтервал між бекапами, і бажано копіюються на інший сервер (якщо загине диск, разом із ним загинуть і журнали).

Сценарій: о 14:32 хтось виконав DELETE FROM orders без WHERE. Бекап зроблено о 03:00.

1. Зупинити запис у базу (режим обслуговування), щоб не накопичувати зміни поверх аварії. Скопіювати поточні бінарні журнали в безпечне місце.

2. Знайти точну позицію помилки:

mysqlbinlog --base64-output=DECODE-ROWS --verbose \
    --start-datetime="2026-10-04 14:30:00" --stop-datetime="2026-10-04 14:35:00" \
    binlog.000057 | grep -n -B5 "DELETE FROM \`app\`.\`orders\`"
# знаходимо "# at 48211337" перед подією

3. Відновити бекап на окремому сервері (не поверх продакшену):

mysql app < backup-03-00.sql

4. Застосувати журнал від позиції бекапу до позиції перед DELETE:

# позиція з дампу: -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000055', SOURCE_LOG_POS=157;
mysqlbinlog --start-position=157 binlog.000055 binlog.000056 > /tmp/redo.sql
mysqlbinlog --stop-position=48211337 binlog.000057 >> /tmp/redo.sql
mysql app < /tmp/redo.sql

З GTID зручніше: --exclude-gtids для транзакції з DELETE і --skip-gtids залежно від сценарію.

5. Що робити з даними після аварії. Події після DELETE (нові замовлення з 14:32 до зупинки) теж є в журналі. Можна:

  • застосувати журнал після помилкової транзакції (--start-position на наступну подію), пропустивши лише її;
  • або відновити на окремому сервері лише постраждалу таблицю й перенести видалені рядки на продакшен, не відкочуючи всю базу.

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

Нюанси:

  • --start-datetime/--stop-datetime неточні (секундна точність, в одну секунду - багато подій) - для точки зупинки краще позиція чи GTID;
  • застосовувати журнали кількох файлів треба одним потоком mysqlbinlog чи по черзі без пропусків;
  • відновлення великої бази з логічного дампу займає години - це час простою, який треба знати заздалегідь (RTO).

Захист від таких аварій до бекапів: відкладена репліка (SOURCE_DELAY), обмежені права застосунку й sql_safe_updates у консольних сесіях.

Докладніше в документації: Відновлення на момент у часі через бінарний журнал

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

CREATE ROLE 'app_read', 'app_write', 'app_ddl';

GRANT SELECT ON app.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app.* TO 'app_write';
GRANT CREATE, ALTER, DROP, INDEX, REFERENCES ON app.* TO 'app_ddl';

CREATE USER 'analyst'@'%' IDENTIFIED BY '...';
GRANT 'app_read' TO 'analyst'@'%';

CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY '...';
GRANT 'app_read', 'app_write' TO 'app'@'10.0.0.%';

Головна пастка - ролі неактивні за замовчуванням. Після GRANT 'app_read' TO 'analyst' і підключення:

SELECT * FROM app.orders;
-- ERROR 1142: SELECT command denied to user 'analyst'@...

SELECT CURRENT_ROLE();   -- NONE

Роль призначена, але не активована в сесії. Варіанти:

-- активувати в поточній сесії
SET ROLE 'app_read';
SET ROLE ALL;

-- зробити ролі активними за замовчуванням при вході
SET DEFAULT ROLE ALL TO 'analyst'@'%';

-- або глобально: активувати всі призначені ролі при вході кожного користувача
SET PERSIST activate_all_roles_on_login = ON;

Для облікового запису застосунку зручно SET DEFAULT ROLE ALL, інакше Laravel отримає помилки прав одразу після деплою.

Перевірити ефективні права:

SHOW GRANTS FOR 'analyst'@'%';                     -- видно лише призначені ролі
SHOW GRANTS FOR 'analyst'@'%' USING 'app_read';    -- права з урахуванням ролі

Корисні можливості:

  • обов'язкові ролі (mandatory_roles) - автоматично призначаються всім користувачам;
  • роль - фактично заблокований обліковий запис, тож ролі можна вкладати одну в одну (GRANT 'app_read' TO 'app_write');
  • REVOKE 'app_write' FROM 'app'@'10.0.0.%' - забрати роль, не перебираючи окремі права.

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

У керованих хмарних MySQL набір доступних привілеїв обмежений провайдером, але ролі працюють так само.

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

caching_sha2_password - плагін автентифікації за замовчуванням з MySQL 8.0. Він замінив mysql_native_password, що спирався на SHA-1 без солі: однакові паролі давали однакові хеші, а вкрадена таблиця mysql.user легко перебиралася.

Як працює новий плагін:

  • пароль зберігається з сіллю і багатьма раундами SHA-256;
  • перша автентифікація користувача після старту сервера - «повна»: пароль треба передати безпечно, тобто через TLS або з шифруванням публічним RSA-ключем сервера;
  • після успішного входу сервер кешує хеш у пам'яті, і наступні підключення проходять швидко без повного обміну.

Звідки беруться помилки підключення:

  1. Старий клієнт не знає плагіна. Повідомлення на кшталт The server requested authentication method unknown to the client - типова історія старих версій PHP. Драйвер mysqlnd підтримує caching_sha2_password з PHP 7.4, тож на сучасному PHP проблеми немає. Але старі GUI-клієнти, бібліотеки інших мов і бінарники в Docker-образах можуть її мати.

  2. Підключення без TLS і без RSA-ключа. Помилка на зразок Authentication requires secure connection. Варіанти: увімкнути TLS (правильно), дозволити клієнту запитати ключ сервера (--get-server-public-key / GET_SOURCE_PUBLIC_KEY=1 для реплікації) чи передати файл ключа.

  3. Перший вхід після перезапуску. Кеш порожній, і клієнт, що раніше працював «без TLS» завдяки кешу, раптом не підключається. Підступна помилка: усе працювало до перезапуску сервера.

MySQL 8.4: mysql_native_password вимкнено за замовчуванням. Облікові записи, створені зі старим плагіном (наприклад, перенесені з 5.7), після оновлення не зможуть увійти, доки плагін не ввімкнуть:

[mysqld]
mysql_native_password = ON

Це тимчасовий милиць: у MySQL 9 плагін видалено зовсім. Правильний шлях - перевести облікові записи:

SELECT user, host, plugin FROM mysql.user WHERE plugin = 'mysql_native_password';

ALTER USER 'app'@'10.0.0.%' IDENTIFIED WITH caching_sha2_password BY 'пароль';

Практичні висновки:

  • перед оновленням до 8.4 знайти всі облікові записи зі старим плагіном і перевести їх;
  • підключення до бази за межами одного хоста - через TLS, що одночасно знімає й питання першої автентифікації;
  • оновити клієнтські бібліотеки й інструменти, а не повертати старий плагін.

Докладніше в документації: Автентифікація caching_sha2_password

MySQL обслуговує кожне клієнтське з'єднання окремим потоком. Кількість одночасних з'єднань обмежена max_connections - за замовчуванням 151. Коли ліміт вичерпано, нові підключення отримують ERROR 1040: Too many connections.

Резерв для адміністратора. MySQL дозволяє одне додаткове з'єднання понад ліміт для користувача з привілеєм CONNECTION_ADMIN, щоб у момент аварії можна було зайти й розібратися. Якщо застосунок підключається під root, він з'їсть і цей резерв. Ще надійніше - окремий адміністративний інтерфейс (admin_address, admin_port) зі своїм лімітом.

Як порахувати потребу. У PHP кожен процес, що обробляє запит, тримає власне з'єднання:

PHP-FPM: pm.max_children = 50 на сервер × 4 вебсервери     = 200
воркери черг (Horizon): 30 процесів × 2 сервери            = 60
планувальник, Pulse, разові команди                        ~ 10
                                                            ─────
                                                             270 + запас

Для Octane/FrankenPHP у режимі воркерів - кількість воркерів на всіх інстансах: з'єднання живуть довго й тримаються постійно.

Чому не можна просто поставити 5000:

  • кожне з'єднання займає пам'ять: буфер потоку плюс буфери сортування, з'єднань і читання, що виділяються під час запиту. Сотні з'єднань з великими запитами можуть вичерпати пам'ять і отримати OOM-killer;
  • тисяча одночасно активних запитів не виконується швидше - вони конкурують за процесор, диск і блокування. Пропускна здатність падає, затримки ростуть.

Корисні дані - не скільки з'єднань відкрито, а скільки одночасно виконують запити:

SHOW GLOBAL STATUS WHERE variable_name IN
    ('Threads_connected', 'Threads_running', 'Max_used_connections',
     'Max_used_connections_time', 'Aborted_connects', 'Connection_errors_max_connections');

Типові причини раптового вичерпання:

  • повільні запити чи блокування: з'єднання не звільняються, а нові запити прибувають - лавина;
  • витік з'єднань у воркерах (довгоживучі процеси, що відкривають нові підключення);
  • різке масштабування вебсерверів чи воркерів без перегляду ліміту бази.

Рішення на рівні архітектури:

  • пул з'єднань перед MySQL - ProxySQL чи MySQL Router: тисячі з'єднань від застосунку мультиплексуються в десятки до бази;
  • пул потоків (thread pool) - у MySQL Enterprise, Percona Server і MariaDB: обмежує кількість одночасно виконуваних запитів незалежно від кількості з'єднань;
  • wait_timeout (за замовчуванням 8 годин) зменшити, щоб забуті неактивні з'єднання закривалися;
  • окремі ліміти на користувача: ALTER USER ... WITH MAX_USER_CONNECTIONS 50 - щоб аналітик чи воркер не забрав усі з'єднання в застосунку.

Докладніше в документації: Керування з'єднаннями

Performance Schema - вбудований механізм інструментування MySQL: сервер збирає статистику про запити, очікування, блокування, використання пам'яті, файловий ввід-вивід. Увімкнено за замовчуванням. Дані лежать у таблицях performance_schema.*, але сирі й незручні.

sys schema - набір подань і процедур поверх Performance Schema та information_schema, які перетворюють сирі дані на зрозумілі звіти (з людськими одиницями часу й розміру).

Найкорисніші подання:

1. Які запити сумарно найдорожчі:

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

Запити згруповано за дайджестом - нормалізованим текстом без конкретних значень (WHERE id = ?), тож тисячі викликів з різними id - один рядок.

2. Повні сканування і сортування на диску:

SELECT * FROM sys.statements_with_full_table_scans ORDER BY total_latency DESC LIMIT 10;
SELECT * FROM sys.statements_with_sorting ORDER BY sort_merge_passes DESC LIMIT 10;
SELECT * FROM sys.statements_with_temp_tables ORDER BY disk_tmp_tables DESC LIMIT 10;

3. Індекси:

SELECT * FROM sys.schema_unused_indexes;        -- не використовувались з моменту старту
SELECT * FROM sys.schema_redundant_indexes;     -- дублюють інші
SELECT * FROM sys.schema_index_statistics ORDER BY rows_selected DESC LIMIT 10;

4. Блокування зараз:

SELECT * FROM sys.innodb_lock_waits\G       -- хто кого чекає, з готовими KILL
SELECT * FROM sys.schema_table_lock_waits\G -- очікування блокувань метаданих

5. Таблиці з найбільшим вводом-виводом і пам'ять:

SELECT * FROM sys.schema_table_statistics ORDER BY total_latency DESC LIMIT 10;
SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10;

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

  • статистика накопичується з моменту старту сервера (чи з останнього скидання). Порівнювати варто зміни за період: зберегти знімок, почекати, порівняти. Процедура sys.diagnostics() формує звіт саме так;
  • скинути лічильники запитів: CALL sys.ps_truncate_all_tables(FALSE); - зручно перед вимірюванням ефекту оптимізації;
  • таблиця дайджестів має обмежений розмір (performance_schema_digests_size), рідкісні запити можуть витіснятися;
  • накладні витрати Performance Schema невеликі (кілька відсотків), але додаткові інструменти (наприклад, історія всіх очікувань) варто вмикати лише на час діагностики.

Практичний ритуал при скаргах «база повільна»: sys.statement_analysis за сумарним часом → EXPLAIN ANALYZE для лідера → sys.innodb_lock_waits, якщо проблема в очікуваннях, а не в самих запитах.

Докладніше в документації: Схема sys

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

CREATE TABLE activity_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    created_at DATETIME NOT NULL,
    payload JSON,
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE COLUMNS (created_at) (
    PARTITION p2026_08 VALUES LESS THAN ('2026-09-01'),
    PARTITION p2026_09 VALUES LESS THAN ('2026-10-01'),
    PARTITION p2026_10 VALUES LESS THAN ('2026-11-01'),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

Типи: RANGE (діапазони, найчастіше дати), LIST (списки значень), HASH і KEY (рівномірний розподіл).

Де секціонування справді допомагає:

  • видалення старих даних. Замість DELETE мільйонів рядків (довга транзакція, роздутий undo, затримка реплік, місце на диску не звільняється):
ALTER TABLE activity_log DROP PARTITION p2026_08;   -- миттєво, з файлом
  • відсікання секцій (partition pruning): запит з умовою на ключ секціонування читає лише потрібні секції. В EXPLAIN - колонка partitions;
  • обслуговування частинами: перебудова чи аналіз окремої секції.

Обмеження, про які часто дізнаються запізно:

  • кожен унікальний ключ, включно з первинним, мусить містити колонку секціонування. Тому в прикладі первинний ключ (id, created_at). Унікальність email у секціонованій за датою таблиці забезпечити неможливо;
  • зовнішні ключі не підтримуються для секціонованих таблиць InnoDB - ні з неї, ні на неї;
  • запити без умови на ключ секціонування перебирають усі секції - це повільніше, ніж одна несекціонована таблиця з хорошим індексом;
  • глобальних індексів немає: індекс існує окремо в кожній секції;
  • до 8192 секцій, але сотні секцій уже уповільнюють відкриття таблиці й планування.

Коли секціонування шкодить: як «прискорювач» звичайної OLTP-таблиці. Таблиця orders на 50 мільйонів рядків з правильними індексами працює чудово - B-дерево на 50 млн рядків має лише 3-4 рівні. Секціонування за user_id не дасть нічого, крім обмежень.

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

Супровід: секції на майбутнє треба створювати заздалегідь (крон-задача раз на місяць: REORGANIZE PARTITION pmax INTO (...)). Laravel-міграції секціонування не підтримують - лише DB::statement().

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