Pagina 1 van 1

MySQL Export en Import Data: Maak een Database backup

MySQL Export en Import Data: Maak een Database backup
   0

MySQL is een van de meest gebruikte database engines, en dat is niet alleen omdat het gratis is, maar ook omdat het snel en betrouwbaar is. Dat wil niet zeggen dat je nooit een backup moet maken.

Er zijn verschillende redenen waarom je een backup (export) wilt maken. Bijvoorbeeld voor als de server crashed, een snapshot voor je veranderingen maakt of omdat je de database wilt verhuizen naar een andere server.

Het maken van een backup is niet altijd even “duidelijk” – daarom in dit artikel een beschrijving van 3 methoden voor MySQL Export en Import van data voor backup en restore doeleinden.




MySQL Export en Import Data

Een typische Export of Import van data, van èèn of meerdere databases, ziet eruit als een enorme hoop SQL statements (tekst) die de database(s) weer van nul opbouwen.

De volgende criteria kunnen gebruikt worden voor een keuze van de gewenste methode:

  • De hoeveelheid data die je wilt Exporteren/Importeren
  • De eindbestemming of oorsprong van de data (lokaal vs. elders in de wereld)
  • Snelheid (hoe snel de export/import is)
  • Beschikbare middelen (ie. phpMyAdmin, shell toegang, TCP/IP verbinding, etc.)

 

De drie methoden die we gaan bekijken:

  • Command-line (mysqldump)
  • MySQL WorkBench
  • phpMyAdmin

 

En de winnaar is …
 

Mijn persoonlijke voorkeur: MySQL WorkBench.

Het is niet alleen eenvoudig in gebruik, maar ook nog eens gratis, snel en robuust zelfs als je met enorme grote backups gaat werken.

 

Ik heb een export/import getest (4 databases) en de tijden vergeleken met verassende resultaten:

Snelheidsvergelijking
 Applicatie  Export  Import
 Command line (mysqldump)  52 sec.  420 sec.
 MySQL WorkBench  57 sec.  418 sec.
 phpMyAdmin*  >300 sec.  >300 sec.

Merk op:

  • MySQL WorkBench gebruikt intern MySQLDump over TCP/IP.
  • De snelheid van MySQL WorkBench en phpMyAdmin hangt ook af van jouw netwerk snelheid (test: 1Gb lokaal netwerk).
  • phpMyAdmin faalde bij zowel de export als de import door een PHP time-out, zelfs met de “partial” optie aangezet.
  • De export van alle drie methoden resulteren in een enkel SQL bestand.
  • Het is interessant om te zien dat Command-line en MySQL Workbench vergelijkbaar presteren.

 

Uiteraard was dit niet de meest perfect benchmark, maar het geeft een idee van de snelheden.

 


 

MySQL Export en Import Data van de Command-line

De meer ervaren MySQL beheerders zullen snel naar de command-line of Shell methode grijpen. Dit met name uit gewoonte maar ook omdat het meer opties biedt. Naast meer opties lijkt de command-line methode sneller en meer geschikt voor erg grote hoeveelheden data. Wat snelheid en data omvang bleek al snel dat MySQL WorkBench het zeker net zo goed kan …

Wanneer gebruik je de Command-line voor Export/Import …

  • Ideaal voor erg grote database(s)
  • Als je geavanceerde opties nodig hebt
  • Geen Web of TCP/IP toegang tot MySQL
  • Snelle data export of import
  • Shell of SSH toegang beschikbaar

 Export Data vanaf de Command-line

Voor het exporteren van data, gebruiken we het “mysqldump” commando, welke je eerst zult moeten vinden. Ik gebruik (onder Linux/QNAP) het volgende:


1
2
cd /
find -name mysqldump

Op een QNAP, resulteert dit in:  ./mnt/ext/opt/mysql/bin/mysqldump

Gebruik dan  cd /mnt/ext/opt/mysql/bin/ om naar deze directory te gaan.

Gebruik voor het exporteren van een enkele database:


./mysqldump -u [username] -p [database_name] > [dumpfilename.sql]

“-u” wordt gebruikt om de gebruikersnaam op te geven, “-p” zorgt ervoor dat jouw wachtwoord gevraagd wordt en “> [dumpfilename.sql]” stuurt de output naar een bestand (dumpfilename.sql).

Het volgende voorbeeld logt in als root, vraagt om het wachtwoord en dumpt de “test” database in het “test.sql” bestand:


1
./mysqldump -u root -p test > test.sql

Voor het dumpen van meerdere databases moet je de optie “–databases” toevoegen. In onderstaand voorbeeld dumpen we de databases “test”, “information_schema” en “mysql” in het bestand “dump.sql”:


./mysqldump -u root -p --databases test information_schema mysql > dump.sql

Om alle databases te dumpen, gebruik:


./mysqldump -u root -p --all-databases > alldatabases.sql

TRIGGER DEFINER problemen …? 

Ik weet zeker dat er nog veel meer mogelijke problemen zijn waar je tegen aan kunt lopen, maar dit is een voorbeeld waar ik tegen aanliep toen ik een dump wilde maken van mijn XBMC databases, waarbij de TRIGGERs gedefinieerd waren voor de verkeerde gebruiker.

Om dit probleem op te lossen moet je eerst de DEFINER van de betreffende trigger(s) naar b.v. “root@localhost” zetten.
Dit gaat het eenvoudigste in phpMyAdmin, waar je een trigger kunt bewereken en dus de DEFINER kunt aanpassen zonder veel moeite. Mocht je het handmatig willen doen dan moet je de trigger eerst verwijderen en dan weer opzetten.

LOCK TABLES Error bij InnoDB …? 

Het blijkt veel voor te komen, als jouw database de InnoDB engine gebruikt, dat je de volgende foutmelding krijgt:
 Got error: 1449: The user specified as a definer ('...') does not exist when using LOCK TABLES .

Om dit probleem op te lossen moet je de volgende opties aan het mysqldump commando toe voegen:  --single-transaction --quick .

 

 Import Data vanaf de Command-line

Een nadeel van de command-line optie is natuurlijk dat je een dump van de ene server naar de andere server moet kopiëren. Je kunt dit natuurlijk via een netwerk share doen, of handig gebruik maken van een programma zoals WinSCP, ForkLift of Filezilla mits je SSH toegang hebt natuurlijk.

Importeren vanaf de command line is relatief eenvoudig en werkt als volgt:


./mysql -u [username] -p [database_name] < [dumpfilename.sql]

Dit gebruikt het “mysql” commando, waarbij “-u” de gebruikersnaam opgeeft, “-p” ervoor zorgt dat jouw wachtwoord gevraagd wordt, en optioneel kun je de database_name opgeven. De “< dumpfilename.sql” zorgt ervoor dat het bestand “dumpfilename.sql” als input gebruikt wordt.

In onze export voorbeelden hebben we SQL bestanden aangemaakt die meerdere databases bevatten en een statement voor het aanmaken van iedere database bevat. We hebben dan voor deze exports de “database_name” parameter niet nodig.

Zoals je zag bij het “mysqldump” voorbeeld, zullen we eerst moeten vinden waar het “mysql” commando staat op onze machine. Dit staat overigens in dezelfde directory als het “mysqldump” commando, maar je kunt het ook zoeken:


1
2
cd /
find -name mysql

Op een QNAP zien we zoiets als dit:


1
2
3
4
5
./mnt/ext/opt/mysql
./mnt/ext/opt/mysql/share/mysql
./mnt/ext/opt/mysql/include/mysql
./mnt/ext/opt/mysql/bin/mysql
./mnt/ext/opt/mysql/lib/mysql

Regel 4 (highlighted) is de regel die we nodig hebben, wat we kunnen zien aan het “/bin/” deel.

Andere Linux installaties zullen een verglijkbaar pad geven, maar zullen kunnen afwijken.

Een voorbeeld van een import die ik deed (voor de test):


./mysql -u root -p < datadump.sql

 

 


 

MySQL Export en Import Data met phpMyAdmin

phpMyAdmin, een gratis, web-gebaseerde MySQL admin tool, welke erg veel gebruikt wordt voor met name web-servers.

Een van de voordelen van phpMyAdmin is dat je eenvoudig via de web-interface beheer taken kunt uitvoeren. En daarbij maakt het niet uit of je naast de server zit, of dat de server aan de andere kant van de wereld staat.

phpMyAdmin maakt bepaalde taken ook stukken eenvoudiger en overzichtelijker.

Wanneer gebruik je phpMyAdmin voor Export/Import …

  • Erg handig voor kleine en snelle exports/imports
  • Geen behoefte aan geavanceerde opties
  • Toegang op afstand via de web-interface
  • Gebruikersvriendelijke web-interface
  • Geen TCP/IP verbinding naar MySQL beschikbaar
  • Geen Shell of SSH toegang

 Export Data met phpMyAdmin

Exporten van groete hoeveelheden data is iets waar phpMyAdmin niet echt geschikt voor is. We kunnen phpMyAdmin niet echt de schuld geven omdat het vaak een time-out limitatie van PHP is en niet van phpMyAdmin.

Exporteren met phpMyAdmin is relatief eenvoudig.

Voor een enkele database, klik de gewenste database aan de linkse kant van het scherm en klik de “Export” tab als de pagina herladen is. Kies hier “Custom” als je aanvullende opties nodig hebt (zoals b.v. CREATE DATABASE), of “Quick” als je een eenvoudige dump nodig hebt. Klik vervolgens “Go” om het proces te starten.

Voor meerdere databases, klik je op de “Home” icoon van phpMyAdmin. Na het herladen van de pagina, klik de “Export” tab.
Standaard worden ALLE databases geëxporteerd – klik op “Custom” als je maar een paar databases wilt exporteren.
Je kunt overwegen om de optie “Add CREATE DATABASE / USE statement” aan te vinken.
Klik de “Go” knop om het proces te starten …

phpMyAdmin - Export meerder databases

phpMyAdmin – Export meerder databases

 Import Data met phpMyAdmin

Importeren et phpMyAdmin is net zo eenvoudig als exporteren – en ook hier kun je tegen time-out problemen aanlopen.

Klik de “Home” icoon en na het herladen van de pagina, klik de “Import” tab.
Vervolgens kilk je op de “Choose File” knop om het eerder geëxporteerde bestand te selecteren. De knop kan er per browsers ander uitzien (voorbeeld is van Google Chrome)

Maak er zeker van dat “Format” op “SQL” staat.

Afhankelijk van de phpMyAdmin versie die je gebruikt, kun je de “Partial import” aanvinken. Deze optie probeert de PHP time-out problemen te omzeilen maar ik moet eerlijk zeggen dat ik er geen voordeel van heb gezien.

Klik vervolgens de “Go” knop om de import te starten.

Importeren via phpMyAdmin kan overigens ERG traag zijn … heb dus geduld en sluit het venster niet voordat phpMyAdmin verteld dat de import klaar is.

phpMyAdmin - Importeren van 1 of meer databases

phpMyAdmin – Importeren van 1 of meer databases

 


 

MySQL Export en Import Data met MySQL WorkBench

MySQL WorkBench is een gratis MySQL admin programma dat beschikbaar is voor verschillende Linux versies, MacOS X en Windows. Je kunt het voor jouw computer op de MySQL WorkBench Download Pagina vinden.

Wanneer gebruik je MySQL WorkBench voor Export/Import …

  • Ideaal voor erg grote hoeveelheden data
  • Geen behoefte voor erg complexe opties
  • Gebruiksvriendelijke gebruikersinterface
  • TCP/IP verbinding naar MySQL is beschikbaar
  • Geen Shell of SSH toegang

Merk op dat er naast MySQL WorkBench ook verschillende andere, vergelijkbare, applicaties bestaan die zeker zo goed werken, en soms zelfs beter. Vaak bieden ze een visuele query designer, wat bouwen van queries vlotter en eenvoudiger maakt, maar vaak zijn ze niet gratis. Een van mijn favorieten is b.v. ook alleen maar voor het Windows platform beschikbaar: EMS SQL Manager voor MySQL.

Na installatie van MySQL WorkBench, zul je een TCP/IP verbinding naar de MySQL database moeten opzetten. Bedenk daarbij dat de “root” gebruiker vaak geen TCP/IP toegang heeft.

Je moet dan dus of een geschikte gebruiker aanmaken, of de rechten van de “root” gebruiker aanpassen. Als je dat laatste kiest, bedenk dan wel de veiligheidsimplicaties … Je zou (wat ik zelf doe) de toegangsrechten lokaal kunnen houden indien de database in jouw eigen netwerkje staat door b.v. “192.168.1.%” te gebruiken (als jouw netwerk van het 192.168.1.xxx formaat is).

Eenmaal opgezet, open de verbinding.

 Export Data met MySQL WorkBench

Links in het venster zie je de “Data Export” optie onder het kopje “MANAGEMENT“. Klik het aan.
Nu zul je zien dat rechts in beeld een lijst met beschikbare databases zichtbaar wordt, waar je kunt aanvinken welke databases je wlt exporteren.

Na het maken van een selectie, kies de optie “Export to Self-Contained File“, kies een naam voor het export bestand en klik de “Start Export” knop. Dit maakt een enkel bestand met alle SQL statements om de database(s), tabel(len) en records weer op te bouwen.

Merk op : MySQL WorkBench heeft ook de optie om ieder onderdeel als individueel bestand te exporten. Dit kan handig zijn voor een latere phpMyAdmin import, maar het wil wel zeggen dat je iedere database en tabel stuk-voor-stuk moet importenen, wat dus erg bewerkelijk kan zijn.

MySQL WorkBench - Export one or more databases

MySQL WorkBench – Export one or more databases

LOCK TABLES Error voor InnoDB …? 

Voor InnoDB databases kun je tegen de volgende foutmelding aanlopen:
 Got error: 1449: The user specified as a definer ('...') does not exist when using LOCK TABLES .

Om dit probleem op te lossen, klik de “Advanced Options…” knop en verwijder het vinkje voor de “local-tables” optie onder het “Other” kopje.

 

Import Data met MySQL WorkBench

Eenmaal verbonden met MySQL, klik de “Data Import/Restore” optie links. Klik vervolgens de “Import from Self-Contained File” en kies het eerder geëxporteerde bestand. Klik vervolgens op “Start Import“.

Hoewel import met MySQL Workbench vlot gaat (zeker in vergelijk met phpMyAdmin), zul je even geduld moeten hebben.

MySQL WorkBench - Importeren van 1 of meer databases

MySQL WorkBench – Importeren van 1 of meer databases

Donatie Opties


Donaties worden zeer gewaardeerd, maar zijn zeker niet vereist. Donaties worden gebruikt voor het dekken van kosten voor web-hosting en project materialen, en eventueel voor een drankje of een snack. Voor wie al gedoneerd heeft: Hartelijk dank! Het is werkelijk geweldig om te zien dat men onze artikelen en applicaties waardeert.

Reacties


Er zijn nog geen reacties geplaatst.
Je kunt jouw eigen opmerkingen plaatsen m.b.v. dit formulier, of een reactie op een opmerking plaatsen door op de "Reageer" knop te klikken.



Jouw Reactie ...

Vriendelijk verzoek om hier geen lange teksten te plaatsen (zoals source codes, log files of config files). Gebruik daarvoor het Forum.

Deel met anderen:
*
*
Houd me op de hoogte van nieuwe reacties (email).
       Gebruik jouw RSS reader om reacties te volgen.


Tweaking4All uses the free Gravatar service for Avatar display.
Tweaking4All zal nooit jouw email adres met anderen delen.