MySQL-databases onder Debian, Ubuntu en Linux dagelijks back-uppen

Draait u Debian, Ubuntu of een andere Linux-distributie op uw VPS, rootserver of dedicated server en wilt u al uw MySQL- en MariaDB-databases automatisch elke dag back-uppen? Dan bent u hier aan het juiste adres. In deze handleiding stelt u een back-upscript in dat elke nacht alle databases als gecomprimeerd SQL-bestand exporteert en oude back-ups automatisch opruimt.

De werkwijze is op alle distributies dezelfde en werkt onder Debian en Ubuntu net zo goed als onder AlmaLinux, Rocky Linux en RHEL. De verschillen zitten niet in het script, maar in de vraag welke databaseserver bij u draait en hoe u daarop inlogt. Daarvoor staat verderop een apart hoofdstuk over Debian tegenover Ubuntu.

Vereisten

U hebt SSH-toegang met rootrechten nodig en een geïnstalleerde MySQL- of MariaDB-server. Aanbevolen zijn de actuele distributieversies: Debian 13 "Trixie" en Debian 12 "Bookworm", Ubuntu 24.04 LTS "Noble Numbat" en Ubuntu 22.04 LTS "Jammy Jellyfish" en daarnaast AlmaLinux en Rocky Linux in de versies 9 en 10. Oudere systemen kunt u beter niet meer productief inzetten: Debian 10 bereikte in juni 2024 het einde van de ondersteuning, Ubuntu 20.04 LTS in mei 2025, en ook CentOS 7 krijgt geen reguliere beveiligingsupdates meer.

Werk het systeem eerst helemaal bij en installeer de teksteditor Nano, als die nog ontbreekt.

Voor Debian en Ubuntu:

apt update && apt upgrade -y

apt install nano -y

Voor AlmaLinux, Rocky Linux en RHEL:

dnf update -y

dnf install nano -y

Op actuele systemen uit de RHEL-familie is dnf de opvolger van yum. Het oude commando werkt daar meestal nog als verwijzing, maar u kunt beter dnf gebruiken.

Houd daarnaast rekening met voldoende opslagruimte. Een week aan gecomprimeerde back-ups neemt afhankelijk van de databasegrootte al snel meerdere gigabytes in beslag. De vrije ruimte controleert u met df -h.

Inloggegevens veilig opslaan

Het wachtwoord hoort niet rechtstreeks in de aanroep van mysqldump, want commandoregels zijn via ps zichtbaar voor alle gebruikers van het systeem. Maak in plaats daarvan een toegangsbestand aan dat alleen root mag lezen:

nano /root/.my.cnf

Inhoud van het bestand:

[client]
user=root
password=UW_DATABASE_WACHTWOORD

Zet daarna de rechten zo dat uitsluitend root toegang heeft:

chmod 600 /root/.my.cnf

Als er helemaal geen rootwachtwoord is

Op Debian en Ubuntu is het databaseaccount root in de regel niet met een wachtwoord beveiligd, maar via de identiteit van de systeemgebruiker. MariaDB noemt die methode unix_socket, MySQL noemt het auth_socket. Er bestaat dan simpelweg geen wachtwoord, en een regel in het toegangsbestand levert niets op. Of dat bij u het geval is, laat deze query zien:

mysql -e "SELECT user, host, plugin FROM mysql.user WHERE user='root';"

Staat daar unix_socket of auth_socket, laat het script dan ofwel als systeemgebruiker root draaien en zie helemaal af van het toegangsbestand, of maak een eigen back-upgebruiker aan. De tweede variant is de betere, omdat die het zonder de volledige rechten van het rootaccount af kan:

CREATE USER 'kh_backup'@'localhost' IDENTIFIED BY 'UW_BACKUP_WACHTWOORD';
GRANT SELECT, SHOW VIEW, EVENT, TRIGGER, LOCK TABLES, RELOAD, PROCESS ON *.* TO 'kh_backup'@'localhost';
FLUSH PRIVILEGES;

In het toegangsbestand vult u dan user=kh_backup in. De rechten zijn bewust krap gehouden: lezen, views bekijken, events, triggers en als terugvaloptie het vergrendelen van tabellen die geen InnoDB gebruiken. RELOAD en PROCESS zijn alleen globaal toe te kennen, vandaar ON *.*. PROCESS heeft MySQL 8 nodig om de tablespace-informatie mee te lezen. Wilt u dat recht niet toekennen, vul dan in het script --no-tablespaces aan. Komt u er helemaal niet meer in, dan helpt het artikel MySQL- en MariaDB-rootwachtwoord resetten.

Back-upscript aanmaken

Nu maken wij een Bash-script dat de export uitvoert en oude back-ups verwijdert. In ons voorbeeld heet het mysql_export_all.sh en staat het in de map /opt/mysqlbackups:

mkdir -p /opt/mysqlbackups

nano /opt/mysqlbackups/mysql_export_all.sh

In dit script zet u de volgende inhoud:

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/opt/mysqlbackups"
KEEP_DAYS=7
DATE=$(date +%Y-%m-%d-%H-%M)

mkdir -p "$BACKUP_DIR"

mysqldump --defaults-extra-file=/root/.my.cnf --all-databases --single-transaction --routines --events | gzip > "$BACKUP_DIR/alldbs_$DATE.sql.gz"

find "$BACKUP_DIR" -type f -name "alldbs_*.sql.gz" -mtime +$KEEP_DAYS -delete

De belangrijkste opties op een rij:

• --defaults-extra-file leest gebruiker en wachtwoord uit het zojuist aangemaakte bestand. Deze optie moet als eerste optie staan.
• --single-transaction zorgt bij InnoDB-tabellen voor een intern consistente stand, zonder de database te vergrendelen.
• --routines en --events nemen opgeslagen procedures en geplande events mee in de back-up. Triggers neemt mysqldump sowieso uit zichzelf mee.
• gzip comprimeert de export en bespaart daarmee flink wat opslagruimte.
• Het commando find verwijdert uitsluitend back-upbestanden die ouder zijn dan zeven dagen. Via KEEP_DAYS past u de bewaartermijn aan.

De regel set -euo pipefail is belangrijker dan hij eruitziet. Zonder pipefail beoordeelt de shell alleen de retourwaarde van gzip, en die is ook nul wanneer mysqldump daarvoor al is afgebroken. U zou dan een technisch foutloos archiefbestand hebben met onvolledige inhoud, en niemand zou het merken.

Welke databaseserver bij u draait, hangt af van de distributie. Debian bevat geen pakket mysql-server en zet volledig in op MariaDB, Ubuntu biedt daarentegen allebei aan. Het script werkt in alle gevallen ongewijzigd:

Script uitvoerbaar maken en testen

Maak het script uitvoerbaar:

chmod +x /opt/mysqlbackups/mysql_export_all.sh

Voer het eenmalig handmatig uit en controleer het resultaat, voordat u het automatiseert:

/opt/mysqlbackups/mysql_export_all.sh

ls -lh /opt/mysqlbackups/

Het aangemaakte bestand hoort duidelijk groter te zijn dan nul bytes. Veelzeggender dan het begin is echter het einde. Controleer daarom of het archief onbeschadigd is en de export werkelijk is doorgelopen:

gzip -t /opt/mysqlbackups/alldbs_*.sql.gz

zcat /opt/mysqlbackups/alldbs_*.sql.gz | tail -n 1

De laatste regel van een volledige export begint met -- Dump completed on. Ontbreekt die, dan is de export afgebroken en is het bestand als back-up waardeloos, ook al is het enkele honderden megabytes groot. Die ene regel is de snelste betrouwbare test die u hebt.

Cronjob voor de dagelijkse back-up instellen

Open de cronjob-editor:

export VISUAL=nano; crontab -e

Voor een dagelijkse back-up om 5 uur 's ochtends voegt u de volgende regel toe:

0 5 * * * /opt/mysqlbackups/mysql_export_all.sh >> /var/log/mysql-backup.log 2>&1

De uitvoer komt daarmee in een logbestand terecht, zodat u fouten later kunt nagaan. Of de cronjob correct is opgeslagen, controleert u met crontab -l. De job moet daarbij in de crontab van root staan. Op veel Ubuntu-images is het inloggen als root uitgeschakeld, daar luidt de aanroep sudo crontab -e. Als gewone gebruiker maakt u anders een eigen crontab aan, en dan struikelt het script later over het toegangsbestand onder /root.

Op minimale installaties uit de RHEL-familie ontbreekt de cron-dienst soms. Zo installeert en activeert u die:

dnf install cronie -y

systemctl enable --now crond

Vanaf nu worden alle databases elke nacht om 5 uur geëxporteerd, en back-ups die ouder zijn dan zeven dagen verdwijnen automatisch. Meer over tijdschema's en veelvoorkomende struikelblokken leest u in Cronjob onder Linux instellen.

Back-up terugzetten

Een back-up is pas iets waard wanneer u hem ook kunt terugzetten. Test het herstel daarom bewust een keer, het liefst op een testsysteem:

zcat /opt/mysqlbackups/alldbs_2026-07-26-05-00.sql.gz | mysql --defaults-extra-file=/root/.my.cnf

Wilt u slechts één enkele database terughalen, pak de back-up dan eerst uit en haal het bijbehorende gedeelte eruit, of maak van losse databases daarnaast aparte back-ups met mysqldump --databases meinedb. Handiger is het om meteen per database een eigen bestand weg te schrijven. Vervang daarvoor in het script de regel met mysqldump door deze lus:

for DB in $(mysql --defaults-extra-file=/root/.my.cnf -N -B -e "SHOW DATABASES;" | grep -Ev '^(information_schema|performance_schema|sys)$'); do
  mysqldump --defaults-extra-file=/root/.my.cnf --single-transaction --routines --events "$DB" | gzip > "$BACKUP_DIR/${DB}_$DATE.sql.gz"
done

De drie uitgesloten databases zijn views op interne servertoestanden en laten zich niet zinvol terugzetten. Pas ook het zoekpatroon in het commando find aan, anders ruimt dat de nieuwe bestandsnamen niet meer op. Voor een complete verhuizing naar een andere server laat WordPress naar een nieuwe server verhuizen de werkwijze aan een praktisch voorbeeld zien.

Back-ups buiten de server bewaren

Back-ups die uitsluitend op dezelfde server staan, helpen niet bij gegevensverlies van het complete systeem. Kopieer de bestanden daarom ook naar een tweede bestemming, bijvoorbeeld met rsync of scp naar een andere server of naar een back-upopslag:

rsync -avz /opt/mysqlbackups/ gebruiker@backupdoel:/pad/naar/backup/

Zet dit commando gewoon aan het einde van uw script, dan loopt de overdracht automatisch mee. Om dat in de cronjob zonder tussenkomst te laten werken, heeft root een SSH-sleutel zonder wachtwoordzin nodig, waarvan het openbare deel op het doelsysteem is opgeslagen. Hoe u die aanmaakt, leest u in Via SSH verbinding maken met de server.

Verschillen tussen Debian en Ubuntu

Beide systemen gebruiken apt, beide slaan de configuratie op onder /etc/mysql/, en het script hierboven draait op allebei ongewijzigd. Toch zijn er zes punten waarop ze van elkaar verschillen, en elk van die punten kan een back-up geruisloos laten mislukken:

Pakketnamen en tools

Wanneer u de database vanaf een andere machine back-upt, hebt u daar alleen het clientpakket nodig. Onder Debian heet dat mariadb-client, onder Ubuntu met MySQL 8 heet het mysql-client-8.0. Voor een script dat op beide systemen moet draaien, bestaat het metapakket default-mysql-client: onder Debian wijst het naar de MariaDB-client en onder Ubuntu naar de MySQL-client.

Vanaf MariaDB 10.5 dragen de tools daarnaast een naam met het voorvoegsel mariadb-, vanaf MariaDB 11 is die naam de eigenlijke en is mysqldump nog slechts een verwijzing daarnaartoe. Beide aanroepen werken daar. Op een Ubuntu-systeem met MySQL 8 bestaat daarentegen uitsluitend mysqldump, een script met mariadb-dump eindigt daar met command not found. Voor scripts die in beide werelden moeten draaien, is mysqldump daarom de juiste aanroep.

Onderhoudsaccount en authenticatie

Op Ubuntu maakt het pakket mysql-server nog altijd het onderhoudsaccount debian-sys-maint aan en schrijft het de inloggegevens daarvan naar /etc/mysql/debian.cnf. Dat bestand is alleen voor root leesbaar en is zonder verdere voorbereiding meteen voor een back-up te gebruiken:

mysqldump --defaults-file=/etc/mysql/debian.cnf --all-databases --single-transaction --routines --events | gzip > /opt/mysqlbackups/alldbs.sql.gz

Onder Debian met MariaDB vanaf versie 10.4 wordt dit account niet meer opnieuw aangemaakt. Het bestand bestaat daar meestal nog wel, maar verwijst in de regel alleen naar root via de socket. Vertrouw er dus niet op dat een script dat onder Ubuntu via dit bestand draait, op Debian hetzelfde doet. Controleren kunt u het in één stap:

mysql --defaults-file=/etc/mysql/debian.cnf -e "SELECT current_user();"

Nieuwe accounts maakt MySQL 8 aan met de methode caching_sha2_password, MariaDB met mysql_native_password. Voor de export via de lokale socket maakt dat niets uit, wel wanneer u vanaf een oudere client over het netwerk een back-up maakt. Meldt die "The server requested authentication method unknown to the client", dan is de client te oud voor MySQL 8 en is een update op zijn plaats.

AppArmor onder Ubuntu

Onder Ubuntu is AppArmor standaard actief en beperkt het de databaseserver, dus het proces mysqld respectievelijk mariadbd. Het beperkt niet de tool mysqldump, en dat verschil bepaalt of u tegen deze valkuil aanloopt.

De export uit deze handleiding schrijft het bestand via de shell (| gzip > ...), dus onder de gebruiker die het script uitvoert. Die weg heeft geen last van AppArmor en werkt in elke map waarin de gebruiker mag schrijven. Zodra echter de server zelf schrijft, gelden er andere regels. Dat is het geval bij mysqldump --tab=/pfad en bij elke SELECT ... INTO OUTFILE, want daarbij maakt het serverproces het bestand aan, niet uw shell. Dan grijpen twee van elkaar onafhankelijke blokkades in: de servervariabele secure_file_priv en het AppArmor-profiel van de server. Onder Ubuntu staat die variabele standaard op /var/lib/mysql-files/, en precies die map is ook in het AppArmor-profiel vrijgegeven. Een doelpad als /opt/mysqlbackups mislukt daarom dubbel.

Lastig daarbij is het zoeken naar de oorzaak. De eerste blokkade meldt zich duidelijk met ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement. De tweede verschijnt daarentegen als een simpele Errcode: 13 "Permission denied", hoewel eigenaar en rechten van de doelmap op het eerste gezicht volkomen in orde zijn. Wie dan bestandsrechten gaat uitdelen, zoekt op de verkeerde plek. Controleer in dat geval allebei:

mysql -e "SHOW VARIABLES LIKE 'secure_file_priv';"

aa-status | grep -Ei 'mysqld|mariadbd'

journalctl -k | grep -i 'apparmor.*DENIED' | tail -n 20

Duikt bij het laatste commando een regel op met apparmor="DENIED" en uw doelpad, dan hebt u de oorzaak gevonden. Het eenvoudigst is om het niet zover te laten komen en bij de export via de pipe te blijven. Hebt u schrijfacties aan serverzijde echt nodig, schrijf dan naar /var/lib/mysql-files/ en verplaats het bestand daarna. Alleen wanneer allebei afvalt, breidt u het profiel uit met het extra pad. Daarvoor is het bestand /etc/apparmor.d/local/usr.sbin.mysqld bedoeld, bij MariaDB navenant usr.sbin.mariadbd, gevolgd door systemctl reload apparmor. AppArmor uitschakelen is geen oplossing, maar het weghalen van een beschermlaag die juist deze server beveiligt.

Veelvoorkomende fouten en oplossingen

De back-up is 0 byte groot: Meestal kloppen de inloggegevens in /root/.my.cnf niet. Test het inloggen met mysql --defaults-extra-file=/root/.my.cnf -e "SHOW DATABASES;". Vaak zit daar het hierboven beschreven geval achter, namelijk dat het account via de socket werkt en een opgeslagen wachtwoord daarom helemaal niet past.

"Access denied" in cron, maar niet op de console: De cronjob draait onder een andere gebruiker dan verwacht. Zet de job in de crontab van root en gebruik in het script uitsluitend absolute paden. Meer oorzaken verzamelt MySQL Access denied for user oplossen.

Unknown table 'COLUMN_STATISTICS' in information_schema: U maakt een back-up van een MariaDB-database met de mysqldump uit MySQL 8, bijvoorbeeld vanaf een Ubuntu-machine. MySQL 8 bevraagt bij de export een tabel die in MariaDB niet bestaat. Hang --column-statistics=0 aan de aanroep. Die optie kent alleen de MySQL-client, op een puur MariaDB-systeem mag u hem niet zetten.

Access denied; you need (at least one of) the PROCESS privilege(s) for this operation: De back-upgebruiker mag onder MySQL 8 de tablespace-informatie niet lezen. Ken PROCESS toe zoals hierboven beschreven of vul --no-tablespaces aan.

mariadb-dump: command not found: Het script komt van een Debian-systeem met MariaDB 11 en draait nu op Ubuntu met MySQL 8. Daar bestaat die naam niet. Schrijf mysqldump, dat werkt op beide systemen.

Can't connect to local MySQL server through socket: De server draait niet, of de socket staat op een ander pad dan verwacht. De mogelijke oorzaken beschrijft MySQL-socketfouten oplossen.

SELinux blokkeert het script (AlmaLinux, Rocky Linux, RHEL): Controleer met ausearch -m avc -ts recent of er een geweigerde toegang is vastgelegd, en pas zo nodig de context van de back-upmap aan.

De schijf loopt vol: Verlaag KEEP_DAYS, of verplaats oudere back-ups naar een externe opslag. Wat er verder nog ruimte opslokt, laat Schijf vol onder Linux opruimen zien.

Foutmelding over --single-transaction bij MyISAM-tabellen: Deze optie werkt alleen bij InnoDB. Bij MyISAM-tabellen kunt u in plaats daarvan --lock-tables gebruiken, dan worden de tabellen voor de duur van de export vergrendeld. Houd er bovendien rekening mee dat de systeemtabellen bij MariaDB in de Aria-engine staan en niet door --single-transaction worden gedekt.

Bent u toch al met de database bezig, neem de rest dan meteen mee: anonieme gebruikers verwijderen, de testdatabase verwijderen en de toegang op afstand voor root uitschakelen. Hoe dat gaat, leest u in MariaDB en MySQL beveiligen.