Ripristino da backup MySQL/mysqldump: un semplice trucco per velocizzare al massimo le operazioni (autocommit=0) [TurboLab.it]

L'argomento --opt di mysqldump si occupa automaticamente di creare un backup (dump) del database MySQL altamente ottimizzato: il file .sql generato presenta dunque quasi tutte le accortezze necessarie per velocizzare il ripristino. Ma c'è ancora una modifica manuale che può ridurre i tempi di ripristino da dump MySQL: autocommit=0. Vediamo come impostarla

In questa guida parleremo nello specifico di una modifica da apportare al backup generato con mysqldump. Per la guida completa a questa utility e la procedura per generare il file di dump:

» Leggi: Database MySQL backup: esempi e guida rapida completa a mysqldump

Segnaliamo che lo script zzmysqldump applica già automaticamente questa modifica a tutti i dump generati. Per maggiori info:

» Leggi: MySQL/mysqldump: creare un file distinto/singolo per ogni database con zzmysqldump (script)

mysqldump e autocommit=0: come funziona

Per impostazione predefinita, MySQL esegue il ripristino dal file di dump generato da mysqldump in modalità autocommit: volendo banalizzare al massimo un argomento complesso, questo significa che il motore esegue e scrive immediatamente ogni istruzione SQL contenuta nel file, salvando ogni volta il risultato dell'operazione.

Modificando il dump, possiamo fare in modo che il sistema mantenga in memoria ogni singola operazione, per poi scrivere il risultato complessivo "in un colpo solo" quando incontra esplicitamente il comando commit.

Proprio questa scrittura "in una volta sola" velocizza il ripristino, poiché la procedura non deve attendere la conferma di corretta riuscita della scrittura da parte del disco migliaia di volte.

Modificare il dump in modo interattivo

Per applicare l'ottimizzazione dobbiamo dunque applicare due semplicissime modifica al file .sql generato da mysqldump prima di importarlo.

In ambito Windows, l'operazione non è banale poiché ci serve un editor esplicitamente realizzato per gestire file di testo di grandi dimensioni. Scegliete quindi il vostro preferito da questa lista e usatelo per aprire il dump .sql:

» Leggi: Programma/editor free per aprire/modificare log, dump, SQL, XML grandi/enormi (100/250/500 MB, 1 GB) su Windows?

In ambito Linux, possiamo affidarci ad un editor da terminale come nano, che non ha problemi a gestire file di grandi dimensioni

» Leggi: Guida: modificare i file di configurazione di Linux Ubuntu/CentOS da terminale è facile con "nano" (alternativa a "vi")

Una volta aperto il dump, copiate questo comando:

SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT=0;

Incollatelo su una nuova riga ad inizio file: sulla riga successiva a Server version, ad esempio:

Ripristino backup MySQL/mysqldump: semplice trucco velocizzare massimo operazioni (autocommit=0)

Scorrete fino alla fine del file (Ctrl+Fine su Windows oppure Ctrl+W seguito da Ctrl+V con nano).

Copiate quanto segue:

COMMIT;

SET AUTOCOMMIT=@OLD_AUTOCOMMIT;

Incollate su una nuova riga prima o dopo Dump completed:

Ripristino backup MySQL/mysqldump: semplice trucco velocizzare massimo operazioni (autocommit=0)

Salvate il file e l'operazione è completata: il file è dunque pronto per essere importato alla massima velocità!

Alternativa: modificare il dump da terminale

In alternativa alla procedura appena illustrata, sotto Linux possiamo affidarci a questo mini-script da lanciare nel terminale per svolgere la modifica istantaneamente (si tratta di un comando singolo, che deve stare su una sola riga):

MY_DUMP_FILE="/home/zane/mio_dump.sql" sh -c 'sed -i "/Server version/ a SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT=0;" "$MY_DUMP_FILE" && echo "COMMIT;" » "$MY_DUMP_FILE" && echo "SET AUTOCOMMIT=@OLD_AUTOCOMMIT;" » "$MY_DUMP_FILE"'

Ovviamente, il percorso /home/zane/mio_dump.sql deve essere personalizzato con quello corretto per raggiungere il file di dump.

Il risultato è equivalente alla modifica manuale vista in precedenza.

mysqldump e autocommit=0: è veramente utile?

In rete c'è che giura di aver rilevato un abbattimento dei tempi di importazione pari a 10 volte semplicemente applicando questa modifica. In realtà i benefici reali dipendono molto dal tipo di tabelle presenti nel dump: quelle in formato MyISAM non beneficiano di alcun miglioramento, mentre quelle InnoDB certamente sì, in particolar modo quando contengono centinaia di migliaia di record.

Tanto per avere un'idea di cosa aspettarsi, ho svolto una rapida prova caricando all'interno di una macchina virtuale Ubuntu un dump da circa 800 MB con tabelle miste MyISAM e InnoDB. I tempi sono stati: