[mysql] Export and Import all MySQL databases at one time

I want to keep a backup of all my MySQL databases. I have more than 100 MySQL databases. I want to export all of them at the same time and again import all of them into my MySQL server at one time. How can I do that?

Based on these answers I've made script which backups all databases into separate files, but then compress them into one archive with date as name.

This will not ask for password, can be used in cron. To store password in .my.cnf check this answer https://serverfault.com/a/143587/62749

Made also with comments for those who are not very familiar with bash scripts.

#!/bin/bash

# This script will backup all mysql databases into 
# compressed file named after date, ie: /var/backup/mysql/2016-07-13.tar.bz2

# Setup variables used later

# Create date suffix with "F"ull date format
suffix=$(date +%F)
# Retrieve all database names except information schemas. Use sudo here to skip root password.
dbs=$(sudo mysql --defaults-extra-file=/root/.my.cnf --batch --skip-column-names -e "SHOW DATABASES;" | grep -E -v "(information|performance)_schema")
# Create temporary directory with "-d" option
tmp=$(mktemp -d)
# Set output dir here. /var/backups/ is used by system, 
# so intentionally used /var/backup/ for user backups.
outDir="/var/backup/mysql"
# Create output file name
out="$outDir/$suffix.tar.bz2"

# Actual script

# Check if output directory exists
if [ ! -d "$outDir" ];then
  # Create directory with parent ("-p" option) directories
  sudo mkdir -p "$outDir"
fi

# Loop through all databases
for db in $dbs; do
  # Dump database to temporary directory with file name same as database name + sql suffix
  sudo mysqldump --defaults-extra-file=/root/.my.cnf --databases "$db" > "$tmp/$db.sql"
done

# Go to tmp dir
cd $tmp

# Compress all dumps with bz2, discard any output to /dev/null
sudo tar -jcf "$out" * > "/dev/null"

# Cleanup
cd "/tmp/"
sudo rm -rf "$tmp"

All the answers I see on this question can have problems with the character sets in some databases due to the problem of redirecting the exit of mysqldump to a file within the shell operator >.

To solve this problem you should do the backup with a command like this

mysqldump -u root -p --opt --all-databases -r backup.sql

To do a good BD restore without any problem with character sets. Obviously you can change the default-character-set as you need.

mysql -uroot -p --default-character-set=utf8
mysql> SET names 'utf8';
mysql> SOURCE backup.sql;

Export all databases in Ubuntu

1 - mysqldump -u root -p --databases database1 database2 > ~/Desktop/databases_1_2.sql

OR

2 - mysqldump -u root -p --all_databases > ~/Desktop/all_databases.sql

I wrote this comment already more than 4 years ago and decided now to make it to an answer.

The script from jruzafa can be a bit simplified:

#!/bin/bash

USER="zend"
PASSWORD=""
ExcludeDatabases="Database|information_schema|performance_schema|mysql"
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | egrep -v $ExcludeDatabases`

for db in $databases; do
    echo "Dumping database: $db"
    mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
    # gzip $OUTPUT`date +%Y%m%d`.$db.sql
done

Note:

1. The excluded databases - prevalently the system tables - are provided in the variable ExcludeDatabases
2. Please be aware that the password is provided in the command line. This is considered as insecure. Study this question.

Why parsing formatted output while the mysql command can do directly what you want?

databases=`mysql -u $USER -p$PASSWORD --batch --skip-column-names -e "SHOW DATABASES;" | grep -E -v "(information|performance)_schema"`

Lists the database names and only this.

Other solution:

It backs up each database into a different file

#!/bin/bash

USER="zend"
PASSWORD=""
#OUTPUT="/Users/rabino/DBs"

#rm "$OUTPUTDIR/*gz" > /dev/null 2>&1

databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
       # gzip $OUTPUT/`date +%Y%m%d`.$db.sql
    fi
done

mysqldump -uroot -proot --all-databases > allDB.sql

note: -u"your username" -p"your password"

Be careful when exporting from and importing to different MySQL versions as the mysql tables may have different columns. Grant privileges may fail to work if you're out of luck. I created this script (mysql_export_grants.sql ) to dump the grants for importing into the new database, just in case:

#!/bin/sh
stty -echo
printf 'Password: ' >&2
read PASSWORD
stty echo
printf "\n"
if [ -z "$PASSWORD" ]; then
        echo 'No password given!'
        exit 1
fi
MYSQL_CONN="-uroot -p$PASSWORD"
mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g'

When you are dumping all database. Obviously it is having large data. So you can prefer below for better:

Creating Backup:

mysqldump -u [user] -p[password]--single-transaction --quick --all-databases | gzip > alldb.sql.gz

If error

-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

Use:

mysqldump -u [user] -p --events --single-transaction --quick --all-databases | gzip > alldb.sql.gz

Restoring Backup:

gunzip < alldb.sql.gz | mysql -u [user] -p[password]

Hope it will help :)