Tophost

Database - mysqldump e backup selettivo

google-plus Scritto da . Postato in Ubuntu

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

Per tutti quelli che hanno escogitato sistemi vari di backup dei propri database attraverso software, script e altro ancora, questo articolo, sarà senz'altro interessante.

Anche io, ho usato vari tipi di backup, dagli automatizzati ai programmi specifici, agli script... La cosa particolare, è che qualche mese fa, dopo aver fatto per una vita tutti i backup dei vari progetti sul mio PC di casa, mi sono reso conto che non avevo tutti i database allineati e questo, perché non avevo mai preso in considerazione il problema in se stesso.

Facevo il backup dei singoli progetti, attraverso BackupWordPress o Akeeba Backup, e dei file di mysql e di apache, ma non avevo salvato tutti i database che avevo in locale con la 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

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 dia questi comandi in automatico, c'è sempre il fatto che se auguratamente 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
#!/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 qui sotto lo script pari pari:

#!/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 riversato 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. wink


 

Se hai gradito l'articolo, condividilo, basta un click!

Aggiungi commento


Codice di sicurezza
Aggiorna

  • logo prontopro
  • logo boutique serramento
  • koilab japan and tropical fish farm
  • Trasloco economico - gruppo Arco Traslochi
  • logo flash pulizie
  • logo revolution
  • logo planetkoi small
  • logo finiture
  • ick logo template
  • montaggio mobili trento

Realizzato da: Daniele Gagliardi
Viale Monache 3 - 38062 Arco - TN
- Contattami -

Per essere più facile ed intuitivo, il blog fa uso dei cookie, piccole porzioni di dati che consentono di capire come gli utenti navighino e ne visualizzino le pagine. I cookie non registrano alcuna informazione personale sull'utente ed eventuali dati identificabili non verranno memorizzati.