Mysql-toegang geweigerd - Hoe kan ik het dumpbestand van mysqldump herstellen? Back-up en herstel van de MySQL-database (MariaDB) met mysqldump.

Het maken van een databasedump (back-up) is erg belangrijk. Daarom heb ik wat opmerkingen gemaakt met voorbeelden van zo'n uitstekend hulpprogramma als mysqldump.

mysqldump - een hulpprogramma waarmee u de inhoud van een database of een reeks databases kunt dumpen om een ​​back-upkopie te maken of gegevens naar een andere SQL-databaseserver te verzenden (niet noodzakelijkerwijs een MySQL-server). De dump bevat een reeks SQL-opdrachten om tabellen te maken en/of te vullen.

mysqldump-u root -p -f naam_database >

Met dit commando maken we een backup van de database onder de naam naam_database naar station C in het bestand mydb_backup_name_database.txt

U hoeft het bestand niet aan te maken; MySQL maakt het zelf.

mysql-u root -p -f naam_database< C:\mydb_backup_name_database.txt

Met dit commando importeren we back-upgegevens uit het bestand C:\mydb_backup_name_database.txt

Opmerking: -f, --force is een optie die specificeert om door te gaan, zelfs als er een SQL-fout wordt ontvangen, d.w.z. fouten negeren. Bijvoorbeeld als er al een identieke rij in de tabel bestaat.

Om te voorkomen dat u om een ​​wachtwoord wordt gevraagd, moet u dit onmiddellijk na -p schrijven, dat wil zeggen zonder spaties. Als het wachtwoord Pwd, dan zou het voorbeeld er als volgt uitzien:

mysqldump-u wortel -p Pwd-f naam_database > C:\mijndb_backup_naam_database.txt

Als u dit commando vaak gebruikt, is het beter om een ​​aparte gebruiker met de nodige rechten aan te maken om de zichtbaarheid van het root-wachtwoord te verminderen

Laten we eens kijken naar subtielere mysqldump-instellingen:

--databases staat toe dat mysqldump de opdrachten CREATE DATABASE /*!33333 IF NOT EXISTS*/ DBNAME en USE DBNAME opneemt in het herstelscript. Hiermee kunt u vanaf het begin werkende databases maken. Dat wil zeggen, zonder --databases te gebruiken, wordt aangenomen dat de gebruiker een enkele database herstelt en expliciet specificeert waar de herstelde gegevens moeten worden geplaatst. Als er een back-up wordt gemaakt met als doel een volledig werkende kopie van de gegevens te maken, bijvoorbeeld op een andere MySQL-server, dan moet u deze sleutel gebruiken;

--alle databases Hiermee kunt u kopieën maken van alle databases die op een bepaalde MySQL-server bestaan. Als u slechts enkele databases wilt kopiëren, hoeft u deze alleen maar op te geven, gescheiden door een spatie, wanneer u mysqldump vanaf de opdrachtregel aanroept (zie hierboven);

Sleutel --hulp. Het mysqldump-programma heeft vele versies. Met deze sleutel kunt u zien welke functies specifiek door uw versie worden ondersteund;

--add-drop-tabel- een schakelaar die mysqldump dwingt een drop table-opdracht aan het resulterende script toe te voegen voordat tabellen worden gemaakt. Hiermee kunt u enkele fouten voorkomen bij het herstellen van een database vanaf een back-up. Natuurlijk moet u er rekening mee houden dat de tabellen in de werkkopie (als er tabellen met dezelfde naam in de back-up voorkomen) uit de hoofddatabase worden verwijderd en opnieuw worden aangemaakt vanuit de back-up voordat ze worden hersteld vanaf de back-up;

--geen gegevens. Met deze sleutel kunt u snel een kopie maken van de tabel-/databasestructuur zonder de gegevens zelf. U hebt bijvoorbeeld een complexe tabel gemaakt en u wilt de structuur ervan voor de toekomst bewaren, maar u hebt de gegevens zelf die in deze tabel staan ​​niet nodig in een reservekopie;

--resultaatbestand=...- deze schakelaar kan worden gebruikt om de uitvoer naar een bestand om te leiden. U kunt een normale Unix-omleiding gebruiken met het commando ">", of u kunt deze sleutel gebruiken. Wie vindt wat leuk?

Nog een zeer nuttige tip voor het gebruik van mysqldump in een hostingomgeving. Bij het gebruik van hosting worden in de regel enkele beperkingen aan de gebruiker opgelegd. U kunt bijvoorbeeld niet meer dan een bepaalde hoeveelheid fysiek geheugen (RAM, RAM) in beslag nemen. mysqldump plaatst standaard alle gegevens die van de MySQL-server worden ontvangen in het geheugen en schrijft deze vervolgens allemaal naar schijf. Dienovereenkomstig, als de provider u toestaat om bijvoorbeeld 30 MB geheugen in beslag te nemen, en de database, die u kopieert met mysqldump, 50 MB in beslag neemt, zal hier natuurlijk een fout optreden - mysqldump zal niet correct kunnen werken en zal crashen, waarover het u zal informeren. Om mysqldump te "forceren" om gegevens rechtstreeks naar schijf te schrijven, in plaats van deze, zelfs tijdelijk, in het geheugen op te slaan, gebruikt u de sleutel --snel. Dit zal het probleem oplossen.

Hier zijn nog een paar nuttige voorbeelden:

mysqldump -u root -p Pwd-f --standaard-tekenset=cp1251 DBNAAM| gzip -c > bestandsnaam.txt .gz

U kunt zo'n archief uitpakken met het commando:

gunzip bestandsnaam.txt.gz

Om te weten op welke datum de back-up is gemaakt, kunt u het volgende commando schrijven:

mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `datum "+%Y-%m-%d"`.gz

en als je dumps moet maken met verschillende coderingen, dan is het handig om variabelen te gebruiken:

set DBKARAKTER= utf8

set DBNAAM= inbreuk

mysqldump -u root -p Pwd-f --standaard-tekenset=$ DBKARAKTER $DBNAAM| bzip2 -c > sql. $DBNAAM.`datum "+%Y-%m-%d"`.bz2

Als je het verwijderen van oude archieven wilt automatiseren, probeer dan cron en de opdracht find te gebruiken, die meestal in Unix te vinden is. Periodiek uitgevoerd

vinden~/map-met-archieven -naam "*.gz" -mtime +7 -exec rm -f () \;

U verwijdert dus archieven die “ouder” zijn dan zeven dagen.

Meestal kunt u uw database eenvoudig beheren via webtools zoals PHPMyAdmin. Helaas zijn er momenten waarop het nodig is om een ​​grote hoeveelheid gegevens te herstellen. Dit kan niet worden bereikt via PHPMyAdmin vanwege buiten PHP (de maximale looptijd van het script staat dit niet toe of het is onmogelijk om een ​​groot archief uit de database te laden).

MySQL gebruiken vanaf de opdrachtregel

Als u MySQL op Linux of MS Windows gebruikt, kunt u de opdrachtregel gebruiken om de meeste taken uit te voeren. Als u MS Windows gebruikt, zorg er dan voor dat het pad naar de uitvoerbare bestanden op de opdrachtregel is opgenomen in het systeempad. Anders moet u het volledige pad naar elke opdracht invoeren.

Een back-up maken van een MySQL-database

MySQL biedt een hulpprogramma genaamd "mysqldump." Kortom, deze tool maakt een plat bestand met MySQL-instructies om de database te repareren. Hier zijn enkele voorbeelden van het gebruik van mysqldump:

Een eenvoudige databaseback-up maken

Hierdoor wordt een bestand gemaakt met alle MySQL-instructies voor het maken van tabellen en het herstellen van gegevens in een bestaande database. Als de doeldatabase tabellen met dezelfde naam bevat, worden deze overschreven. Als u wilt dat bestaande tabellen worden verwijderd en opnieuw worden gebruikt, gebruikt u de optie "add-drop-table":

# mysqldump --add-drop-table -u gebruikersnaam -p DB_naam > uw_bestand.sql

Je kunt dit ook als alternatief kiezen:

# mysqldump --add-drop-databases -u gebruikersnaam -p DB_naam > uw_bestand.sql

Maak een back-up van meerdere databases

U kunt een kopie maken van meerdere databases in één bestand met behulp van de databaseoptie:

# mysqldump -u gebruikersnaam -p --databases uw_database1 uw_database2 uw_database3 > uw_bestand.sql

Het maken van een back-up van alle databases kan worden bereikt met behulp van de optie voor alle databases:

# mysqldump -u gebruikersnaam -p --all-databases > jouw_bestand.sql

Een back-up maken van InnoDB-tabellen

Als de database InnoDB-kolommen heeft, moet u de referentiële integriteit uitschakelen wanneer u de gegevens herstelt. Helaas kan dit niet worden gedaan met het hulpprogramma mysqldump. Om dit te doen, moet je het bestand zelf openen en toevoegen:

SET FOREIGN_KEY_CHECKS = 0;
... En voeg het volgende toe aan het einde van het bestand:
SET FOREIGN_KEY_CHECKS = 1;

MySQL-dumpbestanden comprimeren

Als je mysqldump op een Linux-shell gebruikt, kun je via Gzip comprimeren om het dumpbestand te comprimeren (ervan uitgaande dat gzip is geïnstalleerd):

# mysqldump -u gebruikersnaam -p uw_databasenaam | gzip -c jouw_bestand.sql.gz

Herstellen vanaf een MySQL-back-up

Om een ​​back-up gemaakt met mysqldump te herstellen, moet u de MySQL-opdracht gebruiken. Als uw MySQL-dumpbestand "Create Database" o niet bevat, kunt u de volgende opdracht gebruiken:

# mysql -u gebruikersnaam -p uw_databasenaam< your_file.sql

Maar als dit lukt, gebruik dan dezelfde opdracht zonder de databasenaam op te geven:

# mysql -u gebruikersnaam -p< your_file.sql

Meer informatie over mysqldump is te vinden op de officiële MySQL-website.

./mysql -u -P H < db_dump-file

2018-12-04T00:00Z

2018-12-11T00:00Z

Wanneer we een dumpbestand maken met mysqldump, bevat het een groot SQL-script om de inhoud van de database opnieuw te creëren. Dus we herstellen het door de opdrachtregel van de MySQL-client uit te voeren:

Mysql -uroot -p

(waarbij root onze beheerdersgebruikersnaam voor MySQL is) en nadat we verbinding hebben gemaakt met de database, hebben we opdrachten nodig om de database te maken en het bestand te lezen:

Database new_db maken; gebruik nieuwe_db; \. dumpbestand.sql

De details variëren afhankelijk van welke opties zijn gebruikt bij het maken van het dumpbestand.

2018-12-18T00:00Z

Je hoeft alleen maar dit uit te voeren:

mysql-p-u< db_backup.dump

Als de dump meerdere databases bevat, moet u de databasenaam weglaten:

mysql-p-u< db_backup.dump

Om deze opdrachten uit te voeren, opent u een opdrachtprompt (op Windows) en cdt u naar de map waar mysql.exe het uitvoerbare bestand mysql.exe bevat (u moet er misschien even over nadenken, het zal afhangen van hoe u mysql hebt geïnstalleerd, d.w.z. zelfstandig of als onderdeel van een pakket zoals WAMP). Wanneer u zich in deze map bevindt, hoeft u alleen maar de opdracht in te voeren.

2018-12-25T00:00Z

Het zou zo eenvoudig moeten zijn als dit uitvoeren:

Mysql-u -P< db_backup.dump

Als de dump één enkele database heeft, moet u mogelijk een regel bovenaan het bestand toevoegen:

GEBRUIK ;

Als het een dump van veel databases is, zijn de gebruiksinstructies er al.

Om deze opdrachten uit te voeren, opent u een opdrachtprompt (op Windows) en cdt u naar de map waar mysql.exe het uitvoerbare bestand mysql.exe bevat (u moet er misschien even over nadenken, het zal afhangen van hoe u mysql hebt geïnstalleerd, d.w.z. zelfstandig of als onderdeel van een pakket zoals WAMP). Als u zich eenmaal in die map bevindt, kunt u eenvoudig de opdracht typen zoals ik hierboven deed.

01-01-2019T00:00Z

Eénregelige opdracht om gegenereerde SQL uit mysqldump te herstellen

Mysql-u -P E"bron

2019-01-08T00:00Z

Met behulp van een dumpbestand van 200 MB gemaakt op Linux om te herstellen op Windows met mysql 5.5, had ik meer succes met

Bronbestand.sql

mysql-opdrachtregelbenadering dan het gebruik

mysql< file.sql

opdrachtregelbenadering die in 2006 een 'server has left'-fout veroorzaakt (in Windows)

Vreemd genoeg verwijst de service die tijdens de (mysql)installatie is gemaakt naar een my.ini-bestand dat er niet was. Ik heb het "grote" voorbeeldbestand naar my.ini gekopieerd, dat ik al had aangepast met de aanbevolen vergroting.

Mijn waarden

Max_allowed_packet = 64M interactieve_timeout = 250 wait_timeout = 250

2019-01-15T00:00Z

Als een specifiek voorbeeld van het vorige antwoord:

Ik moest een back-up herstellen, zodat ik deze naar SQL Server kon importeren/migreren. Ik heb alleen MySql geïnstalleerd, maar heb het niet als een service geregistreerd en heb het ook niet aan mijn pad toegevoegd, omdat ik het niet hoef uit te voeren.

Ik gebruikte Windows Verkenner om mijn dumpbestand in C:\code\dump.sql te plaatsen. Open vervolgens MySql via het menu-item Start. De DB gemaakt en vervolgens de bronopdracht met het volledige pad als volgt uitgevoerd:

Mysql> maak database temp mysql> gebruik temp mysql> bron c:\code\dump.sql

Het dumpen en herstellen van een MySQL-database is vrij eenvoudig en handig om op afstand te doen via SSH of rechtstreeks via de serverconsole. Op afstand kan dit met behulp van Putty/Kitty-programma's. U kunt de onderstaande voorbeelden ook op Windows uitvoeren door de opdrachtregel ‘ cmd‘. Hieronder vindt u voorbeelden van hoe u MySQL-databasedumps kunt maken en deze indien nodig kunt herstellen, bijvoorbeeld voor uw website, online winkel of een ander project.

Een MySQL-databasedump maken

Om deze opdrachten uit te voeren, maakt u op afstand verbinding met uw server via SSH met behulp van een van de hierboven genoemde programma's. Nadat u verbinding heeft gemaakt en autoriseert met de server/hosting, kunt u de onderstaande opdrachten invoeren.

# Maak een back-up van één database naar het bestand dump_file.sql mysqldump -uroot -p your_base > dump_file.sql # Op Windows is het het beste om een ​​dump te maken met een iets ander commando # dat voorkomt # dat dumpregels per ongeluk worden overschreven als gevolg van de conversie van nieuwe regels tekens "\r\n" in "\n" mysqldump -uroot -p your_base -r dump_file_utf8.sql # Als u alleen een back-up nodig heeft van individuele tabellen, en niet van de gehele database # (specificeer de tabelnamen gescheiden door een spatie na de databasenaam) mysqldump -uroot -p uw_base TABEL1 TABEL2 TABEL3 > dump_file.sql # Als u alleen een back-up wilt maken van de databasestructuur zonder de gegevens zelf mysqldump -uroot -p --no-data uw_base > dump_file.sql # Maak een back-up van alle databases het bestand current_date.gz mysqldump -uroot -p --all_databases | gzip -c > "date "+%Y-%m-%d"".gz # Back-up, waarbij voor elk record een afzonderlijke INSERT wordt gemaakt # en met een expliciete indicatie van de database die UTF-8 mysqldump -uroot -p codeert --default- character-set=utf8 jouw_base --extended-insert=FALSE | gzip -c > "datum "+%Y-%m-%d"".gz

In het bovenstaande voorbeeld wordt het hulpprogramma gebruikt om een ​​back-up te maken mysqldump, dat bij mysql wordt geleverd. Hieronder volgen de parameters voor het maken van een databaseback-up, die we in meer detail zullen onderzoeken:

• -u– de parameter specificeert de login die zal worden gebruikt om verbinding te maken met de database. In het voorbeeld gebruiken we de root login, die in deze parameter zonder spatie moet worden opgegeven! Als resultaat lijkt het op -uroot
• -P– de parameter geeft aan dat u een wachtwoord moet invoeren voor de opgegeven login. We hebben het leeg gelaten, waardoor het wachtwoord moet worden ingevoerd nadat u op "Enter" hebt gedrukt bij het uitvoeren van de opdracht. U kunt het wachtwoord hier echter opgeven, zoals in de login-parameter, zonder een spatie na -p. Deze methode is echter niet veilig, omdat de console uw opdrachten in een logbestand opslaat en als u dit niet regelmatig wist, het kan door een aanvaller worden bekeken.
• jouw_basis– in plaats van deze regel in het voorbeeld moet u de echte naam opgeven van uw database waarvan u een back-up maakt.
• > – een operator die de richting van actie aangeeft, d.w.z. alsof het aangeeft dat u vanuit de database naar een bestand gaat schrijven.
• dump_bestand.sql– dit is de naam van uw .slq-bestand waarin u uw database moet opslaan. Dit wordt aangegeven met een spatie na de operator ‘>’. U kunt elke andere naam opgeven. Als u bijvoorbeeld wilt dat het systeem automatisch de huidige tijd in de naam invoegt, geeft u gewoon een regel op zoals:
  

  "datum "+%Y-%m-%d""

  "datum "+%Y-%m-%d""

  
  na deze regel in het voorbeeld wordt de bestandsextensie aangegeven ‘ .gz‘. Als gevolg hiervan verschijnt een bestand als ‘ 2014-11-15.gz‘.

  Aandacht! Als u alleen de bestandsnaam opgeeft, wordt deze opgeslagen in dezelfde map waarvoor u deze opdracht uitvoert. Die. als je zoiets ziet bij de opdrachtprompt # , Waar root@dvs dit is de login- en servernaam, het bestand wordt in de directory aangemaakt /thuis. Om het opslaan van het bestand naar een ander pad te wijzigen, geeft u het volledige pad op waar u het bestand wilt opslaan in plaats van de naam, bijvoorbeeld: /var/www/backup/dump_file.sql.

• In het tweede voorbeeld wordt in plaats van de operator ‘ > Er wordt gebruik gemaakt van ‘operator’ | ‘, wat aangeeft dat er een extra gzip-opdracht moet worden uitgevoerd met de parameter ‘ -C‘ waarmee u de dump onmiddellijk in een archief kunt inpakken en deze vervolgens kunt opslaan in een bestand als ‘ 2014-11-15.gz‘, zo meldt de exploitant ‘ > ‘.
• Parameter –geen gegevens Hiermee kunt u alleen de databasestructuur dumpen zonder de gegevens zelf. Heel handig in sommige gevallen waarin de gegevens niet nodig zijn.
• Opties –standaard-tekenset=utf8 En –extended-insert=FALSE. Met de eerste kunt u expliciet de codering opgeven die door deze database wordt gebruikt, waardoor u voorkomt dat de database in de verkeerde codering wordt opgeslagen. In plaats van utf8 kunt u elke andere codering opgeven, bijvoorbeeld cp1251. Met de tweede parameter kunt u opgeven dat bij het exporteren voor elk record een afzonderlijk INSERT-commando moet worden aangemaakt. In sommige gevallen kan dit nodig zijn bij het gedeeltelijk herstellen van gegevens uit een dump.

Een database herstellen vanuit een MySQL-dumpbestand

Laten we nu eens kijken naar het omgekeerde proces van het herstellen van een database vanuit een dumpbestand. Deze actie wordt uitgevoerd met behulp van het mysql-programma. Laten we meteen naar een voorbeeld kijken.

U hebt waarschijnlijk databasedumps meer dan eens moeten importeren. En ondanks de trivialiteit van deze taak, ontstaan ​​​​er vaak vragen en soms problemen die hiermee gepaard gaan. Hieronder vindt u alle code die u nodig hebt om snel een databasedump toe te passen vanaf de opdrachtregel.

NAMEN INSTELLEN utf8; DROP DATABASE `mydb`; CREËER DATABASE `mydb` STANDAARD TEKENSET utf8; GEBRUIK `mijndb`; SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET autocommit=0; SET Foreign_key_checks=0; SET unieke_checks=0; BRON /pad/naar/db_dump.sql; VERBINDEN; SET autocommit=1; SET Foreign_key_checks=1; SET unieke_checks=1;

Met deze opdrachten kunt u snel een databasedump importeren en problemen voorkomen. Bovendien worden sommige van deze problemen zelfs door gewone mysql-clients, zoals phpMyAdmin en anderen, niet opgelost.

Laten we proberen deze code in volgorde te bekijken.

NAMEN INSTELLEN utf8; zegt dat de gegevensuitwisseling zal plaatsvinden in UTF-8-codering en dit is waar het brondumpbestand van de database zich bevindt en dit is wat de uiteindelijke database zal hebben.

DROP DATABASE `mydb`; En CREËER DATABASE `mydb` STANDAARD TEKENSET utf8; presteren vernietiging database en de daaropvolgende creatie ervan met de standaardcodering UTF-8. Het is duidelijk dat hierdoor alle tabellen en gegevens daarin worden gewist, maar dit geeft ons het vertrouwen dat de resulterende database volledig zal overeenkomen met de gegevens in het dumpbestand.

GEBRUIK `mijndb`; geeft aan dat deze specifieke database moet worden gebruikt voor alle opdrachten uit het dumpbestand (controleer het dumpbestand op de aanwezigheid van deze richtlijn die een andere database aangeeft om verwarring te voorkomen).

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; schakelt het verhogen van de auto-increment-teller uit bij het schrijven van een waarde van 0 naar een auto-increment-veld. Wanneer er een NULL- of 0-waarde naar een dergelijk veld wordt geschreven, wordt deze standaard automatisch verhoogd. En hoewel het hebben van een waarde van 0 in een automatisch ophogend veld niet de beste praktijk is, komt het soms toch voor dat er zulke regels in de dump staan ​​(ik kwam iets soortgelijks tegen in een dump van Magento). Het is ook vermeldenswaard dat mysqldump deze richtlijn al een tijdje standaard in zijn dumps opneemt.

SET autocommit=0; voordat u de dump uploadt en VERBINDEN; daarna versnellen ze de uitvoering van INSERT's voor InnoDB-tabellen, omdat wanneer de auto-commit-modus is ingeschakeld, logs bij elke INSERT op schijf worden opgeslagen. Kan helpen bij het rollen van grote stortplaatsen.

SET Foreign_key_checks=0; de meest voor de hand liggende optie, zonder welke de meeste stortplaatsen helemaal niet zouden kunnen worden toegepast. Schakelt controles van externe sleutels uit, waardoor gegevens in willekeurige volgorde kunnen worden geschreven, inclusief met kringverwijzingen tussen externe sleutels. Deze optie versnelt ook de dumpimport.

SET unieke_checks=0; schakelt uniciteitscontroles uit voor secundaire sleutels, wat het importproces opnieuw kan versnellen.

En tot slot, het hoofdteam voor wie dit allemaal was gepland - BRON /pad/naar/db_dump.sql; importeert rechtstreeks een mysql-databasedump uit het opgegeven bestand. Het pad naar het bestand kan absoluut of relatief zijn ten opzichte van de map van waaruit u de MySQL-console hebt geopend.

En nadat het importeren is voltooid, herstellen we de waarde van de vlaggen, hoewel dit niet hoeft te worden gedaan, omdat de opdracht SET in mysql verandert standaard de waarden van variabelen alleen in de context van de huidige sessie.