Hoe maak ik eenvoudig een back-up van mijn MySQL database?

Trefwoorden: mysql, query, statement, dump, data, mysqldump, back-up, database, phpmyadmin, crontab, cron, ssh

1. Inleiding


Bij het opstellen van een database doorloop je als ontwikkelaar doorgaans verschillende stadia. Eenmaal je de volledige databasestructuur hebt uitgekiemd, een dataload hebt uitgevoerd en de data + indexen hebt nagekeken, is het grootste werk normaal gezien achter de rug. Toch is er nog zeker één belangrijke taak waar je als serieuze ontwikkelaar absoluut niet mag aan verzaken: back-ups.

2. Werkwijze


Er is niet één specifieke manier om back-ups te maken. Hoe je te werk gaat, is doorgaans afhankelijk van de situatie en vaak zijn de mogelijkheden legio:

  • Ofwel kopieer je de raw data (de effectieve bestanden op het file systeem).
  • Ofwel schrijf je een script dat alle tabellen (die je wilt back-uppen) overloopt en de gegevens erin wegschrijft naar een back-up.
  • Ofwel gebruik je mysql (bijvoorbeeld commandline) om de tabellen te exporteren
  • Ofwel gebruik je een externe tool, zoals bijvoorbeeld PHPMyAdmin, om de vereiste data te exporteren.
  • Ofwel gebruik je mysqldump.

(Vanzelfsprekend is de bovenstaande lijst niet compleet, er bestaan nog 1001 andere manieren om hetzelfde te bereiken; de lijst was slechts een overzicht van een aantal mogelijk oplossingen.)

In dit artikel gaan we de laatste optie uit de bovenstaande lijst verkennen; mysqldump. Hou er rekening mee dat de voorbeelden bedoeld zijn voor een *NIX omgeving; de specifieke implementatie bij andere operating systems kan soms iets verschillen. We gaan er evenwel van uit dat je reeds minimale kennis van de omgeving hebt alvorens je dit artikel verder leest.

2.1. Wat is mysqldump?

Mysqldump is een (relatief klein) programma dat gebruikt kan worden om één of meerdere databases te exporteren (back-uppen) naar een bestand of een andere server. Mysqldump laat zelfs toe om enkel bepaalde tabellen binnen bepaalde databases te exporteren.

2.2. Algemene gebruik

In de praktijk ondersteunt mysqldump drie grote werkwijzen.

Voor het exporteren van één of meerdere databases:

CODE
  1. mysqldump --databases databasenaam/databasenamen


Voor het exporteren van alle databases:

CODE
  1. mysqldump --all-databases


Voor het exporteren van één of meerdere tabellen binnen een database:

CODE
  1. mysqldump databasenaam tabelnaam/tabelnamen


Zoals je merkt, overlappen de drie commando's elkaar in zekere zin. Normaal gezien specifieer je nog meerdere opties bij elk commando, maar hier komen we later in dit artikel nog op terug.

2.3. In de praktijk

Het mysqldump commando kan je, net als elk ander commando, oproepen aan het opdrachtprompt. Door de opties te specifiëren, kan je aangeven welke database je bijvoorbeeld wil exporteren:

CODE
  1. mysqldump -Q -uikke -pgeheim -hlocalhost databasenaam


In bovenstaand voorbeeld geven we mysqldump aan zich te authenticeren met de gebruikersnaam ikke en het wachtwoord geheim bij de mysql-server die draait op localhost (merk op dat er geen spatie tussen de optie en de waarde, bijvoorbeeld tussen -u en de gebruikersnaam). In feite kan je -hlocalhost weglaten, aangezien mysqldump standaard reeds localhost zal gebruiken. Is je MySQL database bereikbaar op een andere server, bijvoorbeeld mysqlhost, dan kan je via de optie -h die host meegeven: -hmysqlhost. De optie -Q is vooral handig bij grote tabellen; deze optie zorgt er voor dat mysqldump elk record in de (te exporteren) tabellen één per één zal exporteren. Wanneer je deze optie niet specifieert, zal mysqldump standaard (sinds versie 4.1) de te dumpen records in een keer naar het geheugen proberen te lezen. Hoewel deze methode sneller is, kan ze falen bij grotere datasets.

Het zou leuk zijn, moesten we de inhoud van mysqldump in een bestand kunnen opslaan; net als bij alle andere commando's kunnen we de output van mysqldump pipen naar een bestand:

CODE
  1. mysqldump -Q -uikke -pgeheim -hlocalhost databasenaam > backup.sql


Op de locatie van waar je het commando uitvoert, zal nu een bestand backup.sql worden aangemaakt. Dit bestand bevat niets meer dan SQL statements die je eenvoudigweg kan afvuren op mysql.

Naarmate de tabellen in je database groeien, kan opslagruimte mogelijk een probleem vormen. Het is afhankelijk van de situatie vanzelfsprekend, maar doorgaans ga je back-ups niet vaak aanspreken. Ergens is het dus logisch dat we de bestanden gaan comprimeren zodat deze minder ruimte innemen. Mysqldump kan zelf geen bestanden comprimeren, maar we kunnen de output ervan wel doorsturen naar bijvoorbeeld GZip:

CODE
  1. mysqldump -Q -uikke -pgeheim -hlocalhost databasenaam | gzip --best -c -f > backup.sql.gz


Eenvoudig toch?

3. Geautomatiseerde back-ups


Om occasioneel een back-up te maken, volstaat het perfect om mysqldump, voorzien van de juiste opties, uit te voeren. Echt interessant wordt het pas wanneer we zonder enige tussenkomst automatisch een back-up kunnen laten maken. Om dit te doen, kunnen we bijvoorbeeld beroep doen op crontab. Zij die niet bekend zijn met crontab, raad ik aan een korte introductie tot crontab door te nemen.

Het is mogelijk om alle nodige commando's achter elkaar te plaatsen, maar om alles overzichtelijk te houden, groeperen we in dit artikel alle commando's in een apart shell bestand (bijvoorbeeld backup.sh). Wat we willen bereiken, is dat we elke dag een back-up laten genereren van een (fictieve) database met de naam mijn_database. De back-up laten we wegschrijven in een gecomprimeerd bestand dat we opslaan onder een naam die afhankelijk is van de dag waarop de back-up gemaakt werd.

Tot zover de theorie, in praktijk ziet het er zo uit:

CODE
  1. #!/usr/local/bin/bash
  2. HuidigeDatum=`date +%d-%m-%Y`
  3. cd /var/backups/database/
  4. mysqldump -Q -uikke -pgeheim -hlocalhost mijn_database | gzip --best -c -f  > mijn_database.$HuidigeDatum.gz


Het bestand is op zich even ingewikkeld als het groot is; een korte introductie:

Om de back-ups elke dag een unieke naam te geven, slaan we eerst de huidige datum op in een variabele; zo kunnen we deze later eenvoudig aanspreken. Het resultaat zal er overigens uitzien als 16-01-2009. De back-ups zelf willen we opslaan in de map /var/backups/database/, dus wijzigen we de directory naar die locatie. Op de volgende en laatste regel, gebeurt de magie: mysqldump! In principe staat hier niets nieuw; het laatste voorbeeld uit het vorige hoofdstuk is zowat identiek. We loggen aan op localhost met de gebruikersnaam ikke en het wachtwoord geheim en exporteren de inhoud van de database mijn_database. Vervolgens GZippen we de gedumpte gegevens en slaan we deze op in een bestand dat elke dag een andere naam krijgt, bijvoorbeeld mijn_database.16-01-2009.gz.

Indien je van plan bent veel back-ups bij te houden, is het aan te raden om de datum om te draaien zodat deze van het formaat jaar-maand-dag is. Dit maakt het eenvoudig om de archieven te sorteren op datum, ongeacht de bestandsdatum.

Eenmaal dit bestand (backup.sh) is aangemaakt en opgeslagen (bij wijze van voorbeeld plaatsen wij het in /var/backups/), kan je het schedulen in crontab. Wij opteren om elke dag om 3 uur 's nachts een backup te laten maken:

CODE
  1. 0 2 * * * . /var/backups/backup.sh


Wanneer alles goed gaat, zien we nu dagelijks een back-up van alle tabellen in de database mijn_database verschijnen in de map /var/backups/database/

4. Een met mysqldump gemaakte back-up terugzetten


Aangezien de output van mysqldump doorgaans normale SQL statements zijn, kan je ze dus ook gewoon terug uitvoeren aan het MySQL prompt (of via tools van derden zoals PHPMyAdmin). Elk statement één per één uitvoeren kan een tijdrovende karwei zijn, maar ook hier schiet piping ons ter hulp. We kunnen namelijk de inhoud van een dump rechtstreeks aan MySQL feeden.

4.1. Een back-up maken

Een praktijkvoorbeeld; stel we maken een back-up van de database mijn_database:

CODE
  1. mysqldump mijn_database > mijn_database.sql


Het resultaat hiervan is een bestand mijn_database.sql dat zowel de structuur als de data van de volledige database bevat.

4.2. Een back-up terugzetten

Het terugzetten van de database is zo mogelijk nog eenvoudiger:

CODE
  1. mysql mijn_database < mijn_database.sql


Het is wel mogelijk dat je fouten ziet verschijnen omdat bepaalde tabellen reeds bestaan. De tabellen in kwestie droppen zou moeten volstaan om deze fouten de wereld uit te helpen ;-).

4.3. Een gecomprimeerde back-up terugzetten

Stel dat je jouw dump eveneens door gzip haalt en dus gezipt opslaat, kan je deze natuurlijk ook rechtstreeks inladen in MySQL, zonder deze eerst nogmaals te unzippen. De werkwijze is gelijkaardig aan die om een niet-gecomprimeerde dump terug te zetten, enkel voeg je een extra commando toe dat voor het uitpakken zorgt:

CODE
  1. gunzip < mijn_database.sql.gz | mysql mijn_database


Let er evenwel op dat de volgorde hier opmgekeerd is: je geeft je back-up door aan gunzip en het resultaat hiervan gebruik je vervolgens voor het mysql commando.
Eveneens nuttig om weten, is dat gunzip zelf nagaat in welke mate je jouw back-up hebt gecomprimeerd. Zoals je eerder in dit artikel misschien al gemerkt hebt, hebben we de optie --best toegevoegd aan het gzip commando. Dit zorgt ervoor dat onze back-up maximaal gecomprimeerd wordt; een actie die iets meer CPU-kracht vereist, maar in de praktijk vaak de rekenkracht waard is als je kijkt naar de schijfruimte die je wint.

De werkwijze voor bijvoorbeeld het bzip2 commando is gelijkaardig; je gebruikt bzip2 en bunzip2 om de dump respectievelijk te comprimeren en te decomprimeren.

5. Data kopiëren via mysqldump naar een ander server


In sommige gevallen moet je snel gegevens van de ene database kunnen repliceren naar een andere database, op een andere machine. Hoewel hier meer dan één manier voor bestaat, kunnen we ook hiervoor gebruik maken van de mogelijkheden van mysqldump.

Opnieuw een praktijkvoorbeeld; we maken nogmaals een back-up van de database mijn_database, maar ditmaal slaan we de back-up niet op in een bestand, maar sturen we deze rechtstreeks naar de tweede server op de host mysqlhost:

CODE
  1. mysqldump mijn_database | mysql --host=mysqlhost -C mijn_database


Een iets veiligere manier en een oplossing in situaties waarbij je niet rechtstreeks toegang hebt tot de MySQL database op de externe host, is te werken via SSH:

CODE
  1. mysqldump mijn_database | ssh gebruikersnaam@mysqlhost mysql mijn_database


De gebruikersnaam in gebruikersnaam@mysqlhost vervang je door een gebruiker die toegang heeft tot de machine waarop MySQL draait. Het gaat hier bijvoorbeeld om een Linux account, niet om een account in MySQL zelf.

6. Een back-up e-mailen


In geval van een kleine database, is het mogelijk om de dump van deze database naar een e-mail adres te sturen. Eigenlijk hoeven we, net zoals bij het comprimeren van een back-up, niet veel te doen om dit te bekomen:

CODE
  1. mysqldump mijn_database | mail -s 'MySQL Dump' info@example.com


Grote dumps kan je in principe ook doormailen, maar dit is vanzelfsprekend minder praktisch. Je kan natuurlijk altijd de dump comprimeren en deze als bijlage doorsturen. In situaties waarbij de dump echter vrij groot is, zal je doorgaans eerder geneigd zijn om voor een ander soort back-up strategie te opteren.

7. Vaak voorkomende problemen


Hoewel dat het gebruik van mysqldump, eenmaal je het doorhebt, vrij transparant is, kunnen er toch nog vaak ogenschijnlijk vreemde problemen opduiken. Een kort overzicht van mogelijke problemen die je kan tegenkomen alsook wat je kan doen om ze op te lossen.

7.1. Neerwaarts compatibele back-ups aanmaken

De voorbeelden in dit artikel zijn gebaseerd op het gebruik van mysqldump die geleverd wordt bij MySQL 5.1. Mysqldump is een tool die echter al van veel langer bestaat en dus is het mogelijk dat back-ups die gemaakt werden met mysqldump uit (bijvoorbeeld) MySQL 5.1 niet compatibel zijn met MySQL 4.0. Je kan bij het importeren van de back-ups vervolgens vreemde foutmeldingen krijgen, zoals bijvoorbeeld:

Dixit

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1'

De oorzaak hiervan kan zijn dat je een back-up, gemaakt van een database die draait op MySQL 5.1, wil terugzetten op een database die draait op MySQL 4.0. De oplossing is in dat geval vrij eenvoudig, voeg volgende optie toe aan je mysqldump commando:

CODE
  1. --compatible=mysql40

7.2. Access denied for user: xxx@yyy (Using password: YES).

De foutmelding is eigenlijk vrij veelzeggend; je hebt het verkeerde wachtwoord opgegeven in combinatie met de gebruikersnaam. Een veel voorkomende oorzaak van dit probleem is dat je een spatie hebt geplaatst tussen de de parameter -p en het eigenlijke wachtwoord.

8. Slotwoord


Indien al de tabellen in de database(s) die je wil back-uppen gebruik maken van de storage engine MyISAM, is het gebruik van mysqlhotcopy in plaats van mysqldump waarschijnlijk sneller. Aangezien de specifieke toepassing van mysqlhotcopy iets meer beperkt is dan die van mysqldump, werd er in dit artikel niet dieper op in gegaan. Moest je echter geprikkeld zijn na het lezen van dit artikel, is mysqlhotcopy een goede keuze om mee verder te gaan.

Mysqldump biedt heel wat meer mogelijkheden dan degene die we in dit artikel hebben belicht, bijvoorbeeld back-ups genereren van enkel de tabelstructuren of de output XML-valid maken. Zoals altijd, kan je door de optie --help op te geven een lijst met opties en hun effecten bekijken. Zoek je nog meer informatie? Dan is de MySQL documentatie een goede plaats om je speurtocht te storten.

Hou er rekening mee dat de aangehaalde voorbeelden in dit artikel niet altijd de meest efficiënste of zelfs veiligste zullen zijn. Er is doelbewust gekozen om de voorbeelden relatief eenvoudig te houden. Zo is het bijvoorbeeld niet altijd even verstandig om mysqldump te schedulen waarbij je de wachtwoorden rechtstreeks meegeeft; iedereen die ps aux uitvoert zal deze namelijk kunnen zien. Een meer elegantere oplossing is bijvoorbeeld het gebruik van een extern bestand om je wachtwoorden in op te slaan, in combinatie met de mysqldump optie -p.

Werken voorbeelden uit dit artikel niet bij jou? Heb je vragen bij bepaalde hoofdstukken? In dat geval start je best een nieuw onderwerp in onze forums. Gebruik de reactiemogelijkheid bij dit artikel enkel en alleen als je iets nuttig denkt toe te kunnen voegen aan dit artikel ;-).Dit artikel werd geschreven door Martijn op vrijdag 16 januari 2009 om 20:27 en werd sindsdien 21784 keer gelezen.

  • Pagina
  • 1 van 1

Bericht geplaatst door een gast op maandag 9 februari 2009 om 07:55:25.
Is het niet nuttig om in de tekst ook een stuk te wijden aan het e-mailen van een dump? Ik merk dat er heel veel manieren zijn om dit te bekomen en zie heel frequent dat er gebruik gemaakt wordt van CGI/Perl of zelfs PHP om dit te bereiken. Naar mijn mening is dat een beetje een nutteloze omweg, gezien je ook gewoon de output zou kunnen pipen naar het mail commando.

Eric Roodhooft

Opmerking van de crew

Zeer goed idee! Ik heb het artikel bijgewerkt met wat korte informatie (voorbeeld) hieromtrent.
Martijn
Bericht geplaatst door een gast op zaterdag 13 maart 2010 om 14:48:26.
De tip om in het kader van de veiligheid het wachtwoord in een extern bestand te plaatsen is goed. Ik neem aan dat je hierbij doelt op het bestand .my.cnf
Maar is het mogelijk zo'n voorbeeld uit te werken? Ik krijg het niet aan de praat.
Bericht geplaatst door een gast op maandag 1 november 2010 om 11:29:35.
Een prima overzicht. Zeer behulpzaam (thumbsup)
  • Pagina
  • 1 van 1