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.
DevOps/IT Specialist, Musician.
IT Manager – Faculty of Exact Sciences, Bar-Ilan University
Personal Website