Backup en recovery MySQL
Untitled Document
Inleiding
Binnen MySQL heb je verschillende mogelijkheden tot het maken van een backup. Hieronder worden de meest voorkomende backup variaties behandeld. Deze tutorial heb ik gemaakt voor de cursus MySQL maar wil hem jullie niet onthouden omdat het een zeer belangrijk punt is die vaak wordt vergeten.
Logical of Physical backup methode
Wat is dit nu allemaal weer? Logical of Physical? Om dit uit te kunnen leggen moeten we eerst begrijpen hoe de MySQL zijn data opslaat. Logisch voor de gebruiker gezien worden de databases opgeslagen als SQL code. Maar voor de server is dit anders. De server ziet het namelijk niet als SQL code maar slaat de databases op als bestanden / folders op de server.
Logical backup houdt dus in dat de backup wordt gemaakt door de SQL code te backuppen.
Physical backup houdt in dat de backup wordt gemaakt door de bestanden en / of folders te backuppen.
De eigenschappen van de Logical backup zijn:
- De backup wordt gemaakt door de hele MySQL server te doorlopen en de structuur en content op te slaan.
- De backup neemt meer tijd in beslag omdat de server eerst de database moet aanspreken, de code omzetten en dit door te sturen naar de backup.
- De backup is groter dan de Physical backup. Vooral als je de backup als text file opslaat.
- De backup is te maken op server niveau, database niveau en tabel niveau.
- De backup neemt geen log files, configuratie files of andere files mee die de MySQL server gebruikt.
- De backup moet worden gemaakt als de server draait.
De eigenschappen van de Physical backup zijn:
- De backup wordt gemaakt door de bestanden / folders te backuppen van de databases
- De backup is sneller dan de Logical backup omdat het bestand één - op één wordt overgezet.
- De backup is veel kleiner dan de Logical backup.
- De backup kan log files, configuratie files en andere files bevatten die de server gebruikt.
- De backup kan zowel draaiend als niet draaiend gemaakt worden. Wanneer de server draait is het wel vereist om een LOCK op de tabel / database te zetten.
Online of Offline backup methode
Ook hier weer een kleine uitleg wat beide inhoud. Online of ook wel hete backup genoemd wordt gemaakt op een draaiende server. Offline of ook wel koude backup genoemd wordt gemaakt op een niet draaiende server.
De eigenschappen van een online backup:
- Veel minder ingrijpend voor clients omdat de server blijft draaien.
- Er moet een lock op de database / tabel worden gezet om de backup te maken
De eigenschappen van een offline backup:
- De backup is ingrijpend voor clients omdat de server offline moet.
- De backup maken is makkelijker omdat er geen client kan inloggen.
Lokaal of remote backup methode
Op zich spreekt deze methode voor zich. Waar sla je de backup op? Lokaal op de server of op een andere machine? (remote). Deze vraag is afhankelijk van het te gebruiken product. Hieronder vind je een klein overzicht van de mogelijke programma's die je kan gebruiken voor de backup met daarbij vermeld of deze remote of lokaal kan worden opgeslagen.
MySQLDump Deze kan zowel lokaal als remote opslaan. Gebruik makend van de SQL output kan deze beide methodes aan. Voor een gedetailleerde text backup is er alleen de mogelijkheid om op de server op te slaan
MySQLHotCopy Deze kan alleen lokaal opslaan. Dit komt omdat dit programma de server locked om de bestanden te kunnen backuppen.
SELECT ....... INTO OUTFILE Dit commando kan van een remote machine worden aangeroepen maar de output komt lokaal te staan op de server.
Physical backup methodes zoals hierboven uitgelegd worden veelal lokaal opgeslagen omdat de server offline wordt gehaald om de backup te maken.
Full of Incremental backups
De MySQL server biedt mogelijkheid om een complete backup uit te voeren (Full) of alleen een bepaald gedeelte van de server (Incremental). Om een incremental backup uit te voeren dien je de server binary log te enablen. De binary log wordt gebruikt om wijzigingen bij te houden die gemaakt worden op de server.
Meer informatie over het maken van backups kan je vinden op:
Een forum over backup mogelijkheden in MySQL http://forums.mysql.com/list.php?93
Uitgebreidere info over MySQLDump en MySQLHotCopy http://dev.mysql.com/doc/refman/5.0/en/programs.html
Een backup maken van een innoDB database http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html
1.2.2 Voorbeeld backup en recovery strategie
Op MySQL.com staat een voorbeeld van een te volgen strategie. Dit voorbeeld wordt in de komende paragraaf behandeld en behandeld procedures om data te backuppen na een aantal crashes:
- Besturings systeem crash
- Stroom onderbreking
- Beschadigd bestandssysteem
- Hardware problemen
We gaan er van uit dat de database opgeslagen is als innoDB. Deze ondersteund namelijk transacties en automatische crash backups.
Voor de eerste twee crashes kunnen we ervan uitgaan dat de data weer beschikbaar is na een restart van de server. De data die tijdens de crash werd doorgegeven zal door innoDB zelf worden bijgewerkt omdat deze een log file bijhoudt van de wijzigingen. Informatie over dit proces is terug te vinden in de server log van MySQL. Dit kan er als volgt uit zien:
InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections
Wanneer er een van de laatste crashes optreedt is het probleem iets groter. De server kon namelijk zijn schijfbewerkingen niet meer uitvoeren en crashed. Ook zal de server niet opstarten. Installeer dus een nieuwe disk en los eventueel het onderliggende probleem op dat de crash veroorzaakte. Omdat niemand zit te wachten hierop is het van belang dat er backups gemaakt worden.
Backup policy
Zoals hierboven beschreven is het zeer van belang dat er periodiek backups uitgevoerd worden. Er zijn meerdere methodes hiervoor. We bespreken hier de mogelijkheden met mysqldump.
Ervan uitgaande dat de backup zondagnacht om 1 uur gemaakt wordt kan je de volgende commando uitvoeren:
mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql
Dit commando zorgt vooraf dat de benodigde onderdelen gelocked worden. De toevoeging --single-transaction zorgt ervoor dat de backup consistent kan lezen en dat de data niet gewijzigd wordt terwijl er gelezen wordt. De output van deze backup is SQL code die later kan worden gebruikt.
Nu hebben we een full backup gemaakt van de server. Een full backup neemt veel tijd en ruimte in beslag. Beter is het om op vaste tijdstippen een full backup te maken. Bijvoorbeeld elke maand op de eerste zondag om 1 uur 's nachts een full backup. Hoef je dan die tussenliggende weken niet te backuppen? Natuurlijk wel. Dit kan met de incremental backup. De vereiste hieraan is dat je de server start met de optie: --log-bin. Elke keer wanneer de MySQL server herstart wordt maakt deze een bestand aan dat lijkt op server-bin.00001. Na een restart maakt de server vervolgens server-bin.00002 aan etc. Ook staat er een bestand bij dat eindigt op .index Dit bestand houdt alle bin bestandjes bij. Verder kan je ook handmatig aangeven om een nieuwe bin file aan te maken door het commando --flush-logs. Als we vervolgens bovenstaande commando herschirjven zodat er een incremental backup wordt gemaakt en een nieuwe bin file ziet dit er als volgt uit:
mysqldump --single-transaction --flush-logs --master-date=2 --all-databases > backup_sunday_1_PM.sql
Als we dit uitvoeren zien we dat er een nieuwe bin file aangemaakt is. Kijken we vervolgens in de SQL zien we de volgende regels staan:
-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='server-bin.000007',MASTER_LOG_POS=4;
De SQL code bevat alle code tot het aanmaken van de nieuwe bin file. Deze bin files zijn belangrijk voor een incremental backup. Bewaar deze dus op een veilige plaats.
Omdat de bin filetjes kunnen oplopen in aantal en grootte is het handig om deze van tijd tot tijd op te schonen met het commando --delete-master-logs. Doe dit bijvoorbeeld bij een full backup. Let wel op: Als je server een master is van een replica constructie kan het gevaarlijk zijn om dit commando uit te voeren omdat de mogelijkheid bestaat dat de slave nog niet uitgeschreven is.
1.2.3 Recovery
In de vorige paragraaf hebben we geleerd hoe we een backup kunnen maken. Hopelijk gebruik je ze nooit, maar af en toe heb je de backup daadwerkelijk nodig om deze terug te zetten of zoals in het Engels: recovery. In deze paragraaf gaan we kijken hoe we een backup kunnen terugzetten. Dit doen we aan de hand van de twee commando's die we in de vorige paragraaf gebruikt hebben om de backup te maken.
Het terugzetten van een full backup is simpel. Deze bevat namelijk alleen maar SQL code:
shell> mysql < backup_sunday_1_PM.sql
Op dit moment hebben we een backup restored die data terug zet tot zondag 1 uur 's nachts. Om verder te gaan moeten we met de incremental backups gaan werken. Kijkend naar de bin files zien we dat deze de volgende namen hebben: server-bin.0001 en server-bin.0002. Om deze terug te zetten gebruiken we het volgende commando:
shell> mysqlbinlog server-bin.00001 server-bin.00002 | mysql
Zoals je ziet is het maken en terugzetten van backups een niet al te moeilijke taak. Maar het is wel één van de belangrijkste taken die er is binnen de database servers. Niemand wil natuurlijk dat zijn / haar data kwijt is na een crash.
|
Wat is jouw favoriete javascript framework? (S: 18, R: 2)
