How To Backup All Databases in MySQL [ Script ]

Posted September 1, 2015 by  ‐ 1 min read

Hello Guys,

This is a simple bash script I wrote to dump all the databases ( excluding information_schema and mysql. I thought I’d share it with you. Someone might find it useful, others might find it silly 😀 But, anyway, here it is


# Checking if the /root/.my.cnf file exists or not

if [[ ! -f /root/.my.cnf ]]
        echo "/root/.my.cnf does not exist"
        echo "Did not dump any db. Exiting"

dbs=$(mysql -e "SHOW DATABASES" | sed '1d')
mkdir -p dumped_dbs/
for i in $dbs
        if [[ "$i" != "mysql" ]] && [[ "$i" != "information_schema" ]]; then
                echo "Dumping $i to dumped_dbs/${i}.sql"
                mysqldump $i > dumped_dbs/${i}.sql

count=$(ls dumped_dbs/ | wc -l)
echo "============================="
echo "DONE! Dumped $count databases"

While running the script, if you get the error saying /root/.my.cnf does not exist, then you need to create that file with the following content


This is the cnf file that helps us to run MySQL without having to type in the MySQL root password. Once the databases are dumped, you can find individual dumps under the folder dumped_dbs