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

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

Бінарний журнал може записувати зміни трьома способами, що задається 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().

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