Webserver – Automatisch MySQL database backups maken
Ik heb hier als titel meegenomen “automatisch”, want dit programma (MySQLdump) kan via de commandline worden uitgevoerd, zo kun je dus heel makkelijk een “cronjob” laten lopen die bijvoorbeeld om de dag een database naar een bestand dumpt!, dit kun je hieronder vinden.
Wat is MySQLDump?
MySQLDump is een uitvoerbaar .exe bestand dat je kunt vinden in de MySQL installatiefolder: [MySQLFolder]\bin\mysqldump.exe
De MySQLDump utility geeft ons een scala aan mogelijkheden om een database back-up naar een externe bron, zoals een bestand, of zelfs een geheel andere MySQL server die draait op de andere kant van de wereld te geven!
Ik gebruik het woord “backup” nogal losjes hier, omdat MySQL niet daadwerkelijk een backup maakt van onze gegevens. Integendeel, het creëert een set van “CREATE TABLE” en “INSERT INTO” opdrachten die kunnen worden uitgevoerd tegen een MySQL-server voor het opnieuw opbouwens van de database).
Hoe een backup te maken van een database naar een bestand?
Daarvoor kun je de volgende commandolijn opgeven:
mysqldump --user [GEBRUIKERSNAAM] --password=[WACHTWOORD] [DATABASE] > [BESTAND.EXT]
Voorbeeld:
mysqldump --user root --password=[WACHTWOORD] mijndatabase > mijndatabase.sql
OPT commando:
Met het extra commando “–opt” (van optimize=optimaliseren) worden de databases iets geavanceerder weggeschreven, zodoende komen er ook “DROP TABLE IF EXISTS” commando’s in te staan die dus eerst een table verwijderen en vervolgens opnieuw opbouwen, de total commandolijn is dan als volgt:
mysqldump --user [GEBRUIKERSNAAM] --password=[WACHTWOORD] --opt [DATABASE] > [BESTAND.EXT]
Dumpen vanaf een andere computer/server
Met het extra commando “-h” (van host) kun je een MySQL server vanaf een IP adres opgeven om de informatie op te halen!
Ps. zorg wel dat je mysqldump.exe hebt op de desbetreffende computer, deze kun je kopieren uit de [MySQLFolder]\bin\ folder!
ysqldump -h [IPADRES] --user [GEBRUIKERSNAAM] --password=[WACHTWOORD] --opt [DATABASE] > [BESTAND.EXT]
Voorbeeld:
mysqldump -h 192.168.1.101 --user root --password=[WACHTWOORD] --opt mijndatabase > mijndatabase.sql
Hoe zet ik een database terug?
Een database terugzetten kan eenvourig met de volgende commandolijn:
mysqldump --user [GEBRUIKERSNAAM] --password=[WACHTWOORD] [DATABASE] < [BESTAND.EXT]
Voorbeeld:
mysqldump --user root --password=[WACHTWOORD] mijndatabase < mijndatabase.sql
Verkorte commandolijn
Hierboven staan de uitgebreide commandolijn parameters, deze kunnen ook verkort worden opgeschreven.
–password=[WACHTWOORD] kan ook opgegeven worden met -p[WACHTWOORD] (let op: GEEN spatie tussen -p en het wachtwoord!)
De commandolijn:
mysqldump -h [IPADRES] --user [GEBRUIKERSNAAM] --password=[WACHTWOORD] --opt [DATABASE] > [BESTAND.EXT]
Wordt dan:
mysqldump -h [IPADRES] -u [GEBRUIKERSNAAM] -p[WACHTWOORD] --opt [DATABASE] > [BESTAND.EXT]
Voorbeeld:
mysqldump -h 192.168.1.101 -u root -p[WACHTWOORD] --opt mijndatabase > mijndatabase.sql
Automatisch backups maken (Windows)
Hieronder volgt een voorbeeld om zelf via een “cronjob” (op Windows: st) een MySQL backup te laten maken
Maak met notepad een .BAT (Batch) bestand aan (mag ook .cmd heten) (bijvoorbeeld: C:\MySQLBackup.bat met de volgende inhoud:
(pas de gegevens aan op je eigen situatie!)
|
1 2 3 4 5 6 7 8 9 10 11 12 |
@echo off set mySqlPath=[MYSQLFolder] set dbUser=[GEBRUIKERSNAAM] set dbPassword=[WACHTWOORD] set dbName=MijnDatabase set file=%dbName%_%DATE:~3,8%_%time:~0,2%%time:~3,2%%time:~6,2%.sql set path=[BackupFolder zonder \ op het eind] echo Bezig met het dumpen van database: %dbName% ^> ^%path%\%file% echo Een moment geduld... "%mySqlPath%\bin\mysqldump.exe" -u %dbUser% -p%dbPassword% %dbName% > "%path%\%file%" echo Klaar! |
Voorbeeld:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
@echo off set mySqlPath=C:\MySQL set dbUser=root set dbPassword=[WACHTWOORD] set dbName=MijnDatabase set file=%dbName%_%DATE:~3,8%_%time:~0,2%%time:~3,2%%time:~6,2%.sql set path=E:\BACKUPSDB echo Bezig met het dumpen van database: %dbName% echo Een moment geduld... "%mySqlPath%\bin\mysqldump.exe" -u %dbUser% -p%dbPassword% %dbName% > "%path%\%file%" echo Klaar! |
Er is nu een SQL bestand aangemaakt: E:\BACKUPSDB\MijnDatabase_10-05-20_203134.sql
Wil je 1 backup hebben per database (anders worden er oneindig veel bestanden opgeslagen), dan kun je hetvolgende aan het batchbestand toevoegen:
del "%path%\%dbName%*.*" >null
Een backup maken van meerdere databases
Je kan in bovenstaand batch bestand gewoon doorbreien door de variabelen “dbName” en “file” opnieuw in te stellen en het onderste stukje weer te gebruiken, een voorbeeld:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
@echo off set mySqlPath=C:\MySQL set dbUser=root set dbPassword=[WACHTWOORD] set dbName=MijnDatabase1 set file=%dbName%_%DATE:~3,8%_%time:~0,2%%time:~3,2%%time:~6,2%.sql set path=E:\BACKUPSDB echo Bezig met het dumpen van database: %dbName% echo Een moment geduld... del "%path%\%dbName%*.*" >null "%mySqlPath%\bin\mysqldump.exe" -u %dbUser% -p%dbPassword% %dbName% > "%path%\%file%" echo Klaar! set dbName=MijnDatabase2 set file=%dbName%_%DATE:~3,8%_%time:~0,2%%time:~3,2%%time:~6,2%.sql echo Bezig met het dumpen van database: %dbName% echo Een moment geduld... del "%path%\%dbName%*.*" >null "%mySqlPath%\bin\mysqldump.exe" -u %dbUser% -p%dbPassword% %dbName% > "%path%\%file%" echo Klaar! set dbName=MijnDatabase3 set file=%dbName%_%DATE:~3,8%_%time:~0,2%%time:~3,2%%time:~6,2%.sql echo Bezig met het dumpen van database: %dbName% echo Een moment geduld... del "%path%\%dbName%*.*" >null "%mySqlPath%\bin\mysqldump.exe" -u %dbUser% -p%dbPassword% %dbName% > "%path%\%file%" echo Klaar! |
We kunen nu AT (Windows planner) gebruiken om het batch bestand uit te voeren! ga naar de prompt (cmd) en tik het volgende in:
at 23:00 /every:ma,di,wo,do,vr,za,zo C:\MySQLBackup.bat
Nu wordt C:\MySQLBackup.bat elke dag om 23:30 gestart!
Wil je alleen op Ma, Woe en Zat om 01:00 een backup maken, dan tik je hetvolgende in:
at 01:00 /every:ma,wo,za C:\MySQLBackup.bat
De praktijk:
Meer info over AT is hier te lezen.
Bronnen:
sitepoint.com
oskar.org