MySQL vs MariaDB : explosion des binlogs sur disque — comment la maîtriser

Binlogs : pourquoi ils explosent (et pourquoi vous les vouliez)

Le binaire log de MySQL/MariaDB est un journal append-only des modifications. Selon la configuration, il enregistre des requêtes, des images de lignes, ou un mélange (MIXED). Il alimente la réplication et permet la récupération point-in-time. Il croît aussi par définition : c’est un journal. Si vous ne le gérez pas explicitement, il continuera d’écrire jusqu’à ce que le système de fichiers vous inflige une leçon.

La plupart des incidents d’« explosion des binlogs sur disque » ont un ensemble étonnamment restreint de causes racines :

• La rétention n’a jamais été configurée, donc les logs sont pratiquement infinis.
• La rétention existe mais ne peut pas s’appliquer, parce qu’un réplicat (ou un outil de sauvegarde) a encore besoin des anciens logs.
• Amplification d’écritures due au format ROW, à de grosses transactions ou à des mises à jour en masse qui génèrent d’énormes binlogs.
• Temps d’arrêt d’un réplicat qui force le primaire à conserver un arriéré croissant.
• Hypothèses déplacées sur ce que fait « purge », comment les GTID changent l’état, ou ce que lit votre outil de sauvegarde.

Une citation opérationnelle que vous devriez traiter comme une loi de la physique :

« L’espoir n’est pas une stratégie. » — Gene Kranz

La gestion des binlogs n’est pas « configurez et oubliez ». C’est « configurez et surveillez », avec une politique de rétention qui correspond aux objectifs de reprise métier et à la réalité de votre topologie de réplication.

Faits intéressants et un peu d’histoire (parce que le contexte évite les erreurs)

• Fait 1 : Le binlog de MySQL précède le marketing « CDC » moderne. C’était un mécanisme de réplication pragmatique bien avant que les « flux d’événements » ne deviennent à la mode.
• Fait 2 : La réplication basée sur les requêtes (statement) était le comportement par défaut à l’origine ; elle était plus petite mais vulnérable à la non-déterminisme (pensez à NOW(), RAND(), ou des requêtes dépendant des données).
• Fait 3 : Le logging basé sur les lignes (row) est devenu le choix pratique par défaut dans de nombreux environnements car il est déterministe, mais il peut exploser en taille sur des mises à jour massives.
• Fait 4 : Les GTID (Global Transaction ID) ont été introduits pour rendre le basculement de réplication plus sain ; ils n’ont pas supprimé la nécessité d’une discipline de rétention des binlogs.
• Fait 5 : MariaDB a divergé de MySQL et a ajouté sa propre implémentation de GTID ; les sémantiques opérationnelles ne sont pas identiques, notamment autour du nommage et des variables d’état.
• Fait 6 : Les options « expire logs » ont changé de nom au fil des années dans MySQL, et de vieux billets de blog induisent souvent en erreur les personnes sur des versions récentes.
• Fait 7 : Beaucoup de systèmes de sauvegarde qui prétendent faire des sauvegardes « sans verrou » s’appuient quand même sur les binlogs pour assurer la cohérence entre les tables ; supprimer les logs sous eux est un classique auto-sabotage.
• Fait 8 : Les relay logs sur les réplicas peuvent aussi exploser ; les gens accusent les binlogs du primaire alors que le réplicat amasse son propre arriéré.

Blague n°1 : Les binlogs sont comme des reçus. Vous n’en avez pas besoin pour toujours, mais vous regretterez de les détruire cinq minutes avant un audit.

MySQL vs MariaDB : différences importantes pour la croissance des binlogs

De loin, la gestion des binlogs semble la même : définir la rétention, s’assurer que la purge est sûre, surveiller la réplication. De près, MySQL et MariaDB ont suffisamment de différences pour pourrir votre journée si vous copiez-collez des conseils entre eux.

1) Molette de rétention : même intention, noms et cas particuliers différents

MySQL utilisait historiquement expire_logs_days. Les versions récentes de MySQL préfèrent binlog_expire_logs_seconds. Beaucoup de systèmes gardent les deux dans leurs configs parce que personne n’aime effacer des lignes anciennes.

MariaDB supporte expire_logs_days aussi, et supporte binlog_expire_logs_seconds dans les versions plus récentes. Mais le comportement effectif peut varier selon la version et selon la façon dont vous exécutez la réplication et les sauvegardes. Ne devinez pas ; vérifiez via SHOW VARIABLES et en observant les purges réelles.

2) Comportement des GTID : le concept se recoupe ; les détails opérationnels non

Le GTID de MySQL est étroitement intégré à l’état de réplication (gtid_executed, gtid_purged) et tente généralement de rendre les outils de basculement cohérents. Le GTID de MariaDB est semblable dans l’esprit mais diffère dans la représentation et certains flux de basculement.

Pour la rétention des binlogs, le point clé est brutal : les GTID ne réduisent pas l’usage disque en eux-mêmes. Ils réduisent la charge cognitive pour savoir « qu’est-ce qui a été appliqué où », ce qui aide à supprimer des logs en toute sécurité, mais vous avez toujours besoin d’une politique de rétention et de procédures de purge sûres.

3) Paramètres par défaut et « sûreté par défaut » varient selon la distribution

Les configurations packagées en entreprise livrent parfois la journalisation binaire activée sans expiration parce que « la réplication pourrait être nécessaire plus tard ». Ce n’est pas de la prudence ; c’est un échec différé.

4) Compression et chiffrement des binlogs : bonnes options, mais pas gratuites

Selon les versions, vous pouvez avoir des options pour chiffrer et compresser les binlogs. Le chiffrement augmente la charge CPU ; la compression échange CPU contre réduction disque et I/O. Les deux valent la peine d’être envisagées, mais seulement après avoir maîtrisé les fondamentaux (rétention + santé de la réplication).

Playbook de diagnostic rapide : trouver le goulot en quelques minutes

Ceci est le « vous avez 10 minutes avant que le disque soit plein » playbook. Ne débattez pas d’architecture pendant que le serveur pagine.

Première étape : confirmez que ce sont bien les binlogs et quantifiez le taux de croissance

• Combien d’espace disque reste-t-il ?
• Quelle est la taille du répertoire des binlogs ?
• De nouveaux fichiers binlog sont-ils créés rapidement, ou un seul fichier grossit-il vite ?

Deuxième étape : vérifiez si la purge est bloquée par la réplication ou des outils

• Un réplicat est-il fortement en retard ou hors ligne ?
• Un processus de sauvegarde lit-il d’anciens binlogs ?
• Le serveur est-il configuré avec une rétention, et purge-t-il réellement ?

Troisième étape : décidez de l’action d’urgence la « moins mauvaise »

• Si la réplication est saine, purgez en toute sécurité en vous basant sur la position du réplicat/GTID.
• Si la réplication est malsaine, soit réparez rapidement le réplicat, soit acceptez de le casser et planifiez une reconstruction.
• Si vous manquez d’espace maintenant, gagnez du temps : étendez le système de fichiers, déplacez les binlogs, ou arrêtez temporairement les écritures (limitation côté application) pendant que vous reprenez le contrôle.

La priorité tactique est toujours la même : évitez un crash qui corromprait des tables et transformerait un incident gérable en une restauration.

Tâches pratiques (avec commandes) : mesurer, décider, corriger

Vous ne pouvez pas gérer ce que vous ne mesurez pas, et vous ne pouvez pas purger en toute sécurité ce que vous ne comprenez pas. Voici des tâches concrètes à exécuter sur de vrais serveurs. Chaque tâche inclut : commande, sortie d’exemple, ce que cela signifie et la décision à prendre.

Task 1: Check disk headroom and which filesystem is at risk

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

Signification : Vous êtes proche du plein sur / mais MySQL vit sur un autre montage avec de la marge. Si les binlogs sont sur /, vous êtes à quelques minutes de la douleur.

Décision : Confirmez le datadir de MySQL et l’endroit où sont écrits les binlogs ; déplacez-les hors du système de fichiers racine si nécessaire.

Task 2: Find where binlogs actually live (don’t assume)

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/    |
+---------------+--------------------+

Signification : Les binlogs sont sous /var/lib/mysql. Bien. Si vous aviez vu quelque chose comme /var/log ou un petit montage racine, vous auriez votre coupable fumant.

Décision : Si le chemin des binlogs est sur un système de fichiers contraint, planifiez une relocalisation via la configuration et un redémarrage (ou reconfigurez si supporté), pas un symlink nocturne sauf urgence vitale.

Task 3: Identify binlog directory size and biggest files

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

Signification : La ligne binlog.index dans cette sortie est trompeuse car du compte bizarrement pour des motifs creux et métadonnées ; la vraie histoire est que de nombreux fichiers binlog existent et chacun fait ~1G.

Décision : Comptez les fichiers et calculez la taille totale avec un outil plus précis (tâche suivante). Si vous générez beaucoup de fichiers de 1G rapidement, la rotation a lieu ; la croissance est toujours réelle.

Task 4: Count binlog files and total bytes precisely

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

Signification : Vous conservez ~812G de binlogs. Ce n’est pas « un petit arriéré », c’est une décision organisationnelle que personne n’a prise volontairement.

Décision : Déterminez immédiatement si des réplicas ou des flux de sauvegarde exigent cet historique. Si non, purgez en toute sécurité. Si oui, corrigez le blocage ou acceptez la reconstruction d’un réplicat.

Task 5: Confirm binlog retention variables (what the server thinks)

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         |
+---------------------------+-----------+

Signification : La rétention est effectivement désactivée. De plus, ROW + FULL peut être énorme pour des tables larges et des charges à mises à jour lourdes. sync_binlog=1 est durable mais peut amplifier l’I/O.

Décision : Définissez une valeur de rétention qui correspond à vos objectifs de reprise et à la réalité de la réplication. Envisagez binlog_row_image=MINIMAL quand c’est sûr et supporté, mais validez d’abord les besoins applicatifs et de réplication.

Task 6: See what binlogs exist from MySQL’s perspective

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 |

Signification : Le serveur suit les binlogs et leurs tailles. Cette sortie est ce contre quoi vous purgez — ne supprimez pas les fichiers à la main en pensant que MySQL applaudira.

Décision : Si vous devez purger, faites-le via SQL (PURGE BINARY LOGS) ou via des workflows compatibles mysqlbinlog. La suppression manuelle est un dernier recours et suivie habituellement d’un « pourquoi il ne démarre pas ? ».

Task 7: Check replication status on a replica (classic file/position)

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

Signification : Ce réplicat est à jour et exécute les logs courants. Si tous les réplicas ressemblent à cela, vous pouvez généralement purger en toute sécurité les anciens binlogs jusqu’au plus ancien Source_Log_File encore nécessaire.

Décision : Recueillez ceci depuis chaque réplicat. Le « plus ancien binlog nécessaire » parmi les réplicas est votre frontière de purge.

Task 8: Check replication status on a replica (GTID flavor)

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

Signification : GTID est activé et le réplicat est synchronisé. La purge est toujours basée sur ce dont les réplicas ont besoin ; GTID donne juste un état plus propre pour raisonner.

Décision : Utilisez les ensembles GTID pour valider que les réplicas ont exécuté ce que vous prévoyez de purger. Si un réplicat manque des parties de l’ensemble, vous ne pouvez pas purger ces logs sans accepter une reconstruction.

Task 9: Identify a replica that is blocking purges (offline or lagging)

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                                  |
+-----------+------------------+------+-----------+--------------------------------------+

Signification : Vous avez trois réplicas enregistrés. « Enregistré » ne veut pas dire « sain ».

Décision : Connectez-vous à chaque réplicat et exécutez les tâches 7/8. Le pire d’entre eux dicte votre plancher de rétention à moins que vous acceptiez de l’abandonner et de le reconstruire.

Task 10: Check relay log growth on a replica (the other disk bomb)

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

Signification : Le réplicat amasse des relay logs. Cela arrive quand le thread SQL n’arrive pas à suivre, est arrêté ou plante de façon répétée. Les gens « règlent » souvent la rétention des binlogs du primaire alors que c’est le réplicat qui remplit les disques.

Décision : Corrigez le taux d’application du réplicat (indexes, erreurs thread SQL, réplication parallèle) ou reconstruisez-le. Confirmez aussi que le comportement de purge des relay logs est activé et fonctionne.

Task 11: Check for long transactions that create giant binlog events

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                 |
+--------+---------------------+-------+-------------------+

Signification : Une transaction de 5 heures modifiant des millions de lignes est une usine à binlogs. Même si elle est « légitime », c’est un risque opérationnel : elle met à l’épreuve le disque, la réplication et la récupération après crash.

Décision : Coordonnez-vous avec les propriétaires applicatifs : découpez les grosses mises à jour, évitez les transactions monstrueuses et envisagez des mécanismes de limitation.

Task 12: Measure current binlog write throughput (is this a sudden spike?)

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

Signification : Écritures soutenues lourdes. Si cela corrèle avec la croissance des binlogs, vous êtes peut-être dans une tempête d’écritures (job batch, migration de schéma, boucle de retry).

Décision : Identifiez les plus gros générateurs d’écritures (tâche suivante) et décidez de les mettre en pause, les limiter ou les optimiser.

Task 13: Identify top write queries (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

Signification : La charge effectue de grosses mises à jour et suppressions. En format ROW, ce sont des charges de binlog, pas seulement du « travail base de données ».

Décision : Ajoutez des indexes pour réduire le nombre de lignes touchées, découpez les opérations, ou planifiez les maintenances lourdes hors-peak. Si c’est une migration ponctuelle, prévoyez de la marge disque et une frontière de purge.

Task 14: Check whether automatic purge is working (binlog expiry status)

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        |
+---------------------------+----------+

Signification : Pas directement le « statut de purge », mais cela indique que la binlogging est active et comment le cache se comporte. Pour le comportement de purge, validez en observant si d’anciens fichiers disparaissent avec le temps et en vérifiant les variables de rétention.

Décision : Si la rétention est définie et que les fichiers ne s’effacent pas, la réplication/les sauvegardes bloquent probablement la purge ou vous avez un décalage de comportement spécifique à la version.

Task 15: Safely purge binlogs up to a boundary (file-based)

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

Signification : MySQL/MariaDB supprimera les fichiers binlog strictement avant binlog.000780 (sans l’inclure), en mettant à jour l’index et l’état interne.

Décision : Ne faites cela qu’après avoir confirmé que tous les réplicas et consommateurs (sauvegarde/CDC) sont passés au-delà de cette frontière. Si un réplicat a encore besoin de binlog.000702, purger jusqu’à 780 le cassera.

Task 16: Emergency: rotate to a new binlog file (buy small clarity)

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

Signification : Force une rotation des binlogs. Cela ne réduit pas l’utilisation disque, mais peut aider opérationnellement en rendant « ce qui est courant » évident et en créant une frontière propre pour des décisions de purge ultérieures.

Décision : À utiliser pendant la réponse à incident quand vous voulez un point de départ connu pour l’analyse et isoler l’activité courante de l’arriéré.

Rétention et purge : valeurs sûres et pièges

Les binlogs ne sont pas du « poubelle ». Ce sont des « sécurités temporaires ». Votre travail est de décider combien de sécurité vous pouvez vous permettre, puis de l’appliquer sans pitié.

Choisissez une fenêtre de rétention basée sur les objectifs de reprise, pas sur des impressions

Si vous faites des sauvegardes complètes quotidiennes et que vous voulez une récupération point-in-time dans la journée, vous avez typiquement besoin des binlogs depuis le moment de la sauvegarde. Beaucoup d’organisations choisissent 2 à 7 jours pour couvrir la slop opérationnelle : détection tardive, mauvais déploiements, staffing du week-end, temps de reconstruction d’un réplicat.

Cependant : la rétention est contrainte par le consommateur le plus lent. Si vous avez un réplicat fréquemment hors ligne, un connecteur CDC qui se met en pause, ou un système de sauvegarde qui « stream les binlogs quand il en a envie », votre primaire conservera plus que votre politique à moins que vous ne coupiez cette dépendance.

Définissez la rétention explicitement (et confirmez quelle variable s’applique à votre version)

Sur les MySQL modernes, binlog_expire_logs_seconds est le réglage le plus clair. Sur les installations plus anciennes, expire_logs_days peut être le seul qui compte. MariaDB varie selon la version, et certains environnements ont les deux. Vous voulez un réglage faisant autorité et la preuve qu’il fonctionne.

Conseil opérationnel : si vous héritez d’un serveur sans rétention, ne mettez pas « 30 days » comme premier choix « sûr ». C’est comme garder la bombe sous votre bureau. Commencez par une fenêtre réaliste — souvent 3–7 jours — puis augmentez seulement si vous avez prouvé que vous pouvez le stocker et que vous en avez réellement besoin.

Ne purgez pas en supprimant les fichiers

Oui, vous pouvez rm /var/lib/mysql/binlog.000123. Et oui, parfois vous pouvez vous en sortir. Mais c’est comme ça que vous obtenez :

• des réplicas bloqués à demander des binlogs qui n’existent plus
• le serveur confus à propos de son index de binlog
• des workflows de sauvegarde/PITR subtillement cassés

Utilisez PURGE BINARY LOGS sauf si le serveur est arrêté et que vous faites de la chirurgie d’urgence. Même alors, planifiez un redémarrage et attendez-vous à des retombées sur la réplication.

Réplication et latence : la raison la plus courante pour laquelle la purge « ne marche pas »

Quand les gens disent « j’ai mis la rétention mais les binlogs continuent de croître », neuf fois sur dix ils exécutent la réplication et quelque chose est en retard. Le primaire conserve les logs parce que les réplicas en ont besoin. Ce n’est pas le serveur qui vous désobéit ; c’est vous qui demandez deux choses mutuellement exclusives : « garder moins d’historique » et « supporter un réplicat qui a des semaines de retard ».

Comment la réplication bloque la purge en pratique

Blocages courants :

• Un réplicat est hors ligne pour maintenance, problèmes réseau, ou « quelqu’un l’a arrêté » et a oublié.
• Le thread SQL du réplicat est arrêté à cause d’une erreur (clé dupliquée, table manquante après une modification de schéma ratée, etc.).
• Le réplicat est limité I/O en appliquant des événements ROW et ne peut pas suivre.
• Le réplicat a d’énormes relay logs et est bloqué dans l’application lente.
• Des consommateurs non-réplicas (outils CDC, pipelines d’audit, agents de sauvegarde) lisent les binlogs et exigent l’historique.

Soyez décisif au sujet des réplicas malsains

Si un réplicat est hors ligne depuis assez longtemps pour que son arriéré mange le disque du primaire, vous devez décider :

• Le réplicat est-il critique ? Si oui, réparez-le maintenant : restaurez le réseau, résolvez les erreurs SQL, augmentez le parallélisme d’application si approprié, ou reconstruisez-le à partir d’un snapshot frais.
• Est-il « agréable à avoir » ? Si non, cassez-le volontairement : purgez les binlogs pour sauver le primaire, puis reconstruisez le réplicat plus tard.

Ce que vous ne devriez pas faire, c’est garder le primaire dans un état presque plein pour préserver un réplicat négligé. C’est ainsi que vous transformez une chose cassée en deux.

PITR et sauvegardes : conserver la récupération sans logs infinis

Si vous voulez une récupération point-in-time, vous avez besoin de deux ingrédients :

1. une sauvegarde de base cohérente (complète ou chaîne incrémentale)
2. les binlogs depuis le moment de cette sauvegarde de base jusqu’au point de récupération

Le mode défaillant est évident : vous gardez des binlogs « au cas où », mais vous n’avez en fait pas de chaîne de sauvegardes de base valide. Alors vous dépensez du disque pour l’illusion de sécurité. Validez votre processus de restauration. Entraînez-vous. Sinon les binlogs ne sont qu’un confort très coûteux.

Logique pratique de rétention qui marche

• Conservez les binlogs au moins pour le temps maximal dont vous pourriez avoir besoin pour détecter un problème de données (la latence humaine de détection est réelle).
• Conservez les binlogs au moins pour un intervalle complet de sauvegarde plus une marge. Si les sauvegardes sont quotidiennes, 2–7 jours est courant.
• Alignez les frontières de purge avec l’achèvement des sauvegardes. Purger des logs qu’une sauvegarde n’a pas « capturés » (logiquement ou physiquement) est la façon de perdre la PITR.

Blague n°2 : La seule chose plus effrayante qu’un disque plein de binlogs est un disque vide de binlogs cinq minutes après que vous en ayez besoin.

Choix du format de binlog : ROW vs MIXED vs STATEMENT

La taille des binlogs n’est pas seulement « trafic ». C’est aussi « comment vous représentez le trafic ». Les choix de format peuvent changer drastiquement le volume des binlogs.

ROW : déterministe, lourd et souvent le bon défaut

Le format ROW journalise les changements de lignes réels. C’est de l’or pour la correction et la sécurité de la réplication. C’est aussi coûteux quand vous mettez à jour beaucoup de lignes, ou quand les lignes sont larges (beaucoup de colonnes), ou quand vos mises à jour touchent blobs/text.

Si vous êtes en ROW et que vous voyez une explosion des binlogs, ne sautez pas immédiatement vers STATEMENT. D’abord, réduisez le rayon d’action de la charge : découpez les mises à jour, indexez correctement et évitez de toucher des colonnes inutilement.

binlog_row_image : FULL vs MINIMAL

Quand c’est supporté et sûr, binlog_row_image=MINIMAL peut réduire la taille des binlogs en journalisant moins de colonnes. Cela peut grandement aider pour des tables larges où les mises à jour ne touchent que quelques colonnes.

Mais : « sûr » dépend de la version, des outils et si les consommateurs en aval exigent des images complètes. Certains setups CDC veulent des images FULL. Certains pipelines d’audit en dépendent. Connaissez vos consommateurs.

MIXED et STATEMENT : plus petits, mais des angles vifs

La journalisation basée sur les requêtes peut être beaucoup plus petite pour des opérations en masse (une seule requête plutôt que des millions d’événements ligne). Elle peut aussi être non déterministe et casser la réplication de façon subtile. MIXED tente d’être intelligent : il journalise les requêtes quand c’est sûr, les lignes quand ce ne l’est pas.

Dans des environnements de production modernes, ROW est le choix ennuyeux qui vous évite les post-mortems. Si vous optimisez la taille des binlogs en changeant de format, ne le faites qu’après avoir testé la correction de la réplication sous votre charge exacte.

Réalité du stockage : IOPS, fsync, compression et où vont les octets

La croissance des binlogs est un problème à la fois de base de données et de stockage. Si vos disques sont lents, les binlogs peuvent devenir à la fois symptôme et cause : des écritures lourdes remplissent le disque, et la pression I/O ralentit l’application sur les réplicas, ce qui force une rétention plus longue, ce qui remplit encore plus le disque.

sync_binlog et compromis de durabilité

sync_binlog=1 signifie que le serveur effectue des fsync fréquents sur les écritures de binlog. C’est bon pour la durabilité (moins de transactions perdues en cas de crash) et souvent requis pour des garanties strictes de reprise. C’est aussi plus exigeant pour la latence du stockage.

Si votre stockage ne peut pas le supporter, n’« optimisez » pas en relâchant la durabilité à l’aveugle. Corrigez le stockage, répartissez l’I/O, ou montez en charge. Les réglages de durabilité ne sont pas des réglages de performance ; ce sont des réglages de risque.

Séparer les binlogs sur un système de fichiers dédié (parfois)

Placer les binlogs sur leur propre montage peut être une bonne idée :

• empêche les binlogs de remplir le système de fichiers du datadir et de briquer le serveur
• simplifie la surveillance et l’alerte
• peut s’aligner sur un média plus rapide

Mais cela peut aussi créer de la contention si vous les placez sur des disques lents et partagés. L’objectif est l’isolation avec des performances adéquates, pas « le déplacer ailleurs et espérer ».

Compression : utile quand le réseau/I/O est le goulet

Si la charge de binlog est énorme et que votre goulot est disque ou réseau, la compression peut aider. Si le CPU est déjà le goulot, cela peut se retourner contre vous. Mesurez d’abord.

Trois mini-histoires d\u0027entreprise issues du terrain

Mini-histoire 1 : L’incident causé par une mauvaise hypothèse

Ils avaient un primaire et deux réplicas. Simple. L’équipe avait aussi une contrainte de conformité demandant de conserver « assez d’historique pour enquêter ». Quelqu’un a interprété cela comme « conserver indéfiniment les binlogs », parce que les binlogs ressemblent à une piste d’audit si on plisse les yeux.

La mauvaise hypothèse est apparue des mois plus tard pendant un patch du noyau de routine. Un réplicat est resté plus longtemps hors ligne que prévu à cause d’un problème de firmware NIC, donc il a raté beaucoup de changements. Le primaire a gardé les binlogs pour que le réplicat puisse rattraper. Personne n’a remarqué tant que le primaire avait encore du disque. Au début.

Puis un job batch est arrivé : un grand backfill mettant à jour une table large en format ROW. Les binlogs ont explosé. Le primaire a rempli son système de fichiers en plein heures d’ouverture et a cessé d’accepter des écritures. La panne n’a pas commencé avec le job batch ; elle a commencé avec l’hypothèse que « les binlogs sont un journal d’audit » et que « la rétention peut être infinie ».

Après coup, ils ont mis en place deux choses : une vraie pipeline d’audit (séparée des binlogs), et une rétention explicite alignée sur les besoins de backup/PITR. Le réplicat resté hors ligne a été reconstruit à partir d’un snapshot frais. Personne n’a aimé, mais tout le monde a retrouvé le sommeil.

Mini-histoire 2 : L’optimisation qui s\u0027est retournée contre eux

Une autre équipe combattait la latence de réplication et l’usage disque. Quelqu’un a eu une idée brillante : réduire la taille des binlogs en passant de ROW à STATEMENT. Ça a marché en labo. Ça a même marché une semaine en production. Ce qui est la façon habituelle dont ces histoires commencent.

Puis un déploiement a introduit un pattern de requête dépendant d’un comportement non déterministe : une mise à jour basée sur l’heure courante. Sur le primaire, ça s’est comporté d’une manière ; sur un réplicat avec un timing d’exécution légèrement différent, ça s’est comporté autrement. Les réplicas ont divergé silencieusement — pas de crash, pas d’alarme tonitruante, juste des données incorrectes.

Le retour de bâton a été coûteux car subtil. Ils ont passé des jours à concilier les tables, re-cloner les réplicas et mettre en place des contrôles de détection. L’optimisation des binlogs a économisé du disque et leur a coûté de la confiance.

Ils sont revenus à ROW. La vraie solution était ennuyeuse : jobs de maintenance découpés, meilleurs indexes et une politique de rétention permettant de reconstruire rapidement les réplicas quand nécessaire. Le disque est moins cher que la correction, jusqu’à ce que ce ne soit plus le cas.

Mini-histoire 3 : La pratique ennuyeuse mais correcte qui a sauvé la mise

Celle-ci est ma préférée parce qu’elle n’est pas héroïque. Elle est juste compétente. Une entreprise faisait tourner des primaires MySQL avec trois réplicas sur deux régions. Ils traitaient la rétention des binlogs comme faisant partie de la planification de capacité, pas comme une réflexion après coup.

Ils avaient une procédure : vérifier chaque semaine que la position du réplicat le plus ancien était dans la rétention ; faire chaque mois une simulation de PITR dans un bac à sable ; alerter quotidiennement si la croissance du système de fichiers des binlogs dépassait un seuil par heure. Les alertes étaient silencieuses la plupart du temps, ce qui est le but.

Un jour une partition réseau a isolé un réplicat pendant des heures. Les binlogs ont grossi, mais les alertes se sont déclenchées tôt, et l’astreinte pouvait voir le taux de croissance et la fenêtre de rétention. Ils ont étendu le système de fichiers des binlogs (car ils avaient une procédure pré-approuvée), ont ramené le réplicat, puis ont purgé les anciens logs en toute sécurité après sa synchronisation.

Pas d’incident majeur. Pas de salle de crise. Juste un ticket, un graphique et quelques commandes. Le meilleur incident est celui qui ne devient jamais une histoire racontée à une conférence.

Erreurs courantes : symptôme → cause racine → correctif

1) Le disque se remplit même si vous avez défini une rétention

Symptôme : Les fichiers binlog continuent à s’accumuler ; le fichier le plus ancien a des semaines ; les variables de rétention sont définies.

Cause racine : Un réplicat ou un consommateur de binlog est en retard/hors ligne, empêchant la purge (ou vous avez réglé la mauvaise variable pour votre version et elle est ignorée).

Correctif : Identifiez le binlog le plus ancien requis parmi les réplicas, réparez ou reconstruisez le réplicat en retard, vérifiez la bonne variable de rétention via SHOW VARIABLES, et validez que les fichiers s’effacent.

2) La purge casse immédiatement la réplication

Symptôme : Erreurs du thread I/O du réplicat : « Could not find first log file name in binary log index file. »

Cause racine : Vous avez purgé au-delà de ce que le réplicat avait récupéré/exécuté, ou supprimé les fichiers manuellement.

Correctif : Reconstruisez le réplicat à partir d’un snapshot frais, ou si possible re-pointez-le vers une source qui possède encore les logs requis. Cessez de supprimer les binlogs à la main.

3) Le disque du réplicat se remplit alors que le primaire est sain

Symptôme : Le réplicat manque d’espace disque ; le primaire a beaucoup d’espace.

Cause racine : Les relay logs s’accumulent parce que le thread SQL est arrêté/lent ; ou le comportement relay_log_purge n’est pas effectif à cause d’erreurs.

Correctif : Corrigez l’erreur du thread SQL, améliorez le débit d’application, confirmez que la purge des relay logs fonctionne, ou reconstruisez le réplicat.

4) Les binlogs sont énormes pendant les backfills/migrations

Symptôme : Un seul job crée des centaines de Go de binlogs ; les réplicas prennent des heures de retard.

Cause racine : Grosses transactions, format ROW, lignes larges et opérations non découpées.

Correctif : Découpez les opérations, ajoutez/vérifiez les indexes, évitez de toucher des colonnes inutiles, envisagez binlog_row_image=MINIMAL quand c’est sûr, et planifiez les jobs lourds avec une marge de capacité.

5) « Nous avons besoin des binlogs pour l’audit » devient « nous avons perdu un week-end »

Symptôme : Rétention sans fin justifiée par la conformité ; croissance disque constante ; personne ne peut décrire une procédure de reprise ou d’investigation.

Cause racine : Les binlogs sont utilisés comme substitut à une stratégie d’audit dédiée.

Correctif : Construisez un pipeline d’audit/CDC conçu pour la rétention et les requêtes ; conservez les binlogs uniquement aussi longtemps que nécessaire pour la réplication/PITR.

Listes de vérification / plan étape par étape

Étape par étape : maîtriser la croissance des binlogs (sans tout casser)

1. Inventaire des consommateurs : listez les réplicas, outils CDC, agents de sauvegarde qui lisent les binlogs.
2. Mesurez l’empreinte actuelle des binlogs : taille totale, nombre de fichiers, taux de croissance par heure/jour.
3. Vérifiez les réglages de rétention : confirmez quelle variable s’applique à votre version ; supprimez les réglages hérités conflictuels.
4. Vérifiez que la purge est possible : collectez les positions/ensembles GTID des réplicas ; trouvez la plus ancienne frontière requise.
5. Réparez les réplicas en retard/hors ligne : rattrapez-les ou décidez de les reconstruire ; ne les laissez pas prendre en otage votre primaire.
6. Purgez en toute sécurité : utilisez les commandes SQL de purge vers une frontière validée.
7. Mettez des alertes : utilisation disque, taux de croissance du répertoire binlog, latence des réplicas, croissance des relay logs sur les réplicas.
8. Réduisez l’amplification d’écriture : découpez les jobs, indexez correctement, reconsidérez les réglages d’image de ligne quand c’est sûr.
9. Alignez avec les sauvegardes : assurez-vous d’avoir une base de sauvegarde cohérente et des étapes PITR documentées.
10. Exercez la restauration : drills PITR périodiques. Si vous ne pouvez pas restaurer, la rétention n’est que de la théâtralité.

Checklist d\u0027urgence : le disque est à 95 % et ça monte

1. Confirmez quel système de fichiers est plein (df -hT).
2. Confirmez que les binlogs sont l’utilisation dominante (du / find taille totale).
3. Identifiez la position/GTID du réplicat le plus ancien et si un réplicat est hors ligne.
4. Si les réplicas sont sains : purgez jusqu’à la frontière sûre.
5. Si un réplicat est hors ligne et non critique : acceptez de le casser, purgez pour sauver le primaire, reconstruisez-le plus tard.
6. Si vous ne pouvez pas purger assez vite en toute sécurité : étendez le système de fichiers ou déplacez les binlogs pour gagner du temps, puis appliquez le correctif approprié.

FAQ

1) Puis-je désactiver la journalisation binaire pour arrêter la croissance du disque ?

Vous pouvez, mais vous ne devriez probablement pas. Désactiver les binlogs casse la réplication et élimine la PITR. Si vous n’avez vraiment besoin ni de l’un ni de l’autre, désactivez-les délibérément et mettez à jour vos hypothèses opérationnelles. Sinon, corrigez la rétention et la santé de la réplication.

2) Pourquoi expire_logs_days n’a-t-il rien supprimé ?

Soit il est réglé sur 0 (désactivé), soit vous êtes sur une version où une autre variable prend le pas, soit quelque chose a encore besoin des logs (réplicat/consommateur). Vérifiez via SHOW VARIABLES puis contrôlez l’état des réplicas.

3) Quelle est la façon la plus sûre de purger les binlogs ?

Utilisez SQL : PURGE BINARY LOGS TO 'binlog.XXXXXX'; après avoir confirmé que tous les réplicas/consommateurs n’ont plus besoin des logs antérieurs. Le processus humain le plus sûr est de calculer la frontière de purge depuis le réplicat le plus ancien et de le documenter dans le ticket d’incident.

4) Les commandes de purge MariaDB et MySQL sont-elles identiques ?

Les commandes SQL de base sont très similaires. Les différences qui posent problème se situent généralement autour de l’implémentation GTID, des variables spécifiques à la version et de ce que vos outils attendent. Traitez chaque flotte comme son propre écosystème : vérifiez le comportement, ne vous fiez pas à la mémoire.

5) Quelle rétention de binlogs devrais-je conserver ?

Conservez suffisamment pour couvrir : (a) le temps depuis votre dernière sauvegarde de base valide jusqu’à maintenant, plus (b) la latence de détection/réponse, plus (c) le temps de reconstruction d’un réplicat si vous en dépendez. Pour beaucoup d’équipes, c’est 3–7 jours. Pour d’autres, 24 heures. Si vous ne pouvez pas le justifier avec une procédure de récupération, vous n’en avez pas besoin.

6) Les GTID réduisent-ils la taille des binlogs ?

Non. Les GTID vous aident à raisonner sur l’état de réplication et le basculement. La taille des binlogs est dominée par la charge et le format (ROW vs STATEMENT), les images de ligne et les patterns transactionnels.

7) Pourquoi les binlogs sont-ils énormes quand nous exécutons un DELETE ?

En format ROW, un DELETE massif journalise des événements ligne pour chaque ligne affectée. La base de données fait du vrai travail et l’enregistre. La solution est de découper les suppressions, d’assurer des indexes appropriés et de planifier les maintenances lourdes avec de la marge.

8) Si un réplicat a des semaines de retard, dois-je garder les binlogs jusqu’à ce qu’il rattrape ?

Généralement non. Si un réplicat a tant de retard, il est souvent plus rapide et plus sûr de le reconstruire à partir d’un snapshot frais. Garder des semaines de binlogs sur le primaire met la disponibilité du primaire en risque pour un réplicat qui vous échoue déjà.

9) Qu’en est-il de l’« explosion des binlogs » sur un réplicat ?

Cela concerne souvent les relay logs, pas les binlogs. Vérifiez la taille du répertoire des relay logs du réplicat et si l’application SQL est bloquée. Corrigez l’application ou reconstruisez le réplicat ; ne faites pas que gonfler le disque et l’appeler résolu.

10) Puis-je déplacer les binlogs sur un autre disque sans downtime ?

Parfois, selon la version et la configuration, mais planifiez une fenêtre de maintenance. Le déplacement sûr opérationnellement est : arrêter MySQL, déplacer les fichiers, ajuster la config (log_bin_basename path), démarrer MySQL, valider avec SHOW BINARY LOGS. Si vous avez besoin de « zéro downtime », concevez votre topologie (basculement) plutôt que des astuces de système de fichiers.

Conclusion : actions suivantes pour éviter la récidive

Si vous ne retenez qu’une chose : les binlogs ne « prennent pas le contrôle ». Ils font exactement ce que vous leur avez demandé — enregistrer les changements pour toujours — jusqu’à ce que le disque vous force à arrêter de demander.

Actions pratiques à suivre :

1. Définissez une rétention explicite (secondes/jours) et confirmez qu’elle correspond à votre version et purge réellement.
2. Faites de la santé de la réplication un SLO de première classe ; un réplicat cassé n’est pas un accessoire inoffensif, c’est une responsabilité disque.
3. Alignez la rétention des binlogs avec les sauvegardes et effectuez des drills PITR pour savoir ce que vous achetez avec ces octets.
4. Instrumentez le taux de croissance et alertez sur les « Go par heure », pas seulement sur « disque à 90 % ».
5. Réduisez l’amplification d’écriture dans les jobs massifs : découpez, indexez, planifiez et évitez les transactions géantes.

Faites cela, et les binlogs redeviendront ce qu’ils doivent être : un filet de sécurité discret et utile — pas une anthologie d’horreur à thème stockage.