Hoe een back-up te maken van MySQL-databases en deze te herstellen met MySqldump

In deze zelfstudie wordt uitgelegd hoe u een back-up kunt maken van MySQL- of MariaDB-databases vanaf de opdrachtregel met behulp van het hulpprogramma mysqldump.

De back-upbestanden die zijn gemaakt door het hulpprogramma mysqldump zijn in feite een set SQL-instructies die kunnen worden gebruikt om de oorspronkelijke database opnieuw te maken. De opdracht mysqldump kan ook bestanden genereren in CSV- en XML-indeling.

U kunt ook het hulpprogramma mysqldump gebruiken om uw MySQL-database over te dragen naar een andere MySQL-server.

Mysqldump-opdrachtsyntaxis

Voordat we ingaan op het gebruik van de opdracht mysqldump, gaan we eerst kijken naar de basissyntaxis.

De uitdrukkingen van het hulpprogramma mysqldump hebben de volgende vorm:

mysqldump > file.sql

• options - De mysqldump options file.sql - Het dumpbestand (back-up)

Om de opdracht mysqldump te gebruiken, moet de MySQL-server toegankelijk en actief zijn.

Maak een back-up van een enkele MySQL-database

Het meest voorkomende gebruik van de mysqldump-tool is het maken van een back-up van een enkele database.

Als u bijvoorbeeld een back-up van de database met de naam database_name met behulp van de root van de gebruiker en deze wilt opslaan in een bestand met de naam database_name.sql , voert u de volgende opdracht uit:

mysqldump -u root -p database_name > database_name.sql

U wordt gevraagd het rootwachtwoord in te voeren. Na succesvolle authenticatie start het dumpproces. Afhankelijk van de grootte van de database kan het proces enige tijd duren.

mysqldump database_name > database_name.sql

Backup meerdere MySQL-databases

Om met één opdracht een back-up van meerdere MySQL-databases te maken, moet u de optie --database gebruiken, gevolgd door de lijst met databases waarvan u een back-up wilt maken. Elke databasenaam moet worden gescheiden door een spatie.

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

De bovenstaande opdracht maakt een dumpbestand met beide databases.

Maak een back-up van alle MySQL-databases

Gebruik de optie --all-databases om een ​​back-up te maken van alle MySQL-databases:

mysqldump -u root -p --all-databases > all_databases.sql

Hetzelfde als bij het vorige voorbeeld maakt de bovenstaande opdracht een dumpbestand met alle databases.

Maak een back-up van alle MySQL-databases om bestanden te scheiden

Het hulpprogramma mysqldump biedt geen optie om van alle databases een back-up te maken naar afzonderlijke bestanden, maar dat bereiken we eenvoudig met een eenvoudige bash FOR lus:

for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB > "$DB.sql"; done

De bovenstaande opdracht maakt een afzonderlijk dumpbestand voor elke database met de databasenaam als bestandsnaam.

Maak een gecomprimeerde MySQL-databaseback-up

Als de database erg groot is, is het een goed idee om de uitvoer te comprimeren. Om dat te doen, stuurt u de uitvoer naar het hulpprogramma gzip en stuurt u deze door naar een bestand zoals hieronder wordt weergegeven:

mysqldump database_name | gzip > database_name.sql.gz

Maak een back-up met tijdstempel

mysqldump database_name > database_name-$(date +%Y%m%d).sql

De bovenstaande opdracht maakt een bestand met het volgende formaat database_name-20180617.sql

Een MySQL-dump herstellen

U kunt een MySQL-dump herstellen met de mysql tool. De algemene syntaxis van het commando is als volgt:

mysqld database_name < file.sql

In de meeste gevallen moet u een database maken om in te importeren. Als de database al bestaat, moet u deze eerst verwijderen.

In het volgende voorbeeld maakt de eerste opdracht een database met de naam database_name en vervolgens importeert deze de dump database_name.sql :

mysql -u root -p -e "create database database_name"; mysql -u root -p database_name < database_name.sql

Herstel een enkele MySQL-database vanaf een volledige MySQL-dump

mysql --one-database database_name < all_databases.sql

Exporteer en importeer een MySQL-database in één opdracht

In plaats van een dumpbestand van de ene database te maken en de back-up vervolgens in een andere MySQL-database te importeren, kunt u de volgende one-liner gebruiken:

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

De bovenstaande opdracht stuurt de uitvoer naar een mysql-client op de externe host en importeert deze in een database met de naam remote_database_name . Controleer voordat u de opdracht uitvoert of de database al op de externe server bestaat.

Automatiseer back-ups met Cron

Het automatiseren van het back-upproces van de databases is net zo eenvoudig als het maken van een cron-taak waarmee de opdracht mysqldump op een bepaald tijdstip wordt uitgevoerd.

Volg onderstaande stappen om geautomatiseerde back-ups van een MySQL-database in te stellen met behulp van cronjob:

1. Maak een bestand met de naam .my.cnf in uw .my.cnf voor gebruikers:

   sudo nano ~/.my.cnf

   Kopieer en plak de volgende tekst in het.my.cnf-bestand.

   user = dbuser password = dbpasswd

   Vergeet niet om dbuser en dbpasswd te vervangen door de database-gebruiker en het wachtwoord van de gebruiker.

   Beperk machtigingen van het legitimatiegegevensbestand zodat alleen uw gebruiker hier toegang toe heeft:

   chmod 600 ~/.my.cnf

   Maak een map om de back-ups op te slaan:

   mkdir ~/db_backups

   Open uw crontab-bestand van de gebruiker:

   crontab -e

   Voeg de volgende cron-taak toe die elke dag om mydb uur een back-up maakt van een databasenaam mydb :

   0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql

   Vergeet niet uw gebruikersnaam te vervangen door uw daadwerkelijke gebruikersnaam.

U kunt ook een andere cronjob maken om back-ups ouder dan 30 dagen te verwijderen:

find /path/to/backups -type f -name "*.sql" -mtime +30 -delete

Natuurlijk moet u de opdracht aanpassen aan uw back-uplocatie en bestandsnamen. Voor meer informatie over de opdracht Zoeken raadpleegt u Bestanden zoeken in Linux met behulp van de opdrachtregelgids.

Conclusie

Deze tutorial behandelt alleen de basis, maar het zou een goed begin moeten zijn voor iedereen die wil leren hoe MySQL-databases te maken en te herstellen vanaf de opdrachtregel met behulp van het hulpprogramma mysqldump.

U kunt ook de tutorial raadplegen over het resetten van een MySQL-rootwachtwoord voor het geval u het bent vergeten.

mysql mariadb backup mysqldump