MySQL herstellen vanaf dump. MySQL-dumphersteltools - bespaar papegaaibundels! Een MySQL-databasedump maken via de console

Database dumpen en herstellen MySQL-gegevens Het is vrij eenvoudig en handig om op afstand te doen via SSH of rechtstreeks via de serverconsole. Op afstand kan dit via de programma's van Putty/Kitty. 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 # Aan ramen dumpen Het is het beste om een ​​iets andere opdracht te maken die voorkomt # dat dumpregels per ongeluk worden overschreven vanwege de conversie van nieuweregeltekens "\r\n" naar "\n" mysqldump -uroot -p your_base -r dump_file_utf8.sql # Als u een back-up nodig heeft van alleen individuele tabellen, en niet van de gehele database # (specificeer de tabelnamen gescheiden door een spatie na de databasenaam) mysqldump -uroot -p your_base TABLE1 TABLE2 TABLE3 > dump_file.sql # Als u alleen een back-up van de database moet maken structuur zonder de gegevens zelf mysqldump -uroot -p -- no-data your_base > dump_file.sql # Maak een back-up van alle databases naar het bestand current_date.gz mysqldump -uroot -p --all_databases | gzip -c > "date "+%Y-%m-%d"".gz # Back-up, waarbij voor elk record een aparte 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

# Maak een back-up van één database naar dump_file.sql

mysqldump - uroot - p jouw_basis > dump_bestand . sql

# Op Windows kun je het beste een dump maken met een iets ander commando dat dit verhindert

# onbedoeld overschrijven van dumpregels vanwege conversie van nieuweregeltekens "\r\n" naar "\n"

mysqldump - uroot - p jouw_basis - r dump_bestand_utf8 . sql

# Als u alleen een back-up nodig heeft van individuele tabellen, en niet van de hele database

# (geef tabelnamen aan, gescheiden door een spatie na de databasenaam)

mysqldump - uroot - p uw_basis TABEL1 TABEL2 TABEL3 > dump_bestand . sql

# Als u alleen een back-up van de databasestructuur moet maken, zonder de gegevens zelf

mysqldump - uroot - p -- geen - gegevens uw_basis > dump_bestand . sql

# Maak een back-up van alle databases naar het bestand current_date.gz

mysqldump - uroot - p -- all_databases | gzip - c > "datum "+%Y-%m-%d"" . gz

# Back-up, waarbij voor elke invoer een aparte INSERT wordt gemaakt

# en expliciet de databasecodering UTF-8 specificeren

mysqldump - uroot - p -- standaard - karakter - set = utf8 jouw_basis -- uitgebreid - 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 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 geeft aan 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. Bijvoorbeeld zodat het systeem automatisch in de naam invoegt huidige tijd, specificeer gewoon een regel zoals:

    "datum "+%J-%m-%d""

    "datum "+%J-%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 waarin u de opdracht uitvoert deze opdracht. 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 de noodzaak tot presteren aangeeft extra opdracht gzip met parameter ‘ -C‘ waarmee u de dump onmiddellijk in een archief kunt inpakken en deze pas daarna 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 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 het uit te zoeken deze code in volgorde.

NAMEN INSTELLEN utf8; zegt dat de gegevensuitwisseling zal plaatsvinden in UTF-8-codering en dat is waar het zich bevindt bronbestand database dump 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 standaard het verhogen van de teller voor automatisch verhogen uit bij het schrijven van de waarde 0 naar het veld voor automatisch verhogen NULL-waarden of 0, het wordt automatisch verhoogd. En hoewel het hebben van een waarde van 0 in een automatisch incrementeel veld dat niet is beste praktijk, soms komt het nog steeds voor dat er zulke regels in de dump staan ​​(ik kwam iets soortgelijks tegen in een dump van Magento). Het is ook de moeite waard om te vervangen wat mysqldump al een tijdje bevat deze richtlijn standaard in hun stortplaatsen.

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 elke volgorde kunnen worden geschreven, ook met cyclische referenties vreemde 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 mysql-databasedump uit 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.

Het maken van een databasedump (back-up) is erg belangrijk. Daarom heb ik enkele opmerkingen gemaakt met voorbeelden hiervoor uitstekend nut zoals 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 team doen we dat wel back-up van de database gegevens 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 deze opdracht vaak gebruikt, is het beter om dit te doen individuele gebruiker Met noodzakelijke rechten, om het rootwachtwoord minder zichtbaar te maken

Laten we er nog meer overwegen fijne instellingen mijnsqldump:

--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 heel nuttig advies over 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 lenen dan een bepaald bedrag fysiek geheugen(RAM, RAM). 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 te lenen, 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 DB-KARAKTER= utf8

set DBNAAM= inbreuk

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

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

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

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

./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 (in Windows) en cdt u naar de map waar mysql.exe zich bevindt uitvoerbaar bestand mysql.exe (misschien moet u er even over nadenken, het zal afhangen van hoe u mysql hebt geïnstalleerd, dat wil zeggen standalone 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 al aanwezig.

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

teambenadering mysql-reeksen dan gebruiken

mysql< file.sql

naderen binnen opdrachtregel veroorzaakt een foutmelding "server is vertrokken" uit 2006 (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 concreet voorbeeld vorig antwoord:

Ik moest herstellen reservekopie zodat ik het kan importeren/overbrengen naar SQL-server. 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 source-opdracht uitgevoerd met de volledige weg als volgt:

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

Het is vreemd, maar het is best moeilijk om deze informatie op internet te vinden. Een hele hoop linkse aanbevelingen. Maar eigenlijk is alles eenvoudig. U kunt de dump herstellen met het commando:

# mysql-omhoog< mysql_dump.sql.

Vervang in plaats daarvan de gebruikersnaam, ik doe persoonlijk het herstel vanaf de root. En in mysql_dump, sql, wordt feitelijk de dump gemaakt mysqldump nutsvoorziening. De optie -p geeft autorisatie met een wachtwoord aan; na het indrukken van enter wordt u om een ​​wachtwoord gevraagd. Dat is eigenlijk alles. Je kunt een dump maken met het commando:

# mysqldump-u root -p -f databasenaam > bestand_met_dump.sql

Hier specificeert de parameter -f database_name de naam van de database die we opslaan. Als je alles nodig hebt, vervangen we het door --alle databases. Deze optie geeft aan dat alle databases op de server worden opgeslagen. Om het wachtwoord niet elke keer in te voeren, kunt u het rechtstreeks in de opdracht opgeven (dit is nodig als u een cron-dump vanuit een script uitvoert), maar vanuit veiligheidsoogpunt rijzen hier vragen, vooral als de wachtwoord is wortel voor de spier. Nu, als de spier tenminste md5-wachtwoorden zou begrijpen... Het is echter geen feit dat hij het niet begrijpt, misschien weet ik het niet :)

Auteur

Alex Razgibalov

Een gekke man, van onbepaalde leeftijd, die lijdt aan een waan van vervolging. Pathologisch wantrouwend, egoïstisch, autoritair. Secundaire diagnoses - programmeur en assistent. Bezit Engels op een niveau van ongeveer waardeloze conversatie. Ook bekend met enkele andere talen. Hij is geïnteresseerd in alles en iedereen, waardoor zijn kennis op welk gebied dan ook oppervlakkig en oppervlakkig is. Het karakter is oncontroleerbaar. Sterkte - 55 graden.