Pagina1 van 1

Automatische MySQL Backup m.b.v. Replicatie

Automatische MySQL Backup m.b.v. Replicatie
   2

De laatste paar dagen ben ik aan het rommelen geweest met het opzetten van een tweede QNAP NAS als een backup machine voor mijn primaire QNAP. M.b.v. realtime synchronisatie worden nu alle files van mijn QNAP TS-869 Pro automatisch gekopieerd naar mijn backup QNAP (een QNAP TS-569L). Jammer alleen dat de mannen van QNAP even vergeten zijn een optie voor een backup van de MySQL database(s) in te bakken.

Gelukkig heeft MySQL zelf een aantal opties om een backup van de ene database server naar een andere database server te kopiëren … een daarvan wordt “Replication” genoemd en iedere wijziging in de “Master” (bron) wordt automatisch overgnomen door de “Slave” (de backup database server). Dit mechanisme werkt erg goed en de Slave hoeft niet eens in constante verbinding te staan met de Master om de wijzigen over te nemen.

In dit artikel laat ik je zien hoe ik dit voor mijn twee MySQL servers (QNAP) heb gedaan …




Voor we beginnen …

Besturingssysteem verschillen …

Ik baseer de beschrijving in dit artikel op mijn ervaringen met twee QNAP NAS apparaten (TS-869 Pro en TS-569L) die ik gebruik. Echter: de beschreven instructies zouden voor elke MySQL installatie hetzelfde moeten werken. De kleine verschillen zul je vinden in b.v. de locatie van het MySQL configuratie bestand (my.cnf). Bedenk dat voor replicatie het gebruik van twee dezelfde versies van MySQL aan te rade is – of in ieder geval in de zelfde versie reeks.

Wat we nodig hebben …

  • Twee MySQL servers
  • … welke dezelfde MySQL versies draaien (aan te raden),
  • … welke optioneel phpMyAdmin of MySQL WorkBench heeft voor database toegang,
  • … en\of de èèn of andere shell toegang hebben (direct, SSH, Telnet, etc).

 

Aannames …

Voor het gemak definiëren we de IP adressen van de servers als volgt – pas dit aan waar nodig zodat het jouw IP adressen gebruikt:

  • Master (de Bron), met IP adres 192.168.1.100
  • Slave (het Doel), met IP adres 192.168.1.200

 

Andere aannames, maar die niet vereist zijn:

  • Beide MySQL Servers draaien onder Linux (QNAP)
  • Beide MySQL Servers draaien een vergelijkbare MySQL versie (standaard QNAP versie)

 

Methoden voor Automatische MySQL backup

Snapshot Dumps

Een backup maken van MySQL database9s) kan bijvoorbeeld gedaan worden met een SQL dump, waarbij een groot bestand aangemaakt wordt welke SQL statements bevat om de databases van nul weer op te bouwen. Het nadeel is dat je alleen maar een moment opname hebt en dat je een script regelmatig moet draaien om regelmatige dumps te maken.

Voor wie met dit soort dumps al een gewerkt heeft: ze zijn vaak langzaam, produceren grote bestanden en in bepaalde gevallen loop je tegen import of time-out problemen (phpMyAdmin) aan.

Dit is dus niet iets wat werkt voor wat ik in gedachten heb: real-time backups …

Een dump maken is echter niet zinloos, dus als je meer wilt weten lees dan ook ons artikel MySQL Export and Import Database(s).

Replicatie

MySQL heeft een ingebouwde replicatie functie, die real-time-achtig data veranderingen van een Master (de Bron) automatisch kan toepassen op een Slave (het Doel of de Backup). Het is niet echt realtime, maar replicatie kan zelfs gedaan worden naar een database die ergens anders in de wereld staat.

Simpele beschrijving hoe dit werkt:
Elke verandering in de Master Database, genereert SQL statements in een zogenaamde “binary” log. Zodra de Slave verbinding maakt met de Master, vraagt het om de “ongelezen” wijzigingen, download ze vervolgens en past ze toe op zijn eigen (Slave) data … Het voordeel hiervan is dat er niet constant een verbinding hoeft te zijn en data kan worden geüpdate in het tempo dat de Slave en de verbinding kan bijhouden.

Precies wat ik zocht …

Termen, Besturingssystemen en versies …

Master
De “Master” is de “hoofd” database server, de bron van de originele data, of hoe je het ook wilt noemen …

Slave
De “Slave” is de database server die een kopie van de data van de “Master” heeft.

Versies
Master en Slave hoeven niet perse hetzelfde besturingssysteem te draaien. Het is echter wel aan te raden om dezelfde MySQL versie te draaien op beide servers. Je kunt een beetje afwijken wat versie nummer betreft, maar je zult zelf moeten proberen of het correct werkt. Zie ook de MySQL handleiding voor versie compatibiliteit.

Opzetten van Automatische MySQL Backup m.b.v. Replicatie

Uiteraard moeten we eerst MySQL geïnstalleerd hebben staan en voor een QNAP is dat erg eenvoudig.

Ga naar “Control Panel” in de QNAP Web-interface. Onder “Applications” vindt je “MySQL Server“.

Vink de optie “Enable MySQL Server” aan. Als je toegang op afstand nodig hebt, bijvoorbeeld als je MySQL WorkBench gebruikt of als je toegang van een PC of XBMC nodig hebt, vink dan ook de optie “Enable TCP/IP networking” aan.

Vervolgens klik je op “Apply“.

Tip : Op deze pagina vindt je ook een link om phpMyAdmin op jouw QNAP te installeren.

Doorloop deze stappen voor de Slave en Master.

Andere Besturingssystemen

Linux gebruiker (doe dit NIET op jouw QNAP) typen in de shell:


sudo apt-get install mysql-server mysql-client

Windows en MacOS X gebruikers kunnen MySQL downloaden van de MySQL homepage of d.m.v. een WAMP Server of MAMP installatie (of vergelijkbare distributie).

Vanaf dit punt neem ik aan dat MySQL en phpMyAdmin is geinstalleerd en dat we met een QNAP werken. Andere systemen (Linux, Windows, MacOS X) werken hetzelfde met het verschil dat de locatie van het my.cnf bestand anders zal zijn en dat toegang tot phpMyAdmin anders kan zijn.

Opzetten van de Master MySQL server

Stappen:

  • De Master als “Master” opzetten
  • Aanmaken van ee “Slave” gebruiker op de “Master”
  • Herstarten van de “Master” server
  • Volledige data dump maken van de gewenste Master Database(s)

 

– Master als Master opzetten

We gaan nu een van de twee MySQL servers, de server met IP adres 192.168.1.100, opzetten als de Master. Hiervoor moeten we het MySQL configuratie bestand aanpassen.

Dit configuratie bestand, my.cnf, kun je voor een QNAP hier vinden:  /etc/config/my.cnf . Voor andere systemen kan deze locatie anders zijn, zo zien we b.v. bij bepaalde Linux configuraties, b.v. dat my.cnf in /etc/mysql/ staat.

Bestanden op afstand bewerken …
 

Om bestanden op afstand te bewerken gebruiken we vaak SSH …
Programma’s zoals WinSCP (Windows), Filezilla (Windows, MacOS X, Linux) of Forklift (MacOS X) maken dit echter stukken eenvoudiger.

 

In het my.cnf bestand, van de Master MySQL Server (/etc/config/my.cnf) moeten we de volgende parameters toevoegen (vervang deze parameters als ze al bestaan):


1
2
3
4
5
#Master Instellingen
log-bin=mysql-bin
server-id=100
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Regel 2 bepaald waard de log file met wijzigingen voor de Slave worden opgeslagen.

Regel 3 is waar we de master een ID geven.
Dit ID moet uniek zijn in jouw Master-Slave netwerk  en moet een nummer tussen 1 tot 4294967295 zijn. In mijn voorbeeld gebruik ik het laatste nummer van het IP adres zodat ik het e.e.a. makkelijker kan onthouden en herkennen.

Regels 4 en 5 zijn optioneel maar worden in de MySQL handleiding aanbevolen. Als ik het goed begrepen heb dan verbeterd het de synchronisatie en verwijderd het data van de log file als een synchronisatie is afgerond.

 Aanvullende opties …

Er zijn een aantal opties die ik zelf niet gebruikt heb.

Bind aan een IP Adres

Het blijkt dat een aantal Linux versies een geschikt IP adres verbinden wil zien, maar ik heb het zelf dus niet gebruikt.


1
2
3
4
# Choose either localhost (bind 127.0.0.1)
# or the IP address of your server, for example:

bind 192.168.1.100

Bepaal welke database(s) repliceert worden

Je kunt beperken welke databases je wil repliceren, met als voordeel dat de log file geen onnodige data blijft opslaan.

  Selecteren van Databases

In onderstaand voorbeeld gebruiken we “binlog-do-db” zodat we alleen maar de database “test” repliceren.

Je moet, volgens de handleiding, een regel toevoegen voor elke database die je wilt repliceren. Het is echter ook mogelijk om een komma gescheiden lijst op te gene.


1
binlog-do-db=test

  Uitsluiten van Databases

In plaats van het opgeven van de gewenste databases, kun je ook opgeven welke databases je NIET wilt repliceren m.b.v. de “binlog_ignore_db” parameter, welke vergelijkbaar werkt als voorgaand voorbeeld. In onderstaand voorbeeld willen we alle databases repliceren, behalve de “test” database:


1
binlog-ignore-db=test

De regel die ik zelf gebruik, repliceert alle database behalve de database “test”, “information_schema” en “mysql”:


1
binlog-ignore-db=test,information_schema,mysql
– Slave gebruiker aanmaken op de master

Om toegang te hebben (voor de Slave) op de Master, moeten we een gebruiker op de Master een Slave gebruiker aanmaken met REPLICATION SLAVE rechten.
Je zou deze rechten aan een bestaande gebruiker kunnen toevoegen maar het is aan te raden om een specifieke nieuwe Slave gebruiker aan te maken.

Het volgende SQL statement maakt de gebruiker “slave_user” met REPLICATION SLAVE rechten aan – voer het uit in b.v. phpMyAdmin, MySQL WorkBench of in de MySQL command line. Natuurlijk moet je niet vergeten het wachtwoord aan te passen.


1
2
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'wachtwoord';
FLUSH PRIVILEGES;
– Herstarten van de Master

Na deze wijzigingen moeten we de Master MySQL server herstaren en dit kan op twee manieren gedaan worden.

Van de shell:

QNAP (via SSH) en bepaalde Linux versies:
 /etc/init.d/mysqld.sh restart

Andere Linux versies zouden met het volgende MySQL kunnen herstarten:
 sudo service mysql restart

Windows:
 net stop MySQL || net start MySQL

Vanuit de QNAP Web-interface:

Je kunt in de QNAP Web-interface naar “Control Panel Application MySQL Server” gaan en het vinkje voor “Enable MySQL Server” weghalen. Klik vervolgens op “Apply“, en vink de optie “Enable MySQL Server” hierna weer aan en klik weer op “Apply“.

Je kunt de status van de Master met het volgende SQL statement opvrageb:


1
SHOW MASTER STATUS;
– Initiele Data Dump van de Master maken

Na het herstarten van de MySQL server, zal MySQL beginnen met het wegschrijven van wijzigingen op de gewenste databases. Dat wil zeggen: de wijzigingen nadat je de server herstart hebt. De oude data echter wordt niet hierbij meegenomen en we moeten dus een initiële data dump maken die we straks gaan importeren in de Slave.

Merk op : Als je phpMyAdmin opent en naar de beschikbare databases kijkt (klik de “databases” tab), dan zie je meteen welek database wel of niet gerepliceert worden. In onderstaande afbeelding  binlog-ignore-db=test,information_schema,mysql . Zie je hoe alle databases, behalve “information-schema”, “mysql” en “test”, op “  Replicated” staan?

MySQL - phpMyAdmin laat zien welke databases gepliceerd worden

MySQL – phpMyAdmin laat zien welke databases gepliceerd worden

Om de gemiste stappen in te halen moeten we een initiële data dump maken van de Master voor onze Slave MySQL Server.

We hebben hiervoor drie opties: phpMyAdmin, MySQL Workbench en de MySQL Command-line.
Lees het artikel “MySQL Export en Import Data” hoe je dit kunt doen met deze methoden.

Opzetten van de Slave MySQL Server

Stappen:

  • Initiële import van de Master data
  • Voorbereiden van de Slave my.cnf
  • Starten van de Slave functie
  • Oplossen van kleine replicatie problemen op de Slave
– Initiële data import voor de Slave

Als eerste gaan we de data importeren die we daarnet hebben ge-exporteert van de Master, zodat we een “basis” hebben die overeenkomt met de Master, toen we de Master replicatie aan hadden gezet.

Kijk weer naar het artikel “MySQL Export en Import Data” hoe je dit kunt doen.

Na de initiele import is het een goed idee om even te controleren of het e.e.a. overeenkomt met wat we op de Master vinden. Ik pik daarvoor een aantal random tabellen en kijk naar een aantal records die bijvoorbeeld een teller of een tijd/datum hebben.

– Voorbereiden van de Slave my.cnf

Net als voor de Master, moeten we voor de Slave ook de my.cnf bewerken. De volgende regels moeten vervangen of toegevoegd worden aan het my.cnf bestand:


1
2
3
4
5
6
7
8
#Master - Instellingen voor replicatie
log-bin=mysql-bin
relay-log=mysql-relay-bin.log
server-id=200
binlog-ignore-db=test,information_schema,mysql
master-host=192.168.1.100
master-user=slave_user
master-password=password

Vergeet niet dat de regels 4-8 aan te passen voor een correcte server ID (niet hetzelfde als de master!), databases selectie, Master IP adres, gebruikersnaam en wachtwoord van de slave_user.

Herstart MySQL na het opslaan van de wijzigingen (zie de beschijving voor het herstarten zoals we dat gedaan hebben bij de Master).

Je kunt optioneel het volgende SQL statement uitvoeren in b.v. phpMyAdmin, MySQL WorkBench of de MySQL command-line, wat in principe hetzelfde doet als de laatste drie regels in ons my.cnf bestand – voor het geval je die informatie liever niet in leesbare vorm in een bestand wilt hebben.


1
CHANGE MASTER TO MASTER_HOST='192.168.1.100',MASTER_USER='slave_user', MASTER_PASSWORD='password';
 – Starten en in de gaten houden van de Slave

Met het volgende SQL commando starten we de Slave:


1
START SLAVE;

Een “probleem” is achter dat als er iets fout gaat, dat “START SLAVE” je dit niet laat weten!

Het volgende SQL statement laat je echter zien wat de Slave status is:


1
SHOW SLAVE STATUS;

Dit statement is erg handig als je wilt kijken of er fouten waren. Kijk vooral naar de kolommen “Last_Error” en “Last_SQL_Error” …

Tip: Status volgen met phpMyAdmin 

Voor wie phpMyAdmin gebruikt: kijk eens onder de “Replication” tab.
Je zult hier links vinden waar je beter kunt zien wat de status is. Je kunt dit ook met “SHOW SLAVE STATUS\G”, doen, maar dat werk helaas niet onder phpMyAdmin.

– Replicatie problemen oplossen

Een van de problemen waar ik tegen aanlipe is dat Master en Slave toch niet helemaal in sync waren – verschilende “Duplicate Key” errors gaven dit aan. Een dergelijke fout STOPT DE SLAVE functie!

Ik heb een aantal oplossingen gezien die de Master POSITION gebruiken, maar moet zeggen dat geen van deze oplossingen fout-vrijwaren.

Om deze fout meldingen op te lossen, moeten we MySQL (Slave) vertellen dat we een aantal “update stappen” moeten overslaan.
Als je naar de “SHOW SLAVE STATUS” output kijkt, dan zie je een aantal kolommen die met “LOG_POS” eindigen, waar je kunt vinden waar het e.e.a. mogelijk fout liep. Je kunt nu met een SQL statement aangeven hoeveel stappen je kunt “skippen”.


1
2
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
SLAVE START;

Bovenstaand voorbeeld slaat 1 error over en gaat daarna door – tot het klaar is of weer tegen een fout aanloopt. Je kunt een ander nummer dan “1” gebruiken – maar maak er zeker van dat geen vitale informatie overgeslagen wordt.

Een maar klaar (wanneer “LAST_ERROR” en “LAST_SQL_ERROR” leeg blijven) zal de synchronisatie prima verlopen …

Het is aan te raden, zeker als je een hoog nummer had gekozen, om de SKIP_COUNTER terug op nul te zetten met het volgende SQL statement:


1
2
3
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 0;
SLAVE START;

 

Ondersteun ons ...


Jouw ondersteuning wordt zeer gewaardeerd, en hoeft zelfs niets te kosten. Bijvoorbeeld door links naar ons te delen op social media, of andere websites.

Andere vormen kunnen ook gratis zijn (b.v. shoppen op Amazon).
Alle opbrengsten worden gebruikt voor web-hosting kosten, project hardware en software, koffie, etc.

Hartelijk dank voor wie al heeft bijgedragen!
Het is altijd geweldig om te zien hoe men mijn artikeltjes en applicaties weet te waarderen.

Merk op dat het klikken op affiliate links een kleine commissie voor ons kunnen genereren - dit wordt zeer gewaardeerd.

Reacties


Er zijn 2 reacties welke je hieronder kunt lezen.
Je kunt jouw eigen opmerkingen plaatsen m.b.v. dit formulier, of een reactie op een bestaande opmerking plaatsen door op de "Beantwoorden" knop te klikken.

  • 30 okt 2014 - 20:22 - jack Reactie Link

    Dag Hans,

    Mooi artikel over replicatie , maar ik heb de volgende vraag : Kan je een replicatie doen ( bijna synchroon) met mysql database server op verschillende locaties . Dus ééntje bij een hosting bedrijf en ééntje thuis als backupserver qnapnas ? Met behulp workbench of zijn er ander pakketten om dit te doen ?  update enkel van veranderde records in een tabel ,dat je niet heel het dbf moet copieren.Is dit mogelijk ?

    Tweede vraag Ik heb redelijke mysqldatabase  thuis locaal op een qnapnas 269pro 3gb 4Tb. Hetzelfde pakket draait supersnel op een hostingbedrijf  maar locaal draait deze mysql geen meter vooruit ( technisch  zijn beide gelijk rams,cpu  van locaal en host). Aanmaken van een index locaal duurt minuten (bij host 5 sec) terwijl de cpu slechts op 20% wordt gebruikt . Hoe krijg ik die performance hoger meer cpu kracht  ? Cpu blijft constant laag  ..Is daar een instelling in de nas die de cpu limiteert ?  Eventueel php.ini of my.cnf en welke parameters hebben  invloed op de performance?

    Grjtes jack

    Beantwoorden

    jack

    • 30 okt 2014 - 21:22 - hans - Auteur: Reactie Link

      Hoi Jack,

      Eerste antwoord:
      Ja dat is zeker mogelijk. Je zult dan wel in de gaten moeten houden, dat als b.v. de slave this staat, dat je thuis dan wel een vast IP adres moet hebben, en dat in de router poort 3306 gemapped moet staan naar jouw QNAP.
      Ik heb dit met succes eerder gedaan.

      Tweede antwoord:
      Ik zou even de MySQL config files vergelijken. Kan zijn dat je geheugen instellingen op de QNAP niet optimaal zijn.
      Ik moet ook wel zeggen dat de QNAP niet de snelste machine is, en misschien is de database thuis wat meer vervuild door andere data.
      De verschillende database engines (InnoDB b.v.) hebben ook een grote invloed. InnoDB gebruikt bv bestanden waar alles bij elkaar staat. Disk snelheid speelt dan dus een erg belangrijke rol.

      Hopelijk helpt je dit een beetje op weg 

      Beantwoorden

      hans



Jouw Opmerking ...

Plaats hier geen grote bestanden (zoals source codes, log files of config files). Gebruik hiervoor het Forum.

Delen:
*
*
Laat me per email weten als er nieuwe reacties zijn.
       Je kunt jouw RSS reader gebruiken om reacties te volgen.


Tweaking4All gebruikt de gratis Gravatar dienst voor Avatar weergave.