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

Логічний бекап - дані у вигляді 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.

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

Звичайна реплікація MySQL асинхронна: джерело комітить транзакцію, відповідає клієнту «готово» і лише потім репліки забирають зміни. Якщо джерело загинуло одразу після коміту, остання транзакція могла ще не дійти до жодної репліки. Після перемикання на репліку ці транзакції втрачено, хоча застосунок отримав підтвердження: замовлення «оформлене», гроші «списані».

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

-- на джерелі
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET PERSIST rpl_semi_sync_source_enabled = ON;
SET PERSIST rpl_semi_sync_source_timeout = 1000;   -- мс

-- на репліці
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET PERSIST rpl_semi_sync_replica_enabled = ON;

Точка очікування rpl_semi_sync_source_wait_point:

• AFTER_SYNC (за замовчуванням, «без втрат») - джерело записує транзакцію в бінарний журнал, чекає підтвердження репліки, і лише потім комітить у рушії. Інші клієнти не побачать транзакцію на джерелі раніше, ніж вона буде на репліці;
• AFTER_COMMIT - коміт у рушії, потім очікування. Інші сесії можуть побачити дані, які потім пропадуть при перемиканні.

Головне застереження - тайм-аут. Якщо репліки не відповідають довше за rpl_semi_sync_source_timeout (за замовчуванням 10 секунд), джерело тихо переходить в асинхронний режим і продовжує працювати. Гарантія зникає саме тоді, коли щось пішло не так. Це треба моніторити:

SHOW GLOBAL STATUS LIKE 'Rpl_semi_sync_source_status';   -- ON / OFF
SHOW GLOBAL STATUS LIKE 'Rpl_semi_sync_source_no_tx';    -- коміти без підтвердження

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

Налаштування для надійності:

• щонайменше дві напівсинхронні репліки, щоб відмова однієї не перемикала джерело в асинхронний режим;
• rpl_semi_sync_source_wait_for_replica_count - скільки підтверджень чекати;
• автоматичне перемикання (Orchestrator, MySQL Router з InnoDB Cluster) має обирати як нове джерело саме репліку, що має всі підтверджені транзакції.

Від чого не захищає: від логічних помилок (DELETE без WHERE теж реплікується), і не робить читання з репліки свіжим - транзакцію отримано, але ще не обов'язково застосовано. Для справді синхронної реплікації з консенсусом - Group Replication.

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

Звичайна репліка застосовує зміни майже миттєво - разом із помилками. DROP TABLE orders чи UPDATE users SET email = ... без WHERE за мілісекунди стає катастрофою і на всіх репліках.

Відкладена репліка навмисно застосовує зміни із затримкою:

STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 3600;   -- секунди
START REPLICA;

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

Сценарій порятунку. О 14:32 хтось виконав DROP TABLE orders. Відкладена репліка застосувала дані лише до 13:32.

1. Негайно зупинити застосування на відкладеній репліці, поки вона не дійшла до 14:32:

STOP REPLICA SQL_THREAD;

2. Знайти в бінарному журналі позицію чи GTID шкідливої транзакції.
3. Довести репліку до моменту перед аварією:

START REPLICA SQL_THREAD UNTIL SQL_BEFORE_GTIDS = '3e11fa47-...:98765';
-- або UNTIL SOURCE_LOG_FILE = 'binlog.000057', SOURCE_LOG_POS = 48211337

4. Тепер репліка містить стан бази за мить до DROP. Далі:
   • вивантажити звідти таблицю orders і повернути на джерело (а зміни, що відбулися після 14:32, у видаленій таблиці не існують - тож втрачено лише нові замовлення за час аварії);
   • або, якщо пошкоджено багато, підвищити відкладену репліку до джерела, пропустивши шкідливу транзакцію.

Порівняно з відновленням з бекапу: нічний бекап + програвання журналів для великої бази - години. Відкладена репліка вже містить майже все потрібне, лишається догнати хвилини.

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

Що треба знати:

• відкладена репліка не підходить для читання застосунком і для автоматичного перемикання при відмові джерела - її дані застарілі за визначенням. Інструменти перемикання треба налаштувати, щоб вони її ігнорували;
• SHOW REPLICA STATUS показує SQL_Delay і SQL_Remaining_Delay, а затримка в моніторингу виглядатиме як постійне відставання - для цієї репліки сповіщення налаштовують окремо;
• вона не замінює бекапи: якщо помилку помітили через дві години при затримці в одну, вона вже не допоможе.

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

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

Clone plugin (MySQL 8.0.17+) копіює фізичні файли даних InnoDB з працюючого сервера-донора на сервер-одержувач прямо через протокол MySQL, без зупинки донора.

Підготовка:

-- на обох серверах
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

-- на донорі: користувач для клонування
CREATE USER 'clone_user'@'10.0.0.%' IDENTIFIED BY '...';
GRANT BACKUP_ADMIN ON *.* TO 'clone_user'@'10.0.0.%';

-- на одержувачі: хто клонує і звідки можна
GRANT CLONE_ADMIN ON *.* TO 'admin'@'localhost';
SET GLOBAL clone_valid_donor_list = '10.0.0.10:3306';

Клонування (на одержувачі):

CLONE INSTANCE FROM 'clone_user'@'10.0.0.10':3306 IDENTIFIED BY '...';

Що відбувається:

1. дані одержувача видаляються - він стає точною копією донора;
2. файли передаються потоком, паралельно;
3. зміни, що відбуваються на донорі під час копіювання, теж переносяться - результат узгоджений;
4. одержувач автоматично перезапускається (якщо ним керує systemd чи інший супервізор).

Прогрес видно в performance_schema.clone_progress, підсумок - у clone_status.

Налаштування реплікації після клонування: клон зберігає координати - позицію бінарного журналу й множину GTID. З GTID досить:

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

Обмеження:

• однакова версія донора й одержувача (у межах однієї серії допустимі різні патч-версії), та сама платформа й операційна система;
• копіюються лише дані InnoDB; таблиці інших рушіїв створюються порожніми;
• клонування навантажує мережу й диск донора - є налаштування обмеження швидкості (clone_max_data_bandwidth, clone_max_network_bandwidth);
• конфігураційні файли (my.cnf) не переносяться;
• DDL на донорі під час клонування за замовчуванням дозволено (clone_block_ddl = OFF), але чи потрапить його результат у клон, залежить від того, чи завершився він до знімка. Для передбачуваності на час клонування схему краще не змінювати.

Також використовується: InnoDB Cluster і Group Replication самі застосовують clone для додавання нового вузла, коли журналів недостатньо, щоб «наздогнати» кластер.

Для бекапу clone теж придатний (клонування в локальний каталог: CLONE LOCAL DATA DIRECTORY = '/backups/2026-10-04'), але без інкрементних бекапів і з тими ж обмеженнями.

Докладніше в документації: Плагін clone

MySQL 8.4 - перша LTS-версія в новій моделі випусків: LTS отримують лише виправлення протягом довгого часу, а нові можливості виходять у проміжних Innovation-версіях (9.x). Оновлення 8.0 → 8.4 підтримується напряму. Відкат до 8.0 на тих самих файлах не підтримується - лише з бекапу.

Що ламається найчастіше:

1. mysql_native_password вимкнено за замовчуванням. Облікові записи зі старим плагіном після оновлення не увійдуть. Перед оновленням:

SELECT user, host FROM mysql.user WHERE plugin = 'mysql_native_password';
ALTER USER 'app'@'%' IDENTIFIED WITH caching_sha2_password BY '...';

Тимчасово плагін можна ввімкнути (mysql_native_password = ON), але в MySQL 9 його видалено.

2. Видалено старий синтаксис реплікації. CHANGE MASTER TO, START SLAVE, SHOW SLAVE STATUS, SHOW MASTER STATUS, RESET MASTER і опції MASTER_* більше не існують. Треба оновити скрипти, системи моніторингу, Ansible-ролі, інструменти перемикання:

3. Видалено утиліту mysqlpump - замінити на mysqldump чи утиліти MySQL Shell.

4. Змінено значення за замовчуванням InnoDB - продуктивність може змінитися навіть без змін конфігурації:

• innodb_adaptive_hash_index: ON → OFF;
• innodb_change_buffering: all → none;
• innodb_io_capacity: 200 → 10000;
• innodb_log_buffer_size: 16 МБ → 64 МБ;
• temptable_max_ram: 1 ГБ → 3% пам'яті (у межах 1-4 ГБ).

Ці зміни відображають сучасні SSD, але для навантажень, що виграють від адаптивного хеш-індексу, варто порівняти продуктивність до і після.

5. Застарілі й видалені змінні в my.cnf. Сервер не стартує з невідомою змінною. Перевірити конфігурацію на тестовому інстансі.

Процес оновлення:

1. перевірка сумісності - util.checkForServerUpgrade() з MySQL Shell: знаходить застарілі плагіни, синтаксис, проблеми схеми;
2. оновлення на копії продакшену, прогін тестів застосунку і порівняння планів найважливіших запитів;
3. на продакшені - спершу репліки (новіша репліка може реплікувати зі старішого джерела, навпаки - ні), потім перемикання джерела на оновлену репліку;
4. бекап безпосередньо перед оновленням і план відкату (повернення на стару репліку).

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

Докладніше в документації: Що нового в MySQL 8.4

Значення MySQL за замовчуванням розраховані на те, що сервер ділить машину з іншими програмами: буферний пул 128 МБ, журнал повтору 100 МБ. Для виділеного сервера бази це в рази менше, ніж треба.

innodb_dedicated_server = ON (у файлі конфігурації, не на льоту) - InnoDB сам визначає ключові параметри за ресурсами машини. У MySQL 8.4 це два параметри:

innodb_buffer_pool_size:

innodb_redo_log_capacity = (кількість логічних процесорів / 2) ГБ, максимум 16 ГБ.

У MySQL 8.0 ця опція ще й встановлювала innodb_flush_method, у 8.4 - вже ні. Якщо якийсь із параметрів явно задано в конфігурації, явне значення має пріоритет.

Пастка контейнерів. Опція бачить пам'ять, яку повідомляє система. У контейнері з лімітом пам'яті (Docker, Kubernetes) MySQL може побачити пам'ять хоста, і 75% від неї перевищить ліміт контейнера - OOM-kill. У контейнерах параметри краще задавати явно.

Що ще варто переглянути на виділеному сервері:

• max_connections - за реальною кількістю процесів застосунку з запасом, не «про всяк випадок» тисячі;
• innodb_io_capacity / innodb_io_capacity_max - під можливості дисків (у 8.4 типове значення вже розраховане на SSD);
• innodb_flush_log_at_trx_commit = 1 і sync_binlog = 1 - лишити такими на основному сервері: це гарантія стійкості;
• binlog_expire_logs_seconds - щоб покривав інтервал між бекапами, але не з'їв диск;
• long_query_time і slow_query_log - увімкнути з порогом, що має сенс для застосунку;
• max_execution_time - запобіжник для SELECT, якщо застосунок не ставить власних тайм-аутів;
• table_open_cache, table_definition_cache - якщо таблиць тисячі (багатоорендні схеми);
• пам'ять на з'єднання (sort_buffer_size, join_buffer_size) - не збільшувати глобально без потреби: вони множаться на кількість активних з'єднань.

Як перевіряти ефект. Змінювати по одному параметру й вимірювати на реалістичному навантаженні. Популярні «оптимізовані конфіги» з інтернету часто містять параметри для старих версій, які на сучасному MySQL шкодять або вже видалені.

Залишок пам'яті після буферного пулу потрібен ОС (файловий кеш для бінарних журналів), з'єднанням, тимчасовим таблицям (TempTable), Performance Schema. Якщо на тому ж сервері ще й PHP чи Redis, innodb_dedicated_server вмикати не можна - він вважає, що вся пам'ять належить MySQL.

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

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

Сторона сервера. MySQL 8 при першому старті сам генерує самопідписані сертифікати й вмикає TLS за замовчуванням: клієнт, що підтримує TLS, використає його. Але:

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

Заборонити незашифровані підключення:

SET PERSIST require_secure_transport = ON;   -- для всіх, крім сокета

-- або для окремого облікового запису
ALTER USER 'app'@'%' REQUIRE SSL;
ALTER USER 'app'@'%' REQUIRE X509;           -- ще й клієнтський сертифікат

Сторона Laravel. Підключення налаштовується через опції PDO в config/database.php:

'options' => extension_loaded('pdo_mysql') ? array_filter([
    (PHP_VERSION_ID >= 80500 ? Pdo\Mysql::ATTR_SSL_CA : PDO::MYSQL_ATTR_SSL_CA) => env('MYSQL_ATTR_SSL_CA'),
    (PHP_VERSION_ID >= 80500 ? Pdo\Mysql::ATTR_SSL_VERIFY_SERVER_CERT : PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT) => true,
]) : [],

• ATTR_SSL_CA - сертифікат центру сертифікації, яким підписано сертифікат сервера. У хмарних провайдерів (RDS, Cloud SQL, PlanetScale) це їхній публічний CA-бандл;
• ATTR_SSL_VERIFY_SERVER_CERT = true - перевіряти сертифікат сервера. Без цього TLS є, але підключитися можна й до підставного сервера;
• для взаємної автентифікації - ATTR_SSL_CERT і ATTR_SSL_KEY (клієнтські сертифікат і ключ).

У PHP 8.5 константи PDO::MYSQL_ATTR_* оголошено застарілими на користь Pdo\Mysql::ATTR_* - звідси перевірка версії в типовому конфігу Laravel.

Як перевірити, що з'єднання справді зашифроване:

SHOW SESSION STATUS LIKE 'Ssl_cipher';   -- непорожнє значення = TLS
SHOW SESSION STATUS LIKE 'Ssl_version';  -- TLSv1.3 / TLSv1.2

З Laravel - тим самим запитом: DB::select("SHOW SESSION STATUS LIKE 'Ssl_cipher'"). А які з усіх поточних підключень ідуть без шифрування, показує performance_schema.status_by_thread зі змінною Ssl_cipher для кожного потоку.

Для консольного клієнта аналогічний рівень дає --ssl-mode=VERIFY_IDENTITY (перевіряє і ланцюжок, і що ім'я хоста збігається з сертифікатом), а --ssl-mode=REQUIRED лише шифрує без перевірки.

Ротація сертифікатів у MySQL 8 можлива без перезапуску: замінити файли й виконати ALTER INSTANCE RELOAD TLS.

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

Багато системних змінних MySQL динамічні - їх можна змінити на працюючому сервері. Проблема класичного SET GLOBAL: після перезапуску значення повертається до того, що у my.cnf. Хтось виправив налаштування під час інциденту, забув перенести в конфіг - і через місяць після перезапуску проблема повертається.

SET PERSIST (MySQL 8.0+) змінює значення і зараз, і назавжди:

SET PERSIST max_connections = 500;
SET PERSIST long_query_time = 0.5;

Значення зберігається у файлі mysqld-auto.cnf у каталозі даних (JSON) і застосовується при старті після my.cnf - тобто має пріоритет.

Варіанти:

• SET GLOBAL - лише до перезапуску;
• SET PERSIST - зараз і після перезапуску (для динамічних змінних);
• SET PERSIST_ONLY - лише після перезапуску. Для змінних, які не можна змінити на льоту (наприклад, innodb_buffer_pool_instances): «запам'ятати й застосувати при наступному старті».

Скасувати збережене:

RESET PERSIST max_connections;   -- прибрати одну змінну з mysqld-auto.cnf
RESET PERSIST;                   -- усі

RESET PERSIST не змінює поточного значення - лише прибирає його з файлу.

Звідки взялося поточне значення:

SELECT variable_name, variable_source, variable_path, set_time, set_user
FROM performance_schema.variables_info
WHERE variable_name = 'max_connections';
-- variable_source: COMPILED / GLOBAL (my.cnf) / PERSISTED / DYNAMIC ...

SELECT * FROM performance_schema.persisted_variables;

Видно, хто й коли змінив значення - корисно для розслідування інцидентів.

Права: SET PERSIST вимагає SYSTEM_VARIABLES_ADMIN, а PERSIST_ONLY для read-only змінних - ще й PERSIST_RO_VARIABLES_ADMIN.

Підводні камені:

• два джерела правди. Конфігурація розщеплюється між my.cnf (під контролем Ansible, Docker-образу, Git) і mysqld-auto.cnf (зміни з консолі). Система керування конфігурацією може «не бачити» значення, яке реально діє. Практичне правило: SET PERSIST для оперативних змін, а потім - перенести в основну конфігурацію й зробити RESET PERSIST;
• сервер може не стартувати, якщо збережено некоректне значення для PERSIST_ONLY змінної. Вимкнути читання файлу допомагає persisted_globals_load = OFF;
• секрети в mysqld-auto.cnf (наприклад, деякі змінні плагінів) можна зберігати зашифрованими через keyring;
• керовані хмарні бази (RDS, Cloud SQL) зазвичай не дозволяють SET PERSIST - там параметри змінюються через групи параметрів провайдера.

Зміни, що діють не на всіх одразу: SET GLOBAL/PERSIST змінює глобальне значення, а наявні з'єднання зберігають свої сесійні копії змінних. Нове значення sort_buffer_size побачать лише нові підключення - у постійних воркерах (Horizon, Octane) лише після їх перезапуску.

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