Export And Import All MySQL Databases At One Time - Free Websites, Share News And Posts Publicly

192

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?

861

Table of Contents

Export:

mysqldump -u root -p --all-databases > alldb.sql

Look up the documentation for mysqldump. You may want to use some of the options mentioned in comments:

mysqldump -u root -p --opt --all-databases > alldb.sql
mysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql

Import:

mysql -u root -p < alldb.sql

mysqldump -uroot -p –opt –all-databases > alldb.sql – a coder Oct 4, 2013 at 22:20 10

mysqldump -uroot -p –all-databases –skip-lock-tables> alldb.sql – temple Feb 17, 2014 at 23:22

Add –verbose or -v options to see how the dump is progressing. – bagonyi Aug 28, 2014 at 10:35

@HalilÖzgür from the mysqldump man page: “mysqldump does not dump the INFORMATION_SCHEMA or performance_schema database by default. To dump either of these, name it explicitly on the command line and also use the –skip-lock-tables option.” – mmalone Sep 23, 2015 at 23:26

WARNING, import will overwride all your existing MySQL user. – alexandre-rousseau Aug 25, 2017 at 6:41

242

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

I’d prefer this approach, since this makes all databases’ dump as different file. – Arda Oct 2, 2014 at 7:35

You can simplify/improve the script a bit: Replace the 9th line [databases=…] with these two lines: ExcludeDatabases="Database|information_schema|performance_schema|mysql" [next line] databases=`-u $USER -p$PASWORD -e "SHOW DATABASES;" | tr -d "| " | egrep -v $ExcludeDatabases` and remove the if and fi lines completely. In the variable ExcludeDatabases you store the names of databases which should not be dumped [normally system databases]. – Peter VARGA Jan 24, 2015 at 18:47

@jruzafa You can use -Bse "show databases" to avoid extra formatting output and thus you can remove | tr -d "| " | grep -v Database. In my export script this line is databases=`mysql -u $USER -p$PASSWORD -Bse "SHOW DATABASES;" – miquel Nov 7, 2015 at 12:31

Made a reverse (import) script: gist.github.com/tenold/aa5e107d93c0f54436cb – Corey Dec 14, 2015 at 19:15

1 WARNING: The very useful comment by @AlBundy above contains unicode character sequence U+200C U+200B between the “c” and the “h” of the word “scheme”. This breaks copy and pasting that bit. More discussion on this issue here: meta.stackexchange.com/questions/170970/… – Eaten by a Grue Sep 3, 2017 at 4:25

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 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;

What does -r do? – Luca Reghellin Aug 3, 2018 at 7:19

-r sets the name of destination file where the output will be writed on. – NetVicious Aug 20, 2018 at 11:23

Would it be better to use utf8mb4 instead of utf8 for the character set? – kojow7 Oct 16, 2018 at 17:50

1 It depends of what you’re storing in yout tables. utf8 character set has a max of 3 bytes for each character. utf8mb4 has a max of 4 bytes for each character. Obviously if your database it’s on utf8mb4 you should use that character set to restore it. – NetVicious Oct 17, 2018 at 7:04

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
done

Note:

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

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"

Good that you came back to contribute your additions. a vote from me – Fr0zenFyr Jul 2, 2019 at 9:25

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.

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 🙂

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'

Export all databases in Ubuntu

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

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

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

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

Your answer does not really solve the problem, because he is asking to export all databases at once. Maybe you can improve a little bit : mysqldump -uroot -proot –all-databases > allDB.sql note: -u”your username” -p”your password” – Marwan Salim Aug 17, 2018 at 7:15

The below script exports and import databases one by one and keeps deleting sql file after importing. [https://gist.github.com/Shubhamnegi/83b42c4ce80dbc9104c0f9413be17701][1]

source : https://stackoverflow.com/questions/9497869/export-and-import-all-mysql-databases-at-one-time

Chat read-only to anonymous users. Chat with Anyone and Anywhere. Only registered users are allowed to send messages.

Loading the chat ...

66 Register Login

Export:

Import:

Leave a Reply Cancel reply