一次导出和导入所有 MySQL 数据库

Export and Import all MySQL databases at one time

提问by NewUser

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?

我想保留所有 MySQL 数据库的备份。我有 100 多个 MySQL 数据库。我想同时导出所有这些，然后再次将它们全部导入我的 MySQL 服务器。我怎样才能做到这一点？

回答by Shakti Singh

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的文档。您可能想使用评论中提到的一些选项：

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

回答by jruzafa

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

回答by NetVicious

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

我在这个问题上看到的所有答案都可能与某些数据库中的字符集有关，因为mysqldump在 shell 运算符中将退出重定向到文件的问题>。

To solve this problem you should do the backupwith a command like this

要解决此问题，您应该使用这样的命令进行备份

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

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

进行良好的 BD还原，而不会出现任何字符集问题。显然，您可以根据需要更改默认字符集。

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

回答by PeterM

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.cnfcheck this answer https://serverfault.com/a/143587/62749

这个不会问密码，在cron中可以用。要在.my.cnf检查此答案中存储密码https://serverfault.com/a/143587/62749

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

还为那些不太熟悉 bash 脚本的人提供了注释。

#!/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"

回答by msn

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

为什么解析格式化输出而mysql命令可以直接做你想做的？

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.

列出数据库名称，仅此。

回答by Internal Server Error

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:

从不同的 MySQL 版本导出和导入时要小心，因为 mysql 表可能有不同的列。如果您不走运，授予权限可能无法正常工作。我创建了这个脚本 (mysql_export_grants.sql) 来转储用于导入新数据库的授权，以防万一：

#!/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'

回答by ashikpatel

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.

-- 警告：跳过表 mysql.event 的数据。明确指定 --events 选项。

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

希望它会有所帮助:)

回答by Peter VARGA

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

我已经在 4 年前写了这条评论，现在决定回答这个问题。

The scriptfrom jruzafacan be a bit simplified:

该脚本从jruzafa可以有点简单：

#!/bin/bash

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

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

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.

1. 排除的数据库 - 通常是系统表 - 在变量中提供 ExcludeDatabases
2. 请注意，密码是在命令行中提供的。这被认为是不安全的。研究这个问题。

回答by Indal Raj

Export all databases in Ubuntu

导出 Ubuntu 中的所有数据库

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

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

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

回答by Majutharan Majutharan

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

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

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

注意：-u"您的用户名"-p"您的密码"