mysqldump-将多个数据库从单独的mysql帐户转储到一个文件 [英] mysqldump - Dump multiple databases from separate mysql accounts to one file

问题描述

我使用的标准mysqldump命令是

The standard mysqldump command that I use is

mysqldump --opt --databases $dbname --host=$dbhost --user=$dbuser --password=$dbpass | gzip > $filename

转储多个数据库

mysqldump --opt --databases $dbname1 $dbname2 $dbname3 $dbname_etc --host=$dbhost --user=$dbuser --password=$dbpass | gzip > $filename

我的问题是如何将来自不同MySQL帐户的多个数据库转储到一个文件中?

My question is how do you dump multiple databases from different MySQL accounts into just one file?

更新:当我指的是1个文件时，我的意思是1个压缩文件，其中包含针对不同站点的不同sql转储.

UPDATE: When I meant 1 file, I mean 1 gzipped file with the difference sql dumps for the different sites inside it.

推荐答案

似乎没有人澄清，所以我要给我2美分.

Nobody seems to have clarified this, so I'm going to give my 2 cents.

这里要注意，我的经验是在BASH中进行的，并且可能是排他性的，因此变量和循环在您的环境中可能会有所不同.

Going to note here, my experiences are in BASH, and may be exclusive to it, so variables and looping might work different in your environment.

获得存档中包含单独文件的最佳方法是使用ZIP或TAR，由于其简单性和可用性，我更喜欢使用tar.

The best way to achieve an archive with separate files inside of it is to use either ZIP or TAR, i prefer to use tar due to its simplicity and availability.

Tar本身不进行压缩，但是与bzip2或gzip捆绑在一起可以提供出色的效果.由于您的示例使用的是gzip，因此我将在演示中使用它.

Tar itself doesn't do compression, but bundled with bzip2 or gzip it can provide excellent results. Since your example uses gzip I'll use that in my demonstration.

首先，让我们解决MySQL转储的问题，mysqldump命令不会分离文件(无论如何，我还是知道).因此，让我们为每个数据库创建一个文件提供一个小的解决方法.

First, let's attack the problem of MySQL dumps, the mysqldump command does not separate the files (to my knowledge anyway). So let's make a small workaround for creating 1 file per database.

mysql -s -r -p$dbpass --user=$dbuser -e 'show databases' | while read db; do mysqldump -p$dbpass --user=$dbuser $db > ${db}.sql; done

因此，现在我们有了一个字符串，该字符串将显示每个文件的数据库，并将这些数据库导出到您需要的位置，只需在>符号后编辑该部分即可.

So now we have a string that will show databases per file, and export those databases out to where ever you need simply edit the part after the > symbol

接下来，让我们看一下TAR的语法

Next, let's add some look at the syntax for TAR

tar -czf <output-file> <input-file-1> <input-file-2>

由于此配置，它使我们可以指定大量要存档的文件.

because of this configuration it allows us to specify a great number of files to archive.

选项细分如下.

c-压缩/创建存档

z-GZIP压缩

f-输出到文件

j-bzip压缩

我们的下一个问题是保留所有新创建文件的列表，我们将扩展while语句，以便在运行MySQL内部发现的每个数据库时附加到变量.

Our next problem is keeping a list of all the newly created files, we'll expand our while statement to append to a variable while running through each database found inside of MySQL.

DBLIST=""; mysql -s -r -p$dbpass --user=$dbuser -e 'show databases' | while read db; do mysqldump p$dbpass --user=$dbuser $db > ${db}.sql; DBLIST="$DBLIST $DB";  done 

现在我们有了一个DBLIST变量，我们可以使用它来输出将要创建的所有文件，然后我们可以修改1行语句以在处理完所有内容后运行tar命令.

Now we have a DBLIST variable that we can use to have an output of all our files that will be created, we can then modify our 1 line statement to run the tar command after everything has been handled.

DBLIST=""; mysql -s -r -p$dbpass --user=$dbuser -e 'show databases' | while read db; do mysqldump p$dbpass --user=$dbuser $db > ${db}.sql; DBLIST="$DBLIST $DB";  done && tar -czf $filename "$DBLIST"

这是一种非常粗糙的方法，不允许您手动指定数据库，因此，使用以下命令将创建一个包含所有指定数据库的TAR文件.

This is a very rough approach and doesn't allow you to manually specify databases, so to achieve that, using the following command will create you a TAR file that contains all of your specified databases.

DBLIST=""; for db in "<database1-name> <database2-name>"; do mysqldump -p$dbpass --user=$dbuser $db > ${db}.sql; DBLIST="$DBLIST $DB.sql";  done && tar -czf $filename "$DBLIST"

从MySQL数据库遍历MySQL数据库来自以下stackoverflow.com问题"

The looping through MySQL databases from the MySQL database comes from the following stackoverflow.com question "mysqldump with db in a separate file" which was simply modified in order to fit your needs.

要使脚本在1个衬里中自动清理，只需在命令末尾添加以下内容

And to have the script automatically clean it up in a 1 liner simply add the following at the end of the command

&& rm "$DBLIST"

使命令看起来像这样

DBLIST=""; for db in "<database1-name> <database2-name>"; do mysqldump -p$dbpass --user=$dbuser $db > ${db}.sql; DBLIST="$DBLIST $DB.sql";  done && tar -czf $filename "$DBLIST" && rm "$DBLIST"

这篇关于mysqldump-将多个数据库从单独的mysql帐户转储到一个文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！