• hjem
  •  > 
  • Android
  •  > 
  • Eksporter og importer data fra mysql. Dumping av MySQL-databasen og eksport av data til konsollen

Eksporter og importer data fra mysql. Dumping av MySQL-databasen og eksport av data til konsollen

God dag, kollegaer :)

I dag vil jeg fortsette å introdusere deg til å jobbe med MySQL i konsollen og MySQL-kommandolinjen.

Jeg har allerede skrevet artikler om hvordan du utfører grunnleggende handlinger med MySQL-data gjennom konsollen og hvordan du sikkerhetskopierer MySQL-databasen, samt eksporterer informasjonen som er lagret i den.

Den logiske fortsettelsen av denne historien vil være gjenoppretting av databasen og informasjonen som er lagret i den ved hjelp av MySQL-databaseimportoperasjoner. Og viktigst av alt, vi vil fortsette å gjøre dette ved å bruke verktøyet til alle ekte utviklere – gjennom konsollen.

Hvis du trenger instruksjoner for å importere en database via phpMyAdmin, så finner du det i artikkelen om. I den nåværende artikkelen er jeg ikke ivrig etter å beskrive den igjen, spesielt siden dagens materiale utelukkende vil bli viet til å importere MySQL-databasen gjennom konsollen.

Men før vi begynner å gjennomgå metoder og verktøy, noen ord om hva import av en MySQL-database er, hvordan det er og hvordan det best gjøres?

Importere en MySQL-database: hva og hvorfor?

Import av en MySQL-database er en operasjon som fyller databasen med informasjon. I dette tilfellet er datakilden en dumpfil - et øyeblikksbilde av en annen database, opprettet automatisk under eksportoperasjonen, eller et spesielt forberedt SQL-skript.

Import, samt eksport av MySQL-databasen, er det to typer informasjon som er lagret i databasen:

  1. strukturen til databasen, dens tabeller og dataene som er lagret i dem (ofte kalt en databasedump);
  2. ganske enkelt data lagret i en tabell eller samlet inn ved hjelp av PLUKKE UT forespørsler.

Denne artikkelen vil diskutere begge alternativene.

For å gjenopprette en MySQL-database med dens struktur og all lagret informasjon fra en dump, som allerede nevnt, trenger du en databasedumpfil, som er en tekstfil med en hvilken som helst utvidelse (kan forhåndspakkes inn i et arkiv for å redusere størrelsen), som inneholder SQL-kommandoer for å lage selve databasen og tabeller, samt fylle dem med informasjon.

Derfor, for å gjenopprette en MySQL-database fra en dump, må du utføre kommandoene i filen.

For vanlig datagjenoppretting er slike komplikasjoner ikke nødvendige. Det er nok å ha en testfil tilgjengelig, hvor informasjonen vil være strukturert på samme måte som i databasetabellen: antall kolonner med informasjon tilsvarer antall tabellpostattributter.

For disse formålene vil en vanlig txt-fil være egnet, dataene som vil bli separert, eller filer opprettet i spesielle regnearkredigerere (Microsoft Office Excel, OpenOffice, etc.) med en utmerket utvidelse: xls, csv, odt, etc.

Disse formatene er til og med å foretrekke, fordi Når du oppretter dem, legges dataskilletegn automatisk til av redaktører, og det er ikke nødvendig å angi dem separat, som i tilfellet med en vanlig tekstfil.

Legge til data til MySQL: Verktøy

Når det gjelder verktøyene for å importere en MySQL-database, kan jeg si at det i dag er tre av dem.

Jeg vil liste dem, fra det laveste nivået og slutter med det høyeste nivået (fra synspunktet om å bruke alle slags skjell og tillegg):

  1. Serverkonsoll og MySQL-kommandolinje;
  2. Skript skrevet på programmeringsspråk som lar deg registrere data i MySQL ved hjelp av språkverktøy;
  3. Ferdige programmer som gir et visuelt grensesnitt for arbeid med databasen (samme phpMyAdmin, MySQL WorkBench, MySQL Manager, etc.).

Jeg tror at rekkefølgen på verktøyene ikke vil reise noen spørsmål for noen, fordi... Programmeringsspråkverktøy fungerer som regel på grunnlag av MySQL-konsollkommandoer, og programmer er basert på skript eller jobber direkte med MySQL fra kommandolinjen.

På en eller annen måte er konsollen i forkant av alt, og de gjenværende verktøyene er faktisk emulatorene.

Derfor, ved å bruke konsollen når du importerer data til MySQL, kan du omgå ulike typer begrensninger satt av innstillingene for programmeringsspråk på webserveren og selve programmene (som forresten ikke alltid er mulig å endre).

På grunn av dette kan du ikke bare laste en MySQL-database gjennom konsollen raskere, men også gjøre denne operasjonen mulig i prinsippet, fordi Skript og programmer har en tendens til å avbryte importen når maksimal skriptutførelsestid er nådd eller ikke starter i det hele tatt på grunn av størrelsen på den nedlastede filen.

Jeg tror alle som noen gang har prøvd å laste opp en stor dump til en MySQL-database via phpMyAdmin forstår hva jeg snakker om.

Ofte er disse grensene årsaken til feil ved import av en MySQL-database, som du aldri vil se når du bruker konsollen.

De er selvfølgelig ikke konstante, og de kan endres, men dette er en ekstra hodepine, som forresten kanskje ikke kan løses for vanlige brukere.

Jeg håper at jeg har motivert deg til å importere MySQL-databasen via konsollen (både strukturen og individuelle data).

Og på dette positive notatet går vi videre til den etterlengtede praksisen og vurderer metoder og kommandoer for konsolloverføring av data til databasen.

Hvordan gjenopprette en MySQL-database fra en dump via konsollen?

Så, for å distribuere en MySQL-dump fra konsollen, er det to måter:

  1. bruke en kommando på MySQL-kommandolinjen;
  2. i selve serverkonsollen.

La oss starte i rekkefølge.

Så, for å importere en MySQL-databasedump til en eksisterende lagring via , må vi først starte den og velge ønsket database som vi skal laste opp dumpen vår til.

Gjennomføringen av disse handlingene er beskrevet i detalj i artikkelen koblet til ovenfor, så hvis du trenger en beskrivelse av dem, ta dem derfra, fordi Jeg vil ikke duplisere dem for andre runde.

Etter at du har gjort det ovenfor, skriv inn følgende kommando i MySQL Shell:

Kildebane_og_dump_filnavn;

Alt som gjenstår for oss er å studere meldingene i konsollen om fremdriften til operasjonene i dumpen.

Uten først å bytte til ønsket database, etter tilkobling til MySQL-serveren i konsollen, kan dumpen importeres med følgende kommando:

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

Det er alt. Det viktigste er å vente til importen er ferdig hvis filen er veldig stor. Fullføringen av dumpen kan bedømmes etter når serverkonsollen er tilgjengelig igjen.

Faktisk er dette ulempen med denne metoden sammenlignet med den forrige, fordi i den første er det mulig å overvåke operasjonene som utføres på databasen under import, men i den andre er det ikke.

Hvis dumpfilen er pakket inn i et arkiv, må den pakkes ut samtidig når den lastes ned.

På Linux kan dette gjøres slik:

Gunzip > [archive_file_name.sql.gz] | mysql -u -s

I Windows er det ikke noe standardverktøy for å pakke ut arkivet i konsollen, så du må installere det i tillegg.

Som du kan se, er import av en MySQL-dump via konsollen en veldig enkel operasjon, som utføres med én kommando. Så du trenger ikke være utvikler for å utføre denne prosedyren.

Hvis du plutselig ikke vet hvordan du starter serverkonsollen, kan du finne denne informasjonen i artikkelen om MySQL-kommandolinjen, lenken som jeg allerede har lagt ut tidligere.

Ved å bruke de beskrevne metodene er det forresten også mulig å importere en MySQL-tabell, og ikke hele databasen. I dette tilfellet må dumpen du laster opp inneholde operasjonene for å opprette den og fylle den med data.

Laste data inn i en MySQL-database fra en fil i konsollen

Vi snakket om å gjenopprette en MySQL-database fra en dump i konsollen. Nå er tiden inne for å finne ut hvordan du kan importere data fra filer, inkludert xls og csv, til en MySQL-database på samme måte.

For denne oppgaven har vi igjen de samme to verktøyene som i forrige tilfelle: MySQL-kommandolinjen og serverkonsollen.

La oss starte anmeldelsen i rekkefølge igjen.

Så for å importere en fil i MySQL-kommandolinjen, kjører vi den på nytt og går til databasen som dataene skal lastes inn i.

LAST DATAINFIL "path_and_name_of_dump_file" INTO TABLE `database_table` KOLONNER AVSLUTTET AV "," OMKLUTTET AV "\"" LINJER AVSLUTTET AV "\n";

Ikke glem det hvis MySQL-serveren ble startet med alternativet --sikker-fil-priv(noe som ofte skjer når du bruker MySQL-distribusjoner inkludert i WAMP/MAMP-sammenstillinger), så må filnavnet spesifiseres under hensyntagen til systemvariabelen secure_file_priv.

For å importere en MySQL-database til serverkonsollen uten å gå inn i MySQL Shell, trenger vi verktøyet mysqlimport, inkludert i MySQL-distribusjonen, og dens følgende kall:

mysqlimport –u brukernavn –p databasenavn navn_og_sti_til_import_fil

Dette verktøyet er en analog av SQL-kommandoen LAST DATAINFIL, kun for kommandolinje. Men spørsmålet oppstår, hvorfor, blant parametrene for samtalen, er ikke tabellen som dataene fra filen skal lastes inn i?

Faktum er at mysqlimport ganske enkelt fysisk ikke har denne parameteren. I stedet må navnet på tabellen som dataene skal lastes inn i, vises i navnet på den importerte filen.

De. hvis du vil importere en Excel-tabell til en MySQL-tabell brukere, så skal filen din kalles users.xls.

Utvidelsen til den importerte filen, som allerede nevnt, kan være hva som helst.

Med mysqlimport kan du også laste inn flere xls- eller csv-filer til MySQL samtidig. For at dataene skal nå målet, må navnene på filene og databasetabellene, som i forrige eksempel, også samsvare.

Hvis plutselig kolonnene i den importerte filen ikke er i samme rekkefølge som kolonnene i databasetabellen, må du bruke alternativet —kolonner i følgende form for å avklare rekkefølgen:

Mysqlimport –u brukernavn –p databasenavn --kolonner kolonne1, kolonne2, ... navn_og_sti_til_import_fil

Naturligvis, i eksemplene mine vurderte jeg ikke hele listen over mysqlimport-parametere, fordi noen av dem er svært spesifikke og brukes ekstremt sjelden i praksis.

Hvis du vil gjøre deg kjent med dem, er deres fullstendige liste tilgjengelig her - https://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html

Funksjoner for å laste data inn i en MySQL-database fra en dump

Hvis du vil at prosessen med å importere en stor MySQL-database skal gå raskere, må du lage en databasedump ved å bruke spesielle alternativer for mysqldump-kommandoen, som jeg skrev om i min forrige artikkel om eksport av en MySQL-database, hvor lenken var postet i teksten tidligere.

Dessverre har ikke MySQL-databaseimportkommandoene i seg selv slike alternativer.

Det eneste er at for å øke hastigheten når du laster en stor databasedump, kan du bruke følgende funksjon.

1. Åpne dumpfilen (helst i filbehandlere, siden vanlige redaktører rett og slett kan bli overveldet av store filer).

2. Skriv følgende linjer i begynnelsen av filen:

SET fremmed_nøkkel_sjekker = 0; SET UNIQUE_CHECKS = 0; SET AUTOCOMMIT = 0;

Merk! De kan allerede være der eller kommentert (mange programmer som lager dumper kan legge dem til automatisk)

3. På slutten av filen skriver vi de omvendte handlingene:

SET fremmed_nøkkel_sjekker = 1; SET UNIQUE_CHECKS = 1; SET AUTOCOMMIT = 1;

Forresten, disse kommandoene vil ikke bare bidra til å fremskynde importprosessen, men også gjøre det mulig.

Faktum er at hvis du noen gang har sett på dumpfilen for å importere en MySQL-database, har du kanskje lagt merke til at operasjonen med å sette strukturen til de lastede tabellene ser slik ut:

DROP TABELL HVIS FINNES `klienter`; LAG TABELL `klienter` (...);

De. det søkes i databasen etter en tabell med samme navn som den som importeres, og hvis en blir funnet, slettes den og opprettes på nytt.

Og hvis plutselig en eksisterende tabell er koblet med fremmednøkler til andre, vil hele lastingen mislykkes.

Derfor er deaktivering av sjekken for eksistensen av fremmednøkler og andre også en utmerket garanti for vellykket gjennomføring av MySQL-databaseimportprosessen.

Funksjoner ved å importere csv til MySQL-database og andre filer

Når du laster data inn i en MySQL-database fra tekstfiler, kan det hende du også må deaktivere fremmednøkler.

Dessuten, i motsetning til den forrige situasjonen, vil det i dette tilfellet ikke være mulig å skrive direktiver inn i filen, fordi SQL-kommandoer i den vil ikke bli akseptert og utført.

I en tidligere artikkel om eksport av en MySQL-database, nevnte jeg allerede hvordan du gjør dette ved å bruke følgende operasjon på MySQL-kommandolinjen:

SET FOREIGN_KEY_CHECKS=0;

Jeg nevnte imidlertid ikke at MySQL-systemvariabelen FOREIGN_KEY_CHECKS har to betydninger: global og sesjonell (for gjeldende økt).

Den globale verdien til MySQL-variabler er gyldig for enhver handling på MySQL-serveren til den startes på nytt. Deretter vil verdiene til variablene bli tilbakestilt og de vil bli tildelt standardverdier.

Sesjonsverdien til MySQL-systemvariabelen angis kun for varigheten av brukerens økt med MySQL-serveren. En økt eller økt begynner når en klient kobler seg til serveren, da den blir tildelt en unik tilkoblings-ID, og slutter når den kobles fra serveren, noe som kan skje når som helst (for eksempel på grunn av et tidsavbrudd).

Hvorfor bestemte jeg meg for å huske dette?

Fordi når jeg utførte kommandoer for å laste en fil inn i en MySQL-database gjennom serverkonsollen, uten å gå inn i MySQL Shell, oppdaget jeg at deaktivering av fremmednøkkelkontroll ved å bruke den tidligere beskrevne metoden ikke fungerer.

Konsollen viste fortsatt en feilmelding forårsaket av tilstedeværelsen av fremmednøkler i tabellen.

Og det oppsto av den grunn at kommandoen ovenfor deaktiverte kontrollen for eksistensen av fremmednøkler i økten, og ikke globalt, som i tillegg til den angitte metoden også kan utføres som følger:

SET SESSION variabelnavn = variabelverdi; SET @@session.variable_name = variabel_verdi; SET @@variabelnavn = variabel_verdi;

I kommandoene ovenfor er variabelen tydelig merket som økt.

Og siden jeg lastet en csv-fil inn i en MySQL-tabell gjennom serverkonsollen, uten en direkte tilkobling til MySQL-serveren, ble det ikke opprettet en økt der min øktvariabelverdi ville fungere.

Jeg endte opp med å sette FOREIGN_KEY_CHECKS til global og importen var vellykket.

Du kan gjøre dette på en av følgende måter:

SET GLOBAL variabel_navn = variabel_verdi; SET @@global.variable_name = variabel_verdi;

Etter å ha endret verdiene, er det en god idé å gå gjennom variabelens verdier for å bekrefte at endringene trådte i kraft. For å vise økt- og globale verdier samtidig, bruk følgende kommando:

VELG @@GLOBAL.foreign_key_checks, @@SESSION.foreign_key_checks;

Dette avslutter dagens artikkel om import av en MySQL-database. Del dine inntrykk og din egen utvikling i kommentarfeltet. Jeg tror at mange vil være interessert i din erfaring.

Ser deg igjen! 🙂

P.S.: hvis du trenger en nettside eller trenger å gjøre endringer på en eksisterende, men det er ikke tid eller lyst til dette, kan jeg tilby mine tjenester.

Mer enn 5 års erfaring profesjonell nettsideutvikling. Jobbe med PHP, OpenCart,

Når du bare begynner å lage en nettside, gjør du det vanligvis på en lokal server. Når den er klar, må den flyttes til en ekstern server. Det er ikke vanskelig å kopiere filer, men her er hvordan importere database til en ekstern server? Sånn ca hvordan importere en database til PHPMyAdmin, vil jeg forklare deg i denne artikkelen.

Det er mange måter database import Jeg skal imidlertid fortelle deg hva jeg synes er den enkleste, og den jeg bruker selv.

Trinn 1

Det første du må gjøre er eksportere database fra din nåværende plassering (spesielt en lokal server). Målet vårt er å få SQL-spørring vår database. For å gjøre dette må du gjøre følgende:

Steg 2

Det andre og siste trinnet er å gjøre SQL-spørring, som du kopierte, til PHPMyAdmin, som ligger på serveren der du trenger importere database. For å gjøre dette, følg disse trinnene:

Som et resultat vil alle tabellene dine med alle poster bli opprettet på den nye serveren.

Som du kan se, prosessen eksportere og importere en database i PHPMyAdmin forenklet til et minimum, så det vil ikke være noen problemer med dette.

Til slutt vil jeg gi deg ett råd til. Faktum er at det veldig ofte er en situasjon når du trenger det ikke importer hele databasen, men for eksempel bare ett bord. Da er prinsippet helt det samme, bare når du eksporterer, må du velge ikke bare databasen, men også tabellen som skal eksporteres. Og så igjen i toppmenyen klikk på " Eksport". Da er alt det samme database import.

Hvis du har sikkerhetskopiert eller eksportert databasen til en SQL-fil, kan du importere den til en av hostingkontoens MySQL-databaser via phpMyAdmin.

Merk. Det skal ikke være en CREATE DATABASE-linje i en MySQL-database. Ellers kan importen mislykkes.

phpMyAdmin-grensesnittet lar deg importere 8 MB data om gangen. Hvis du trenger å importere en større fil, del den i flere 8 MB deler.

Merk følgende! Hvis du importerer en database for administrert WordPress-hosting for å holde nettstedet ditt i orden.

Importere SQL-filer til MySQL-databaser ved hjelp av phpMyAdmin

Etter dette vil SQL-filen kjøre og oppdatere databasen du valgte i SQL-filen. Gjenoppretting av databasen kan ta flere minutter.

Merk. Hvis du mottar feilmeldingen Script timeout passert, hvis du ønsker å fullføre importen, vennligst send inn samme fil på nytt og importen vil fortsette, du kan umiddelbart velge den samme filen og gjenoppta prosessen.

Jeg blir ofte spurt om hvordan man importerer en stor MySQL-database til en server. Det er kjent at phpMyAdmin som standard har begrensninger på størrelsen på den importerte databasen.

Hvis dumpen din ikke er mye større enn den akseptable grensen, kan du dele den opp i flere deler og importere den i flere omganger. Dette er berettiget hvis for eksempel grensen er 2 MB, og databasen din er 5-10 MB stor. Det er klart at å "kutte" en 100 MB database i 50 deler er en ganske arbeidskrevende og tidkrevende prosess.

Det er flere alternativer for å løse dette problemet.

Redigering av webserverkonfigurasjonen

På VDS/VPS er det ingen spesielle problemer med dette, du trenger bare å korrigere konfigurasjonen. Øk nemlig de tillatte maksimalverdiene for filer lastet opp til serveren i php.ini, den maksimale størrelsen for filer som overføres ved hjelp av POST-metoden:

Post_max_size = 2000M upload_max_filesize = 2000M

I tillegg, hvis databasen din er veldig stor, bør du øke den maksimalt tillatte skriptkjøringstiden.

Max_execution_time = 32000 max_input_time = 32000

Og bare i tilfelle, kan du øke størrelsen på den tillatte mengden RAM:

Memory_limit = 512M

Etter å ha gjort endringer, sørg for å starte webserveren på nytt.

Det er klart at denne metoden ikke er egnet for virtuell hosting, fordi... det innebærer ikke muligheten til å redigere konfigurasjoner.

Sypex dumper

Du kan bruke tredjepartsprogramvare. Og den første applikasjonen det er verdt å ta hensyn til er Sypex Dumper.

Etter å ha brukt den for første gang for mange år siden og setter pris på alle dens muligheter og fordeler, kan jeg trygt merke den som "må ha". Sypex Dumper er en PHP-serverapplikasjon som ikke krever installasjon. Det er nok å kopiere det, for eksempel til roten av nettstedet ditt i sxd-katalogen og kalle det i nettleseren: http://Your_Site/sxd/. Det er verdt å merke seg at du først bør plassere en dump av databasen i sikkerhetskopikatalogen. Etter initialisering av skriptet vil du se autorisasjonspanelet for tilkobling til databasen. Skriv inn brukernavn og passord. Vert og port er valgfritt bare hvis de er spesifikke.

Etter autorisasjon kan du gå direkte til import av databasen. I felt "Database" databasen du er koblet til vil bli valgt, og i feltet "Fil" du vil se dumpen du tidligere lastet opp til Backup-katalogen.

I de fleste tilfeller er det ikke nødvendig med ytterligere innstillinger lenger, og du kan trygt starte importen ved å klikke på "Kjør"-knappen. Import, avhengig av størrelsen på databasen og Internett-tilkoblingshastigheten, kan ta litt tid. Under import kan du se hvilke tabeller som for øyeblikket importeres til databasen. Når skriptet er fullført, vil du se utførelsesloggen. Det ser omtrent slik ut:

Det er alt, faktisk - databasen er importert!

Konsoll

Vi vil ikke vurdere å importere gjennom konsollen. Jeg tror folk som bruker konsollen uten meg vet hvordan de importerer en database. Men det er bedre for vanlige brukere uten spesiell opplæring å ikke gå dit. Siden utføring av noen kommandoer kan føre til alvorlige konsekvenser, inkludert fullstendig krasj av serveren.

Endelig

Jeg antar ikke å si at Sydex Dumper er den eneste og riktige løsningen. Det er andre mer elegante metoder som krever at brukeren har viss kunnskap og passende tilgang til serverinnstillingene.

Men i et delt vertsmiljø vil Sydex Dumper absolutt være din uunnværlige assistent.

Abonner på mitt telegram og bli den første til å motta nytt materiale, inkludert de som ikke er på siden.

Hilsen, venner! 🙂

I dag bestemte jeg meg for å fortsette samtalen om å jobbe med MySQL i konsollen og ta hensyn til prosedyren for å eksportere en MySQL-database.

I denne artikkelen vil jeg snakke om hvordan du dumper en MySQL-database, samt laster opp data fra MySQL til en Excel-fil og csv-format.

Vi vil se på ulike alternativer for å hente informasjon fra: opprette en dump av en eller flere databaser, eksportere data fra individuelle tabeller og vilkårlige resultater PLUKKE UT forespørsler.

Vi vil også snakke om hvordan du sender ut data fra en MySQL-database i serverkonsollen og MySQL-kommandolinjen.

I denne artikkelen vil jeg ikke snakke om hvordan du eksporterer data ved hjelp av phpMyAdmin og andre visuelle verktøy.

For det første fordi det allerede er nok materiale om dette emnet på Internett. Dessuten er det materiale av høy kvalitet, som jeg ikke er ivrig etter å kopiere og lime inn.

Og for det andre gjennomgikk jeg selv kort prosessen med å sende ut informasjon fra en MySQL-database til en SQL-fil i en av artiklene mine, der jeg snakket om .

Så hvis du ikke er en profesjonell utvikler eller systemadministrator som kan ha nytte av informasjon om arbeid med konsollen, og du bare kom for instruksjoner om hvordan du eksporterer databasen til phpMyAdmin, kan du begrense deg til å lese informasjonen på lenken ovenfor .

Jeg vil at du skal forstå meg riktig: Jeg ønsker ikke å fornærme deg på noen måte, men jeg vil bare at du skal bruke tiden din med maksimal nytte for virksomheten din og få det du leter etter.

Dette avslutter den innledende delen, og vi går videre til en gjennomgang av konsollkommandoer for å lage en MySQL-databasedump, som jeg bestemte meg for å sortere etter mengden data som lagres: starter fra eksport av hele databasen, slutter med individuelle tabeller og resultatene av vilkårlige spørsmål.

Opprette en MySQL-databasedump via konsollen

Jeg vil gjøre en liten presisering helt i begynnelsen.

Databasedump er en fil med et sett med SQL-kommandoer, som, når den er lansert, lar deg lage databaser og tabeller, samt fylle dem med informasjon. Dumpen er nødvendig for de som ønsker å laste ned en MySQL-database for å kopiere den til en annen server eller i en eksisterende.

Dessuten, hvis noen ikke er klar over det, er en sikkerhetskopi av en MySQL-database i hovedsak en dump av den laget på en viss tid, som lar deg gjenopprette strukturen og dataene til databasen om nødvendig.

Eksporter data- dette er ganske enkelt å trekke ut informasjon fra tabeller i tekstform for videre arbeid med tekst eller grafiske redaktører.

Derfor vil kommandoene for disse handlingene være litt forskjellige.

For å lage en databasedump har MySQL et innebygd verktøy kalt mysqldump, som må brukes utenfor MySQL-kommandolinjen i serverkonsollen eller annet skall.

Så for det enkleste og vanligste alternativet - eksport av data fra en spesifikk database i MySQL-konsollen for å overføre den til en annen server eller intern kopiering, må du kjøre følgende kommando:

Mysqldump -u brukernavn -p databasenavn > bane_og_dump_filnavn

Dette verktøyet kan lage MySQL-databasedumper bare i form av filer med SQL-kommandoer, så uansett hvilken utvidelse du velger for filen din, vil innholdet være det samme uansett. Og ikke glem å sjekke skrivetillatelsene til katalogen den skal ligge i før du eksporterer informasjon fra MySQL slik at filen kan opprettes.

Hvis du plutselig trenger å lage en dump av alle databasene på serveren, bruk følgende kommandoalternativ:

Mysqldump -u brukernavn -p --all-databases > path_and_dump_file_name

For å dumpe bare noen få spesifikke databaser, må du kalle mysqldump med følgende parametere:

Mysqldump -u brukernavn -p --databaser databasenavn1, databasenavn2, ... > bane_og_dumpfilnavn

Som et resultat vil du i hvert tilfelle motta en MySQL-databasedump som inneholder kommandoer for å lage strukturen til de inneholdte tabellene (felt, deres typer, indekser, nøkler, etc.), samt operasjoner for å fylle dem med data.

Dette alternativet er kun egnet for å gjenopprette og kopiere hele databaser.

Vi vil snakke videre om hvordan du lager sikkerhetskopier av visse MySQL-tabeller og får dataene deres i lesbar form.

Dumping av en MySQL-tabell og eksport av data

For å lage en dump av visse MySQL-databasetabeller, trenger vi det samme verktøyet mysqldump, kalt med følgende parametere:

Mysqldump -u brukernavn -p databasenavn tabellnavn1, tabellnavn2, ... > bane_og_dumpfilnavn

Når du kaller mysqldump, kan du spesifisere de nødvendige tabellene som parameterverdi --tabeller, når parameteren brukes --databaser vil bli ignorert:

Mysqldump -u brukernavn -p --databaser databasenavn1, databasenavn2 --tabeller tabellnavn1, tabellnavn2, ... > bane_og_dumpfilnavn

Eksempelet ovenfor vil vise følgende feil:

Mysqldump: Fikk feil: 1049: Ukjent database "database_name1," når du valgte databasen

Som du kan se, vil kun den siste databasen fra listen bli brukt. I prinsippet er denne oppførselen ganske logisk, fordi De angitte tabellene vises kanskje ikke i alle databaser.

Ok, vi har mottatt en dump av MySQL-databasetabeller. Den kan brukes til å gjenopprette dem eller kopiere dem sammen med strukturen.

Men hva om du bare trenger å få informasjonen lagret i dem, og helst i en lesbar form, slik at du kan sende den til lederen og se den i en vanlig tekst- eller regnearkredigerer? MySQL har også verktøy for dette.

Alternativet til å ringe verktøyet vil hjelpe oss med å oppnå planene våre mysql fra konsollen med visse parametere:

Mysql -u brukernavn -p databasenavn -e "VELG * FRA tabellnavn"

Denne kommandoen vil tillate oss å utføre en spørring til den nødvendige databasen og sende ut resultatet til konsollen uten å gå til MySQL-kommandolinjen.

Vel, for ikke å sende ut data til konsollen, men for å skrive det til en fil, må du supplere kommandoen som følger:

Mysql -u brukernavn -p -e "VELG * FRA tabellnavn" > bane_og_filnavn

Takket være disse konstruksjonene kan vi ikke bare få data lagret i alle felt i tabellen, men også i spesifikke. For å gjøre dette, erstatt bare jokertegnene (*)-symbolet med de nødvendige, atskilt med komma.

Som et resultat vil utgangen være en vanlig tekstfil som vil inneholde navnene på feltene i form av en overskrift og informasjon om dem for alle poster. Den kan åpnes i en vanlig tekstredigerer, uansett hvilken oppløsning du gir den når du oppretter den.

Hvis du vil eksportere data fra en MySQL-database i xls- eller csv-format slik at den resulterende filen vises riktig i regnearkredigering, så vil vi fortelle deg hvordan du gjør dette litt senere :)

Lage sikkerhetskopier og sende ut data fra en MySQL-database ved hjelp av spørringer

Vi snakket om hvordan du dumper en MySQL-database - en eller flere, så vel som individuelle tabeller. Men noen ganger i praksis er det tilfeller når du trenger å eksportere et datasett som ikke er begrenset til én tabell. Eller du trenger bare å velge noen data fra tabellen.

Utviklere av bedriftsprosjekter møter spesielt ofte dette når ledere ber dem om å gi alle slags statistiske data. Eller når du trenger å sikkerhetskopiere en bestemt del av tabellen for raskt å gjenopprette den.

For sikkerhetskopiering trenger vi det samme verktøyet mysqldump, som må kalles slik:

Mysqldump -u brukernavn -p databasenavn tabellnavn --der "oppslag" > bane_og_dump_filnavn

Som et resultat vil vi motta en fil med SQL-kommandoer for å lage en tabell med hele strukturen, som etter opprettelsen vil bli fylt med informasjon valgt ved hjelp av en oppslagsspørring.

Hvis vi bare trenger å få dataene lagret i en eller flere tabeller, må vi endre kommandoen som ble brukt i forrige tilfelle når vi henter alle dataene i tabellen, bare med noen avklaringer:

Mysql -u brukernavn -p -e "VELG * FRA tabellnavn WHERE-oppslag" > bane_og_filnavn

Som du forstår, i tillegg til de ulike presiseringene som er spesifisert i forespørselen ved hjelp av direktivet HVOR, kan du bruke andre SQL-konstruksjoner: BLI MED, UNION etc.

Du kan samle inn hvilken som helst statistikk du vil :)

Den samme handlingen kan også utføres når du arbeider på MySQL-kommandolinjen ved å bruke følgende kommando:

VELG * FRA database_table WHERE oppslag INTO UTFIL "path_and_file_name";

Denne kommandoen er spesielt utviklet for å lage filer med prøveresultater. Dessuten kan resultatene ikke bare eksporteres til filer, men også skrives til variabler, og utdataene kan formateres på forskjellige måter.

Hvis det ovennevnte er ditt tilfelle, kan du finne en fullstendig liste over parametere og alternativer for å kalle denne kommandoen her - https://dev.mysql.com/doc/refman/5.7/en/select-into.html

For å avslutte min korte ekskursjon til mysqldump, vil jeg gi en variant av å kalle en kommando med en liste over parametere for å lage en optimalisert dump av MySQL-databasen og tabeller, gjenopprette databasen og individuelle tabeller som vil ta kortere tid enn med en vanlig samtale:

mysqldump -u brukernavn -h host_or_IP_MySQL_server -p --no-autocommit --opt database_name > path_and_dump_file_name;

For eksperimentets skyld brukte jeg dette alternativet til å dumpe en MySQL-database på 143 MB i størrelse. Den påfølgende gjenopprettingen tok 59 sekunder sammenlignet med 1 minutt og 3 sekunder da databasen ble gjenopprettet fra en dump laget ved å kalle mysqldump uten spesielle parametere.

Jeg er enig i at dette er en liten ting. Men dette er bare når det gjelder en gitt mengde data. Hvis du bruker denne teknikken når du oppretter en dump større enn 1 GB, vil forskjellen være større.

Hvis du støter på en slik situasjon, ikke glem å først arkivere MySQL-databasedumpen. Det beste er tar.gz. Da vil restitusjonen ta enda kortere tid.

Eksporter data fra MySQL til Excel og csv-filer

Det var ikke for ingenting at jeg kombinerte informasjon om utdata fra MySQL til disse to formatene i en blokk, fordi... de er veldig like, de brukes på omtrent samme måte (for å strukturere informasjon i form av tabeller) og de samme kommandoene for eksport vil bli kalt.

Som du vet, er den eneste vesentlige forskjellen mellom disse formatene at xls- og xlsx-utvidelsene har filer opprettet i Microsoft Office Excel, som bare fungerer under Windows, mens csv-filer er mer universelle og operasjoner med dem er mulig i mange redaktører.

Dette betyr ikke at xls ikke åpnes andre steder enn Microsoft Office Excel. Den samme OpenOffice bekrefter det motsatte.

Men for at dette skal være mulig, må denne støtten finnes i programvareproduktet. csv-filer er lesbare selv i et vanlig tekstredigeringsprogram som Notepad, men dette skjemaet vil ikke være fullt lesbart.

La meg begynne med det faktum at du bare kan eksportere resultatene av SQL-spørringer til xls eller csv, som vi lærte å jobbe med tidligere, fordi det vil være umulig å sende ut hele databasen til én fil i én operasjon.

For det første er ikke dette optimalt, fordi... en slik fil åpnes neppe hvis det er store mengder informasjon lagret i databasen. Og for det andre er det ikke klart hvordan du deler informasjonen inne i filen i tabeller og felt.

Nei, det er selvfølgelig mulig å gjøre dette, men det er usannsynlig at det gjøres med én kommando og generelt er det lite sannsynlig at noen vil gjøre dette i konsollen. Jeg tror at for disse formålene trenger du spesiell programvare eller i det minste et skript.

Hvis du plutselig vet hvordan du kan eksportere informasjon fra hele MySQL-databasen til en eller flere xls-filer i konsollen samtidig, så skriv om det i kommentarfeltet. Jeg tror det vil være nyttig for mange å lese om dette.

Så hvis vi snakker om hvordan du eksporterer data fra MySQL til xls og csv, så kan dette gjøres direkte i serverkonsollen gjennom verktøyet mysql eller i arbeidet som jeg introduserte deg med i min forrige artikkel.

La oss starte i rekkefølge.

Du kan eksportere data fra en MySQL-database til csv- og xls-formater direkte i serverkonsollen ved å bruke følgende kommandoer.

På Linux systemer:

Mysql -u brukernavn -d databasenavn -p -e "VELG * FRA databasetabell;" | sed "s/"/\"/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > bane_og_filnavn. csv

I prinsippet, hvis det er absolutt nødvendig, kan du bruke denne kommandoen til å eksportere MySQL-data til en Excel-fil. Men for å være ærlig, jeg har ikke taklet dette i praksis, og jeg aner ikke hva som vil komme ut til slutt, fordi... Jeg jobber for tiden på Windows. Hvis du bruker denne kommandoen under Linux, vennligst skriv i kommentarene om resultatene av arbeidet ditt. Jeg tror informasjonen vil være av interesse for alle.

På Windows:

Dessverre vil ikke eksport av data fra MySQL-tabeller til csv ved hjelp av kommandoen ovenfor fungere i dette tilfellet, fordi Windows, i motsetning til Linux, har ikke en innebygd konsollkommando for å jobbe med tråder, som sed i Linux.

Selvfølgelig kan du installere det, men det er for mye problem. Alternativt kan du bruke CygWin— Linux-konsoll-emulator for Windows-systemer.

Det er bra hvis du allerede har det installert. Ellers vil eksport av data fra MySQL-databasen ved å bruke den valgte metoden gi oss for mye trøbbel.

Men å trekke ut informasjon til en xls-fil er så enkelt som 5 kopek :) Det er veldig enkelt å starte det på følgende måte, som jeg prøvde personlig:

Mysql -u brukernavn -d databasenavn -p -e "VELG * FRA databasetabell;" > bane_og_filnavn.xls

Denne filen åpnet i Microsoft Office Excel uten problemer i det hele tatt. Det eneste er at når du åpner den, ble det vist en melding på skjermen som advarte om at det faktiske formatet til filen som åpnes avviker fra den spesifiserte filtypen.

Men når du bekreftet handlingen, åpnet dokumentet uten problemer - all informasjon ble delt inn i celler i den formen den ble lagret i selve tabellen.

Jeg vet ikke, kanskje hvis du utfører noen spesifikke handlinger i Microsoft Office Excel, vil det oppstå problemer i fremtiden; jeg har ikke gravd så dypt. Da jeg så gjennom dataene på vanlig måte, så jeg i det minste ikke noe uvanlig.

Hvis du støter på problemer mens du bruker xls-filen eksportert fra MySQL, enten i dette programmet eller i andre, vennligst gi meg beskjed i kommentarfeltet.

Ved å bruke metoden beskrevet ovenfor kan du i prinsippet eksportere innholdet i en MySQL-database til en csv-fil. Men da vil dataene fra ulike felt i tabellen bli skrevet massevis, uten skilletegn, som kanskje ikke vises godt i ulike programmer for arbeid med tabeller, som vanligvis fungerer med csv-filer.

OpenOffice bryr seg forresten ikke :) Det avgrenset automatisk informasjonen som ble oppnådd på måten vi eksporterte innholdet i MySQL-databasen til xls. Jeg vet ikke hvordan han gjør det, men jeg anbefaler å bruke det :)

Vel, den samme Microsoft Office Excel viste all informasjon som tilsvarer én post i tabellen, og skrev den i én celle uten noen skilletegn. Jeg tror andre tabellredaktører vil gjøre det samme.

Derfor, når du eksporterer en MySQL-database til csv-filer, må du gjøre dette ved å skille informasjonen med spesialtegn som forstås av redaktører.

Og så nærmet jeg meg gradvis den andre metoden for å eksportere MySQL-data til csv og xls, som er å bruke MySQL-kommandolinjen.

Så for å eksportere MySQL-data til en csv-fil på denne måten, trenger vi følgende kommando:

VELG * FRA database_table INTO OUTFILE "path_and_file_name.csv" FELT AVSLUTTET AV "," OMKLUTTET AV """ LINJER AVSLUTTET AV "\n";

Som et resultat av kjøringen vil du motta en csv-fil i banen du spesifiserte da du ringte, som vil åpnes riktig i de fleste moderne regnearkredigerere. Bare i tilfelle minner jeg deg om at du bare må kjøre denne kommandoen etter å ha koblet til MySQL-databasen.

Denne kommandoen er også flott for å eksportere MySQL-data til en xls-fil for riktig visning i Microsoft Office Excel. Bare i dette tilfellet trenger vi ikke separatorer, fordi de vil forstyrre å dele informasjon i celler:

SELECT * FRA database_table INTO OUTFILE "path_and_file_name.xls";

Men i praksis er ikke alt så enkelt som jeg beskrev. Mens du kjører kommandoen, kan du støte på følgende feil i konsollen som hindrer eksporten i å fullføre:

FEIL 1290 (HY000): MySQL-serveren kjører med --secure-file-priv-alternativet, så den kan ikke utføre denne setningen

Det er forårsaket av det faktum at MySQL-serveren ble startet med alternativet --sikker-fil-priv. Personlig møtte jeg dette problemet på grunn av det faktum at for å jobbe i konsollen bruker jeg MySQL-distribusjonen som er inkludert i WAMP OpenServer-settet, som igjen starter MySQL-serveren på denne måten.

Det er to måter å løse problemet på:

  • Endre MySQL-serverens oppstartsparametere
  • Endre banen til den endelige MySQL-eksportfilen

Den første metoden virket for komplisert for meg, fordi... Jeg måtte fordype meg i OpenServer-konfigurasjonen, som ikke ble skrevet av meg med alle de påfølgende omstendighetene 🙂 Derfor bestemte jeg meg for å ta den andre veien. Hvis du støter på et lignende problem, gjenta etter meg.

Først må du gå til MySQL-kommandolinjen og kjøre en av følgende kommandoer:

VIS VARIABLER SOM "secure_file_priv"; VELG @@GLOBAL.secure_file_priv;

Resultatet av å utføre begge vil være verdien av den globale MySQL-variabelen secure_file_priv, som inneholder banen til katalogen der MySQL-dataeksport og -import kan utføres (i fremtiden en lenke til en artikkel om dataimport).

De. når du bruker kommandoer LAST DATA Og VELG... I UTFIL eksporterte og importerte filer kan bare finnes i denne katalogen.

I mitt tilfelle ble denne variabelen generelt satt til NULL, fordi Jeg, som jeg allerede sa, bruker MySQL-verktøy fra distribusjonen som er inkludert i OpenServer for å jobbe i konsollen. Denne verdien indikerte at MySQL-dataeksport- og importoperasjonene ved bruk av de angitte kommandoene var fullstendig lukket.

Som det viste seg senere, er dette en vanlig situasjon når du bruker boksede WAMP- og MAMP-servere.

Dessverre var det i mitt tilfelle ikke mulig å bruke de vanlige metodene for å endre verdiene til MySQL globale variabler:

SET variabel_navn = verdi;

Som et resultat så jeg bare følgende feil i konsollen:

FEIL 1238 (HY000) på linje 1: Variabel "secure_file_priv" er en skrivebeskyttet variabel.

Som et resultat, for å endre verdien av en variabel secure_file_priv og åpne eksport- og importoperasjonene, måtte jeg gå inn i MySQL-konfigurasjonsfilen mysql.ini, som ligger i rotkatalogen til MySQL-distribusjonen, eller kan nås på en annen måte hvis MySQL er inkludert med WAMP/LAMP/ MAMP-serverbygging.

Forresten, hvis du vil endre banen til filutvekslingsspolkatalogen, må du gjøre det samme.

I mitt tilfelle eksisterte denne variabelen allerede i konfigurasjonen, bare i kommentert form:

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

Hvis du ikke har det, så skriv det fra bunnen av i delen (i hvert fall for meg lå den der).

Jeg fjernet det og bestemte meg for å bruke det i den formen det ble skrevet i. De. når jeg eksporterer data fra MySQL og importerer dem tilbake, vil filene mine nå lagres i en katalog c:\openserver\brukerdata\temp\.

Etter å ha endret konfigurasjonen (hvilken som helst), ikke glem å starte serveren din på nytt eller en separat tjeneste hvis innstillinger du har redigert, hvis mulig, for at endringene skal tre i kraft!

For å være sikker, etter å ha startet MySQL-serveren på nytt, vis variabelen på nytt secure_file_priv og kopier verdien til utklippstavlen.

Og nå må vi kalle kommandoen som i begynnelsen, bare før navnet på filen som informasjon fra MySQL-databasen vil bli lagret i, skriv banen som er lagret i variabelen vi endrer i følgende form:

VELG * FRA database_table INTO OUTFILE "secure_file_priv_value\file_name.csv";

Etter dette fungerte eksport av data fra MySQL i mitt tilfelle.

Viktig poeng! Hvis du jobber med MySQL under Windows, så ikke glem å endre "\" til "/" når du spesifiserer banen til filen, ellers vil feilen bli --sikker-fil-priv vil fortsatt vises.

Dette avslutter artikkelen om hvordan du dumper en MySQL-database og dens tabeller, samt utdata fra MySQL-tabeller til forskjellige formater. Skriv anmeldelsene dine i kommentarfeltet og del med alle skriptalternativene du bruker oftest i praksis.

Hvis du likte artikkelen, kan du takke forfatteren ved å legge ut artikkelen på nytt på sosiale nettverk eller økonomisk bruke skjemaet nedenfor slik at du kan betale for grunnleggende hosting.

Lykke til alle sammen og på gjensyn! 🙂

P.S.: hvis du trenger en nettside eller trenger å gjøre endringer på en eksisterende, men det er ikke tid eller lyst til dette, kan jeg tilby mine tjenester.

Mer enn 5 års erfaring profesjonell nettsideutvikling. Jobbe med PHP