mysqldump with db in a separate file

mysql -uroot -N -e 'show databases' | while read dbname; do mysqldump -uroot --complete-insert --some-other-options "$dbname" > "$dbname".sql; done

Creating backups per database is indeed much more efficient. Not only easier to restore once needed, but also I experienced that making a backup of the whole database would break in case one table was broken/corrupt. And by creating backups per database it will only break for that database and the rest is still valid.

The oneliner we created to backup our mysql databases is:

mysql -s -r -u bupuser -pSecret -e 'show databases' | while read db; do mysqldump -u bupuser -pSecret $db -r /var/db-bup/${db}.sql; [[ $? -eq 0 ]] && gzip /var/db-bup/${db}.sql; done

Best to create a new readonly mysql user 'bupuser' with passsword 'Secret' (change!). It will first retrieve the list of databases. Then loop and for each database create a dump.sql file to /var/db-bup (you can change). And only when there are no errors encountered then also gzip the file which will really drastically save storage. When some databases encountered errors then you will see the .sql file and not the .sql.qz file.

Here an easy script that will:

• dump all DB a compress the output -> SCHEMA_NAME.sql.gz
• use [autocommit/unique_checks/foreign_key_checks] to speed up import
• exclude default schemas

File: Dump_all.sh

How to use:
./Dump_all.sh -> will dump all DB
./Dump_all.sh SCHEMA_NAME -> will dump SCHEMA_NAME DB

#!/bin/bashMYSQL_USER="root"MYSQL_PASS="YOUR_PASS"echo "-- START --"echo "SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;" > tmp_sqlhead.sqlecho "SET autocommit=1;SET unique_checks=1;SET foreign_key_checks=1;" > tmp_sqlend.sqlif [ -z "$1" ]  then    echo "-- Dumping all DB ..."    for I in $(mysql -u $MYSQL_USER --password=$MYSQL_PASS -e 'show databases' -s --skip-column-names);     do      if [ "$I" = information_schema ] || [ "$I" =  mysql ] || [ "$I" =  phpmyadmin ] || [ "$I" =  performance_schema ]  # exclude this DB      then         echo "-- Skip $I ..."       continue      fi      echo "-- Dumping $I ..."      # Pipe compress and concat the head/end with the stoutput of mysqlump ( '-' cat argument)      mysqldump -u $MYSQL_USER --password=$MYSQL_PASS $I | cat tmp_sqlhead.sql - tmp_sqlend.sql | gzip -fc > "$I.sql.gz"     doneelse      I=$1;      echo "-- Dumping $I ..."      # Pipe compress and concat the head/end with the stoutput of mysqlump ( '-' cat argument)      mysqldump -u $MYSQL_USER --password=$MYSQL_PASS $I | cat tmp_sqlhead.sql - tmp_sqlend.sql | gzip -fc > "$I.sql.gz" fi# remove tmp filesrm tmp_sqlhead.sqlrm tmp_sqlend.sqlecho "-- FINISH --"