Export and Import all MySQL databases at once

Question

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 once. How can I do that?

Andryc

Answer 1

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

Coetaneous

mysqldump -uroot -p --opt --all-databases > alldb.sql – Eveevection

mysqldump -uroot -p --all-databases --skip-lock-tables> alldb.sql – Burger

Add --verbose or -v options to see how the dump is progressing. – Trivalent

-uroot is the same as -u root? – Shipworm

If there is some records with big values: mysqldump -uroot -p --opt --all-databases --max_allowed_packet=512M > alldb.sql – Wail

@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." – Ardehs

@Ardehs true, it seems it has started not dumping performance_schema in 5.5+ (5.5 vs 5.1 vs 5.0). Anyway, 3 years is a long enough time for any change to happen :) – Menispermaceous

@Flimm mysql should be mysqldump -u root -p --all-databases | gzip > alldb.sql.gz – Fredela

If you want to compress the backup, then pipe to gzip, like this: mysqldump -u root -p --all-databases | gzip > alldb.sql.gz – Midwest

@PabloArmentano Yes, -uroot is the same as -u root. Where it gets weird is -pPassword is not the same as -p Password=> use the first in this case – Courtnay

WARNING, import will overwride all your existing MySQL user. – Greatest

This method can break UTF8 characters in subtle ways. Please, use --result-file=dump.sql instead! – Newcomen

I suggest the "--routines" option. Without it the functions and stored procedure will not be part of the backup – Ima

@ErikPerik Presumably this only applies to mysql instances running on MS Windows servers? I've mysqldump'ed UTF8 databases on linux servers a million times using > dump.sql and have never seen a problem. The documentation linked above specifically mentions Windows as the case in which --result-file might be necessary, but with reference to UTF-16. – Kashgar

@Kashgar I always use --result-file because it has broken things for me in the past. Unfortunately I do not have a specific example in mind. – Newcomen

mysqldump -u root -p -A -R -E --triggers --single-transaction --hex-blob --max_allowed_packet=1G > full_backup.sql – Eulogia

may cause "Table 'user' already exists" error during import, see #25788044 – Landlord

Answer 2

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

Nikolos

I'd prefer this approach, since this makes all databases' dump as different file. – Kelleekelleher

This is a great solution. It would be better if you had a description to explain what the script does (It backs up each database into a different file). – Platyhelminth

@Platyhelminth ok, I uploaded what you have told me over the description, thanks for the suggestion – Nikolos

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]. – Really

Very handy script, but I noticed it does not work when a DB name contains a slash: dump.sh: line 14: 20150922.some/name.sql: No such file or directory. Otherwise it worked perfectly. – Bayonet

@AnshulMishra yo save the code in file backup.sh $ chmod +x backup.sh $ ./backup.sh – Nikolos

@Nikolos 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;" – Dubitation

Made a reverse (import) script: gist.github.com/tenold/aa5e107d93c0f54436cb – Ostracism

One thing to consider when doing this is that if a database gets removed, then its file will remain. This may likely be fine, but if you're committing these dumps to some backup system, you will need to clean up deleted dbs manually. – Chapen

you may also want to add --batch option to all mysql commands, otherwise script may break if you run mysql command from docker container. mysql command defaults to interactive output, and runs in batch mode only if piping is used – Idaline

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/… – Charlesettacharleston

I suggest the "--routines" option. Without it the functions and stored procedure will not be part of the backup – Ima

@lucaferrario Thank you. Due to the fact I cannot edit the old comment I added now my own answer with the correct commands. – Really

Answer 3

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 this command line. The -r flag does the same as the shell operator > but without the character-set problems.

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

To do a good BD restore without any problem with character sets use these commands (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;

Mannuela

What does -r do? – Hugo

-r sets the name of destination file where the output will be writed on. – Mannuela

Would it be better to use utf8mb4 instead of utf8 for the character set? – Kamchatka

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. – Mannuela

Answer 4

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:

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.

Really

Answer 5

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"

Uxmal

Good that you came back to contribute your additions. a vote from me – Groenendael

Answer 6

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.

Roley

Answer 7

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'

Rowell

Answer 8

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 :)

Flyn

Answer 9

Export database:

for export you can go with this command:

mysqldump -u root -p --all-databases > alldb.sql
              ↓                         ↓
         (you user name)           (file name will be save)

and actually, I wrote this post because there is no reason to back up all the databases in one file. it might cause an error while you're importing it. for example, there are some default databases and there is no reason to back up them.

therefore you should choose databases that you want to backup.

mysqldump -u root -p --databases YourDBName1 YourDBName2 YourDBName3 > tutorials_query1.sql
                                     ↓            ↓            ↓
                             (your databases name that you want to backup)

you can get all the databases name with this command:

sudo mysql -u root -p

and enter your password and then in front of mysql> run this command:

SHOW DATABASES;

---

Import database:

mysql -u root -p < alldb.sql
           ↓            ↓
  (your username)   (it can be the full path like "/home/yoursqlfile.sql")

Mannose

Answer 10

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

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

Francisco

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" – Ame

Answer 11

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

Equiponderate

Answer 12

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

Latecomer

Answer 13

I successfully exported all databases using this command.

.\mysqldump -u root -p --all-databases > D:\laragon\tmp\alldb.sql

My Tools: Xampp, Windows Powershell

Ritzy

Answer 14

Another, solution to export or backup all databases without affecting the server performance.

According to this docs, there're three methods to accomplish this task:

1. Backing Up All Databases of a Selected Connection
2. Backing Up the Databases From a List
3. Backing Up the Databases By a Mask

Method 1:

1. Open a plain text editor such as Notepad.

2. Type in the following code:

   Set-Location -Path "C:\Program Files\MySQL\MySQL Server 5.7\bin\" -PassThru
   
   .\mysql.exe --host=localhost --user=root --password=root --skip-column-names --execute="SELECT s.SCHEMA_NAME FROM information_schema.SCHEMATA s WHERE s.SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema') and s.SCHEMA_NAME like '%$args%' " | Out-File "D:\backup\all_databases_backup\PowerShell\DB_by_mask.txt"
   
    foreach($DBname in Get-Content "D:\backup\all_databases_backup\PowerShell\DB_by_mask.txt")
    {Write-Host $DBname
    &"C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com" /backup /connection:"User Id=root;Password=root;Host=localhost;Port=3306;Character Set=utf8" /database:$DBname /outputfile:"D:\backup\all_databases_backup\PowerShell\DB_by_mask_backup\$DBname.sql"}
   

Where:

C:\Program Files\MySQL\MySQL Server 5.7\bin\ – server path.

D:\backup\all_databases_backup\PowerShell\DB_by_mask_backup – location at your computer to store output files.

C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com – dbForge Studio for MySQL path.

3. Assign your own values to the User Id, Password, Host and Port parameters.

4. Save the file with the .PS1 file extension (for example, DB_by_mask_backup.ps1).

Script Execution

You need to execute the script with an extra parameter. For example, DB_by_mask_backup.ps1 test_DB_name.

After the backup has been completed successfully, a folder DB_by_mask_backup with SQL files will be created.

Oleander

Answer 15

For example, you export the schema and data of all databases to backup.sql as shown below. *My answer explains how to export the schema and data of all databases in detail:

mysqldump -u john -p -A > backup.sql

Then, you need to input a password after running the command above:

Enter password:

Now, you can restore all databases with backup.sql at once not selecting a database one by one as shown below. *My answer explains how to import the schema and data of all databases in detail:

mysql -u john -p < backup.sql

Then, you need to input a password after running the command above:

Enter password:

Or, after login, you can restore all databases with backup.sql and \. or source at once not selecting a database one by one as shown below:

mysql -u john -p
...
mysql> \. backup.sql

Or:

mysql -u john -p
...
mysql> source backup.sql

Truman

Answer 16

This answer is a bit late but this script may help, also improved from other answers.

#!/usr/bin/env bash

MYSQL_CONN="-uroot"
MYSQL_CONF="--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset --insert-ignore --routines"
TARGET_PATH="/root/backup/"
#BUCKET_NAME="web"
TIMESTAMP=`date +%Y%m%d`
FILENAME="BACKUP-${TIMESTAMP}"

cd ${TARGET_PATH} || exit;

echo "Dumping database: mysql with users"
mysqldump ${MYSQL_CONN} ${MYSQL_CONF} --system=all mysql | gzip > "db.sql.gz"

ExcludeDatabases="sys|tmp|information_schema|performance_schema|mysql|Database"
databases=$(mysql ${MYSQL_CONN} -e "SHOW DATABASES;" | tr -d "| " | egrep -v ${ExcludeDatabases})
for db in $databases; do
    echo "Dumping database: $db"
    mysqldump ${MYSQL_CONN} ${MYSQL_CONF} --databases "${db}" | gzip > "${db}-${TIMESTAMP}.sql.gz"
done

#- group db exports
zip -0 -q "db_${TIMESTAMP}_sql.zip" *.sql.gz;
rm *.sql.gz;

Nuncupative