In this guide, we will show you how to create backups for each MySQL database in your server. We will also share commands of how to import such backups. This is normally handy most times than creating a huge .SQL file with all your databases backed up in there.
Create individual .SQL files for each database
mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > "$dbname".sql; done
Create individual .SQL files for each database to a particular location, say /home/dbbackups
mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > /home/dbbackups/"$dbname".sql; done
Create individual .SQL files for reach database and compress them
mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > "$dbname".sql; [[ $? -eq 0 ]] && gzip "$dbname".sql; done
Importing individual .SQL files into MySQL
Warning! This will overwrite your databases without prompting for confirmation. Use cautiously.
for sql in *.sql; do dbname=${sql/\.sql/}; echo -n "Now importing $dbname ... "; mysql $dbname < $sql; echo " done."; done
Credits
https://ma.ttias.be/mysql-back-up-take-a-mysqldump-with-each-database-in-its-own-sql-file/