Ciao,
scrivo questa breve guida per chiarire una volta per tutte come si DEVONO fare i backup di un server mysql.
Di certo avrete notato difficoltà nella migrazione di un server mysql da una macchina ad un'altra circa tabelle corrotte, perdita di dati, alterazione delle tabelle e calo di performance e stabilità.
Questo accade perché vi limitate a copiare le cartelle sotto /var/db/mysql e li ributtate in una nuova installazione di mysql su un'altra macchina. Questa operazione può risultare la più semplice e veloce, ma è quella più sbagliata e che può creare casini, facendovi perdere molto più tempo di quanto ne avete risparmiato optando per questa soluzione invece di quella corretta.
Qualche spiegazione del perché perdete dati:
La soluzione è la creazione di dump. I dump non sono altro che file in linguaggio SQL contententi le query per ricreare i database e le tabelle, comprensive di dati.
Da un file di query SQL è sempre possibile ripristinare uno stato di una o più tabelle su diverse versioni di mysql, e con le opportune modifiche è persino possibile importare delle tabelle su un altro tipo di RDBMS.
Prima di passare al lato pratico è giusto specificare il significato di alcuni termini:
Backup
Per creare un dump di backup esistono numerose utility: client mysql grafici avanzati come Navicat, pannelli di controllo web come PHPMyAdmin e Webmin oppure utility da linea di comando come mysqldump incorporata nell'installazione di mysql.
Il modo più semplice, ma limitato per creare un dump di un database o di una tabella è tramite Navicat.
Cliccando con il tasto destro sulla tabella o database si seleziona la voce "Dump SQL file" e si sceglie tra solo struttura o struttura e dati. Come facilmente intuibile la prima opzione si limita ad eseguire la copia delle strutture delle tabelle, ma senza riportare i dati. Quindi quando andrà fatto un restore le tabelle risulteranno vuote. Quindi una volta selezionata l'opzione desiderata, Navicat vi chiederà di specificare il percorso dove verrà salvato il file di dump sul PC locale. Fatto tutto ci vorrà del tempo per permettere a Navicat di eseguire la copia interrogando il database. Con Navicat è possibile eseguire il backup di un database alla volta solo manualmente.
Per delle funzionalità in più come backup automatici e ulteriori opzioni per la creazione dei dump è possibile sfruttare i pannelli di controllo web. Ad esempio con Webmin potete schedulare a crontab i dump dei database specificando ulteriori opzioni avanzate utili per un restore più adatto alle vostre esigenze. Inoltre potete scegliere se mantenere il file dump sulla macchina con MySQL oppure scaricarla in locale.
Il top per utilizzi avanzati è l'utility da linea di comando mysqldump. Comoda per backup automatici su supporto remoto personalizzati. E' un utility rilasciata dai manteiner di MySQL e ha un'infinità di opzioni per creare la copia di backup perfetta per le vostre esigenze. Rimando alla documentazione ufficiale per l'elenco di tutte le opzioni e vari esempi: http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
Essendo un utility da linea di comando (CLI) è necessario collegarsi alla shell della vostra macchina ad esempio in SSH tramite Putty. Create quindi il comando mysqldump con le opzioni che vi servono e lanciatelo. Vi creerà un file di dump.
Un esempio di comando mysqldump avanzato è il seguente:
Questo comando va a creare una copia dell'intero database "account" compressa con algoritmo bzip2 con grado compressione 9, utilizzando l'utente mysql "bck" (creato appositamente per i backup) e password "La_vostra_password", con set di caratteri "big5" (cinese), opzione DROP TABLE prima della creazione di ogni tabella (cancella e ricrea la tabella da 0), salvataggo di eventuali commenti ai campi delle tabelle, verbosità del comando al fine di verificare l'andamento della creazione del backup, inserimento dei dati tramite INSERT IGNORE, e altre opzioni spiegate meglio sulla documentazione ufficiale che vi ho linkato in precedenza.
Un file di dump può risultare molto grosso, in quanto vengono riportate una grossa quantità di query SQL in formato facilmente leggibile. Essendo alla fine solo file di testo, la compressione fa miracoli. Percui consiglio di comprimere i file di dump per guadagnare molto spazio (circa il 95%).
Restore
Una volta create le opportune copie di backup verrà il momento di ripristinare i dati, non solo in caso di perdite di dati, ma anche per testare l'effettiva utilità delle copie di backup o per migrare su un nuovo sistema.
Anche qui le possibilità sono tante: tramite Navicat, PHPMyAdmin, Webmin o linea di comando.
Tramite Navicat è possibile eseguire un restore semplicemente eseguendo il file di dump creato in precedenza anche con un altro software. Per farlo collegatevi al db interessato (se non c'è crearlo), cliccare con il tasto destro del mouse sul database->Execute SQL File. Selezionate il vostro file (non compresso) e eseguite. Navicat ricreerà le tabelle eseguendo in ordine le query che compongono il file di dump.
E' analogo anche per i pannelli di controllo PHPMyAdmin e Webmin: selezionate il file dump ed eseguitelo.
Da linea di comando basta utilizzare il classico client mysql. Di seguito un esempio:
Se nel dump è specificata la selezione del database, non serve specificarlo nel comando mysql.
Questo comando così come è nell'esempio esegue il file dump senza mostrare output, tranne se ci sono errori, poi chiude la connessione. Verificate se ha creato le tabelle e se ci sono i valori.
In caso vi dia problemi l'esecuzione del dump, come le date non supportate o valori enum mancanti, dovrebbe bastare settare l'sql_mode più permissivo. Per cambiare l'sql_mode in modo permanente dovete editare il file my.cnf (solitamente lo trovate sotto /var/db/mysql o sotto /usr/local/etc/mysql) e inserire sotto la categoria [mysqld] la seguente voce:
riavviate mysql con
e potrete eseguire il file dump.
Se volete ripristinare solo una parte del dump, ad esempio avete il dump di tutto un database, ma volete ripristinare una tabella, aprite il file con un editor di testo e cercare la tabella interessata. Troverete il DROP della tabella, la CREATE, e le eventuali INSERT.
Copiate quella parte e eseguitela come una serie di query.
scrivo questa breve guida per chiarire una volta per tutte come si DEVONO fare i backup di un server mysql.
Di certo avrete notato difficoltà nella migrazione di un server mysql da una macchina ad un'altra circa tabelle corrotte, perdita di dati, alterazione delle tabelle e calo di performance e stabilità.
Questo accade perché vi limitate a copiare le cartelle sotto /var/db/mysql e li ributtate in una nuova installazione di mysql su un'altra macchina. Questa operazione può risultare la più semplice e veloce, ma è quella più sbagliata e che può creare casini, facendovi perdere molto più tempo di quanto ne avete risparmiato optando per questa soluzione invece di quella corretta.
Qualche spiegazione del perché perdete dati:
- I datafile vengono creati in base al sistema in cui sono stati creati e sopratutto alla versione di mysql.
- Mentre lo storage engine MyISAAM è legato ai 3 file nella cartella del suo db, InnoDB invece memorizza altre informazioni anche nei database di sistema, rendendo così inutilizzabili le tabelle esportate erroneamente.
- Alcune funzionalità atte a rendere più stabile e performante il database e relative tabelle rischiano di non venire sfruttate correttamente.
La soluzione è la creazione di dump. I dump non sono altro che file in linguaggio SQL contententi le query per ricreare i database e le tabelle, comprensive di dati.
Da un file di query SQL è sempre possibile ripristinare uno stato di una o più tabelle su diverse versioni di mysql, e con le opportune modifiche è persino possibile importare delle tabelle su un altro tipo di RDBMS.
Prima di passare al lato pratico è giusto specificare il significato di alcuni termini:
- Copia di Backup: azione di replicazione di dati con lo scopo di prevenire la perdita dei dati. Un backup per essere definito tale deve essere facilmente ripristinabile e consistente.
- Restore: alcuni la chiamano ancora backup erroneamente, ma in realtà è l'operazione inversa al backup, in quanto consiste nel ripristinare uno stato precedente dei dati da una copia o più di backup.
- Datafile: Sono i file contenenti i dati in un formato facilmente interpretabile dal suo software, non human readable, che permette una gestione più performante e stabile dei dati da parte del software. Nel caso di MySQL per FreeBSD, i datafile sono posizionati fisicamente sotto il path /var/db/mysql/<database>/
Backup
Per creare un dump di backup esistono numerose utility: client mysql grafici avanzati come Navicat, pannelli di controllo web come PHPMyAdmin e Webmin oppure utility da linea di comando come mysqldump incorporata nell'installazione di mysql.
Il modo più semplice, ma limitato per creare un dump di un database o di una tabella è tramite Navicat.
Cliccando con il tasto destro sulla tabella o database si seleziona la voce "Dump SQL file" e si sceglie tra solo struttura o struttura e dati. Come facilmente intuibile la prima opzione si limita ad eseguire la copia delle strutture delle tabelle, ma senza riportare i dati. Quindi quando andrà fatto un restore le tabelle risulteranno vuote. Quindi una volta selezionata l'opzione desiderata, Navicat vi chiederà di specificare il percorso dove verrà salvato il file di dump sul PC locale. Fatto tutto ci vorrà del tempo per permettere a Navicat di eseguire la copia interrogando il database. Con Navicat è possibile eseguire il backup di un database alla volta solo manualmente.
Per delle funzionalità in più come backup automatici e ulteriori opzioni per la creazione dei dump è possibile sfruttare i pannelli di controllo web. Ad esempio con Webmin potete schedulare a crontab i dump dei database specificando ulteriori opzioni avanzate utili per un restore più adatto alle vostre esigenze. Inoltre potete scegliere se mantenere il file dump sulla macchina con MySQL oppure scaricarla in locale.
Il top per utilizzi avanzati è l'utility da linea di comando mysqldump. Comoda per backup automatici su supporto remoto personalizzati. E' un utility rilasciata dai manteiner di MySQL e ha un'infinità di opzioni per creare la copia di backup perfetta per le vostre esigenze. Rimando alla documentazione ufficiale per l'elenco di tutte le opzioni e vari esempi: http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
Essendo un utility da linea di comando (CLI) è necessario collegarsi alla shell della vostra macchina ad esempio in SSH tramite Putty. Create quindi il comando mysqldump con le opzioni che vi servono e lanciatelo. Vi creerà un file di dump.
Un esempio di comando mysqldump avanzato è il seguente:
Bash:
/usr/local/bin/mysqldump account --add-drop-table --comments --create-options --verbose --insert-ignore --dump-date --extended-insert --force --hex-blob --max_allowed_packet=128MB --u bck --password="La_vostra_password" --default-character-set=big5 | bzip2 -9 > /tmp/mysqldump/account.sql.bz2Un file di dump può risultare molto grosso, in quanto vengono riportate una grossa quantità di query SQL in formato facilmente leggibile. Essendo alla fine solo file di testo, la compressione fa miracoli. Percui consiglio di comprimere i file di dump per guadagnare molto spazio (circa il 95%).
Restore
Una volta create le opportune copie di backup verrà il momento di ripristinare i dati, non solo in caso di perdite di dati, ma anche per testare l'effettiva utilità delle copie di backup o per migrare su un nuovo sistema.
Anche qui le possibilità sono tante: tramite Navicat, PHPMyAdmin, Webmin o linea di comando.
Tramite Navicat è possibile eseguire un restore semplicemente eseguendo il file di dump creato in precedenza anche con un altro software. Per farlo collegatevi al db interessato (se non c'è crearlo), cliccare con il tasto destro del mouse sul database->Execute SQL File. Selezionate il vostro file (non compresso) e eseguite. Navicat ricreerà le tabelle eseguendo in ordine le query che compongono il file di dump.
E' analogo anche per i pannelli di controllo PHPMyAdmin e Webmin: selezionate il file dump ed eseguitelo.
Da linea di comando basta utilizzare il classico client mysql. Di seguito un esempio:
Bash:
mysql -u root --password="La_tua_password" account < account.sqlQuesto comando così come è nell'esempio esegue il file dump senza mostrare output, tranne se ci sono errori, poi chiude la connessione. Verificate se ha creato le tabelle e se ci sono i valori.
In caso vi dia problemi l'esecuzione del dump, come le date non supportate o valori enum mancanti, dovrebbe bastare settare l'sql_mode più permissivo. Per cambiare l'sql_mode in modo permanente dovete editare il file my.cnf (solitamente lo trovate sotto /var/db/mysql o sotto /usr/local/etc/mysql) e inserire sotto la categoria [mysqld] la seguente voce:
Codice:
sql_mode = NO_ENGINE_SUBSTITUTION
Bash:
service mysql-server restartSe volete ripristinare solo una parte del dump, ad esempio avete il dump di tutto un database, ma volete ripristinare una tabella, aprite il file con un editor di testo e cercare la tabella interessata. Troverete il DROP della tabella, la CREATE, e le eventuali INSERT.
Copiate quella parte e eseguitela come una serie di query.
