Heb ik --skip-lock-tables nog nodig als --single-transaction al gezet is?

Ja, als er MyISAM-tabellen tussen zitten, want --single-transaction beschermt alleen InnoDB. Beide zetten is onschadelijk op een pure InnoDB-schema en correct op een gemengd schema.

Is --hex-blob veilig voor niet-binaire tekstkolommen?

Ja. Alleen BLOB, BINARY, VARBINARY en BIT-kolommen worden als hex-literals weggeschreven; elke andere kolomtype komt gewoon als quoted string in de dump.

Waarom niet gewoon op mysqldump --opt vertrouwen voor verstandige defaults?

Die staat standaard aan en dekt het merendeel van deze flags, maar niet --hex-blob, --set-gtid-purged=OFF of --column-statistics=0, en dat zijn juist de drie die de restore daadwerkelijk redden.

mysqldump-flags voor legacy MySQL: de .my.cnf cheatsheet

Het is vrijdag 19:40 en we staan op het punt een dump van 14 GB te maken van een Magento 1.9-winkel, voordat de hosting maandag een geforceerde PHP-upgrade doorvoert. De database heeft BLOBs in core_config_data, utf8mb4-klantnotities met af en toe een 4-byte karakter, en binlogs ingesteld voor GTID. Als één van die drie dingen voor jouw dump geldt, laat de standaard mysqldump-aanroep je stilletjes vallen bij de restore.

De cheatsheet hieronder is de .my.cnf en het mysqldump-commando dat we op de server zetten voor elke legacy MySQL-export. Daarna de drie flags die we hebben toegevoegd na restores die om 02:00 misliepen. Kopieer het bestand, pas het credentials-blok aan en draai het.

De .my.cnf die we voor elke dump neerzetten

We bewaren dit bestand op ~/.my.cnf met chmod 600. Het scopet de credentials per tool, zodat mysql, mysqldump en mysqladmin allemaal het juiste lezen zonder dat het wachtwoord lekt in de shell-history of in ps-output.

[client]
host                     = 127.0.0.1
port                     = 3306
user                     = backup
password                 = "long-random-string-here"
default-character-set    = utf8mb4

[mysql]
prompt                   = "\\u@\\h [\\d]> "
no-auto-rehash

[mysqldump]
single-transaction
quick
skip-lock-tables
routines
events
triggers
hex-blob
default-character-set    = utf8mb4
max-allowed-packet       = 512M
net-buffer-length        = 16384
column-statistics        = 0
set-gtid-purged          = OFF
order-by-primary

Twee dingen om te verifiëren voordat je iets draait. Ten eerste: chmod 600 ~/.my.cnf zodat het bestand niet wereld-leesbaar is. Zonder dat waarschuwt MySQL je wel, maar leest het bestand alsnog, en op een shared server heb je dan net je credentials gelekt. Ten tweede die backup-user. Wij geven die SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER en PROCESS en verder niets. De officiële mysqldump-referentie documenteert de minimale grants als je nog verder wil snijden.

Het mysqldump-commando dat erbij hoort

Met het option file op zijn plek krimpt het commando tot de stukken die per dump daadwerkelijk veranderen: welke database, waar het bestand heen gaat, en of je een gzip-pipe wilt.

mysqldump \
  --databases magento_prod \
  | gzip --rsyncable \
  > /backups/magento_prod-$(date +%Y%m%d-%H%M).sql.gz

Een paar opmerkingen over de vorm van die regel:

--databases (meervoud) zet bovenaan de dump een CREATE DATABASE en USE, waardoor het restore-script de naam van de doel-database niet hoeft te kennen. De moeite waard.
gzip --rsyncable maakt het gzipped bestand diffbaar voor back-uptools zoals rsnapshot. De compressieratio is fractioneel slechter en de wall-clock is gelijk.
De single-transaction-flag uit het option file geeft InnoDB een consistente snapshot zonder schrijfacties te locken. Op een drukke WooCommerce-winkel is dat het verschil tussen een hapering van vier seconden en een storing van negentig seconden. De handleiding over consistent reads legt het mechanisme uit.

Drie flags die een restore hebben gered

De defaults zijn prima voor een dump die je maakt en herstelt op dezelfde server, zelfde versie, zelfde character set, zelfde dag. Ze zijn niet prima als je een verouderde site verhuist tussen hosts, en dat is het meeste werk. Deze drie hebben ons betrapt.

hex-blob

Zonder --hex-blob worden BLOBs en BINARY-kolommen weggeschreven als escaped strings. De dump opent in je editor, ziet er aannemelijk uit, en bij de restore zijn de bytes stilletjes opnieuw gecodeerd via de connection-charset. We liepen hier tegenaan op een WordPress-site waar wp_options een serialized PHP-object bevatte met image-bytes erin. De restore las schoon, maar elke option die binary data raakte unserialiseerde naar false. Met --hex-blob wordt elke binaire waarde geschreven als 0xDEADBEEF… en overleeft die ongeacht welke character set de restorende client besluit te gebruiken.

set-gtid-purged=OFF

Managed MySQL (RDS, Aurora, Cloud SQL) levert standaard met GTIDs aan. Standaard zet mysqldump bovenaan het bestand een SET @@GLOBAL.GTID_PURGED='...'. Restore dat op een verse dev-machine en je krijgt:

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only
be set when @@GLOBAL.GTID_EXECUTED is empty.

--set-gtid-purged=OFF haalt het statement weg. De dump herstelt nog steeds correct; je krijgt alleen geen replicatie-coördinaten die je op de dev-machine toch niet ging gebruiken.

column-statistics=0

Een MySQL 8-client die een 5.7-server dumpt, probeert te lezen uit information_schema.COLUMN_STATISTICS, wat niet bestaat op 5.7. Je krijgt:

mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, ...)
FROM information_schema.COLUMN_STATISTICS ...': Unknown table
'COLUMN_STATISTICS' in information_schema (1109)

Halverwege een halve terabyte, na middernacht. --column-statistics=0 slaat de lookup over. Wij zetten het op elke legacy dump, want de kosten van het aan hebben terwijl je het niet nodig hebt zijn nul, en de kosten van het missen op het moment dat je het wel nodig hebt zijn de dump zelf.

Wat je controleert nadat de dump klaar is

Drie snelle checks. Als één ervan faalt, is de dump verdacht en draai je opnieuw voordat je afmeldt.

# 1. Bestand eindigt met de verwachte sentinel
zcat backup.sql.gz | tail -1
# -- Dump completed on 2026-06-11  ...

# 2. Rij-aantal ziet er logisch uit voor een bekende tabel
zcat backup.sql.gz | grep -c "^INSERT INTO \`wp_posts\`"

# 3. Character set overleefde de round-trip
zcat backup.sql.gz | grep -m1 "DEFAULT CHARSET"
# DEFAULT CHARSET=utf8mb4

De voettekst 'Dump completed' is de enige ingebouwde integriteits-check die mysqldump biedt. Het is geen hash, maar de afwezigheid ervan is een betrouwbaar signaal dat het proces overleed voordat de staart werd weggeschreven.

Hoe dit past in een werksessie

Toen we Pier bouwden kwamen we op elke legacy site die we aanraakten precies deze volgorde tegen: .my.cnf neerzetten, dump draaien, voettekst checken, restoren op staging, die ene flag vinden die we vergeten waren. De manier waarop we het binnen de MySQL editor hebben opgelost, was om het option file in de docking-flow te bakken, zodat de flags hierboven altijd staan en de resulterende .sql.gz in de version history belandt naast de bestandswijzigingen van dezelfde sessie.

Als je vandaag verder niets doet, zet dan het .my.cnf-blok hierboven in ~/.my.cnf op de server vanwaar je dumps draait, doe er chmod 600 op, en draai een dump die je al vertrouwt opnieuw. Je ziet het commando korter worden en groeien in wat het overleeft.