Backup DB mysql da script .bat

Uno dei modi più pratici che ho trovato di schedulare un backup automatico di un database mysql è quello di creare un task (Task Scheduler) ed eseguire il file bat che esegue il backup dei database presenti sul server.

@echo off
:: ---- SETUP ----
:: db user name
set dbuser=backups
:: db user password
set dbpass=pAssW03d
:: db list (lista di DB separati da uno spazio)
set dblist=database1 database2 database3
:: backup directory destinazione dump
set bkupdir=C:\Backup
:: Cartella di installazione mysql
set mysqldir=C:\mysql
:: -----------------------------------------------------
:: NON MODIFICATE SOTTO
echo MySQLDump start >> log.txt
for %%f in (%dblist%) do (
echo backing up %%f ...
"%mysqldir%\bin\mysqldump" --user=%dbuser% --password=%dbpass% --databases %%f --opt --quote-names --allow-keywords --events --routines --complete-insert --skip-lock-tables > "%bkupdir%\%%f.sql"
echo OK! 
echo backup di %%f OK! >> log.txt
)
echo MySQLDump Finito>> log.txt
echo ----- >> log.txt
echo Procedura terminata correttamente!

Prima cosa creiamo una cartella nel disco C:  e al suo interno creiamo un file di testo che rinominiamo in mysqldump.bat, lo editiamo con un editor di testo incollandoci lo script sopra e modificando i dati di setup come spiegato sotto:

SETUP

Nella prima parte dobbiamo compilare i parametri di accesso necessari ad eseguire il dump del database.

• dbuser: Utente del database con i privilegi necessari per eseguire il dump su ogni database selezionato nella lista.
• dbpass: Password di connessione dell’utente.
• dblist: Lista dei database sui quali eseguire il dump.
• bkupdir: Cartella di destinazione del file .sql generato dopo il dump.
• mysqldir: Percorso di installazione del mysql (nella cartella bin al suo interno deve essere presente il file mysqldump.exe).

Una volta compilati i capi del setup se i permessi sul database sono corretti e l’utente dispone dei permessi di scrittura nella cartella di destinazione possiamo eseguire lo script.

log.txt

Per sicurezza generiamo anche un file di log per verificare eventuali problemi in caso di esecuzione schedulata senza la nostra presenza.
In questo script il log viene generato nella stessa posizione da dove viene lanciato lo script.

Esecuzione del DUMP

La stringa che esegue il dump è:

"%mysqldir%\bin\mysqldump" --user=%dbuser% --password=%dbpass% --databases %%f --opt --quote-names --allow-keywords --events --routines --complete-insert --skip-lock-tables > "%bkupdir%\%%f.sql"
echo OK!

Cerchiamo di spiegarla:

“%mysqldir%\bin\mysqldump”

Esecuzione del file mysqldump.exe conteuto nella cartella bin di installazione del mysql.

–user=%dbuser%

Utente con i permessi per l’esecuzione del dump del database.

–password=%dbpass%

La password dell’utente con i permessi per eseguire il dump del database.

–databases %%f

Viene selezionato dalla variabile %f il database sul quale eseguire il dump.

–opt

Nella guida ufficiale si dice che non è necessario specificarlo dato che è abilitato di default, serve comunque a includere una serie di parametri quali  –add-drop-table –add-locks–create-options –disable-keys –extended-insert –lock-tables –quick –set-charset

–quote-names

Aggiunge un apostrofo ai nomi delle tabelle e delle colonne.

–allow-keywords

Permette la creazione di campi con nomi di keyword anteponendo il nome della tabella al campo.

–events

Esporta nel dump anche gli eventi schedulati.

–routines

Con questa opzione vengono esportati anche le funzioni e le procedure.

–complete-insert

Se non aggiungiamo l’opzione –complete-insert le tabelle vengono esportate senza valori pertanto tutte le tabelle sono vuote, abbiamo quindi solo la struttura senza dati.

–skip-lock-tables

Nel caso non vengano “lockate” le tabelle non si garantiscono i permessi di lettura/scrittura alle tabelle agli altri utenti, quindi quindi non possiamo “usare” il database durante il dump, questo serve a garantire la coerenza dei dati durante il dump ma allo stesso tempo ne blocca l’utilizzo nel caso di un database di produzione.

> “%bkupdir%\%%f.sql”

Fine di destinazione contenente tutto l’export del database mysql.

 

Per i dettagli di tutte le opzioni disponibili per il dump del database mysql rimando al link della guida: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html