Exporteer databases via de console. Database exporteren (PHPMyAdmin)

Deze procedure omvat het overbrengen van gegevens van de ene database (A) naar de andere (B). In de regel bevindt database B zich op de hosting (of in Denver), en database A bevindt zich op de computer van de gebruiker en is een bestand met de sql-extensie. Database A heeft een andere naam: Dump.

Hoe importeer ik een database?

Een MySQL-database importeren met SSH

Deze methode wordt vrij zelden gebruikt, maar we zullen het beschrijven. Vul eerst de database in waaruit u deze naar de server van de hostingprovider gaat importeren, waar uw websitebestanden worden opgeslagen. Gebruik vervolgens de opdracht om de database te importeren:

mysql -uUSERNAME -pUSERPASSWORD DBNAAM< DUMPFILENAME.sql

mysql --user=GEBRUIKERSNAAM --password=GEBRUIKERSPASSWORD DBNAAM< DUMPFILENAME.sql

In plaats van woorden geschreven in hoofdletters vervangen we:
GEBRUIKERSNAAM - gebruikersnaam van de database, bijvoorbeeld uhosting_databaseuser;

USERPASSWORD - databasegebruikerswachtwoord, bijvoorbeeld Rjkweuik12;

DBNAME - naam van de database waarin de import zal plaatsvinden, bijvoorbeeld uhosting_databasenaam

DUMPFILENAME - naam van het dumpbestand waaruit gegevens worden geïmporteerd. Hier moeten we ook het pad opgeven naar de database die we naar de server van de hostingprovider hebben geüpload.

Goedendag, collega's :)

Vandaag zal ik u verder laten kennismaken met het werken met MySQL in de console en de MySQL-opdrachtregel.

Ik heb al artikelen geschreven over het uitvoeren van basisacties met MySQL-gegevens via de console, het maken van een back-up van de MySQL-database en het exporteren van de informatie die daarin is opgeslagen.

Het logische vervolg van dit verhaal is het herstel van de database en de daarin opgeslagen informatie met behulp van MySQL-database-importbewerkingen. En, belangrijker nog, we zullen dit blijven doen met behulp van de tool van alle echte ontwikkelaars – via de console.

Als je instructies nodig hebt voor het importeren van een database via phpMyAdmin, dan kun je deze vinden in het artikel over. In het huidige artikel wil ik het niet opnieuw beschrijven, vooral omdat het materiaal van vandaag uitsluitend gewijd zal zijn aan het importeren van de MySQL-database via de console.

Maar voordat we methoden en hulpmiddelen gaan beoordelen, een paar woorden over wat het importeren van een MySQL-database is, hoe het is en hoe u dit het beste kunt doen?

Een MySQL-database importeren: wat en waarom?

Het importeren van een MySQL-database is een handeling waarbij de database wordt gevuld met informatie. In dit geval is de gegevensbron een dumpbestand: een momentopname van een andere database, automatisch gemaakt tijdens de exportbewerking, of een speciaal voorbereid SQL-script.

Bij het importeren en exporteren van de MySQL-database zijn er twee soorten informatie opgeslagen in de database:

de structuur van de database, de tabellen en de gegevens die daarin zijn opgeslagen (gewoonlijk een databasedump genoemd);
eenvoudigweg gegevens opgeslagen in een tabel of verzameld met behulp van SELECTEER verzoeken.

In dit artikel worden beide opties besproken.

Om een MySQL-database met zijn structuur en alle opgeslagen informatie uit een dump te herstellen, zoals reeds vermeld, heeft u een databasedumpbestand nodig, een tekstbestand met elke extensie (kan vooraf in een archief worden ingepakt om de grootte te verkleinen), met SQL-opdrachten voor het maken van de database zelf en tabellen, en voor het vullen ervan met informatie.

Om een MySQL-database vanuit een dump te herstellen, moet u daarom de opdrachten in het bestand uitvoeren.

Voor regulier gegevensherstel zijn dergelijke complicaties niet nodig. Het volstaat om een testbestand beschikbaar te hebben, waarin de informatie op dezelfde manier zal worden gestructureerd als in de databasetabel: het aantal kolommen met informatie komt overeen met het aantal tabelrecordattributen.

Voor deze doeleinden is een gewoon txt-bestand geschikt, waarvan de gegevens worden gescheiden, of bestanden die zijn gemaakt in speciale spreadsheet-editors (Microsoft Office Excel, OpenOffice, enz.) met een uitstekende extensie: xls, csv, odt, enz.

Deze formaten verdienen zelfs de voorkeur, omdat Bij het maken ervan worden gegevensscheidingstekens automatisch toegevoegd door editors, en het is niet nodig om ze afzonderlijk in te voeren, zoals in het geval van een gewoon tekstbestand.

Gegevens toevoegen aan MySQL: hulpmiddelen

Wat betreft de tools voor het importeren van een MySQL-database kan ik zeggen dat er vandaag de dag drie zijn.

Ik zal ze opsommen, beginnend bij het laagste niveau en eindigend met het hoogste niveau (vanuit het oogpunt van het gebruik van allerlei soorten shells en add-ons):

Serverconsole en MySQL-opdrachtregel;
Scripts geschreven in programmeertalen waarmee u gegevens in MySQL kunt vastleggen met behulp van taalhulpmiddelen;
Kant-en-klare programma's die een visuele interface bieden voor het werken met de database (dezelfde phpMyAdmin, MySQL WorkBench, MySQL Manager, enz.).

Ik denk dat de volgorde van de gereedschappen bij niemand vragen zal oproepen, omdat... Programmeertaalhulpmiddelen werken in de regel op basis van MySQL-consoleopdrachten, en programma's zijn gebaseerd op scripts of werken rechtstreeks met MySQL vanaf de opdrachtregel.

Op de een of andere manier loopt de console voorop, en de overige tools zijn in feite de emulators ervan.

Daarom kunt u door de console te gebruiken bij het importeren van gegevens in MySQL verschillende soorten beperkingen omzeilen die zijn ingesteld door de instellingen van programmeertalen op de webserver en de programma's zelf (die overigens niet altijd kunnen worden gewijzigd).

Hierdoor kun je niet alleen sneller een MySQL-database via de console laden, maar deze handeling in principe ook mogelijk maken, omdat Scripts en programma's hebben de neiging het importeren te onderbreken wanneer de maximale uitvoeringstijd van het script wordt bereikt of helemaal niet starten vanwege de grootte van het gedownloade bestand.

Ik denk dat iedereen die ooit heeft geprobeerd een dump in een grote MySQL-database te laden via phpMyAdmin begrijpt waar ik het over heb.

Vaak zijn deze limieten de oorzaak van fouten bij het importeren van een MySQL-database, die je nooit zult zien als je de console gebruikt.

Ze zijn natuurlijk niet constant en kunnen worden gewijzigd, maar dit is een extra hoofdpijn, die overigens misschien niet oplosbaar is voor gewone gebruikers.

Ik hoop dat ik je heb gemotiveerd om de MySQL-database via de console te importeren (zowel de structuur als de individuele gegevens).

En met deze positieve noot gaan we verder met de langverwachte praktijk en kijken we naar methoden en opdrachten voor console-overdracht van gegevens naar de database.

Hoe herstel ik een MySQL-database vanuit een dump via de console?

Er zijn dus twee manieren om een MySQL-dump vanaf de console te implementeren:

een opdracht gebruiken op de MySQL-opdrachtregel;
in de serverconsole zelf.

Laten we op volgorde beginnen.

Dus om een MySQL-databasedump in een bestaande opslag te importeren via , moeten we deze eerst starten en de gewenste database selecteren waarin we onze dump zullen uploaden.

De implementatie van deze acties wordt in detail beschreven in het hierboven gelinkte artikel, dus als je een beschrijving ervan nodig hebt, ga dan verder, want Ik wil ze niet dupliceren voor de tweede ronde.

Nadat je het bovenstaande hebt gedaan, voer je de volgende opdracht in MySQL Shell in:

Bronpad_en_dump_bestandsnaam;

Het enige dat ons nog rest, is het bestuderen van de berichten in de console over de voortgang van de bewerkingen in de dump.

Zonder eerst naar de gewenste database over te schakelen, kan, na verbinding te hebben gemaakt met de MySQL-server in de console, de dump worden geïmporteerd met het volgende commando:

Mysql -u gebruikersnaam -p databasenaam< путь_и_имя_файла_дампа

Dat is het. Het belangrijkste is om te wachten tot het importeren is voltooid als het bestand erg groot is. De voltooiing van de dump kan worden beoordeeld aan de hand van het moment waarop de serverconsole weer beschikbaar is.

Dit is trouwens het nadeel van deze methode vergeleken met de vorige, omdat in het eerste geval is het mogelijk om de bewerkingen die tijdens het importeren op de database worden uitgevoerd te monitoren, maar in het tweede geval is dit niet het geval.

Als het dumpbestand in een archief is ingepakt, moet het tijdens het downloaden tegelijkertijd worden uitgepakt.

Op Linux kan dit als volgt worden gedaan:

Gunzip > [archiefbestandsnaam.sql.gz] | mysql-u-p

In Windows is er geen standaardhulpprogramma voor het uitpakken van het archief in de console, dus u zult het extra moeten installeren.

Zoals u kunt zien, is het importeren van een MySQL-dump via de console een zeer eenvoudige handeling, die met één commando wordt uitgevoerd. U hoeft dus geen ontwikkelaar te zijn om deze procedure uit te voeren.

Als u plotseling niet weet hoe u de serverconsole moet starten, kunt u deze informatie vinden in het artikel over de MySQL-opdrachtregel, de link waarnaar ik eerder al heb gepost.

Overigens is het met de beschreven methoden ook mogelijk om een MySQL-tabel te importeren, en niet de hele database. In dit geval moet de dump die u uploadt de bewerkingen bevatten voor het maken ervan en het vullen met gegevens.

Gegevens in een MySQL-database laden vanuit een bestand in de console

We hadden het over het herstellen van een MySQL-database vanaf een dump in de console. Dit is het moment om uit te zoeken hoe je op dezelfde manier gegevens uit bestanden, inclusief xls en csv, in een MySQL-database kunt importeren.

Voor deze taak hebben we opnieuw dezelfde twee tools als in het vorige geval: de MySQL-opdrachtregel en de serverconsole.

Laten we de beoordeling opnieuw op volgorde beginnen.

Dus om een bestand op de MySQL-opdrachtregel te importeren, voeren we het opnieuw uit en gaan we naar de database waarin de gegevens worden geladen.

LAAD DATA INFILE "pad_en_naam_van_dump_bestand" IN TABEL `database_tabel` KOLOMMEN BEËINDIGD DOOR "," INGESLOTEN DOOR "\"" REGELS BEËINDIGD DOOR "\n";

Vergeet dat niet als de MySQL-server met de optie is gestart --secure-file-priv(wat vaak gebeurt bij het gebruik van MySQL-distributies die zijn opgenomen in WAMP/MAMP-assemblages), dan moet de bestandsnaam worden opgegeven, rekening houdend met de systeemvariabele secure_file_priv.

Om een MySQL-database in de serverconsole te importeren zonder naar MySQL Shell te gaan, hebben we het hulpprogramma nodig mijnsqlimport, opgenomen in de MySQL-distributie, en de volgende oproep:

mysqlimport –u gebruikersnaam –p databasenaam naam_en_pad_naar_importbestand

Dit hulpprogramma is een analoog van de SQL-opdracht LAAD GEGEVENSINBESTAND, alleen voor de opdrachtregel. Maar de vraag rijst: waarom behoort dan niet tot de parameters van de aanroep de tabel waarin de gegevens uit het bestand worden geladen?

Feit is dat mysqlimport deze parameter simpelweg fysiek niet heeft. In plaats daarvan moet de naam van de tabel waarin de gegevens worden geladen, voorkomen in de naam van het geïmporteerde bestand.

Die. als u een Excel-tabel in een MySQL-tabel wilt importeren gebruikers, dan zou uw bestand moeten worden aangeroepen gebruikers.xls.

De extensie van het geïmporteerde bestand kan, zoals reeds vermeld, van alles zijn.

Met mysqlimport kunt u ook meerdere xls- of csv-bestanden tegelijk in MySQL laden. Om ervoor te zorgen dat de gegevens hun bestemming bereiken, moeten de namen van de bestanden en databasetabellen, zoals in het vorige voorbeeld, ook overeenkomen.

Als de kolommen in het geïmporteerde bestand plotseling niet in dezelfde volgorde staan als de kolommen van de databasetabel, dan moet u om hun volgorde te verduidelijken de optie —columns in de volgende vorm gebruiken:

Mysqlimport –u gebruikersnaam –p databasenaam --kolommen kolom1, kolom2, ... naam_en_pad_naar_importbestand

Uiteraard heb ik in mijn voorbeelden niet rekening gehouden met de volledige lijst met mysqlimport-parameters, omdat sommige ervan zijn zeer specifiek en worden in de praktijk uiterst zelden gebruikt.

Als u er vertrouwd mee wilt raken, kunt u hun volledige lijst hier vinden - https://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html

Kenmerken van het laden van gegevens in een MySQL-database vanuit een dump

Als je wilt dat het proces van het importeren van een grote MySQL-database sneller gaat, dan moet je een databasedump maken met behulp van de speciale opties van het mysqldump-commando, waarover ik schreef in mijn vorige artikel over het exporteren van een MySQL-database, waarvan de link was eerder in de tekst geplaatst.

Helaas hebben de importopdrachten van de MySQL-database zelf dergelijke opties niet.

Het enige is dat u de volgende functie kunt gebruiken om de snelheid te verhogen bij het laden van een grote databasedump.

1. Open het dumpbestand (bij voorkeur in bestandsbeheerders, aangezien gewone editors eenvoudigweg overweldigd kunnen raken door grote bestanden).

2. Schrijf de volgende regels aan het begin van het bestand:

SET buitenlandse_sleutel_checks = 0; UNIEKE_CHECKS INSTELLEN = 0; AUTOCOMMIT = 0 INSTELLEN;

Let op! Mogelijk zijn ze er al of zijn ze becommentarieerd (veel programma's die dumps maken, kunnen ze automatisch toevoegen)

3. Aan het einde van het bestand schrijven we de omgekeerde acties:

SET buitenlandse_sleutel_checks = 1; UNIEKE_CHECKS INSTELLEN = 1; AUTOCOMMIT = 1 INSTELLEN;

Overigens zullen deze commando's niet alleen het importproces helpen versnellen, maar het ook mogelijk maken.

Feit is dat als je ooit naar het dumpbestand voor het importeren van een MySQL-database hebt gekeken, je misschien hebt gemerkt dat de werking van het instellen van de structuur van de geladen tabellen er als volgt uitziet:

DROP TABLE INDIEN BESTAAT `klanten`; MAAK TABEL `klanten` (...);

Die. Er wordt in de database gezocht naar een tabel met dezelfde naam als de tabel die wordt geïmporteerd, en als er een wordt gevonden, wordt deze verwijderd en opnieuw gemaakt.

En als plotseling een bestaande tabel door middel van externe sleutels aan andere wordt gekoppeld, mislukt het hele laden.

Daarom is het uitschakelen van de controle op het bestaan van externe sleutels en andere ook een uitstekende garantie voor de succesvolle voltooiing van het importproces van de MySQL-database.

Kenmerken van het importeren van csv in de MySQL-database en andere bestanden

Wanneer u gegevens vanuit tekstbestanden in een MySQL-database laadt, moet u mogelijk ook externe sleutels uitschakelen.

Bovendien zal het, in tegenstelling tot de vorige situatie, in dit geval niet mogelijk zijn om richtlijnen in het bestand te schrijven, omdat SQL-opdrachten daarin worden niet geaccepteerd en uitgevoerd.

In een vorig artikel over het exporteren van een MySQL-database heb ik al vermeld hoe je dit kunt doen met behulp van de volgende bewerking op de MySQL-opdrachtregel:

SET FOREIGN_KEY_CHECKS=0;

Ik heb daar echter niet vermeld dat de MySQL-systeemvariabele FOREIGN_KEY_CHECKS heeft twee betekenissen: globaal en sessief (voor de huidige sessie).

De globale waarde van MySQL-variabelen is geldig voor elke actie op de MySQL-server totdat deze opnieuw wordt opgestart. Vervolgens worden de waarden van de variabelen gereset en krijgen ze standaardwaarden toegewezen.

De sessiewaarde van de MySQL-systeemvariabele wordt alleen ingesteld voor de duur van de sessie van de gebruiker met de MySQL-server. Een sessie of sessie begint wanneer een client verbinding maakt met de server, waarna er een unieke aan wordt toegewezen verbindings-id, en eindigt wanneer de verbinding met de server wordt verbroken, wat op elk moment kan gebeuren (bijvoorbeeld vanwege een time-out).

Waarom besloot ik dit te onthouden?

Omdat ik bij het uitvoeren van opdrachten om een bestand in een MySQL-database te laden via de serverconsole, zonder naar de MySQL Shell te gaan, ontdekte dat het uitschakelen van de controle van externe sleutels met behulp van de eerder beschreven methode niet werkt.

De console vertoonde nog steeds een foutmelding veroorzaakt door de aanwezigheid van externe sleutels in de tabel.

En het ontstond om de reden dat het bovenstaande commando de controle op het bestaan van externe sleutels binnen de sessie heeft uitgeschakeld, en niet globaal, wat, naast de aangegeven methode, ook als volgt kan worden uitgevoerd:

SESSIE INSTELLEN variabele_naam = variabele_waarde; SET @@sessie.variabele_naam = variabele_waarde; SET @@variabelenaam = variabele_waarde;

In de bovenstaande opdrachten is de variabele duidelijk gemarkeerd als sessie.

En aangezien ik via de serverconsole een csv-bestand in een MySQL-tabel laadde, zonder een directe verbinding met de MySQL-server, werd er geen sessie aangemaakt waarin de waarde van mijn sessievariabele zou werken.

Uiteindelijk heb ik FOREIGN_KEY_CHECKS ingesteld op globaal en het importeren was succesvol.

U kunt dit op een van de volgende manieren doen:

SET GLOBAL variabele_naam = variabele_waarde; SET @@global.variabele_naam = variabele_waarde;

Nadat u de waarden heeft gewijzigd, is het een goed idee om de waarden van de variabele te controleren om te verifiëren dat de wijzigingen van kracht zijn geworden. Om sessie- en globale waarden tegelijkertijd weer te geven, gebruikt u de volgende opdracht:

SELECTEER @@GLOBAL.foreign_key_checks, @@SESSION.foreign_key_checks;

Hiermee wordt het artikel van vandaag over het importeren van een MySQL-database afgesloten. Deel uw indrukken en uw eigen ontwikkelingen in de reacties. Ik denk dat velen geïnteresseerd zullen zijn in jouw ervaringen.

Tot ziens! 🙂

P.S.: heeft u een website nodig of wilt u wijzigingen aanbrengen in een bestaande, maar heeft u daar geen tijd of zin voor, dan kan ik u van dienst zijn.
Meer dan 5 jaar ervaring professionele websiteontwikkeling. Werken met PHP, Open winkelwagen, WordPress, Laravel, Jii, MySQL, PostgreSQL, JavaScript, Reageren, Hoekig en andere webontwikkelingstechnologieën.
Ervaring met het ontwikkelen van projecten op verschillende niveaus: landingspagina's, bedrijfswebsites, Online winkels, CRM, portalen. Inclusief ondersteuning en ontwikkeling HighLoad-projecten. Stuur uw sollicitaties per e-mail [e-mailadres beveiligd].

In dit artikel zullen we twee vragen bespreken: hoe u databases kunt importeren en hoe u het probleem met het importeren van grote databases kunt oplossen door de instellingen in php.ini te wijzigen.

Zoals u weet, is een gegevensback-up een garantie dat u uw website kunt herstellen als de werking van de site om wat voor reden dan ook plotseling wordt verstoord. Hoe herstel ik een website vanaf een back-up? Er zijn verschillende manieren, we zullen bekijken hoe u een mysql-database in phpMyAdmin kunt importeren. Voordat u met het database-importproces begint, moet u alle tabellen verwijderen uit de database waarin u bestanden gaat importeren.

Om tabellen te verwijderen, logt u in op de phpMyAdmin-startpagina en volgt u deze stappen:

2 - klik op Alles markeren;
3 - selecteer Verwijderen in het vervolgkeuzemenu en klik op OK,

Nu kunt u beginnen met het importeren van de mysql-database. Om dit te doen, volgt u deze stappen:

1 - selecteer de gewenste database;
2 - open het tabblad Importeren in het hoofdmenu;
3 - Zoek via Bladeren de back-up met de database en klik op OK.

Als je alles goed hebt gedaan, verschijnt er een bericht dat aangeeft dat de import succesvol was in het phpMyAdmin-venster.

Importeren van grote mysql-databases

Webmasters komen vaak het probleem tegen bij het importeren van grote mysql-databases. Het probleem is te wijten aan het feit dat in PHPMyAdmin het downloaden van een databasedump wordt beperkt door de grootte van het geüploade bestand, dat standaard niet groter mag zijn dan 2 MB (2.048 KB). Wanneer u probeert een dump van een grote database te downloaden, verschijnt er een foutmelding: “De downloadbestandsgrootte is waarschijnlijk te groot...” etc.

Laten we eens kijken naar een manier om een dump van een grote database te importeren. De limiet voor de importbestandsgrootte wordt bepaald door de PHP-configuratie. PHP-configuratie-instellingen worden gespecificeerd in het php.ini-bestand. Dit betekent dat als we grote databases willen importeren, we de maximale grootte van geüploade bestanden in het php.ini-configuratiebestand moeten wijzigen.

Waar bevindt het php.ini-bestand zich? U kunt het pad naar het php.ini-bestand als volgt berekenen:

Maak een info.php-bestand en plaats de volgende code daarin;
Upload het gemaakte bestand naar de hoofdmap van de site (www, public_html);
Voer het adres http://uw_site/info.php in de adresbalk van uw browser in;
Zoek in het geopende venster Geladen configuratiebestand, waar het pad naar het bestand wordt aangegeven;

Om een grote mysql-database te importeren, moet u de php.ini-richtlijnen in PHPMyAdmin wijzigen.

Het php.ini-bestand is gevonden, nu hoeft u het alleen nog maar te bewerken, of beter gezegd, verschillende richtlijnen in het bestand te wijzigen, zoals hieronder weergegeven:

Maximale tijd (sec) om het script volledig te laden:
max_execution_time = 60
De maximale tijd (seconden) die is opgegeven voor het script om alle gegevens te ontvangen:
max_invoer_tijd = 90
De maximale hoeveelheid geheugen die het script mag gebruiken:
geheugen_limiet = 128M
Maximaal toegestane grootte van geüploade bestanden:
upload_max_filesize = 200M
De maximale grootte van overgedragen gegevens die PHP accepteert:
post_max_size = 180M

Door de bovenstaande richtlijnen in php.ini te wijzigen, konden we databases tot 180 M importeren via PHPMyAdmin.

U kunt ook de gratis versie van Sypex Dumper gebruiken om een databasedump te importeren. De dumper is geoptimaliseerd voor maximale werksnelheid en werkt met databases van ongeveer 800 megabytes.

Gegroet, vrienden! 🙂

Vandaag besloot ik het gesprek over het werken met MySQL in de console voort te zetten en aandacht te besteden aan de procedure voor het exporteren van een MySQL-database.

In dit artikel zal ik het hebben over het dumpen van een MySQL-database en het uploaden van gegevens van MySQL naar een Excel-bestand en csv-formaat.

We zullen verschillende opties bekijken om informatie op te halen uit: het creëren van een dump van een of meerdere databases, het exporteren van gegevens uit individuele tabellen en willekeurige resultaten SELECTEER verzoeken.

We zullen ook praten over hoe u gegevens uit een MySQL-database kunt uitvoeren in de serverconsole en de MySQL-opdrachtregel.

In dit artikel zal ik het niet hebben over het exporteren van gegevens met phpMyAdmin en andere visuele hulpmiddelen.

Ten eerste omdat er op internet al voldoende materiaal over dit onderwerp te vinden is. Bovendien is het materiaal van hoge kwaliteit, dat ik niet graag kopieer en plak.

En ten tweede heb ik zelf kort het proces besproken van het uitvoeren van informatie uit een MySQL-database naar een SQL-bestand in een van mijn artikelen, waar ik het over had.

Dus als u geen professionele ontwikkelaar of systeembeheerder bent en baat zou kunnen hebben bij informatie over het werken met de console, en u kwam alleen voor instructies over het exporteren van de database naar phpMyAdmin, dan kunt u zich beperken tot het lezen van de informatie via de bovenstaande link. .

Ik wil dat u mij goed begrijpt: ik wil u op geen enkele manier beledigen, maar ik wil alleen dat u uw tijd besteedt met maximaal voordeel voor uw bedrijf en krijgt waar u naar op zoek was.

Dit is het inleidende deel en we gaan verder met een overzicht van consoleopdrachten voor het maken van een MySQL-databasedump, die ik heb besloten te sorteren op de hoeveelheid gegevens die wordt opgeslagen: beginnend met het exporteren van de hele database, eindigend met individuele tabellen en de resultaten van willekeurige vragen.

Een MySQL-databasedump maken via de console

Ik wil eerst een kleine verduidelijking geven.

Databasedump is een bestand met een reeks SQL-opdrachten waarmee u, wanneer het wordt gestart, databases en tabellen kunt maken en deze kunt vullen met informatie. De dump is nodig voor degenen die een MySQL-database willen downloaden om deze naar een andere server of binnen een bestaande server te kopiëren.

En als iemand het niet weet: een back-up van een MySQL-database is in wezen een dump ervan die in een bepaalde periode is gemaakt, waardoor u indien nodig de structuur en gegevens van de database kunt herstellen.

Gegevens exporteren- dit is eenvoudigweg het extraheren van informatie uit tabellen in tekstvorm voor verder werk met tekst- of grafische editors.

Daarom zullen de opdrachten voor deze acties enigszins verschillen.

Om een databasedump te maken, heeft MySQL een ingebouwd hulpprogramma genaamd mysqldump, die buiten de MySQL-opdrachtregel in de serverconsole of een andere shell moet worden gebruikt.

Dus voor de eenvoudigste en meest gebruikelijke optie: gegevens exporteren uit een specifieke database in de MySQL-console om deze naar een andere server over te brengen of intern te kopiëren, moet u de volgende opdracht uitvoeren:

Mysqldump -u gebruikersnaam -p databasenaam > pad_en_dump_bestandsnaam

Dit hulpprogramma kan alleen MySQL-databasedumps maken in de vorm van bestanden met SQL-opdrachten, dus ongeacht welke extensie u voor uw bestand kiest, de inhoud ervan zal in ieder geval hetzelfde zijn. En vergeet niet de schrijfrechten te controleren van de map waarin het zich zal bevinden voordat u informatie uit MySQL exporteert, zodat het bestand kan worden gemaakt.

Als u plotseling een dump moet maken van alle databases op de server, gebruik dan de volgende opdrachtoptie:

Mysqldump -u gebruikersnaam -p --alle databases > pad_en_dump_bestandsnaam

Om slechts een paar specifieke databases te dumpen, moet u mysqldump aanroepen met de volgende parameters:

Mysqldump -u gebruikersnaam -p --databases database_naam1, database_naam2, ... > pad_en_dumpbestandsnaam

Als gevolg hiervan ontvangt u in elk geval een MySQL-databasedump met opdrachten voor het creëren van de structuur van de opgenomen tabellen (velden, hun typen, indexen, sleutels, enz.), evenals bewerkingen voor het vullen ervan met gegevens.

Deze optie is alleen geschikt voor het herstellen en kopiëren van volledige databases.

We zullen verder praten over het maken van back-ups van bepaalde MySQL-tabellen en het verkrijgen van hun gegevens in leesbare vorm.

Een MySQL-tabel dumpen en gegevens exporteren

Om een dump van bepaalde MySQL-databasetabellen te maken, hebben we hetzelfde hulpprogramma nodig mysqldump, aangeroepen met de volgende parameters:

Mysqldump -u gebruikersnaam -p databasenaam tabelnaam1, tabelnaam2, ... > pad_en_dumpbestandsnaam

Wanneer u mysqldump aanroept, kunt u de vereiste tabellen opgeven als parameterwaarde --tafels, wanneer de parameter wordt gebruikt --databases zal worden genegeerd:

Mysqldump -u gebruikersnaam -p --databases databasenaam1, database_naam2 --tabellen tabelnaam1, tabelnaam2, ... > pad_en_dumpbestandsnaam

In het bovenstaande voorbeeld wordt de volgende fout weergegeven:

Mysqldump: Kreeg foutmelding: 1049: Onbekende database "database_name1", bij het selecteren van de database

Zoals u kunt zien, wordt alleen de nieuwste database uit de lijst gebruikt. In principe is dit gedrag vrij logisch, omdat Het is mogelijk dat de opgegeven tabellen niet in alle databases voorkomen.

Oké, we hebben een dump met MySQL-databasetabellen ontvangen. Het kan worden gebruikt om ze te herstellen of samen met de structuur te kopiëren.

Maar wat als u de informatie die erin is opgeslagen, bij voorkeur in een leesbare vorm wilt hebben, zodat u deze naar de manager kunt sturen en in een gewone tekst- of spreadsheeteditor kunt bekijken? MySQL heeft hier ook tools voor.

De mogelijkheid om het nutsbedrijf te bellen zal ons helpen onze plannen te verwezenlijken mysql vanaf de console met bepaalde parameters:

Mysql -u gebruikersnaam -p databasenaam -e "SELECT * FROM tabelnaam"

Met deze opdracht kunnen we een query uitvoeren op de vereiste database en het resultaat naar de console uitvoeren zonder naar de MySQL-opdrachtregel te gaan.

Om de gegevens niet naar de console uit te voeren, maar om deze naar een bestand te schrijven, moet u de opdracht als volgt aanvullen:

Mysql -u gebruikersnaam -p -e "SELECT * FROM tabelnaam" > pad_en_bestandsnaam

Dankzij deze constructies kunnen we niet alleen gegevens verkrijgen die in alle velden van de tabel zijn opgeslagen, maar ook in specifieke velden. Om dit te doen, vervangt u gewoon het jokertekens (*)-symbool door de vereiste tekens, gescheiden door komma's.

Als gevolg hiervan zal de uitvoer een gewoon tekstbestand zijn dat de namen van de velden in de vorm van een koptekst zal bevatten, en informatie daarover voor alle records. Het kan worden geopend in een gewone teksteditor, ongeacht welke resolutie u eraan geeft bij het maken ervan.

Als u gegevens uit een MySQL-database in xls- of csv-formaat wilt exporteren, zodat het resulterende bestand correct wordt weergegeven in spreadsheet-editors, dan zullen we u later vertellen hoe u dit moet doen :)

Back-ups maken en gegevens uit een MySQL-database uitvoeren met behulp van queries

We hebben gesproken over hoe je een MySQL-database kunt dumpen - een of meerdere, maar ook individuele tabellen. Maar soms zijn er in de praktijk gevallen waarin u een dataset moet exporteren die niet beperkt is tot één tabel. Of u hoeft slechts enkele gegevens uit de tabel te selecteren.

Vooral ontwikkelaars van bedrijfsprojecten worden hier vaak mee geconfronteerd als managers hen vragen allerlei statistische gegevens aan te leveren. Of wanneer u een back-up van een bepaald deel van de tabel moet maken om deze snel te kunnen herstellen.

Voor back-up hebben we hetzelfde hulpprogramma nodig mysqldump, die als volgt moet worden aangeroepen:

Mysqldump -u gebruikersnaam -p databasenaam tabelnaam --waar "zoeken" > pad_en_dump_bestandsnaam

Als gevolg hiervan ontvangen we een bestand met SQL-opdrachten om een tabel met de volledige structuur te maken, die na het maken wordt gevuld met informatie die is geselecteerd met behulp van een opzoekquery.

Als we alleen de gegevens in een of meer tabellen willen opslaan, moeten we de opdracht die in het vorige geval werd gebruikt wijzigen bij het ophalen van alle gegevens in de tabel, alleen met enkele verduidelijkingen:

Mysql -u gebruikersnaam -p -e "SELECT * UIT tabelnaam WAAR opzoeken" > pad_en_bestandsnaam

Zoals u begrijpt, wordt naast de verschillende verduidelijkingen die in het verzoek zijn gespecificeerd, gebruik gemaakt van de richtlijn WAAR, kunt u andere SQL-constructies gebruiken: MEEDOEN, UNIE enz.

Je kunt alle statistieken verzamelen die je wilt :)

Dezelfde actie kan ook worden uitgevoerd wanneer u op de MySQL-opdrachtregel werkt met behulp van de volgende opdracht:

SELECT * VAN database_tabel WAAR opzoeken IN OUTFILE "pad_en_bestandsnaam";

Deze opdracht is specifiek ontworpen om bestanden met bemonsteringsresultaten te maken. Bovendien kunnen de resultaten niet alleen naar bestanden worden geëxporteerd, maar ook naar variabelen worden geschreven, en kunnen de uitvoergegevens op verschillende manieren worden opgemaakt.

Als het bovenstaande jouw geval is, dan kun je hier een volledige lijst met parameters en opties vinden om deze opdracht aan te roepen - https://dev.mysql.com/doc/refman/5.7/en/select-into.html

Ter afsluiting van mijn korte excursie naar mysqldump zou ik een optie willen geven om een commando aan te roepen met een lijst met parameters om een geoptimaliseerde dump van de MySQL-database en -tabellen te maken, waarbij het herstellen van de database en individuele tabellen minder tijd zal kosten dan met een gewone oproep:

mysqldump -u gebruikersnaam -h host_of_IP_MySQL_server -p --no-autocommit --opt database_name > pad_en_dump_bestandsnaam;

Omwille van het experiment heb ik deze optie gebruikt om een MySQL-database van 143 MB groot te dumpen. Het daaropvolgende herstel duurde 59 seconden, vergeleken met 1 minuut en 3 seconden toen de database werd hersteld vanaf een dump die was gemaakt door mysqldump aan te roepen zonder speciale parameters.

Ik ben het ermee eens dat dit een kleinigheid is. Maar dit is alleen het geval bij een bepaalde hoeveelheid gegevens. Als u deze techniek gebruikt bij het maken van een dump groter dan 1 GB, zal het verschil groter zijn.

Als u een dergelijke situatie tegenkomt, vergeet dan niet eerst de MySQL-databasedump te archiveren. Het beste is tar.gz. Dan duurt het herstel nog minder tijd.

Exporteer gegevens van MySQL naar Excel- en csv-bestanden

Niet voor niets heb ik in één blok informatie over het uitvoeren van informatie uit MySQL naar deze twee formaten gecombineerd, omdat... ze lijken erg op elkaar, ze worden ongeveer op dezelfde manier gebruikt (om informatie in de vorm van tabellen te structureren) en dezelfde exportopdrachten worden aangeroepen.

Zoals u weet is het enige significante verschil tussen deze formaten dat de xls- en xlsx-extensies bestanden bevatten die zijn gemaakt in Microsoft Office Excel, dat alleen onder Windows werkt, terwijl csv-bestanden universeler zijn en bewerkingen ermee in veel editors mogelijk zijn.

Dit betekent niet dat xls nergens anders kan worden geopend, behalve in Microsoft Office Excel. Hetzelfde OpenOffice bevestigt het tegenovergestelde.

Maar om dit mogelijk te maken, moet deze ondersteuning aanwezig zijn in het softwareproduct. csv-bestanden zijn zelfs leesbaar in een gewone teksteditor zoals Kladblok, maar dit formulier zal niet volledig leesbaar zijn.

Laat ik beginnen met het feit dat je de resultaten van SQL-query's alleen kunt exporteren naar xls of csv, waarmee we eerder hebben leren werken, omdat het zal onmogelijk zijn om de hele database in één keer in één bestand uit te voeren.

Ten eerste is dit niet optimaal, omdat... Het is onwaarschijnlijk dat een dergelijk bestand wordt geopend als er een grote hoeveelheid informatie in de database is opgeslagen. En ten tweede is het niet duidelijk hoe de informatie in het bestand in tabellen en velden moet worden opgesplitst.

Nee, het is natuurlijk mogelijk om dit te doen, maar het is onwaarschijnlijk dat dit met één commando wordt gedaan en in het algemeen is het onwaarschijnlijk dat iemand dit in de console zal doen. Ik denk dat je voor deze doeleinden speciale software of op zijn minst een script nodig hebt.

Als je ineens weet hoe je informatie uit de hele MySQL-database tegelijk naar een of meerdere xls-bestanden in de console kunt exporteren, schrijf er dan over in de reacties. Ik denk dat het voor velen nuttig zal zijn om hierover te lezen.

Dus als we het hebben over het exporteren van gegevens van MySQL naar xls en csv, dan kan dit rechtstreeks in de serverconsole worden gedaan via het hulpprogramma mysql of in, het werk waarmee ik je in mijn vorige artikel introduceerde.

Laten we op volgorde beginnen.

U kunt gegevens uit een MySQL-database rechtstreeks in de serverconsole naar csv- en xls-formaten exporteren met behulp van de volgende opdrachten.

Op Linux systemen:

Mysql -u gebruikersnaam -d databasenaam -p -e "SELECT * FROM database_table;" | sed "s/"/\"/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > pad_en_bestandsnaam. csv

Indien absoluut noodzakelijk kunt u met dit commando in principe MySQL-gegevens naar een Excel-bestand exporteren. Maar eerlijk gezegd heb ik hier in de praktijk nog niet mee te maken gehad en ik heb geen idee wat er uiteindelijk uit zal komen, want... Ik ben momenteel bezig met Windows. Als u deze opdracht onder Linux gebruikt, schrijf dan in de opmerkingen over de resultaten van uw werk. Ik denk dat de informatie voor iedereen interessant zal zijn.

Op Ramen:

Helaas zal het exporteren van gegevens uit MySQL-tabellen naar csv met behulp van de bovenstaande opdracht in dit geval niet werken, omdat Windows heeft, in tegenstelling tot Linux, geen ingebouwde consoleopdracht voor het werken met threads, zoals sed in Linux.

Natuurlijk kun je het installeren, maar het is te veel gedoe. Als alternatief kunt u gebruiken CygWin— Linux-console-emulator voor Windows-systemen.

Het is goed als je het al hebt geïnstalleerd. Anders zal het exporteren van gegevens uit de MySQL-database met behulp van de gekozen methode ons te veel problemen opleveren.

Maar het extraheren van informatie in een xls-bestand is net zo eenvoudig als 5 kopeken :) Het is heel gemakkelijk om het op de volgende manier te starten, wat ik persoonlijk heb geprobeerd:

Mysql -u gebruikersnaam -d databasenaam -p -e "SELECT * FROM database_table;" > pad_en_bestandsnaam.xls

Dit bestand werd zonder problemen geopend in Microsoft Office Excel. Het enige is dat bij het openen een bericht op het scherm werd weergegeven met de waarschuwing dat het daadwerkelijke formaat van het geopende bestand afwijkt van de opgegeven extensie.

Maar bij het bevestigen van de actie werd het document zonder problemen geopend - alle informatie werd verdeeld in cellen in de vorm waarin het in de tabel zelf was opgeslagen.

Ik weet het niet, misschien zullen er in de toekomst problemen ontstaan als je specifieke acties uitvoert in Microsoft Office Excel; zo diep heb ik niet gegraven. Toen ik normaal door de gegevens keek, zag ik tenminste niets ongewoons.

Als u problemen ondervindt bij het gebruik van het xls-bestand dat uit MySQL is geëxporteerd, in dit programma of in andere programma's, laat het me dan weten in de reacties.

Met de hierboven beschreven methode kunt u in principe de inhoud van een MySQL-database exporteren naar een csv-bestand. Maar dan worden de gegevens uit verschillende velden van de tabel massaal geschreven, zonder scheidingstekens, wat slecht kan worden weergegeven in verschillende programma's voor het werken met tabellen, die meestal met csv-bestanden werken.

Het maakt OpenOffice overigens niets uit :) Het scheidde automatisch de informatie die werd verkregen door de manier waarop we de inhoud van de MySQL-database naar xls exporteerden. Ik weet niet hoe hij het doet, maar ik raad aan om het te gebruiken :)

Welnu, dezelfde Microsoft Office Excel gaf alle informatie weer die overeenkomt met één record in de tabel en schreef deze in één cel zonder scheidingstekens. Ik denk dat andere tabeleditors hetzelfde zullen doen.

Wanneer u een MySQL-database naar csv-bestanden exporteert, moet u dit daarom doen door de informatie te scheiden met speciale tekens die door editors worden begrepen.

En toen benaderde ik geleidelijk de tweede methode om MySQL-gegevens naar csv en xls te exporteren, namelijk door de MySQL-opdrachtregel te gebruiken.

Om MySQL-gegevens op deze manier naar een csv-bestand te exporteren, hebben we dus de volgende opdracht nodig:

SELECTEER * VAN database_tabel IN OUTFILE "pad_en_bestandsnaam.csv" VELDEN BEËINDIGD DOOR "," INGESLOTEN DOOR """ REGELS BEËINDIGD DOOR "\n";

Als gevolg van de uitvoering ervan ontvangt u een csv-bestand in het pad dat u hebt opgegeven bij het bellen, dat correct wordt geopend in de meeste moderne spreadsheet-editors. Voor het geval dat, herinner ik u eraan dat u deze opdracht alleen hoeft uit te voeren nadat u verbinding hebt gemaakt met de MySQL-database.

Deze opdracht is ook ideaal voor het exporteren van MySQL-gegevens naar een xls-bestand voor correcte weergave in Microsoft Office Excel. Alleen in dit geval hebben we geen scheidingstekens nodig, omdat ze zullen de verdeling van informatie in cellen verstoren:

SELECTEER * VAN database_tabel IN OUTFILE "pad_en_bestandsnaam.xls";

In de praktijk is echter niet alles zo eenvoudig als ik heb beschreven. Terwijl u de opdracht uitvoert, kunt u in de console de volgende fout tegenkomen, waardoor de export niet kan worden voltooid:

ERROR 1290 (HY000): De MySQL-server draait met de optie --secure-file-priv en kan deze instructie dus niet uitvoeren

Het wordt veroorzaakt doordat uw MySQL-server met deze optie is gestart --secure-file-priv. Persoonlijk kwam ik dit probleem tegen vanwege het feit dat ik voor het werken in de console de MySQL-distributiekit gebruik die is opgenomen in de WAMP OpenServer-kit, die op zijn beurt de MySQL-server op deze manier lanceert.

Er zijn twee manieren om het probleem op te lossen:

Wijzig de opstartparameters van de MySQL-server
Wijzig het pad naar het uiteindelijke MySQL-exportbestand

De eerste methode leek mij te ingewikkeld, omdat... Ik zou me moeten verdiepen in de OpenServer-configuratie, die niet door mij is geschreven met alle daaruit voortvloeiende omstandigheden 🙂 Daarom besloot ik de tweede weg te bewandelen. Als u een soortgelijk probleem tegenkomt, herhaal dan na mij.

Eerst moet u naar de MySQL-opdrachtregel gaan en een van de volgende opdrachten uitvoeren:

TOON VARIABELEN ZOALS "secure_file_priv"; SELECTEER @@GLOBAL.secure_file_priv;

Het resultaat van het uitvoeren van beide is de waarde van de globale MySQL-variabele secure_file_priv, dat het pad bevat naar de map waarmee MySQL-gegevensexport- en -importbewerkingen kunnen worden uitgevoerd (in de toekomst een link naar een artikel over gegevensimport).

Die. bij het gebruik van commando's GEGEVENS LADEN En SELECTEER... IN OUTFILE geëxporteerde en geïmporteerde bestanden kunnen alleen in deze map worden geplaatst.

In mijn geval was deze variabele over het algemeen ingesteld op NUL, omdat Ik gebruik, zoals ik al zei, MySQL-hulpprogramma's uit de distributie in OpenServer om in de console te werken. Deze waarde gaf aan dat de export- en importbewerkingen van MySQL-gegevens met behulp van de opgegeven opdrachten volledig waren afgesloten.

Zoals later bleek, is dit een veel voorkomende situatie bij het gebruik van boxed WAMP- en MAMP-servers.

Helaas was het in mijn geval niet mogelijk om de gebruikelijke methoden te gebruiken om de waarden van de globale MySQL-variabelen te wijzigen:

SET variabele_naam = waarde;

Als gevolg hiervan zag ik alleen de volgende fout in de console:

ERROR 1238 (HY000) op regel 1: Variabele "secure_file_priv" is een alleen-lezen variabele.

Als gevolg hiervan kunt u de waarde van een variabele wijzigen secure_file_priv en de export- en importbewerkingen te openen, moest ik naar het MySQL-configuratiebestand mysql.ini gaan, dat zich in de hoofdmap van de MySQL-distributie bevindt, of op een andere manier toegankelijk is als MySQL is opgenomen in uw WAMP/LAMP/ MAMP-server bouwen.

Trouwens, als je het pad naar de spoolmap voor bestandsuitwisseling wilt wijzigen, moet je hetzelfde doen.

In mijn geval bestond deze variabele al in de configuratie, alleen in commentaarvorm:

Secure-file-priv = "%dprogdir%\\userdata\\temp"

Als je het niet hebt, schrijf het dan helemaal opnieuw in de sectie (althans voor mij was het daar gevestigd).

Ik heb het commentaar verwijderd en besloten het te gebruiken in de vorm waarin het was geschreven. Die. bij het exporteren van gegevens uit MySQL en het terug importeren ervan, worden mijn bestanden nu in een map opgeslagen c:\openserver\gebruikersdata\temp\.

Vergeet na het wijzigen van de configuratie (wie dan ook trouwens) niet om uw server of een aparte service waarvan u de instellingen heeft bewerkt, opnieuw op te starten, indien mogelijk, om de wijzigingen door te voeren!

Geef voor de zekerheid de variabele opnieuw weer nadat u de MySQL-server opnieuw hebt opgestart secure_file_priv en kopieer de waarde naar het klembord.

En nu moeten we de opdracht aanroepen zoals aan het begin, alleen vóór de naam van het bestand waarin informatie uit de MySQL-database zal worden opgeslagen, het pad schrijven dat is opgeslagen in de variabele die we veranderen in de volgende vorm:

SELECTEER * VAN database_tabel IN OUTFILE "secure_file_priv_value\file_name.csv";

Hierna werkte het exporteren van gegevens uit MySQL in mijn geval.

Belangrijk punt! Als u met MySQL onder Windows werkt, vergeet dan niet om “\” te wijzigen in “/” bij het opgeven van het pad naar het bestand, anders wordt de fout weergegeven --secure-file-priv zal nog steeds verschijnen.

Hiermee wordt het artikel afgesloten over het dumpen van een MySQL-database en de bijbehorende tabellen, en over het uitvoeren van gegevens uit MySQL-tabellen in verschillende formaten. Schrijf uw beoordelingen in de opmerkingen en deel met iedereen uw scriptopties die u in de praktijk het vaakst gebruikt.

Als je het artikel leuk vond, kun je de auteur bedanken door het artikel opnieuw te posten op sociale netwerken of door het onderstaande formulier financieel te gebruiken, zodat je kunt betalen voor de basishosting.

Veel succes allemaal en tot ziens! 🙂

P.S.: heeft u een website nodig of wilt u wijzigingen aanbrengen in een bestaande, maar heeft u daar geen tijd of zin voor, dan kan ik u van dienst zijn.
Meer dan 5 jaar ervaring professionele websiteontwikkeling. Werken met PHP

Als u een back-up van uw database heeft gemaakt of deze naar een SQL-bestand heeft geëxporteerd, kunt u deze via phpMyAdmin in een van de MySQL-databases van uw hostingaccount importeren.

Opmerking. Er mag geen CREATE DATABASE-regel in een MySQL-database voorkomen. Anders kan het importeren mislukken.

Met de phpMyAdmin-interface kunt u 8 MB aan gegevens tegelijk importeren. Als u een groter bestand wilt importeren, splitst u het op in meerdere delen van 8 MB.

Aandacht! Als u een database importeert voor beheerde WordPress-hosting om uw website soepel te laten werken.

SQL-bestanden importeren in MySQL-databases met behulp van phpMyAdmin

Hierna wordt het SQL-bestand uitgevoerd en wordt de database bijgewerkt die u in het SQL-bestand hebt geselecteerd. Het herstellen van de database kan enkele minuten duren.

Opmerking. Als u de foutmelding 'Scripttime-out voorbij' ontvangt, dient u hetzelfde bestand opnieuw in als u het importeren wilt voltooien. Het importeren wordt dan hervat. U kunt onmiddellijk hetzelfde bestand selecteren en het proces hervatten.