MySQL vs MariaDB: вибух бінлогів на диску — як утримати їх під контролем

Бінлоги: чому вони розростаються (і навіщо вони вам були)

Бінарний журнал MySQL/MariaDB — це запис лише для додавання змін. Залежно від конфігурації він зберігає оператори, знімки рядків або щось між ними (MIXED). Він живить реплікацію і дозволяє відновлення по точці часу. Він також за визначенням росте: це журнал. Якщо ви явно ним не керуєте, він писатиме доти, доки файлову систему не змусить навчитися життю.

Більшість інцидентів «вибуху диску бінлогами» мають доволі невеликий набір корінних причин:

• Політика збереження ніколи не була налаштована, тому логи фактично безкінечні.
• Політика є, але не може спрацювати, бо репліка (або інструмент бекапу) все ще потребує старих логів.
• Прискорення записів через формат ROW, великі транзакції або масові оновлення породжує величезні бінлоги.
• Простій репліки змушує первинний сервер зберігати все більший запас.
• Неправильні припущення про те, що робить «purge», як змінюють GTID, або що читає ваш інструмент для бекапів.

Одна оперативна цитата, яку слід сприймати як закон фізики:

«Надія — не стратегія.» — Джин Кранц

Управління бінлогами — це не «налаштував і забув». Це «налаштував і моніториш», з політикою збереження, що відповідає бізнес-цілям відновлення та реаліям вашої топології реплікації.

Цікавинки й трохи історії (бо контекст запобігає помилкам)

• Факт 1: Бінлог MySQL передував сучасному маркетингу «CDC». Це був прагматичний механізм реплікації задовго до того, як «стріми подій» стали модними.
• Факт 2: Реплікація на основі операторів була оригінальним за замовчуванням; вона була менша за розміром, але вразлива до неоднозначностей (подумайте NOW(), RAND() та запити, що «залежать від даних»).
• Факт 3: Логування по рядках стало практичним стандартом у багатьох середовищах, бо воно детерміноване, але може роздутися при масових оновленнях.
• Факт 4: GTID (Global Transaction ID) було введено, щоб зробити відмовостійкий перемикач більш прогнозованим; воно не усунуло потреби дисциплінованого зберігання бінлогів.
• Факт 5: MariaDB відійшла від MySQL і додала свою реалізацію GTID; операційні семантики не ідентичні, особливо щодо іменування та змінних стану.
• Факт 6: Опції «expire logs» змінювали назви з роками в MySQL, і старі блоги часто вводять в оману людей на нових версіях.
• Факт 7: Багато систем бекапу, які заявляють «без блокування», все одно покладаються на бінлоги для консистентності між таблицями; видалення логів під ними — класичний себезастріл.
• Факт 8: Relay-логи на репліках також можуть вибухнути; люди звинувачують первинний сервер, тоді як репліка тихо накопичує власний запас.

Жарт №1: бінлоги — як чеки. Вам не потрібно зберігати їх вічно, але пошкодуєте, якщо знищите за п’ять хвилин до аудиту.

MySQL vs MariaDB: відмінності, що впливають на ріст бінлогів

З першого погляду управління бінлогами виглядає однаково: налаштуйте збереження, переконайтеся, що очищення безпечне, та стежте за реплікацією. Ближче — MySQL і MariaDB мають достатньо відмінностей, щоб зіпсувати вам день, якщо ви копіпастите поради між ними.

1) Ручки збереження: схожа ідея, різні назви і крайові випадки

MySQL історично використовував expire_logs_days. Новіші версії MySQL віддають перевагу binlog_expire_logs_seconds. Багато систем досі мають обидва в конфігах, бо нікому не хочеться видаляти старі рядки.

MariaDB також підтримує expire_logs_days, а у новіших релізах підтримується й binlog_expire_logs_seconds. Але ефективна поведінка може відрізнятися за версією та способом запуску реплікації й бекапів. Не вгадуйте; перевіряйте через SHOW VARIABLES і спостерігайте фактичні очищення.

2) Поведінка GTID: концепція схожа; операційні деталі — ні

GTID MySQL тісно інтегрований зі станом реплікації (gtid_executed, gtid_purged) і зазвичай робить інструменти відмово-перемикання консистентнішими. GTID MariaDB схожий за ідеєю, але відрізняється в представленні і деяких робочих процесах відновлення.

Для збереження бінлогів ключовий момент простий: GTID сам по собі не зменшує використання диску. Він зменшує когнітивне навантаження у питаннях «що де застосовано», що допомагає безпечно видаляти логи, але вам все одно потрібна політика збереження і безпечні процедури очищення.

3) Налаштування за замовчуванням і «безпечно за замовчуванням» залежать від дистрибутива

Пакетні конфігурації в ентерпрайз-середовищах іноді поставляються з увімкненим бінлогуванням і без налаштованого терміну зберігання, бо «реплікація може знадобитися пізніше». Це не обережність; це відкладена аварія.

4) Стиснення та шифрування бінлогів: корисні функції, але не безкоштовні

Залежно від версій у вас можуть бути опції шифрування та стиснення бінлогів. Шифрування збільшує навантаження на CPU; стиснення міняє CPU на економію диска й вводу/виводу. Обидві варто розглянути, але лише після налагодження основ (збереження + здоров’я реплікації).

Швидкий план діагностики: знайдіть вузьке місце впродовж хвилин

Це «у вас 10 хвилин до заповнення диску» план. Не сперечайтеся про архітектуру, поки сервер підтискає вас.

Перше: підтвердьте, що це бінлоги, і кількісно оцініть швидкість росту

• Скільки місця залишилось на диску?
• Який розмір каталогу бінлогів?
• Чи створюються нові бінлог-файли швидко, чи один файл росте швидко?

Друге: перевірте, чи блокується очищення реплікацією або інструментами

• Чи є якась репліка з великим лагом або офлайн?
• Чи читає старі бінлоги процес бекапу?
• Чи налаштовано збереження і чи воно реально очищує?

Третє: вирішіть найменш поганий аварійний захід

• Якщо реплікація здорова — безпечно очистіть, виходячи з позиції репліки/GTID.
• Якщо репліка не здорова — або швидко ремонтуйте репліку, або прийміть, що її зламаєте і заплануйте відновлення.
• Якщо зараз немає місця — виграйте час: розширте файлову систему, перемістіть бінлоги або тимчасово зупиніть записи (тротлінг застосунку), доки не відновите контроль.

Тактичний пріоритет завжди один: уникнути краху, що пошкоджує таблиці і перетворює керований інцидент на повне відновлення.

Практичні завдання (з командами): виміряйте, вирішіть, виправте

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

Завдання 1: Перевірте запас дискового простору і яка файлов система під загрозою

cr0x@server:~$ df -hT
Filesystem     Type   Size  Used Avail Use% Mounted on
/dev/nvme0n1p2 ext4   450G  430G   20G  96% /
/dev/nvme1n1p1 xfs    1.8T  1.1T  700G  62% /var/lib/mysql

Значення: Ви близькі до заповнення на /, але MySQL живе на іншому маунті з запасом. Якщо бінлоги на /, у вас лишаються хвилини до проблем.

Рішення: Підтвердьте datadir MySQL і шлях запису бінлогів; перемістіть їх з кореневої файлової системи, якщо потрібно.

Завдання 2: Знайдіть, де реально живуть бінлоги (не припускайтесь)

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'log_bin%'; SHOW VARIABLES LIKE 'datadir';"
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| log_bin       | ON                        |
| log_bin_basename | /var/lib/mysql/binlog   |
| log_bin_index | /var/lib/mysql/binlog.index |
+---------------+---------------------------+
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| datadir       | /var/lib/mysql/    |
+---------------+--------------------+

Значення: Бінлоги під /var/lib/mysql. Добре. Якби ви побачили щось на кшталт /var/log або маленький кореневий маунт — це був би ваш курильний стовпчик.

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

Завдання 3: Визначте розмір каталогу бінлогів і найбільші файли

cr0x@server:~$ sudo du -sh /var/lib/mysql/binlog* 2>/dev/null | sort -h | tail -n 5
1.0G    /var/lib/mysql/binlog.000812
1.0G    /var/lib/mysql/binlog.000813
1.0G    /var/lib/mysql/binlog.000814
1.0G    /var/lib/mysql/binlog.000815
812G    /var/lib/mysql/binlog.index

Значення: Рядок з binlog.index вивід вводить в оману, бо du дивно рахує посилання та метадані; реальна історія в тому, що існує багато бінлог-файлів і кожен ~1G.

Рішення: Порахуйте файли й підрахуйте загальний розмір точніше (наступне завдання). Якщо ви генеруєте багато файлів по 1G швидко, ротація відбувається, але ріст усе ще реальний.

Завдання 4: Порахуйте файли бінлогів і загальний обсяг точно

cr0x@server:~$ sudo find /var/lib/mysql -maxdepth 1 -type f -name 'binlog.*' -printf '%s\n' | awk '{sum+=$1} END{printf "files=%d total=%.2fG\n", NR, sum/1024/1024/1024}'
files=815 total=812.45G

Значення: Ви зберігаєте ~812G бінлогів. Це не «трохи беклогу», це організаційне рішення, якого ніхто навмисно не приймав.

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

Завдання 5: Підтвердьте змінні збереження бінлогів (що сервер думає)

cr0x@server:~$ mysql -e "SHOW VARIABLES WHERE Variable_name IN ('expire_logs_days','binlog_expire_logs_seconds','sync_binlog','binlog_format','binlog_row_image');"
+---------------------------+-----------+
| Variable_name             | Value     |
+---------------------------+-----------+
| binlog_expire_logs_seconds| 0         |
| binlog_format             | ROW       |
| binlog_row_image          | FULL      |
| expire_logs_days          | 0         |
| sync_binlog               | 1         |
+---------------------------+-----------+

Значення: Збереження фактично вимкнено. Також ROW + FULL може бути величезним для широких таблиць і інтенсивних оновлень. sync_binlog=1 забезпечує стійкість, але може посилити I/O.

Рішення: Встановіть значення збереження, що відповідає вашим цілям відновлення і реаліям реплікації. Розгляньте binlog_row_image=MINIMAL, де безпечно і підтримується, але спочатку перевірте сумісність застосунку й реплікації.

Завдання 6: Подивіться, які бінлоги існують з перспективи MySQL

cr0x@server:~$ mysql -e "SHOW BINARY LOGS;" | tail -n 6
| binlog.000810 | 1073741961 |
| binlog.000811 | 1073741982 |
| binlog.000812 | 1073741991 |
| binlog.000813 | 1073742005 |
| binlog.000814 | 1073742011 |
| binlog.000815 |  932145331 |

Значення: Сервер відстежує бінлоги і їхні розміри. Цей вивід — те, проти чого ви робите очищення — не видаляйте файли вручну і не чекайте, що MySQL буде «аплодувати».

Рішення: Якщо потрібно очистити — робіть це через SQL (PURGE BINARY LOGS) або через робочі процеси, що враховують mysqlbinlog. Ручне видалення — останній засіб і зазвичай супроводжується «чому він не стартує?»

Завдання 7: Перевірка стану реплікації на репліці (класичний файл/позиція)

cr0x@server:~$ mysql -e "SHOW REPLICA STATUS\G" | egrep 'Source_Log_File|Read_Source_Log_Pos|Relay_Source_Log_File|Exec_Source_Log_Pos|Seconds_Behind_Source|Replica_IO_Running|Replica_SQL_Running' -n
12:Source_Log_File: binlog.000702
13:Read_Source_Log_Pos: 98433122
28:Relay_Source_Log_File: binlog.000702
29:Exec_Source_Log_Pos: 98433122
34:Seconds_Behind_Source: 0
40:Replica_IO_Running: Yes
41:Replica_SQL_Running: Yes

Значення: Ця репліка синхронізована і виконує поточні логи. Якщо всі репліки такі — зазвичай можете безпечно очищати старі бінлоги до найстарішого Source_Log_File, що все ще потрібен.

Рішення: Зберіть це з кожної репліки. «Найстаріший потрібний бінлог» між усіма репліками — ваша межа очищення.

Завдання 8: Перевірка стану реплікації на репліці (GTID-варіант)

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'gtid_mode'; SHOW MASTER STATUS\G; SHOW REPLICA STATUS\G" | egrep 'gtid_mode|Executed_Gtid_Set|Retrieved_Gtid_Set|File:|Position:|Seconds_Behind_Source' -n
1:gtid_mode	ON
7:File: binlog.000815
8:Position: 932145331
19:Retrieved_Gtid_Set: 2f1d3c0a-2f9d-11ee-8f6a-0242ac120002:1-93381221
20:Executed_Gtid_Set: 2f1d3c0a-2f9d-11ee-8f6a-0242ac120002:1-93381221
26:Seconds_Behind_Source: 0

Значення: GTID увімкнено і репліка синхронізована. Очищення все ще базується на тому, що репліки потребують; GTID просто дає чистіший стан для міркувань.

Рішення: Використовуйте GTID-набори, щоб перевірити, що репліки виконали те, що ви плануєте видалити. Якщо репліка пропускає частини набору — ви не можете очищати ці логи без прийняття відновлення репліки.

Завдання 9: Знайдіть репліку, що блокує очищення (офлайн або з великим лагом)

cr0x@server:~$ mysql -e "SHOW REPLICA HOSTS;"
+-----------+------------------+------+-----------+--------------------------------------+
| Server_id | Host             | Port | Rpl_recovery_rank | Master_id                    |
+-----------+------------------+------+-----------+--------------------------------------+
| 102       | db-replica-a     | 3306 | 0         | 101                                  |
| 103       | db-replica-b     | 3306 | 0         | 101                                  |
| 104       | db-replica-c     | 3306 | 0         | 101                                  |
+-----------+------------------+------+-----------+--------------------------------------+

Значення: У вас зареєстровано три репліки. «Зареєстровано» не означає «здорові».

Рішення: Зайдіть на кожну репліку і запустіть Завдання 7/8. Найгірша з них диктує ваш мінімум збереження, якщо ви не готові відтворити її.

Завдання 10: Перевірте ріст relay-логів на репліці (інша бомба для диска)

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'relay_log%';"
+-------------------+-----------------------------------+
| Variable_name     | Value                             |
+-------------------+-----------------------------------+
| relay_log         | /var/lib/mysql/relaylog           |
| relay_log_index   | /var/lib/mysql/relaylog.index     |
| relay_log_info_file | relay-log.info                  |
+-------------------+-----------------------------------+

cr0x@server:~$ sudo find /var/lib/mysql -maxdepth 1 -type f -name 'relaylog.*' -printf '%s\n' | awk '{sum+=$1} END{printf "relay files=%d total=%.2fG\n", NR, sum/1024/1024/1024}'
relay files=390 total=412.77G

Значення: Репліка накопичує relay-логи. Це відбувається, коли SQL-потік не встигає, зупинено або падає повторно. Люди часто «фіксують» збереження бінлогів на первинному сервері, тоді як саме репліка заповнює диск.

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

Завдання 11: Перевірте довгі транзакції, що створюють величезні бінлог-події

cr0x@server:~$ mysql -e "SELECT trx_id, trx_started, TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) AS age_s, trx_rows_modified FROM information_schema.innodb_trx ORDER BY age_s DESC LIMIT 5;"
+--------+---------------------+-------+-------------------+
| trx_id | trx_started         | age_s | trx_rows_modified |
+--------+---------------------+-------+-------------------+
| 987655 | 2025-12-31 09:11:02 | 18420 | 2289341           |
| 987654 | 2025-12-31 13:55:10 |   122 | 0                 |
+--------+---------------------+-------+-------------------+

Значення: Транзакція на 5 годин, що модифікує мільйони рядків, — це фабрика бінлогів. Навіть якщо вона «правомірна», це операційний ризик: навантажує диск, реплікацію і відновлення після збою.

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

Завдання 12: Виміряйте поточну пропускну здатність запису бінлогів (чи це раптовий сплеск?)

cr0x@server:~$ sudo iostat -dx 1 3 | egrep 'Device|nvme1n1'
Device            r/s     w/s   rMB/s   wMB/s avgrq-sz avgqu-sz await  svctm  %util
nvme1n1          12.0   980.0    0.4    86.2    176.2     4.12   4.1   0.7   72.0
nvme1n1          10.0  1100.0    0.3    92.5    172.0     6.88   6.2   0.8   89.0
nvme1n1          11.0  1205.0    0.3   101.1    171.4     8.40   7.1   0.9   96.0

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

Рішення: Визначте топ-писачів (наступне завдання) і вирішіть, чи призупиняти, тротлити чи оптимізувати їх.

Завдання 13: Визначте найпомітніші записуючі запити (performance_schema digest)

cr0x@server:~$ mysql -e "SELECT DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, ROUND(SUM_TIMER_WAIT/1e12,1) AS total_s FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_ROWS_AFFECTED DESC LIMIT 3\G"
*************************** 1. row ***************************
DIGEST_TEXT: UPDATE orders SET status = ? WHERE created_at < ? AND status = ?
COUNT_STAR: 412
SUM_ROWS_AFFECTED: 18922341
total_s: 980.2
*************************** 2. row ***************************
DIGEST_TEXT: DELETE FROM sessions WHERE expires_at < ?
COUNT_STAR: 8122
SUM_ROWS_AFFECTED: 4491122
total_s: 122.8

Значення: Навантаження виконує великі оновлення і видалення. У форматі ROW це бінлог-повідомлення, а не лише «робота бази даних».

Рішення: Додайте індекси, щоб зменшити число торкнутих рядків, розбивайте операції на частини або плануйте важкі завдання в менш завантажений час. Якщо це одноразова міграція — плануйте додатковий запас диска і межу очищення.

Завдання 14: Перевірте, чи автоматичне очищення працює (стан експірації бінлогів)

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Binlog%';"
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| Binlog_cache_disk_use     | 22       |
| Binlog_cache_use          | 98121    |
| Binlog_stmt_cache_disk_use| 0        |
| Binlog_stmt_cache_use     | 0        |
+---------------------------+----------+

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

Рішення: Якщо збереження встановлено, а файли не віддаляються — ймовірно репліка/бекап блокує очищення або у вас версійна невідповідність у поведінці.

Завдання 15: Безпечно очистити бінлоги до межі (за файлом)

cr0x@server:~$ mysql -e "PURGE BINARY LOGS TO 'binlog.000780';"

Значення: MySQL/MariaDB видалить бінлог-файли строго раніше за binlog.000780 (не включаючи його), оновивши індекс і внутрішній стан.

Рішення: Робіть це тільки після підтвердження, що всі репліки й споживачі (бекап/CDC) пройшли цю межу. Якщо одна репліка ще потребує binlog.000702, очищення до 780 її зламає.

Завдання 16: Аварійно: поверніться до нового бінлог-файлу (виграйте трохи ясності)

cr0x@server:~$ mysql -e "FLUSH BINARY LOGS;"

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

Рішення: Використовуйте під час інциденту, коли хочете відомої «точки відліку» для аналізу і відокремити поточну активність від беклогу.

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

Бінлоги не «сміття». Вони «тимчасова безпека». Ваше завдання — вирішити, скільки безпеки ви можете дозволити собі, а потім безжально це застосувати.

Виберіть вік збереження, виходячи з цілей відновлення, а не від відчуттів

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

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

Задайте збереження явно (і підтвердьте, яку змінну використовує ваша версія)

У сучасному MySQL binlog_expire_logs_seconds — ясніший руків. На старих інсталяціях може бути лише expire_logs_days. MariaDB варіюється за версією, і деякі середовища несуть обидві налаштування. Вам потрібне одне авторитетне налаштування і доказ, що воно працює.

Оперативна порада: якщо успадкували сервер без збереження, не ставте «30 днів» як «безпечний» перший крок. Так ви лишаєте бомбу під письмовим столом. Почніть з реалістичного вікна — часто 3–7 днів — і збільшуйте тільки якщо довели можливість зберігати і воно дійсно потрібне.

Не очищайте видаленням файлів

Так, ви можете rm /var/lib/mysql/binlog.000123. І так, інколи можна уникнути негараздів. Але це як правило призводить до:

• реплік, що застрягли з помилкою і просять логи, яких немає
• сервер, заплутаний у своєму індексі бінлогів
• зламані бекап/PITR робочі процеси у тонкі способи

Використовуйте PURGE BINARY LOGS, якщо тільки сервер не зупинений і ви не робите хірургію при катастрофі. Навіть тоді Плануйте рестарт і очікуйте наслідків для реплікації.

Реплікація та лаг: найпоширеніша причина, чому очищення «не працює»

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

Як реплікація блокує очищення на практиці

Поширені блокувальники:

• Репліка офлайн для обслуговування, мережевих проблем або «хтось її зупинив» і забув.
• SQL-потік репліки зупинений через помилку (duplicate key, відсутня таблиця після невдалого зміни схеми тощо).
• Репліка I/O забита при застосуванні ROW-подій і не може встигнути.
• Репліка накопичує великі relay-логи і застрягла в повільному застосуванні.
• Споживачі, що не є репліками (CDC-інструменти, пайплайни аудиту, агенти бекапу) читають бінлоги і вимагають історії.

Будьте рішучими щодо нездорових реплік

Якщо репліка була офлайн достатньо довго, щоб її беклог з’їв диск первинного, потрібно вирішити:

• Чи критична ця репліка? Якщо так — виправте її зараз: відновіть мережу, вирішіть помилки SQL, збільшіть паралелізм застосування за потреби або відтворіть її зі свіжого знімка.
• Чи «приємно мати»? Якщо ні — навмисно зламайте її: очистіть бінлоги, щоб врятувати первинний сервер, а потім відтворіть репліку пізніше.

Не тримайте первинний сервер майже повним тільки щоб зберегти занедбану репліку. Так ви перетворюєте одну поломку на дві.

PITR і бекапи: як зберегти відновлення без нескінченних логів

Якщо ви хочете відновлення по точці часу, вам потрібні дві інгредієнти:

1. послідовний базовий бекап (повний або інкрементний ланцюжок)
2. бінлоги з часу цього базового бекапу до точки відновлення

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

Практична логіка збереження, що працює

• Тримайте бінлоги принаймні стільки, скільки вам може знадобитися, щоб виявити проблему з даними (латентність виявлення людиною реальна).
• Тримайте бінлоги принаймні на інтервал одного повного бекапу плюс запас безпеки. Якщо бекапи щоденні — зазвичай 2–7 днів.
• Підганяйте межі очищення під завершення бекапу. Очищення логів, які бекап ще не «захопив», — шлях до втрати PITR.

Жарт №2: єдине, що страшніше за диск, повний бінлогів — це диск без бінлогів за п’ять хвилин до того, як вони знадобляться.

Формат рядків: ROW vs MIXED vs STATEMENT

Розмір бінлога — це не тільки «трафік». Це також «як ви представляєте трафік». Вибір формату може суттєво змінити обсяг бінлогів.

ROW: детермінований, важкий і зазвичай правильний за замовчуванням

Формат ROW логгить фактичні зміни рядків. Це золото для коректності і безпеки реплікації. Це також дорого, коли ви оновлюєте багато рядків, або коли рядки широкі (багато колонок), або коли оновлення зачіпають blobs/text.

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

binlog_row_image: FULL проти MINIMAL

Де підтримується і безпечно, binlog_row_image=MINIMAL може зменшити розмір бінлога, логгячи менше колонок. Це сильно допомагає для широких таблиць, де оновлюються лише кілька колонок.

Але: «безпечно» залежить від версії, інструментів і чи потребують downstream-споживачі повних знімків. Деякі CDC-набори хочуть FULL-знімки. Деякі аудиторські пайплайни від них залежать. Знайте своїх споживачів.

MIXED і STATEMENT: менше місця, але з гострими краями

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

У сучасних продакшен-середовищах ROW — нудний вибір, що тримає вас поза постмортемами. Якщо ви оптимізуєте розмір бінлогів через зміну формату, робіть це лише після тестування коректності реплікації під вашим точним навантаженням.

Реальність зберігання: IOPS, fsync, стиснення і куди йдуть байти

Ріст бінлогів — це проблема БД і проблема зберігання. Якщо ваші диски повільні, бінлоги можуть стати одночасно симптомом і причиною: важкі записи заповнюють диск, а I/O тиск сповільнює застосування репліки, що змушує довше зберігати логи, що знову заповнює диск.

sync_binlog і компроміс стійкості

sync_binlog=1 означає, що сервер часто робить fsync під час запису бінлога. Це добре для стійкості (менше транзакцій втрачається при краху) і часто необхідне для жорстких гарантій відновлення. Це також більш вимогливо до затримки зберігання.

Якщо ваше сховище не витягує це, не «оптимізуйте» шляхом бездумного послаблення стійкості. Виправте сховище, розподіліть I/O або масштабуйтесь. Налаштування стійкості — не налаштування продуктивності; це ручки ризику.

Виділити бінлоги на окрему файлову систему (іноді)

Помістити бінлоги на власний маунт може бути розумним кроком:

• запобігає заповненню файлової системи datadir і «запалюванню» сервера
• спрощує моніторинг і алерти
• може співпасти з швидшими носіями

Але це також може створити конкуренцію, якщо ви перемістите їх на повільні загальні диски. Мета — ізоляція з адекватною продуктивністю, а не «перемістити кудись і надіятися».

Стиснення: корисне, коли вузьке місце — мережа/IO

Якщо payload бінлога величезний і вузьке місце — диск або мережа, стиснення може допомогти. Якщо CPU вже завантажений, воно може зіграти злий жарт. Спершу виміряйте.

Три міні-історії з практики

Міні-історія 1: інцидент через неправильне припущення

У них був первинний сервер і дві репліки. Простенько. Команда також мала вимогу відповідності, щоб зберігати «достатньо історії для розслідування». Хтось витлумачив це як «зберігати бінлоги вічно», бо бінлоги нібито виглядають як журнал аудиту, якщо прищуритись достатньо сильно.

Неправильне припущення виявилося через місяці під час рутинного оновлення ядра. Одна репліка довше була офлайн через проблему з прошивкою NIC, тож пропустила багато змін. Первинний сумлінно зберігав бінлоги, щоб репліка могла наздогнати. Ніхто не помітив, бо первинний ще мав диск. Спочатку.

Потім з’явилася пакетна задача: велике донаповнення з оновленнями широкої таблиці в ROW-форматі. Бінлоги підскочили. Первинний заповнив файлову систему посеред робочого дня і припинив приймати записи. Аварія не почалася з пакетної задачі; вона почалася з припущення, що «бінлоги — це журнал аудиту» і «збереження може бути нескінченним».

Після інциденту вони впровадили дві речі: реальний pipeline аудиту (окремо від бінлогів) і явну політику збереження бінлогів, що погоджена з бекап/PITR потребами. Репліку, що була офлайн, відтворили зі свіжого снапшоту. Ніхто не був радий, але всі спокійно спали.

Міні-історія 2: оптимізація, що дала протилежний ефект

Інша команда боролася з лагом реплікації і використанням диску. Хтось придумав ідею: зменшити розмір бінлогів, перемкнувши з ROW на STATEMENT. У лабораторії це спрацювало. Тиждень у продакшені теж. Такі історії зазвичай так і починаються.

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

Наслідки були дорогі, бо субтильні. Вони витратили дні на звірку таблиць, переклонування реплік і побудову перевірок виявлення. Оптимізація бінлога заощадила диск і коштувала довіри.

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

Міні-історія 3: нудна, але правильна практика, що врятувала день

Це моя улюблена, бо тут немає героїзму. Є компетентність. Компанія запускала MySQL-праймарі з трьома репліками в двох регіонах. Вони трактували збереження бінлогів як частину планування ємності, а не як післядум.

У них був рукопис: щотижня перевіряти, що найстаріша позиція репліки в межах збереження; щомісяця проводити PITR-дрил у песочниці; щодня тримати алерт, якщо зростання файлової системи бінлогів перевищує поріг за годину. Алерти були тихі більшість часу — саме для цього вони і потрібні.

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

Немає аутейджу. Немає воєнної кімнати. Просто тикет, графік і кілька команд. Найкращий інцидент — це той, який ніколи не стає історією на конференції.

Поширені помилки: симптом → корінна причина → виправлення

1) Диск заповнюється, хоча ви налаштували збереження

Симптом: Файли бінлогів продовжують накопичуватися; найстаріший файл — тижнями старий; змінні збереження встановлені.

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

Виправлення: Визначте найстаріший потрібний бінлог серед реплік, відремонтуйте або відтворіть відстаючу репліку, перевірте правильну змінну через SHOW VARIABLES і підтвердьте, що файли старіють і видаляються.

2) Очищення негайно ламає реплікацію

Симптом: Помилки I/O-потоку репліки: «Could not find first log file name in binary log index file.»

Корінна причина: Ви очистили далі, ніж репліка встигла забрати/виконати, або видаляли файли вручну.

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

3) Диск репліки заповнюється, а первинний виглядає нормально

Симптом: Репліка не має місця; первинний має запас.

Корінна причина: Relay-логи накопичуються, бо SQL-потік зупинено/повільний; або relay_log_purge не працює через помилки.

Виправлення: Виправте помилку SQL-потоку, підвищте пропускну здатність застосування, підтвердіть очищення relay-логів або відтворіть репліку.

4) Бінлоги величезні під час бекапу/міграцій

Симптом: Одна робота створює сотні ГБ бінлогів; репліки відстають годинами.

Корінна причина: Великі транзакції, формат ROW, широкі рядки і не розбиті операції оновлення/видалення.

Виправлення: Розбивайте операції, додайте/перевірте індекси, уникайте торкання непотрібних колонок, розгляньте binlog_row_image=MINIMAL там, де безпечно, і плануйте важкі роботи з запасом ємності.

5) «Нам потрібні бінлоги для аудиту» перетворюється на «ми втратили вихідні»

Симптом: Нескінченне збереження під приводом відповідності; постійне зростання диска; ніхто не може описати процедуру відновлення чи розслідування.

Корінна причина: Бінлоги використовують замість спеціального аудиторського журналу.

Виправлення: Побудуйте pipeline аудиту/CDC, призначений для зберігання й запитів; зберігайте бінлоги лише стільки, скільки потрібно для реплікації/PITR.

Чек-листи / покроковий план

Покроково: візьміть ріст бінлогів під контроль (не ламаючи нічого)

1. Перелік споживачів: перерахунок реплік, CDC-інструментів, агентів бекапу, що читають бінлоги.
2. Виміряйте поточний слід бінлогів: загальний розмір, кількість файлів, швидкість росту за годину/день.
3. Підтвердьте налаштування збереження: з’ясуйте, яка змінна діє для вашої версії; приберіть конфліктні старі налаштування.
4. Підтвердьте, що очищення можливе: зберіть позиції реплік/GTID-набори; знайдіть найстарішу потрібну межу.
5. Виправте відстаючі/офлайн репліки: або наздоганяйте їх, або вирішуйте відтворити; не дозволяйте їм утримувати первинний у заручниках.
6. Очищайте безпечно: використовуйте SQL-команди очищення до перевіреної межі.
7. Налаштуйте алерти: використання диска, швидкість росту каталогу бінлогів, лаг репліки, ріст relay-логів на репліках.
8. Зменшіть прискорення записів: розбивайте роботи, правильно індексуйте, перегляньте налаштування row image там, де безпечно.
9. Синхронізуйте з бекапами: гарантуйте послідовну базу бекапів і задокументовані кроки PITR.
10. Практикуйте відновлення: періодичні дрилі PITR. Якщо ви не можете відновити — збереження лише імітація.

Аварійний чек-лист: диск на 95% і росте

1. Підтвердьте, яка файлову систему заповнено (df -hT).
2. Підтвердьте, що бінлоги домінують у використанні (du / find загальний розмір).
3. Визначте найстарішу позицію репліки/GTID і чи якась репліка офлайн.
4. Якщо репліки здорові: очистіть до безпечної межі.
5. Якщо репліка офлайн і не критична: прийміть її ламання, очистіть, щоб врятувати первинний, відтворіть потім.
6. Якщо ви не можете очищати достатньо швидко: розширте файлову систему або перемістіть бінлоги, щоб виграти час, а потім зробіть правильний фікс.

ЧаПи

1) Чи можна просто вимкнути бінлогування, щоб зупинити ріст диска?

Можна, але, ймовірно, не варто. Вимкнення бінлогів ламає реплікацію і позбавляє PITR. Якщо вам справді не потрібні ні те, ні інше — вимкніть це свідомо і оновіть операційні припущення. Інакше виправляйте збереження і здоров’я реплікації.

2) Чому expire_logs_days нічого не видалив?

Або воно встановлено в 0 (вимкнено), або ви на версії, де інша змінна має пріоритет, або щось все ще потребує логів (репліка/споживач). Перевірте через SHOW VARIABLES, а потім статус реплік.

3) Який найбезпечніший спосіб очищення бінлогів?

Використовуйте SQL: PURGE BINARY LOGS TO 'binlog.XXXXXX'; після підтвердження, що всі репліки/споживачі більше не потребують ранніх логів. Найбезпечніший «людський процес» — обчислити межу очищення від найстарішої репліки і задокументувати це в інцидент-тікеті.

4) Чи однакові команди очищення в MariaDB і MySQL?

Основні SQL-команди дуже схожі. Відмінності, що болять, зазвичай пов’язані з реалізацією GTID, версіонно-специфічними змінними і тим, що чекають ваші інструменти. Ставтеся до кожного кластера як до власної екосистеми: перевіряйте поведінку, не покладайтеся на пам’ять.

5) Скільки збереження бінлогів мені тримати?

Тримайте стільки, щоб покрити: (a) час від вашого останнього валідного базового бекапу до тепер, плюс (b) латентність виявлення/реагування, плюс (c) час відтворення репліки, якщо ви на неї покладаєтесь. Для багатьох команд це 3–7 днів. Для деяких — 24 години. Для інших — довше. Якщо ви не можете це обґрунтувати історією відновлення, вам воно не потрібне.

6) Чи зменшують GTID розмір бінлогів?

Ні. GTID допомагає у міркуванні про стан реплікації і перемиканні. Розмір бінлогів визначається навантаженням і форматом (ROW vs STATEMENT), знімками рядків і патернами транзакцій.

7) Чому бінлоги величезні, коли ми виконуємо DELETE?

У форматі ROW велике видалення логгить подію рядка для кожного видаленого рядка. БД виконує реальну роботу і її фіксує. Виправлення: розбивати delete на порції, мати коректні індекси і планувати важкі роботи з запасом ємності.

8) Якщо репліка відстає тижнями, чи тримати бінлоги, поки вона не наздожене?

Зазвичай ні. Якщо репліка настільки відстає, зазвичай швидше і безпечніше відтворити її зі свіжого снапшоту. Тримати тижні бінлогів на первинному — значить ризикувати доступністю первинного заради репліки, яка вже підвела вас.

9) Що робити з «вибухом бінлогів» на репліці?

Часто це relay-логи, а не бінлоги. Перевірте каталог relay-логів на репліці і чи зупинено застосування SQL. Виправте застосування або відтворіть репліку; не просто збільшуйте диск і не називайте це вирішенням.

10) Чи можна перемістити бінлоги на інший диск без простою?

Іноді, залежно від версії і конфігурації, але плануйте вікно обслуговування. Операційно безпечний крок: зупинити MySQL, перемістити файли, налаштувати конфіг (log_bin_basename шлях), запустити MySQL, перевірити SHOW BINARY LOGS. Якщо вам потрібен «без простою», треба проектувати топологію (failover), а не хитрувати з файловими системами.

Висновок: наступні дії, що реально запобігають повторенню

Якщо ви запам’ятаєте одну річ: бінлоги не «виходять з-під контролю». Вони роблять саме те, про що ви їх просили — зберігають зміни назавжди — доки диск не примусить вас припинити просити.

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

1. Встановіть явне збереження (в секундах/днях) і підтвердьте, що воно підходить для вашої версії і реально очищує.
2. Зробіть здоров’я реплікації пріоритетом SLO; зламана репліка — не нешкідливий елемент, а ризик для диска.
3. Зв’яжіть збереження бінлогів з бекапами і проводьте PITR-дрилі, щоб знати, за що платите цими байтами.
4. Інструментуйте швидкість росту і ставте алерти на «ГБ на годину», а не лише «диск на 90%».
5. Зменште прискорення записів у масових роботах: розбивайте, індексуйте, плануйте і уникайте гігантських транзакцій.

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