Importare o ripristinare grandi quantità di dati in MySQL: tips & tricks

di Giulio di Bari :: 07 marzo 2018

Condividi su

Abbiamo visto, in un articolo precedente, come utilizzare mysqldump per creare un backup del nostro database MySQL, e come importare il file ottenuto sul server MySQL.

L'opzione "--opt", prevista di default da mysqldump, crea un file .sql già ottimizzato per il ripristino.

Tuttavia, in presenza di grandi quantità di dati da importare, puoi mettere in atto alcune accortezze al fine di velocizzare il processo di importazione.

Per fare questo devi modificare manualmente il file ottenuto da mysqldump: apri con l'editor che preferisci il il file sql ottenuto e segui queste istruzioni !

Disabilita gli indici MySQL

Sulla macchina in cui stai importando il file, disattiva gli aggiornamenti degli indici fino al termine dell'importazione.

Per disabilitare gli indici MySQL, il file da importare deve contenere, per ogni tabella da importare e prima di importarne i records, questo comando, dove al posto di "la_mia_tabella" devi indicare il nome della tabella da importare

ALTER TABLE `la_mia_tabella` DISABLE KEYS;

Al termine delle istruzioni relative all'importazione della tabella, riabilita gli indici così

ALTER TABLE `la_mia_tabella` ENABLE KEYS;

Ripeti questa operazione per tutte le tabelle presenti nel file

Se hai esportato il file con mysqldump, o con PhpMyAdmin, il file conterrà già i comandi per disabilitare e riabilitare gli indici, quindi non è necessario aggiungerli. Tuttavia verifica sempre la loro presenza.

Tabelle MyISAM

Se stai usando questo tipo di tabelle, modifica il file .sql, sostituendo alla istruzione INSERT, l'istruzione INSERT DELAYED.

Se viene specificata la clausola DELAYED, il server inserisce la riga o le righe in un buffer, e il client che ha effettuato l’insert può proseguire il lavoro. Se la tabella è occupata il sistema mantiene le righe. Nel momento in cui la tabella è libera il sistema inizia con l’inserimento di esse, controllando periodicamente se vi sono richieste verso la tabella. Nel momento in cui il sistema verifica una richiesta sospende l’inserimento.

Tabelle InnoDB

Se stai utilizzando tabelle di tipo InnoDB puoi ottenere una riduzione dei tempi di importazione aggiungendo all'inizio del file queste righe che disabilitano una serie di controlli che MySQL prevede di default e questo consente una importazione più rapida dei dati

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS = 0;

Alla fine del file aggiunti queste righe che riabilitano i controlli che abbiamo temporaneamente disattivato

SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
SET AUTOCOMMIT = 1;

Vediamo assieme il significato di questi controlli

AUTOCOMMIT

Per impostazione predefinita, MySQL esegue il ripristino dal file ottenuto con mysqldump in modalità autocommit: in poche parole, il motore di MySQL, esegue immediatamente ogni istruzione SQL contenuta nel file, scrive su disco, e attende la conferma di corretta scrittura da parte del disco. Questo si ripete migliaia di volta, tante quante le istruzioni presenti nel file.

Per alleggerire questo processo, modifichiamo il file aggiungendo all'inizio del file stesso, "SET AUTOCOMMIT = 0;" cioè disabilitiamo l'autocommit. Così facendo il sistema non procederà più record per record, ma manterrà in memoria ogni singola operazione, per poi scrivere il risultato complessivo in un colpo solo, quando incontrerà il comando commit e cioè alla fine del file dove abbiamo indicato "SET AUTOCOMMIT = 1".

UNIQUE CHECKS

Le creazione di chiavi secondarie uniche è, su tabelle InnoDB, una operazione storicamente lenta. Tuttavia è possibile creare gli indici dopo che i dati sono stati caricati velocizzando quindi il processo di importazione.

Se le tue tabelle hanno questo tipo di chiavi è possibile accelerare l'importazione di una tabella disattivando temporaneamente i controlli di unicità durante l'operazione di importazione. L'importante è che i dati non contengano chiavi duplicate, proprio perchè il controllo verrà saltato.

Per fare questo settiamo UNIQUE_CHECKS a 0 prima dell'importazione: in questo modo il motore di archiviazione può ignorare il controllo dei duplicati di chiavi secondarie secondarie.

Al termine dell'importazione riassegnamo il valore 1.

FOREIGN KEY CHECKS

Se sono presenti delle chiavi esterne ("FOREIGN KEY") nelle tabelle, è possibile velocizzare le importazioni delle tabelle disattivando i controlli delle chiavi esterne per tutta la durata della sessione di importazione.

Anche in questo caso settiamo a 0 il FOREIGN_KEY_CHECKS prima dell'importazione, e riassegnamo il valore 1 al termine dell'importazione.

Potrebbe interessarti

• Script per realizzare un backup automatico dei dati di MySQL utilizzando mysqldump, in bash e in php
• Ripristino completo di una replica MySQL bloccata.
• Effettuare il backup, e il ripristino, di un database MySQL con mysqldump, in locale o in remoto: guida ed esempi.
• Come configurare un sistema di Replicazione Master Slave su Mysql