MySQL转储结构的所有表和一些数据

I'm trying to dump the structure of all the tables in our database, and then only the data of the ones I specifically want, but i seem to be doing something wrong as I'm not getting the empty tables created for the ones I exclude from the data dump.

我试图转储数据库中所有表的结构，然后只转储我特别想要的表的数据，但是我似乎做错了什么，因为我没有为我从数据转储中排除的表创建空表。

I have a text file which specifies which tables I want to dump the data for (called showtables.txt):

我有一个文本文件，它指定了我要转储数据的表(名为showtable .txt):

SHOW TABLES FROM mydb
WHERE Tables_in_mydb NOT LIKE '%_history'
AND Tables_in_mydb NOT LIKE '%_log';

I am then doing this command to dump the structure of all tables, and then the data of the tables returned by that query in the text file:

然后，我执行此命令转储所有表的结构，然后在文本文件中转储该查询返回的表的数据:

mysqldump -u root -pmypassword mydb --no-data > mydump.sql; mysql -u root -pmypassword < showtables.txt -N | xargs mysqldump mydb -u root -pmypassword > mydump.sql -v

I am getting the dump of all the tables included in the results of the showtables query, but I am not getting the structures of the rest of the tables.

我正在获取showtables查询结果中包含的所有表的转储，但是我没有获得其余表的结构。

If I run just the structure part as a single command, that works fine and I get the structures dumped for all tables. But combining it with the data dump seems to not work.

如果我只运行结构部分作为一个单一的命令，那就很好了，我得到了所有表的转储结构。但是将其与数据转储相结合似乎是行不通的。

Can you point me to where I'm going wrong with this?

你能指出我哪里出了问题吗?

Thanks.

谢谢。

2 个解决方案

#1

I think you've got the order of your commandline arguments wrong (the redirection to a file should be the end), and you need an extra parameter for xargs so we can specify the database name to mysqldump.

我认为您的命令行参数的顺序是错误的(重定向到文件应该是结束)，并且您需要xargs额外的参数，以便我们可以将数据库名指定到mysqldump。

Additionally, you need to append >> the dump data, otherwise you'd be overwriting the mydump.sql file for each table:

此外，需要附加>>转储数据，否则将覆盖mydump。每个表的sql文件:

mysqldump -u root -pmypassword mydb --no-data > mydump.sql
mysql -u root -pmypassword -N < showtables.txt | xargs -I {} mysqldump -v -u root -pmypassword mydb {} >> mydump.sql

Sources: http://www.cyberciti.biz/faq/linux-unix-bsd-xargs-construct-argument-lists-utility/

来源:http://www.cyberciti.biz/faq/linux-unix-bsd-xargs-construct-argument-lists-utility/

#2

Working off of Jon's answer, but the -I in xargs will run a separate mysqldump command for each table. Easier to just allow the xargs default which appends the output of the previous command to the next command. mysqldump's last argument is a list of all tables you'd like to dump.

使用Jon的答案，但是xargs中的-I将为每个表运行一个单独的mysqldump命令。只允许xargs默认值，它将前一个命令的输出附加到下一个命令。mysqldump的最后一个参数是要转储的所有表的列表。

My solution also shows connecting through a bastion host. gzip'ing before streaming over the SSH connection is vastly faster than sending the uncompressed SQL over the wire.

我的解决方案也显示通过一个bastion主机连接。在通过SSH连接进行流操作之前，gzip比通过网络发送未压缩的SQL要快得多。

FILE=~/production.sql.gz
HOST=ext-db-read-0.cdzvblmx0n9h.us-west-1.rds.amazonaws.com
USER=username
PASS="s3cret"
DB=myapp_prod
EXCLUDE="'activities', 'changelogs'"

ssh bastion.mycompany.com <<EOF > $FILE
    mysqldump -h $HOST -u $USER -p$PASS $DB --no-data | gzip
    mysql -h $HOST -u $USER -p$PASS -N -e "SHOW TABLES WHERE Tables_in_$DB NOT IN ($EXCLUDE)" $DB | xargs mysqldump -v -h $HOST -u $USER -p$PASS $DB | gzip
EOF

If you don't want to save .gz just pipe it through gzip -d:

如果你不想保存。gz，就通过gzip -d来传输:

ssh bastion.mycompany.com <<EOF | gzip -d > $FILE etc

ssh bastion.mycompany.com < $FILE等。

or directly to your local db:

或直接到你的本地数据库:

ssh bastion.mycompany.com <<EOF | gzip -d | mysql -uroot myapp_development

#1