Bash: Loop List of Hosts and Dump all Databases

I needed to create fresh sql-dumps one a significant number of hosts. On each host it was required to create a sqldump-folder and dump all databases as sql files into it.

First let’s create a file called hostlist and insert a host/hostname each line. Then run this bash-oneliner:

#!/bin/bash
for host in `cat hostlist` ; do 
    ssh -l root $host '[ -x /usr/bin/mysqldump ] && ( umask 0077 ; 
    [ -d /var/lib/mysql/dumps ] || mkdir /var/lib/mysql/dumps ; 
    cd /var/lib/mysql/dumps ; 
    for db in `mysql -s -e "show databases" | grep -v "dumps"` ; do 
        mysqldump --all --complete-insert --add-drop-table --add-locks --quote-names $db > $db.sql ; 
    done )' ; 
done