Database - mysqldump e backup selettivo

A chi non è capitato di dover effettuare un backup dei propri database sul proprio server, in locale o sul server di produzione?

Questo articolo sarà senz'altro interessante per coloro che hanno escogitato sistemi vari di backup dei propri database attraverso software, script e altro ancora.

Anche io ho usato vari sistemi di backup, da quelli automatizzati ai programmi specifici, agli script... Qualche mese fa dopo che per un lungo periodo avevo svolto abitualmente i backup dei vari progetti sul mio PC, mi sono reso conto che non avevo tutti i database allineati e questo perché non avevo mai dedicato la giusta attenzione alla cosa.

Facevo il backup dei singoli progetti, attraverso BackupWordPress, Duplicator o Akeeba Backup, e dei file di mysql e di apache, ma non avevo salvato tutti i database che avevo in locale con conseguente perdita di dati.

Anche perché pur avendo fisicamente i file dei database, non è proprio un gioco da ragazzi ripristinarli, cioé non si può fare un copia incolla e sperare che tutto funzioni come per magia. Non su Linux almeno. A tal proposito scrissi un articolo che trattava proprio questo tipo di problematica:

Ubuntu 14.04 - Recuperare database mysql da file

Da allora ho escogitato un sistema che mi consentisse lanciando un singolo script di effettuare il backup di tutti i database mysql:

Il comando mysqldump

Se si deve fare un backup da linea di comando o creare uno script che faccia uso del comando mysqldump, farlo database per database, non è un problema se gli elementi da salvare sono pochi:

mysqldump  --skip-extended-insert --skip-quick --databases --password="yyy" database_name > database_name.sql

Ma se il vostro server ha almeno una ventina di database può essere una cosa fastidiosa dover eseguire venti volta la stessa istruzione.

Se poi generate uno script che esegua questi comandi in automatico, c'è sempre il fatto che se (ve lo auguro) i vostri progetti crescono in numero, sarete costretti a ritoccare lo script di volta in volta per poter includere i nuovi database. E se ve lo dimenticate?

Può dunque essere utile fare il dump di tutti i database con una sola istruzione:

mysqldump --user=xxx --password="yyy" -A > all_dbs.sql

o se preferite

mysqldump --user=xxx --password="yyy" --all-databases > all_dbs.sql

Se doveste avere problemi o errori nell'eseguire il backup, usate l'opzione -fv che permetterà di ignorare e procedere con la maggioranza degli errori.

Backup selettivo

Tuttavia, può essere utile fare un backup selettivo, soprattutto se certi errori sono insormontabili per alcuni problemi a viste o tabelle che generano errori e impediscono il buon procedere del backup. Ci sono poi i database di sistema come information_schema che non occorre salvare (e di default infatti non sono inclusi).

Il problema è che se da una parte mysqldump prevede l'opzione --all-database, non esiste una opzione contraria o una ipotetica --ignore-database.

Siamo costretti a ricorrere a qualche trucchetto per ottenere l'effetto voluto. Ma non preoccupatevi. E' abbastanza semplice e di facile utilizzo:

Script - Lista Database da escludere

Il primo metodo consiste, nel genereare uno script che:

• Crei prima una lista di database "on the fly" meno i database indesiderati
• Richiami mysqldump dandogli in pasto la lista dei database appena generata

#!/bin/sh

BD=`date +%d-%m-%Y`
DATABASE_LIST=$(mysql --user=xxx --password="yyy" -NBe 'show schemas' | grep -wv 'database1\|database2\|database3\|databaseX')
mysqldump --user=xxx --password="yyy" --databases $DATABASE_LIST > mysql_backup_$BD.sql

Script - File di configurazione

mysqldump, consente di usare un file di configurazione per dettargli quali database ignorare... Non solo, in realtà il file di configurazione, può contenere anche un elenco di opzioni del comando stesso. Per permettere di saltare i database, in realtà, si dice a mysqldump di saltare un elenco di tabelle grazie al comando --ignore-table.

Per generare il file di configurazione, da Terminale dare i seguenti comandi:

echo '[mysqldump]' > config.cnf
mysql -NBe "select concat('ignore-table=', table_schema, '.', table_name) \
  from information_schema.tables \
  where table_schema in ('database1', 'database2', 'database3','...','databaseX' )" \
  >> config.cnf

Ora il file config.cnf risulterà avere un contenuto simile a questo:

[mysqldump]
ignore-table=information_schema.CHARACTER_SETS
ignore-table=information_schema.COLLATIONS
ignore-table=information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
ignore-table=information_schema.COLUMNS
...

Quindi generate uno script che sia più o meno come questo:

#!/bin/sh

BD=`date +%d-%m-%Y`
mysqldump --defaults-file=./config.cnf --user=xxx --password="yyy" --all-databases > mysql_backup_$BD.sql

Come si può notare dai comandi dello script, mysqldump, usa il file di configurazione appena creato per effettuare il backup ed escludere le tabelle dei database indesiderati. La cosa positiva, è che se volete ritoccare il file di configurazione, lo potete fare a mano aggiungendo tabelle da ignorare ( --ignore-table=... ) o lanciare nuovamente il comando e sovrascrivere il file di configurazione per includere altri database tra quelli da escludere dal backup.

Script - Lista Database da includere

Un'altra opzione che consente svolgere un backup selettivo del nostro ambiente MySQL, è quello che ho trovato nel sito:

 How can I mysqldump all databases except the mysql schema?

Riporto pari pari lo script qui sotto:

#!/bin/sh

MYSQL_USER=xxx
MYSQL_PASS=yyy
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done

cd /var/www

MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > backups/all-dbs.sql

Come si può notare, riprende un po' la filosofia dello script precedente con appoggio ad un file di configurazione.

Anche questo script, salva una lista, ma di database da includere nel backup (non di tabelle da ignorare questa volta). Questa lista, è salvata nel file /tmp/DatabasesToDump.txt

• Lo script, ha in esso già incluse le variabili contenenti utente e password per la connessione al server MySQL, la cui stringa viene poi composta ed assegnata alla variabile MYSQL_CONN.
• La variabile SQL contiene la query al database information_schema. Query che serve per ottenere la lista dei database esclusi quelli il cui nome è presente nella clausola NOT IN ('...','...',[...]).
• In seguito al comando mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}, viene generato il file DatabasesToDump.txt
• Il contenuto del file, viene poi 'copiato' nella variabile DBLIST.
• Poi ci si sposta nella cartella /var/www directory che conterrà il dump o file sql contenente il backup.
• Per ultimo, esegue il backup con il comando mysqldump dandogli in pasto la lista dei database di cui fare il dump ( --databases ${DBLIST} ).

Il salvare la lista di database in una lista delimitata da spazi, può anche essere 'evitata' o aggirata, semplicemente generando al volo una variabile che contenga l'elenco:

DBLISTRAW=`mysql ${MYSQL_CONN} -ANe"${SQL}"`

Insomma, le possibiltà sono tante, basta scegliere il metodo che più si confà alle nostre necessità di backup.

Sperando di esservi stato utile raccogliendo e provando varie soluzioni trovate sul web, mi auguro che come me riusciate a ottenere ciò che volete: La sicurezza di un backup e i vostri dati al sicuro.