Overview

In this tutorial I will give an example to a BASH scripts that will help you backup multiple MySQL databases at once.

Backup multiple MySQL databases

First we need to tell the script to generate text file with a DB list using ‘mysql’ and ‘mysqldump’ commands:

echo "Generating DB list file"; mysql -u MYSQLUSERNAME -pMYSQLUSERPASSWORD -Bse 'show databases' > /listfile.txt

No we can export the DB from the list into files (all but ‘information_schema’ DB):

echo "Dumping SQL Databases"; cat /listfile.txt | while read line do dbname=$line if \[ $line != "information\_schema" \] ; then mysqldump -u MYSQLUSERNAME -pMYSQLUSERPASSWORD $dbname > /$dbname.sql fi done

Don’t forget to replace “MYSQLUSERNAME” “MYSQLUSERPASSWORD” with your User and Password.

You can create a single “tar.gz” file from your exported databases using this command:

tar -cvzf /\*.sql > /filename.tar.gz

Enjoy.