Ruwe .sql-dumps lezen zonder import: een CLI-playbook

Een klant stuurt je een backup-prod.sql.gz van 3,2 GB via WeTransfer met de melding: "het ontbrekende gebruikersaccount zit er ergens in, alleen weten we niet waar." Importeren betekent een wegwerp-database opzetten, veertig minuten wachten tot de load klaar is, en dan SELECT-statements schrijven op een server die je morgen toch weggooit. De ruwe .sql-dump op schijf lezen duurt minder dan een minuut, kost geen RAM en beantwoordt precies dezelfde vraag.

De CLI-workflow voor het lezen van een ruwe .sql-dump is ouder dan de meeste frameworks waarop we sites uitleveren, en hij wint nog steeds van elke GUI als het om forensisch werk op grote bestanden gaat. Hieronder de toolkit waar een handvol van ons direct naar grijpt zodra er een dump in een Slack-thread belandt.

Wat het bestand is voordat je het opent

Een MySQL .sql-dump is een script in platte tekst met CREATE TABLE, INSERT INTO en bovenaan een paar session-instellingen. mysqldump schrijft ze in een voorspelbare volgorde, en dat is de reden dat elke truc hieronder werkt.

Begin met de goedkoopst mogelijke vragen. Is het bestand überhaupt gecomprimeerd?

file backup-prod.sql.gz
# backup-prod.sql.gz: gzip compressed data, from Unix, original size 3221225472

Goed. Stream het nu zonder het naar schijf uit te pakken:

zcat backup-prod.sql.gz | head -50

De eerste vijftig regels vertellen je welke MySQL-versie de dump heeft geproduceerd, de character set, en (als je client het heeft onthouden) de naam van de database. Als head eindeloos blijft hangen, is het bestand niet daadwerkelijk gzipped en kijk je naar een binaire blob die iemand hernoemd heeft.

Alle tabellen in seconden op een rij

De CREATE TABLE-statements staan in een mysqldump-output regel-geanker, wat betekent dat grep ze vindt zonder rij-data te scannen:

zcat backup-prod.sql.gz | grep -nE "^CREATE TABLE"
# 142:CREATE TABLE `wp_commentmeta` (
# 168:CREATE TABLE `wp_comments` (
# 234:CREATE TABLE `wp_options` (
# 412:CREATE TABLE `wp_postmeta` (
# 1238941:CREATE TABLE `wp_posts` (

De regelnummers tellen, want ze vertellen je grofweg hoe het bestand op schijf is opgebouwd. Als wp_options op regel 234 staat en wp_posts op regel 1,2 miljoen, dan is dat geen bug. wp_options is klein, en wp_posts is waar het gewicht zit.

Wil je per tabel een schatting van het aantal rijen zonder SQL te parsen? Tel de INSERT-statements:

zcat backup-prod.sql.gz | grep -c "^INSERT INTO \`wp_postmeta\`"
# 8742

Dat is niet het exacte aantal rijen (elke INSERT bevat meerdere tuples), maar wel een bruikbare proxy voor "is deze tabel 50 rijen of 5 miljoen".

Eén tabel uit een dump van vier gigabyte trekken

Dit is de truc die de hele post terugverdient. mysqldump zet elke tabel tussen twee comment-blokken die er zo uitzien:

-- Table structure for table `wp_users`
DROP TABLE IF EXISTS `wp_users`;
CREATE TABLE `wp_users` (...);
INSERT INTO `wp_users` VALUES (...);
-- Table structure for table `wp_usermeta`

sed kan precies één tabel uitsnijden door op de markerregels te matchen:

zcat backup-prod.sql.gz \
  | sed -n '/^-- Table structure for table `wp_users`/,/^-- Table structure for table/p' \
  > wp_users.sql

Je hebt nu een bestand van 40 KB dat binnen twee seconden importeert in een wegwerp-database. Dezelfde one-liner werkt voor users_field_data van Drupal, customer_entity van Magento, of wat je verouderde site ook gebruikt. Wissel de tabelnaam en draaien.

Jagen op één enkele rij

De vraag over die "ontbrekende gebruiker" wordt een one-liner. Zoek het e-mailadres ergens in de dump:

zgrep -n "marieke@example.nl" backup-prod.sql.gz

Zit het e-mailadres begraven in een gigantische extended INSERT, dan krijg je een regelnummer en een muur aan tekst. Herformatteer de matchende regel zo dat elke tuple op een eigen regel komt:

zgrep "marieke@example.nl" backup-prod.sql.gz \
  | sed 's/),(/),\n(/g' \
  | grep "marieke@example.nl"

Dat laatste filter print alleen de tuple die je zoekt. Je weet nu het ID van de gebruiker, de gehashte password, de registratiedatum en welke meta-rijen je terug zou moeten kopiëren. Totale tijd: onder de vijf seconden op een dump van 3 GB.

Twee dumps diffen zonder ook maar één van beide te importeren

Klanten sturen voortdurend back-ups van "voor" en "na", meestal met de vraag "wat is er veranderd?" Beide in tijdelijke databases importeren en vergelijkingsqueries draaien is het voor de hand liggende antwoord, en bijna altijd het verkeerde voor een eerste pass. Diff eerst de schemas:

zcat backup-monday.sql.gz | grep -E "^CREATE TABLE" | sort > schema-mon.txt
zcat backup-friday.sql.gz | grep -E "^CREATE TABLE" | sort > schema-fri.txt
diff schema-mon.txt schema-fri.txt

Is het schema identiek, dan zit de wijziging in rij-data en kun je verder graven met grep. Is er een tabel bijgekomen of verdwenen, dan heb je het gevonden zonder de importkosten te betalen.

Voor diffen op rij-niveau van één tabel haal je dezelfde tabel uit beide dumps met het sed-recept hierboven, en draai je daarna diff op de twee wp_options.sql-bestanden. De meeste "de site brak op vrijdag"-incidenten lossen op bij deze stap, omdat de gewijzigde rij iets duidelijks is: een verouderde URL in siteurl, een plugin-lijst die omsloeg in active_plugins, een cache-invalidator die rommel wegschreef.

Toekijken hoe een trage operatie afrondt

Eén laatste tool die zichzelf terugverdient op grote dumps: pv (pipe viewer). Wanneer je de dump uiteindelijk wél importeert, pipe je hem door pv heen zodat je de voortgang ziet:

pv backup-prod.sql.gz | gunzip | mysql -u root -p target_db
# 1.34GiB 0:04:12 [5.42MiB/s] [=======>          ] 41% ETA 0:06:01

Geen gestaar meer naar een bevroren terminal met de vraag of MySQL nog werkt of overleden is. Dezelfde truc werkt voor elke lange pipe: pv ertussen, voortgangsbalk onderaan het scherm.

Toen we Pier bouwden voor het bewerken van verouderde WordPress- en Drupal-sites via FTP en MySQL, was dit een van de workflows waar we aan de terminal naar bleven grijpen, zelfs nadat de in-app MySQL editor live ging. De recepten hierboven horen in het spiergeheugen van elke developer, of je nu ooit onze app opent of niet.

Het kleinste wat je vandaag kunt doen: pak een .sql-back-up die al op je laptop staat en draai er zcat backup.sql.gz | grep -E "^CREATE TABLE" op. Je ziet de vorm van je database in drie seconden.