Binnen MySQL heb je verschillende mogelijkheden tot het maken van een backup. Hieronder worden de meest voorkomende backup variaties behandeld.
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:
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:
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.
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.