从 --all-database sql.gz 文件恢复所有 mysql 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23180963/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
restore all mysql database from a --all-database sql.gz file
提问by ahhmarr
I've backed all my mysql databases with he following command
我已经使用以下命令支持了我所有的 mysql 数据库
mysqldump -u root -ppasswod --all-databases | gzip > all.sql.gz
just wanted to know will I be able to restore all of the database with following command
只是想知道我是否能够使用以下命令恢复所有数据库
gunzip < alldb.sql.gz | mysql -u root -ppassword -h localhost
can you also tell me how to back up all of mysql users too?
你也能告诉我如何备份所有的mysql用户吗？
I cant test it because I'm not sure and I don't want to break any db on my current system
我无法测试它，因为我不确定，而且我不想破坏当前系统上的任何数据库
回答by mockinterface
Yes. Generally, to restore compressed backup files you can do the following:
是的。通常，要恢复压缩的备份文件，您可以执行以下操作：
gunzip < alldb.sql.gz | mysql -u [uname] -p[pass] [dbname]
Please consult How to Back Up and Restore a MySQL Database
Note that the --all-databasesoption is applicable to backup only. The backup file itself will contain all the relevant CREATE DATABASE quux;commands for the restore.
请注意，该--all-databases选项仅适用于备份。备份文件本身将包含CREATE DATABASE quux;用于恢复的所有相关命令。
回答by Drew
This is the command I use to backup all databases in MySQL:
这是我用来备份 MySQL 中所有数据库的命令：
mysqldump -u USERNAME -p --all-databases --events --ignore-table=mysql.event --extended-insert --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers | gzip > "all_databases.gz"
- The '--all-databases' option tells the command to include all of the databases. If you want to specify one or more then remove that option and replace it with '--databases dbname1 dbname2 dbnameX'
- To backup all of your mysql users, passwords, permissions then include the 'mysql' database in your backup. The --all-databases option includes this database in the backup.
- The '--routines' option includes stored procedures and functions in the backup.
- The '--triggers' option includes any triggers in the backup.
- '--all-databases' 选项告诉命令包含所有数据库。如果要指定一个或多个，请删除该选项并将其替换为“--databases dbname1 dbname2 dbnameX”
- 要备份所有 mysql 用户、密码、权限，然后在备份中包含“mysql”数据库。--all-databases 选项将此数据库包含在备份中。
- '--routines' 选项包括备份中的存储过程和函数。
- '--triggers' 选项包括备份中的任何触发器。
To restore from a *.gz mysqldump file:
从 *.gz mysqldump 文件恢复：
gunzip < all_databases.gz | mysql -u USERNAME -p
回答by Ravi Tyagi
To display a progress bar while importing a sql.gz file, download pv and use the following:
要在导入 sql.gz 文件时显示进度条，请下载 pv 并使用以下命令：
pv mydump.sql.gz | gunzip | mysql -u root -p
pv mydump.sql.gz | gunzip | mysql -u 根 -p
If PV command is not installed on your system then try below command relatively
如果您的系统上没有安装 PV 命令，那么请相对尝试以下命令
In CentOS/RHEL
在 CentOS/RHEL
yum install pv
yum 安装 pv
In Debian/Ubuntu
在 Debian/Ubuntu 中
apt-get install pv
apt-get 安装 pv
In MAC
在 MAC
brew install pv
酿造安装光伏
Output Something like that -->
输出类似的东西-->
pv mydump.sql.gz | gunzip | mysql -u root -p dbname
pv mydump.sql.gz | gunzip | mysql -u root -p 数据库名
Enter password:
输入密码：
255MiB 0:05:49 [ 748kiB/s] [===========> ] 30%
255MiB 0:05:49 [ 748kiB/s] [============> ] 30%
回答by M6R7M
/applications/MAMP/library/bin/mysql -u root -p < all_dbs2.sql
/applications/MAMP/library/bin/mysql -u root -p < all_dbs2.sql
