Accesso MySQL negato - Come recuperare il file dump da mysqldump? Backup e ripristino del database MySQL (MariaDB) con mysqldump.

Saluti, amici! 🙂

Oggi ho deciso di continuare la conversazione su come lavorare con MySQL nella console e di prestare attenzione alla procedura di esportazione del database Dati MySQL.

In questo articolo parlerò di come eseguire il dump di un database MySQL e di caricare dati da MySQL a file Excel e formato CSV.

Considereremo varie opzioni campionamento di informazioni da: creazione di un dump di uno o più database, esportazione di dati da singole tabelle e risultati arbitrari SELEZIONARE richieste.

Parleremo anche di come generare dati da un database MySQL nella console del server e nella riga di comando MySQL.

In questo articolo non parlerò di come esportare dati utilizzando phpMyAdmin e altri strumenti visivi.

Innanzitutto perché su Internet c'è già abbastanza materiale su questo argomento. Inoltre materiale di qualità, che non ho voglia di copiare e incollare.

E, in secondo luogo, io stesso ho brevemente esaminato il processo di output delle informazioni da un database MySQL a un file SQL in uno dei miei articoli, in cui ho parlato di .

Quindi, a meno che tu non sia uno sviluppatore professionista o amministratore di sistema, che potrebbe trovare utili informazioni su come lavorare con la console e sei venuto solo per istruzioni su come esportare il database su phpMyAdmin, puoi limitarti a leggere le informazioni al link sopra.

Voglio che tu mi capisca bene: non voglio offenderti in nessun modo, ma voglio solo che tu trascorra il tuo tempo con massimo beneficio per il lavoro e hanno ottenuto quello che stavano cercando.

Con questo si conclude la parte introduttiva e si passa al ripasso dei comandi da console per la creazione di un dump del database MySQL, che ho deciso di ordinare in base alla quantità di dati da salvare: partendo dall'esportazione dell'intero database, per finire con le singole tabelle e i risultati di domande arbitrarie.

Creazione di un dump del database MySQL tramite la console

Vorrei fare una piccola precisazione fin dall'inizio.

Dump del databaseè un file con una serie di comandi SQL che, una volta avviato, consente di creare database e tabelle, nonché di riempirli di informazioni. Il dump è necessario per chi vuole scaricare un database MySQL per copiarlo su un altro server o all'interno di uno già esistente.

Inoltre, se qualcuno non lo sa, un backup del database MySQL è, essenzialmente, un dump dello stesso certo periodo tempo, che consente di ripristinare la struttura e i dati del database, se necessario.

Esporta dati sta semplicemente estraendo informazioni dalle tabelle in forma di testo Per ulteriori lavori con editor di testo o grafici.

Pertanto, i comandi per queste azioni saranno leggermente diversi.

Per creare un dump del database, MySQL dispone di un'utilità integrata chiamata mysqldump, che deve essere utilizzato all'esterno riga di comando MySQL nella console del server o in un'altra shell.

Quindi, per l'opzione più semplice e più comune: esportare i dati da un database specifico a Console MySQL Per trasferirlo su un altro server o copia interna, è necessario eseguire il seguente comando:

Mysqldump -u nomeutente -p nome_database > nome_percorso_e_dump_file

Questa utility può creare dump del database MySQL solo sotto forma di file con comandi SQL, quindi non importa quale estensione scegli per il tuo file, il suo contenuto sarà comunque lo stesso. E non dimenticare di controllare i permessi di scrittura della directory in cui si troverà prima di esportare le informazioni da MySQL in modo che il file possa essere creato.

Se all'improvviso è necessario eseguire un dump di tutti i database sul server, utilizzare la seguente opzione di comando:

Mysqldump -u nomeutente -p --all-databases > nome_percorso_e_dump_file

Per eseguire il dump solo di alcuni database specifici, dovrai chiamare mysqldump con i seguenti parametri:

Mysqldump -u nomeutente -p --databases nome_database1, nome_database2, ... > nome_percorso_e_file_dump

Di conseguenza, in ogni caso riceverai un dump del database MySQL contenente i comandi per creare la struttura delle tabelle contenute (campi, i loro tipi, indici, chiavi, ecc.), nonché le operazioni per riempirle con i dati.

Questa opzione è adatta solo per ripristinare e copiare interi database.

Come eseguire il backup di alcune tabelle MySQL e ricevere i relativi dati forma leggibile, ne parleremo ulteriormente.

Dumping di una tabella MySQL ed esportazione dei dati

Per creare un dump di alcune tabelle del database MySQL, avremo bisogno della stessa utility mysqldump, chiamato con i seguenti parametri:

Mysqldump -u nome utente -p nome_database nome_tabella1, nome_tabella2, ... > nome_percorso_e_file_dump

Quando chiami mysqldump, puoi specificare le tabelle richieste come valore del parametro --tabelle, quando viene utilizzato il parametro --banche dati verrà ignorato:

Mysqldump -u nome utente -p --databases nome_database1, nome_database2 --tables nome_tabella1, nome_tabella2, ... > nome_percorso_e_file_dump

L'esempio precedente visualizzerà il seguente errore:

Mysqldump: errore ricevuto: 1049: database sconosciuto "database_name1" quando si seleziona il database

Come puoi vedere, verrà utilizzato solo il database più recente dall'elenco. In linea di principio, questo comportamento è abbastanza logico, perché Le tabelle specificate potrebbero non essere visualizzate in tutti i database.

Ok, abbiamo ricevuto un dump delle tabelle del database MySQL. Può essere utilizzato per ripristinarli o copiarli insieme alla struttura.

Ma cosa succede se hai solo bisogno di memorizzare le informazioni al loro interno e, preferibilmente, in una forma leggibile in modo da poterle inviare al gestore e visualizzarle in un normale editor di testo o foglio di calcolo? MySQL dispone di strumenti anche per questo.

L'opzione di chiamare l'utilità ci aiuterà a realizzare i nostri piani mysql dalla console con alcuni parametri:

Mysql -u nomeutente -p nome_database -e "SELECT * FROM nome_tabella"

Questo comando ci consentirà di eseguire una query sul database richiesto e di inviare il risultato alla console senza passare alla riga di comando MySQL.

Bene, per non inviare dati alla console, ma per scriverli su un file, è necessario integrare il comando come segue:

Mysql -u nomeutente -p -e "SELECT * FROM nometabella" > percorso_e_nomefile

Grazie a queste costruzioni possiamo ottenere non solo dati memorizzati in tutti i campi della tabella, ma anche in campi specifici. Per fare ciò basta sostituire il simbolo dei caratteri jolly (*) con quelli obbligatori, separati da virgole.

Di conseguenza, il nostro output sarà il solito file di testo, che conterrà i nomi dei campi sotto forma di intestazione e le informazioni su di essi per tutti i record. Può essere aperto normalmente editor di testo, indipendentemente dalla risoluzione che gli dai durante la creazione.

Se desideri esportare dati da un database MySQL a xls o formato csv in modo che il file risultante venga visualizzato correttamente negli editor di tabelle, parleremo di come farlo un po' più tardi :)

Creazione di backup ed output di dati da un database MySQL utilizzando query

Abbiamo parlato di come eseguire il dump di un database MySQL: uno o più, nonché singole tabelle. Ma a volte nella pratica ci sono casi in cui è necessario esportare un set di dati che non è limitato a una tabella. Oppure devi selezionare solo alcuni dati dalla tabella.

Gli sviluppatori di progetti aziendali si trovano spesso di fronte a questo problema quando i manager chiedono loro di fornire tutti i tipi di dati statistici. O quando è necessario eseguire il backup di una determinata parte della tabella per ripristinarla rapidamente.

Per il backup avremo bisogno della stessa utility mysqldump, che dovrà chiamarsi così:

Mysqldump -u nome_utente -p nome_database nome_tabella --where "ricerca" > nome_percorso_e_dump_file

Di conseguenza, riceveremo un file con i comandi SQL per creare una tabella con tutta la sua struttura, che, dopo la creazione, verrà riempita con le informazioni selezionate tramite una query di ricerca.

Se abbiamo solo bisogno di ottenere i dati memorizzati in una o più tabelle, allora dovremo modificare il comando utilizzato nel caso precedente per recuperare tutti i dati nella tabella, solo con alcune precisazioni:

Mysql -u nome_utente -p -e "SELECT * FROM nome_tabella DOVE ricerca" > nome_percorso_e_nome_file

Come avete capito, oltre ai vari chiarimenti specificati nella richiesta, si avvale della direttiva DOVE, puoi utilizzare altri costrutti SQL: GIUNTURA, UNIONE eccetera.

Puoi raccogliere tutte le statistiche che desideri :)

La stessa azione può essere eseguita anche quando si lavora sulla riga di comando MySQL utilizzando il seguente comando:

SELECT * FROM tabella_database DOVE cerca INTO OUTFILE "percorso_e_nome_file";

Questo comando è specificamente progettato per creare file con risultati di campionamento. Inoltre, i risultati non solo possono essere esportati su file, ma anche scritti su variabili, e i dati di output possono essere formattati in vari modi.

Se quanto sopra è il tuo caso, allora lista completa Puoi trovare i parametri e le opzioni per chiamare questo comando qui: https://dev.mysql.com/doc/refman/5.7/en/select-into.html

Alla fine del suo breve escursione per mysqldump vorrei dare un'opzione per chiamare un comando con un elenco di parametri per creare un dump ottimizzato del database e delle tabelle MySQL, ripristinando il database e le singole tabelle da cui richiederà meno tempo rispetto a una chiamata normale:

Mysqldump -u nome utente -h host_o_IP_server_MySQL -p --no-autocommit --opt nome_database > percorso_e_nome_file_dump;

Per motivi di esperimento ho usato questa opzione per scaricare un database MySQL di 143 MB. Il successivo ripristino ha richiesto 59 secondi rispetto a 1 minuto e 3 secondi quando il database è stato ripristinato da un dump effettuato chiamando mysqldump senza parametri speciali.

Sono d'accordo che questa è una piccola cosa. Ma questo è solo nel caso dato volume dati. Se utilizzi questa tecnica quando crei un dump più grande di 1 GB, la differenza sarà più significativa.

Se ti imbatti in una situazione del genere, non dimenticare di archiviare prima il dump del database MySQL. Il migliore è tar.gz. Quindi il recupero richiederà ancora meno tempo.

Esporta dati da MySQL a Excel e file CSV

Non per niente ho unito le informazioni sull'output di informazioni da MySQL in questi due formati in un unico blocco, perché... sono molto simili, vengono utilizzati più o meno allo stesso modo (per strutturare le informazioni sotto forma di tabelle) e verranno chiamati gli stessi comandi per l'esportazione.

Come sai, l'unica differenza significativa tra questi formati è questa xls e xlsx hanno file creati nel programma Microsoft Office Excel, che funziona solo su Windows, mentre i file CSV sono più universali e le operazioni con essi sono possibili in molti editor.

Ciò non significa che xls sia disponibile ovunque tranne che in Microsoft Ufficio Excel, non si aprirà. Lo stesso OpenOffice conferma il contrario.

Ma per una tale possibilità questo supporto deve essere presente in prodotto software. I file CSV sono leggibili anche in un normale editor di testo come Blocco note, ma questo formato non sarà interamente leggibile.

Vorrei iniziare con il fatto che solo i risultati possono essere esportati in xls o csv query SQL, con cui abbiamo imparato a lavorare in precedenza, perché sarà impossibile generare l'intero database in un unico file in un'unica operazione.

Innanzitutto, questo non è ottimale, perché... è improbabile che un file di questo tipo si apra se nel database è memorizzato un grande volume di informazioni. E, in secondo luogo, non è chiaro come suddividere le informazioni all'interno del file in tabelle e campi.

No, ovviamente è possibile farlo, ma è improbabile che ciò venga fatto con un comando e in generale è improbabile che qualcuno lo faccia nella console. Penso che per questi scopi avrai bisogno di un software speciale o almeno di uno script.

Se all'improvviso sai come puoi esportare informazioni dall'intero database MySQL a uno o più file xls ov nella console, quindi scrivilo nei commenti. Penso che leggere questo argomento sarà utile a molti.

Quindi, se stiamo parlando di come esportare dati da MySQL a xls e csv, questo può essere fatto direttamente nella console del server tramite l'utilità mysql oppure nell'opera con cui vi ho presentato nel mio precedente articolo.

Cominciamo in ordine.

Esporta i dati dal database MySQL in CSV e formati xlsÈ possibile utilizzare i seguenti comandi direttamente nella console del server.

SU Linux sistemi:

Mysql -u nomeutente -d nome_database -p -e "SELECT * FROM tabella_database;" | sed "s/"/\"/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > percorso_e_nome_file. csv

In linea di principio, se assolutamente necessario, è possibile utilizzare questo comando per esportare i dati MySQL in un file Excel. Ma, a dire il vero, non ho affrontato questo problema in pratica e non ho idea di cosa verrà fuori alla fine, perché... Attualmente sto lavorando su Windows. Se usi questo comando sotto Linux, scrivi nei commenti i risultati del tuo lavoro. Penso che le informazioni interesseranno a tutti.

SU finestre:

Esporta dati da Tabelle MySQL in CSV con il comando precedente in in questo caso, purtroppo, non sarà possibile, perché Windows, a differenza di Linux, non ha built-in comando della consolle per lavorare con i thread, che è sed in Linux.

Certo, puoi installarlo, ma è troppo complicato. In alternativa è possibile utilizzare CygWin- emulatore Console Linux per sistemi Windows.

Va bene se lo hai già installato. Altrimenti, esportare i dati dal database MySQL utilizzando il metodo scelto ci creerà troppi problemi.

Ma estrarre le informazioni in un file xls è facile come 5 centesimi :) È molto semplice avviarlo nel seguente modo che ho provato personalmente:

Mysql -u nomeutente -d nome_database -p -e "SELECT * FROM tabella_database;" > percorso_e_nome_file.xls

Ha aperto questa vita in Microsoft Office Excel senza alcun problema. L'unica cosa è che all'apertura, sullo schermo è stato visualizzato un messaggio che avvisava che il formato effettivo del file aperto è diverso dall'estensione specificata.

Ma quando si conferma l'azione, il documento si apre senza difficoltà: tutte le informazioni sono state divise in celle nella forma in cui erano archiviate nella tabella stessa.

Non lo so, forse se esegui azioni specifiche in Microsoft Office Excel, in futuro sorgeranno problemi; non ho approfondito così tanto. Quando si visualizzano i dati normalmente, almeno, non ho visto nulla di insolito.

Se riscontri problemi durante l'utilizzo del file xls esportato da MySQL, sia in questo programma che in altri, faccelo sapere nei commenti.

Utilizzando il metodo sopra descritto è possibile esportare in linea di principio il contenuto di un database MySQL in un file CSV. Ma poi i dati provenienti da diversi campi della tabella verranno scritti in massa, senza delimitatori, il che potrebbe non essere visualizzato bene vari programmi per lavorare con le tabelle, in cui di solito lavorano con file CSV.

OpenOffice, tra l'altro, non se ne preoccupa :) Ha delimitato automaticamente le informazioni ottenute nel modo in cui abbiamo esportato il contenuto del database MySQL in xls. Non so come faccia, ma consiglio di usarlo :)

Ebbene, lo stesso Microsoft Office Excel ha visualizzato tutte le informazioni corrispondenti a un record nella tabella, scrivendole in una cella senza delimitatori. Immagino che In un modo simile Sono in arrivo altri editor di tabelle.

Pertanto, quando si esporta un database MySQL in file CSV, è necessario farlo separando le informazioni personaggi speciali, percepito dagli editori.

E poi mi sono avvicinato gradualmente al secondo metodo Esportazione MySQL dati in csv e xls, che implica l'utilizzo della riga di comando MySQL.

Quindi, per esportare i dati MySQL in un file CSV in questo modo, abbiamo bisogno del seguente comando:

SELECT * FROM database_table INTO OUTFILE "path_and_file_name.csv" CAMPI TERMINATI DA "," CHIUSI DA """ RIGHE TERMINATE DA "\n";

Come risultato della sua esecuzione, riceverai un file CSV nel percorso specificato durante la chiamata, che si aprirà correttamente nella maggior parte dei moderni editor di fogli di calcolo. Per ogni evenienza, ti ricordo di scappare questo comando necessario solo dopo la connessione a un database MySQL.

Questo comando è ottimo anche per esportare dati MySQL in un file xls visualizzazione corretta in Microsoft Office Excel. Solo che in questo caso non abbiamo bisogno di separatori, perché interferiranno con la divisione delle informazioni in celle:

SELECT * FROM database_table INTO OUTFILE "percorso_e_nome_file.xls";

Tuttavia, in pratica, non tutto è così semplice come ho descritto. Durante l'esecuzione del comando, potresti riscontrare il seguente errore nella console che impedisce il completamento dell'esportazione:

ERRORE 1290 (HY000): Il Server MySQL sta correndo con il--secure-file-priv in modo che non possa eseguire questa istruzione

È causato dal fatto che il tuo server MySQL è stato avviato con l'opzione --secure-file-priv. Personalmente ho riscontrato questo problema perché per lavorare in console utilizzo il kit di distribuzione MySQL incluso nel kit WAMP OpenServer, che a sua volta avvia in questo modo il server MySQL.

Esistono due modi per risolvere il problema:

• Modifica i parametri di avvio del server MySQL
• Cambia percorso in fascicolo finale Esportazione MySQL

Il primo metodo mi sembrava troppo complicato, perché... Dovrei approfondire la configurazione di OpenServer, che non è stata scritta da me con tutte le circostanze che ne conseguono 🙂 Pertanto ho deciso di intraprendere la seconda strada. Se incontri problema simile- ripeti dopo di me.

Per prima cosa devi andare alla riga di comando di MySQL ed eseguire uno dei seguenti comandi:

MOSTRA VARIABILI COME "secure_file_priv"; SELEZIONA @@GLOBAL.secure_file_priv;

Il risultato dell'esecuzione di entrambi sarà il valore della variabile globale MySQL secure_file_priv, che contiene il percorso della directory attraverso la quale è possibile eseguire le operazioni di esportazione e importazione dei dati MySQL (in futuro, un collegamento ad un articolo sull'importazione dei dati).

Quelli. quando si utilizzano i comandi CARICAMENTO DATI E SELEZIONA... NELL'OUTFILE i file esportati e importati possono trovarsi solo all'interno di questa directory.

Nel mio caso, questa variabile era generalmente impostata su NULLO, Perché Come ho già detto, per lavorare nella console utilizzo le utilità MySQL della distribuzione inclusa in OpenServer. Questo valore ha indicato che le operazioni di esportazione e importazione dei dati MySQL utilizzando i comandi specificati sono state completamente chiuse.

Come si è scoperto in seguito, questa è una situazione comune quando si utilizzano server WAMP e MAMP in scatola.

Purtroppo nel mio caso non è stato possibile utilizzare i consueti metodi di modifica dei valori delle variabili globali MySQL:

SET nome_variabile = valore;

Di conseguenza, ho visto solo il seguente errore nella console:

ERRORE 1238 (HY000) alla riga 1: la variabile "secure_file_priv" è una variabile di sola lettura.

Di conseguenza, per modificare il valore di una variabile secure_file_priv e aprire le operazioni di esportazione e importazione, dovevo accedere al file di configurazione di MySQL mysql.ini, che si trova nella directory principale della distribuzione MySQL, o a cui è possibile accedere in un altro modo se MySQL è incluso nel tuo WAMP/LAMP/ Creazione del server MAMP.

A proposito, se desideri modificare il percorso della directory di spool di scambio file, dovrai fare lo stesso.

Nel mio caso questa variabile esisteva già nella configurazione, solo in forma commentata:

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

Se non ce l'hai, scrivilo da zero nella sezione (almeno per me si trovava lì).

L'ho rimosso dal commento e ho deciso di usarlo nella forma in cui è stato scritto. Quelli. quando esporto i dati da MySQL e li importo nuovamente, i miei file verranno ora archiviati in una directory c:\openserver\datiutente\temp\.

Dopo aver modificato la configurazione (qualsiasi, tra l'altro), non dimenticare di riavviare il server o un servizio separato di cui hai modificato le impostazioni, se possibile, affinché le modifiche abbiano effetto!

Per sicurezza, dopo aver riavviato il server MySQL, visualizza nuovamente la variabile secure_file_priv e copia il suo valore negli appunti.

E ora dobbiamo chiamare il comando come all'inizio, solo prima del nome del file in cui verranno salvate le informazioni dal database MySQL, scrivi il percorso memorizzato nella variabile che stiamo modificando nella seguente forma:

SELECT * FROM database_table INTO OUTFILE "secure_file_priv_value\file_name.csv";

Successivamente, nel mio caso l'esportazione dei dati da MySQL ha funzionato.

Punto importante! Se lavori con MySQL sotto Windows, non dimenticare di cambiare "\" in "/" quando specifichi il percorso del file, altrimenti verrà visualizzato l'errore --secure-file-priv continuerà comunque ad apparire.

Questo è l'articolo su come eseguire il dump di un database MySQL e delle sue tabelle, nonché sui dati di output dalle tabelle MySQL a vari formati, giunge alla fine. Scrivi le tue recensioni nei commenti e condividi con tutti le opzioni di script che usi più spesso nella pratica.

Se l'articolo ti è piaciuto puoi ringraziare l'autore ripubblicando l'articolo in mezzi di comunicazione sociale o finanziariamente utilizzando il modulo sottostante, in modo da poter pagare l'hosting di base.

Buona fortuna a tutti e ci vediamo di nuovo! 🙂

PS: se hai bisogno di un sito web o devi apportare modifiche ad uno già esistente, ma non c'è né tempo né voglia per farlo, posso offrire i miei servizi.

Più di 5 anni di esperienza sviluppo di siti web professionali. Lavorare con PHP

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

2018-12-04T00:00Z

2018-12-11T00:00Z

Quando creiamo un file dump con mysqldump, contiene un grande script SQL per ricreare il contenuto del database. Quindi lo ripristiniamo eseguendo la riga di comando del client MySQL:

Mysql -uroot -p

(dove root è il nostro nome utente amministratore per MySQL) e dopo esserci collegati al database abbiamo bisogno dei comandi per creare il database e leggere il file:

Crea database nuovo_db; usa new_db; \. dumpfile.sql

I dettagli cambieranno a seconda delle opzioni utilizzate durante la creazione del file dump.

2018-12-18T00:00Z

Devi solo eseguire questo:

mysql -p -u< db_backup.dump

Se il dump contiene più database, dovresti omettere il nome del database:

mysql -p -u< db_backup.dump

Per eseguire questi comandi, aprire un prompt dei comandi (su Windows) e accedere alla directory in cui si trova mysql.exe file eseguibile mysql.exe (potresti doverci pensare un po', dipenderà da come hai installato mysql, cioè standalone o come parte di un pacchetto come WAMP). Quando ti trovi in ​​questa directory, dovresti semplicemente inserire il comando.

25-12-2018T00:00Z

Dovrebbe essere semplice come eseguire questo:

MySQL -u -P< db_backup.dump

Se il dump ha un singolo database, potrebbe essere necessario aggiungere una riga all'inizio del file:

UTILIZZO ;

Se si tratta di un dump di molti database, le istruzioni use sono già presenti.

Per eseguire questi comandi, apri un prompt dei comandi (su Windows) e inserisci la directory in cui mysql.exe contiene l'eseguibile mysql.exe (potresti doverci pensare un po', dipenderà da come hai installato mysql, cioè standalone o come parte di un pacchetto come WAMP). Una volta che sei in quella directory, puoi semplicemente digitare il comando come ho fatto sopra.

2019-01-01T00:00Z

Comando di una riga per ripristinare l'SQL generato da mysqldump

MySQL -u -P E"fonte

2019-01-08T00:00Z

Utilizzando un file dump da 200 MB creato su Linux per ripristinarlo su Windows con mysql 5.5, ho avuto più successo Con

File sorgente.sql

approccio da riga di comando di mysql rispetto all'utilizzo

mysql< file.sql

approccio da riga di comando che causa alcuni errori del 2006 "il server è uscito" (su Windows)

Stranamente, il servizio creato durante l'installazione (mysql) fa riferimento a un file my.ini che non era presente. Ho copiato nel mio.ini il file di esempio "grande", che avevo già modificato con l'incremento consigliato.

I miei valori

Pacchetto_max_consentito = 64M timeout_interattivo = 250 timeout_attesa = 250

2019-01-15T00:00Z

COME esempio concreto risposta precedente:

Avevo bisogno di ripristinare il backup in modo da poterlo importare/trasferire su server SQL. Ho installato solo MySql ma non l'ho registrato come servizio né l'ho aggiunto al mio percorso poiché non ho bisogno di eseguirlo.

ero solito Windows Explorer per inserire il mio file dump in C:\code\dump.sql. Quindi apri MySql dalla voce del menu Start. Creato il DB, quindi eseguito il comando source con il percorso completo in questo modo:

Mysql> crea database temp mysql> usa temp mysql> sorgente c:\code\dump.sql

Il dump e il ripristino di un database MySQL sono abbastanza semplici e convenienti da eseguire in remoto tramite SSH o direttamente tramite la console del server. In remoto, questo può essere fatto utilizzando i programmi Putty/Kitty. Puoi anche eseguire gli esempi seguenti su Windows eseguendo la riga di comando " cmq‘. Di seguito sono riportati esempi su come creare dump del database MySQL e quindi ripristinarli se necessario, ad esempio per il tuo sito web, negozio online o qualche altro progetto.

Creazione di un dump del database MySQL

Per eseguire questi comandi, connettiti da remoto al tuo server tramite SSH utilizzando uno dei programmi sopra elencati. Dopo esserti connesso e autorizzato al server/hosting, puoi inserire i comandi seguenti.

# Eseguire il backup di un database nel file dump_file.sql mysqldump -uroot -p your_base > dump_file.sql # On discarica di WindowsÈ meglio creare un comando leggermente diverso che impedisca # la sovrascrittura accidentale delle righe di dump dovuta alla conversione dei caratteri di nuova riga "\r\n" in "\n" mysqldump -uroot -p your_base -r dump_file_utf8.sql # Se è necessario un backup solo di singole tabelle e non dell'intero database # (specificare i nomi delle tabelle separati da uno spazio dopo il nome del database) mysqldump -uroot -p your_base TABLE1 TABLE2 TABLE3 > dump_file.sql # Se è necessario creare un backup solo del database struttura senza i dati stessi mysqldump -uroot -p -- no-data your_base > dump_file.sql # Effettua il backup di tutti i database nel file current_date.gz mysqldump -uroot -p --all_databases | gzip -c > "date "+%Y-%m-%d"".gz # Backup, dove viene creato un INSERT separato per ogni voce # e con indicazione esplicita codifica database UTF-8 mysqldump -uroot -p --default-character-set=utf8 your_base --extended-insert=FALSE | gzip -c > "data "+%Y-%m-%d"".gz

Nell'esempio sopra, l'utilità viene utilizzata per creare un backup mysqldump, che è incluso in mysql. Di seguito i parametri per la creazione del backup del database, che esamineremo più nel dettaglio:

• -u– il parametro specifica il login che verrà utilizzato per connettersi al database. Nell'esempio utilizziamo il login root, che deve essere specificato in questo parametro senza spazi! Di conseguenza, assomiglia a -uroot
• -P– il parametro indica che è necessario inserire una password per l'accesso specificato. Lo abbiamo lasciato vuoto, per cui la password dovrà essere inserita dopo aver premuto "Invio" durante l'esecuzione del comando. Tuttavia, puoi specificare la password proprio qui, come nel parametro login, senza uno spazio dopo -p, tuttavia, questo metodo non è sicuro, poiché la console salva i tuoi comandi in un file di registro e se non lo cancelli regolarmente, potrebbe essere visualizzato da un utente malintenzionato.
• tua_base– invece di questa riga nell'esempio, è necessario specificare vero nome Il tuo database per il quale stai creando un backup.
• > – un operatore che mostra la direzione dell'azione, ovvero come se indicasse che stai per scrivere dal database in un file.
• dump_file.sql– questo è il nome del tuo file .slq in cui devi salvare il tuo database. È indicato con uno spazio dopo l'operatore ">". È possibile specificare qualsiasi altro nome. Ad esempio, in modo che nel nome il sistema inserisca automaticamente ora attuale, basta specificare una riga come:
  

  "data "+%Y-%m-%d""

  "data "+%Y-%m-%d""

  
  dopo questa riga nell'esempio è indicata l'estensione del file ' .gz‘. Di conseguenza, un file come " 2014-11-15.gz‘.

  Attenzione! Se specifichi solo il nome del file, questo verrà salvato nella stessa directory in relazione alla quale esegui questo comando. Quelli. se vedi qualcosa di simile al prompt dei comandi # , Dove root@dvs questo è il login e il nome del server, il file verrà creato nella directory /casa. Per modificare il salvataggio del file in un percorso diverso, specificare il percorso completo per salvare il file anziché il nome, ad esempio: /var/www/backup/dump_file.sql.

• Nel secondo esempio, invece dell'operatore ' > Viene utilizzato l'operatore | ‘, che indica la necessità di eseguire comando aggiuntivo gzip con parametro ' -C' che ti consente di comprimere immediatamente il dump in un archivio e solo successivamente salvarlo in un file come ' 2014-11-15.gz‘, come riportato dall’operatore ‘ > ‘.
• Parametro -nessun dato ti consente di scaricare solo la struttura del database senza i dati stessi. Molto utile in alcuni casi quando i dati non sono necessari.
• Opzioni –set-di-caratteri-default=utf8 E –insert-esteso=FALSO. Il primo permette di specificare esplicitamente la codifica utilizzata da questo database, evitando così di salvare il database con la codifica errata. Invece di utf8, è possibile specificare qualsiasi altra codifica, ad esempio cp1251. Il secondo parametro consente di specificare che durante l'esportazione deve essere creato un comando INSERT separato per ciascun record. In alcuni casi, ciò potrebbe essere necessario durante il ripristino parziale dei dati da un dump.

Ripristino di un database da un file di dump MySQL

Ora diamo un'occhiata al processo inverso di ripristino di un database da un file di dump. Questa azione eseguito utilizzando il programma mysql. Vediamo subito un esempio.

Probabilmente hai dovuto importare i dump del database più di una volta. E nonostante la banalità di questo compito, spesso sorgono domande e talvolta problemi ad esso associati. Di seguito è riportato tutto il codice necessario per applicare rapidamente un dump del database dalla riga di comando.

IMPOSTA NOMI utf8; DROP DATABASE `miodb`; CREA DATABASE `mydb` SET DI CARATTERI PREDEFINITO utf8; USA `miodb`; IMPOSTA SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; IMPOSTA commit automatico=0; SET foreign_key_checks=0; SET controlli_unici=0; FONTE /percorso/del/db_dump.sql; COMMETTERE; IMPOSTA commit automatico=1; SET foreign_key_checks=1; SET controlli_unici=1;

Questi comandi ti permetteranno di importare rapidamente un dump del database ed evitare problemi. Inoltre, alcuni di questi problemi non vengono risolti nemmeno dai comuni client mysql, come phpMyAdmin e altri.

Proviamo a capirlo questo codice al fine.

IMPOSTA NOMI utf8; dice che lo scambio di dati avverrà con la codifica UTF-8 ed è lì che si trova file originale dump del database e questo è ciò che avrà il database finale.

DROP DATABASE `miodb`; E CREA DATABASE `mydb` SET DI CARATTERI PREDEFINITO utf8; eseguire distruzione database e la sua successiva creazione con la codifica predefinita UTF-8. È chiaro che ciò cancellerà tutte le tabelle e i dati in esse contenuti, ma questo ci darà la certezza che il database risultante corrisponderà completamente ai dati nel file di dump.

USA `miodb`; indica che questo particolare database deve essere utilizzato per tutti i comandi del file dump (controllare nel file dump la presenza di questa direttiva che indica un database diverso per evitare confusione).

IMPOSTA SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; disabilita l'aumento del contatore di incremento automatico quando si scrive il valore 0 nel campo di incremento automatico. Per impostazione predefinita, quando si scrive in tale campo Valori NULL o 0 viene incrementato automaticamente. E sebbene avere un valore pari a 0 in un campo incrementale automatico non lo sia la migliore pratica, a volte capita ancora che ci siano righe del genere nel dump (ho riscontrato qualcosa di simile in un dump di Magento). Vale anche la pena notare che mysqldump include questa direttiva per impostazione predefinita nei suoi dump ormai da qualche tempo.

IMPOSTA commit automatico=0; prima di caricare il dump e COMMETTERE; successivamente, accelerano l'esecuzione degli INSERT per le tabelle InnoDB, poiché quando la modalità di commit automatico è abilitata, i log vengono salvati su disco con ogni INSERT. Può aiutare quando si rotolano discariche di grandi dimensioni.

SET foreign_key_checks=0; l’opzione più ovvia, senza la quale la maggior parte dei dump non potrebbero essere applicati. Disabilita i controlli delle chiavi esterne, consentendo la scrittura dei dati in qualsiasi ordine, incluso with collegamenti circolari tramite chiavi esterne. Questa opzione accelera anche l'importazione del dump.

SET controlli_unici=0; disabilita i controlli di unicità per le chiavi secondarie, che ancora una volta possono accelerare il processo di importazione.

E infine, squadra centrale, per il bene del quale tutto questo è stato pianificato - FONTE /percorso/del/db_dump.sql; importa direttamente il dump database mysql da file specificato. Il percorso del file può essere assoluto o relativo alla directory da cui hai effettuato l'accesso alla console mysql.

E una volta completata l'importazione, ripristiniamo il valore dei flag, anche se ciò non è necessario, perché il comando IMPOSTATO in mysql, per impostazione predefinita, modifica i valori delle variabili solo nel contesto della sessione corrente.

Effettuare un dump del database (backup) è molto importante. Quindi ho fatto alcuni commenti con esempi per questo ottima utilità come mysqldump.

mysqldump - un'utilità che consente di scaricare il contenuto di un database o di un insieme di database da creare copia di backup o inviare dati a un altro server di database SQL (non necessariamente un server MySQL). Il dump conterrà una serie di comandi SQL per creare e/o popolare tabelle.

mysqldump-u root -p -f nome_database >

Con questo comando effettuiamo un backup del database con il nome nome_database sul disco C nel file mydb_backup_name_database.txt

Non è necessario creare il file; MySQL lo creerà da solo.

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

Con questo comando importiamo i dati di backup dal file C:\mydb_backup_name_database.txt

Nota: -f, --force è un'opzione che specifica di continuare anche se viene ricevuto un errore SQL, ad es. ignorare gli errori. Ad esempio, se nella tabella esiste già una riga identica.

Per evitare che venga richiesta la password è necessario scriverla subito dopo -p, cioè senza spazi. Se la password Pwd, l'esempio sarebbe simile al seguente:

mysqldump-u radice -p Pwd-f nome_database > C:\miodb_backup_nome_database.txt

Se usi spesso questo comando, è meglio creare un utente separato con i diritti necessari per ridurre la visibilità della password di root

Consideriamo di più impostazioni raffinate mysqldump:

--banche dati consente a mysqldump di includere i comandi CREATE DATABASE /*!33333 IF NOT EXISTS*/ DBNAME e USE DBNAME nello script di ripristino. Ciò ti consentirà di creare database funzionanti da zero. Cioè, senza utilizzare --databases, si presuppone che l'utente stia ripristinando un singolo database e specificando esplicitamente dove posizionare i dati ripristinati. Se viene creato un backup con l'obiettivo di creare una copia completamente funzionante dei dati, ad esempio su un altro server MySQL, è necessario utilizzare questa chiave;

--tutti-database ti consente di creare copie di tutti i database esistenti su un determinato server MySQL. Se hai bisogno di fare copie solo di alcuni database, basta specificarli separati da uno spazio quando chiami mysqldump dalla riga di comando (vedi sopra);

Chiave --aiuto. Il programma mysqldump ha molte versioni. Puoi vedere quali funzionalità sono supportate specificamente dalla tua versione utilizzando questa chiave;

--add-drop-tabella- un'opzione che forzerà mysqldump ad aggiungere un comando drop table allo script risultante prima di creare le tabelle. Ciò consentirà di evitare alcuni errori durante il ripristino di un database da un backup. Naturalmente è necessario tenere presente che le tabelle presenti nella copia di lavoro (se nel backup esistono tabelle con lo stesso nome) verranno eliminate dal database principale e ricreate dal backup prima di essere ripristinate dal backup;

--nessun dato. Utilizzando questa chiave, puoi creare rapidamente una copia della struttura della tabella/database senza i dati stessi. Ad esempio, hai creato una tabella complessa e vorresti salvarne la struttura per il futuro, ma non hai bisogno dei dati stessi presenti in questa tabella in una copia di backup;

--file-risultato=...- questa opzione può essere utilizzata per reindirizzare l'output su un file. È possibile utilizzare il normale reindirizzamento Unix con il comando ">" oppure è possibile utilizzare questa chiave. A chi piace cosa;

Un altro molto consiglio utile sull'utilizzo di mysqldump in un ambiente di hosting. Di norma, quando si utilizza l'hosting, all'utente vengono imposte alcune restrizioni. Ad esempio, non puoi prendere in prestito più di un certo importo memoria fisica(RAM, RAM). mysqldump per impostazione predefinita mette in memoria tutti i dati ricevuti dal server MySQL e quindi li scrive tutti su disco. Di conseguenza, se il provider ti consente di prendere in prestito, ad esempio, 30 MB di memoria e il database che stai copiando utilizzando mysqldump occupa 50 MB, ovviamente qui si verificherà un errore: mysqldump non sarà in grado di funzionare correttamente e si bloccherà, cosa di cui ti informerà. Per "forzare" mysqldump a scrivere i dati direttamente su disco, anziché archiviarli, anche temporaneamente, in memoria, utilizzare la chiave --presto. Questo risolverà il problema.

Ecco un altro paio di esempi utili:

mysqldump -u root -p Pwd-f --set-di-caratteri-predefiniti=cp1251 DBNOME| gzip -c > nomefile.txt .gz

Puoi decomprimere un archivio di questo tipo con il comando:

nomefile gunzip.txt.gz

Per sapere in quale data è stato effettuato il backup è possibile scrivere il seguente comando:

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

e se devi creare dump con codifiche diverse, allora è conveniente usare le variabili:

impostato DBCARATTERE=utf8

impostato DBNOME= violazione

mysqldump -u root -p Pwd-f --set-di-caratteri-predefiniti=$ DBCARATTERE $DBNOME| bzip2 -c > sql. $NOMEDB.`data "+%Y-%m-%d"`.bz2

Se vuoi automatizzare la rimozione dei vecchi archivi, prova a utilizzare cron e il comando find, che di solito si trova in Unix. In esecuzione periodicamente

Trovare~/directory-con-archivi -name "*.gz" -mtime +7 -exec rm -f () \;

Pertanto, eliminerai gli archivi "più vecchi" di sette giorni.