Databasebeheerders en systeembeheerders moeten back-ups maken van hun MySQL-database, en het mysqldump-hulpprogramma is het meest gebruikte hulpmiddel om een volledige database of een enkel object om te zetten in een tekstbestand. Nadat het mysqldump-hulpprogramma een back-upbestand heeft gemaakt, kan het worden gebruikt om data naar een server te herstellen of data naar een andere database te migreren.
Wat is mysqldump?
De mysqldump-tool is een commando-line hulpprogramma dat bij MySQL wordt geleverd om SQL-statements te exporteren die een database of een subset van objecten in een database kunnen herbouwen. Het kan worden gebruikt om een back-up van databaseobjecten en -gegevens te maken, of het kan worden gebruikt om een database van de ene server naar de andere over te dragen. Beheerders kunnen mysqldump gebruiken om data te exporteren naar een specifieke indeling zoals XML en CSV.
Hoe u mysqldump kunt gebruiken om een back-up te maken van één tabel
Het mysqldump-hulpprogramma kan worden gebruikt om alleen specifieke tabellen binnen een database te exporteren. Stel bijvoorbeeld dat u slechts één klanttabel uit een e-commercedatabase wilt exporteren. De klanttafelgegevens kunnen vervolgens worden gebruikt om marketingmails te verzenden. Het mysqldump-hulpprogramma exporteert de klantentabel en de bijbehorende data naar een tekstbestand.
Opmerking: Om een opdracht in mysqldump succesvol uit te voeren, moet de gebruikersnaam die wordt gebruikt om toegang te krijgen tot de database de juiste machtigingen hebben om toegang te krijgen tot objecten. Zonder toegang exporteert de opdracht geen schema, object of data.
U moet de databasenaam en de tabelnaam opgeven om objecten met succes naar een tekstbestand te exporteren. Als u wilt verifiëren dat u de juiste tabelnaam hebt, zorg er dan eerst voor dat u bent geverifieerd in de database waarmee u wilt werken. Voer vervolgens de volgende SQL-opdracht uit in de MySQL-opdrachtregel of de Workbench-toepassing:
SHOW TABLES;
Nadat u de juiste tabelnaam hebt gevonden, kunt u de tabel exporteren naar een tekstbestand. De volgende opdracht exporteert de Klantentabel van de database myDB naar de file customers.sql:
mysqldump -u username -p password myDB Customer > customers.sql
De termen "gebruikersnaam" en "wachtwoord" moeten worden vervangen door uw eigen gebruikersnaam en wachtwoord. U kunt meerdere tabellen exporteren door elke tabel te scheiden van een spatieteken. Het volgende exporteert de klant- en ordertabellen naar customers_orders.sql:
mysqldump -u username -p password myDB Customer Order > customers_orders.sql
Nadat u de SQL-statements naar een bestand hebt geëxporteerd, kunt u het SQL-bestand openen met een standaard teksteditor of openen met MySQL Workbench. MySQL Workbench is een afzonderlijke installatie van de MySQL-database, maar u kunt deze gratis downloaden van de MySQL-website. Het is het beste om het te openen met Workbench, want dan kunt u de overzichten uitvoeren en data importeren naar de MySQL-databaseserver.
Voorbeelden mysqldump
Het mysqldump-hulpprogramma wordt voornamelijk gebruikt voor back-ups of het overbrengen van data naar een andere databaseserver. Of u nu een back-up van uw database maakt of data naar een andere server wilt overdragen, het mysqldump-hulpprogramma werkt hetzelfde. Een veelgebruikt gebruik voor een mysqldump-back-up is het creëren van een testomgeving vanuit de productiedatabase.
Om een productiedatabase te gebruiken om vervolgens een testomgeving te creëren, kunt u een volledige back-up van uw database maken met behulp van het mysqldump-hulpprogramma. De volgende opdracht maakt een back-up van uw gehele database genaamd myDB en exporteert deze naar een bestand genaamd myDB.sql:
mysqldump -u username -p password -databases myDB > myDB.sql
Log nu in op uw testserver. Er moet ook MySQL zijn geïnstalleerd, zodat u de geëxporteerde data kunt importeren. Merk op dat de exportopdracht het teken '>' gebruikt om data van een database naar een SQL-bestand te verplaatsen. Het importproces gebruikt het teken '<'. De volgende opdracht importeert alle data in het myDB.sql-bestand naar een testomgeving met dezelfde database genaamd myDB:
mysqldump -u username -p password -databases myDB < myDB.sql
Met de bovenstaande twee commando's repliceert u data van de oorspronkelijke myDB-database in productie naar uw testomgeving. De testomgeving geeft ontwikkelaars en mensen met kwaliteitsborging de mogelijkheid om te werken met echte data en een databasestructuur die eventuele problemen van klanten of werknemers nabootst voor het oplossen van problemen.
Basisgebruik van mysqldump
Het .sql-bestand dat tijdens een mysqldump wordt gemaakt, is een eenvoudig tekstbestand. U kunt het openen in elke teksteditor, maar het is het beste om het te openen met MySQL Workbench. Workbench-kleurcodescommando's en -datatypes, waardoor de SQL-commando's gemakkelijker te lezen zijn.
U kunt het .sql-bestand in Workbench openen via het menu Bestand. Klik op "Open SQL Script" in het dialoogvenster en kies het .sql-bestand dat u wilt lezen. Als het .sql-bestand is geopend, kunt u het lezen en uitvoeren met het menu-item "SQL Script uitvoeren" als u ervoor kiest om de opdrachten uit te voeren. Workbench draait op Windows en Linux, zodat u op deze manier een script kunt uitvoeren op beide besturingssystemen.
U kunt ook een mysqldump-bestand uitvoeren in Windows en Linux met behulp van het mysqldump-hulpprogramma. De volgende opdracht voert de SQL-statements uit op een MySQL-database in zowel Windows als Linux vanuit het mysqldump-hulpprogramma:
mysqldump -u username -p password -databases myDB < myDB.sql
Het bovenstaande commando importeert data uit het myDB.sql mysqldump-bestand en voert de SQL-statements uit op de lokale databaseserver. De opdracht importeert data naar de myDB-database.
Als er geen data in de database of een van de tabellen van de database aanwezig is, worden de databasestructuren nog steeds aangemaakt en wordt de database aan het schema toegevoegd. De tabelstructuren zijn beschikbaar voor u om er in de toekomst data aan toe te voegen.
Geavanceerd gebruik van mysqldump
Een grote database kan tabellen met terabytes aan data bevatten. Te veel exporten kunnen opslagbronnen uitputten, maar u kunt elk bestand comprimeren om de opslagruimte te verminderen die nodig is voor MySQL-data-exports. Het comprimeren van een bestand kan meerdere gigabytes opslagruimte opslaan die nodig is voor zeer grote bestanden. De volgende mysqldump-opdracht exporteert de myDB-database en comprimeert deze met behulp van het gzip-hulpprogramma naar een bestand met de naam myDB.sql.gz:
mysqldump -u username -p password -databases myDB | gzip myDB.sql.gz
Back-ups bevatten vaak gevoelige data, dus het versleutelen is noodzakelijk voor compliance en dataprotectie. U hebt een tool van derden nodig om data te versleutelen. In dit voorbeeld gebruiken we encryptie om het myDB.sql-bestand te versleutelen. U hebt een sleutel nodig (vergelijkbaar met een wachtwoordzin) om een bestand te versleutelen, maar u moet deze sleutel onthouden om het te decoderen. Het encryptiehulpprogramma zal u vragen om een sleutel in te voeren wanneer u het bestand versleutelt, of u kunt een sleutel opslaan in een beveiligd bestand waar het opnieuw kan worden gebruikt om een .sql-bestand te versleutelen en te decoderen.
In het volgende voorbeeld wordt het geëxporteerde .sql-bestand versleuteld met behulp van een opgeslagen sleutel in het bestand met de naam mykey.key:
mysqldump -u username -p password -databases myDB | ccrypt -k mykey.key > myDB.sql.cpt
Merk op dat de data wordt geëxporteerd naar een .cpt-bestand. In dit .cpt-bestand worden alle versleutelde data opgeslagen. U hebt dezelfde sleutel nodig om het bestand te decoderen. U kunt het decoderen met de volgende komma:
cdecrypt -k mykey.key myDB.sql.cpt
De meeste databases bevatten meer dan tabellen en data. Activeringen en procedures moeten ook worden geback-upt. Standaard wordt de export van het mysqldump-hulpprogramma geactiveerd met een standaardexport, maar beheerders kunnen de opdracht expliciet vertellen om ze te exporteren. Procedures worden niet standaard geëxporteerd, dus beheerders moeten ze expliciet exporteren. De volgende opdracht exporteert triggers en procedures samen met andere databaseobjecten en data:
mysqldump -u gebruikersnaam -p wachtwoord -databases myDB –triggers=true –routines=true > myDB.sql
Best practices voor mysqldump
Telkens wanneer u het mysqldump-hulpprogramma moet gebruiken, kunt u het vrij gebruiken zonder de productiviteit te onderbreken. Het is vooral nuttig wanneer u regelmatig back-ups of exporten van uw database en de data ervan moet maken. Back-ups moeten vaak worden gemaakt, zodat data kunnen worden opgehaald in geval van een databasestoring, malware of datacorruptie.
De frequentie van back-ups hangt af van de hoeveelheid data die u elke dag aan een database toevoegt en de frequentie van de wijzigingen die in de database worden aangebracht. Over het algemeen hangt de frequentie waarmee u de mysqldump-opdracht gebruikt af van de hoeveelheid data die verloren kan gaan zonder een negatieve impact te hebben op de omzet en bedrijfscontinuïteit. U hebt niet altijd een volledige back-up nodig, maar sommige bedrijven gebruiken het mysqldump-hulpprogramma eenmaal per week en anderen gebruiken het eenmaal per dag. Bepaal de frequentie van volledige back-ups die nodig zijn voor uw bedrijf en gebruik automatiseringstools om mysqldump met een vastgesteld interval uit te voeren.
Conclusie
Het beheren van een database is een fulltime taak, maar het mysqldump-hulpprogramma maakt het beheer veel efficiënter. U kunt deze gebruiken voor volledige back-ups van uw data of gebruiken om data tussen servers over te dragen. Het is compatibel met Windows of Linux, dus u kunt het gebruiken in elke omgeving die uw MySQL-server host.
