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

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

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

Логічний бекап - дані у вигляді SQL чи CSV (mysqldump, утиліти MySQL Shell):

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

Фізичний бекап - копія файлів даних (Percona XtraBackup, MySQL Enterprise Backup, знімки диска):

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

Гарячий, теплий, холодний:

• гарячий - база працює, читання й запис дозволені (mysqldump --single-transaction для InnoDB, XtraBackup);
• теплий - читати можна, писати ні (дамп з блокуванням таблиць);
• холодний - сервер зупинено, копіюються файли. Найпростіший, але з простоєм.

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

Знімки файлової системи (LVM, ZFS, хмарні диски) - миттєвий фізичний бекап. Щоб знімок був узгодженим, InnoDB має бути готовий: знімок робиться при FLUSH TABLES WITH READ LOCK або покладається на відновлення після збою (InnoDB відновиться, як після раптового вимкнення).

Як обрати стратегію - від вимог, а не від інструментів:

• RPO (recovery point objective) - скільки даних можна втратити. «Нічого» - потрібні бінарні журнали, що безперервно копіюються за межі сервера;
• RTO (recovery time objective) - скільки триває відновлення. «15 хвилин» для бази на 500 ГБ - фізичний бекап чи готова репліка, але не mysqldump.

Типова схема для середнього проєкту:

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

Репліка - не бекап. Випадковий DROP TABLE миттєво реплікується на всі репліки. Від помилок людей захищають бекапи й відкладені репліки.

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

Докладніше в документації: Типи бекапів