Linux 从 mysql.sql 转储文件中提取表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3935939/
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
Extracting a table from a mysql.sql dump file
提问by Ryan
I have a products table in a mysql.sql file. I wish to extract that one table and place it in it's own file. How would you go about doing this?
我在 mysql.sql 文件中有一个产品表。我希望提取那张表并将其放入它自己的文件中。你会怎么做呢？
回答by Pekka
I know of no tool to parse raw mySQL dump files this way.
我知道没有工具可以通过这种方式解析原始 mySQL 转储文件。
Either copy+paste it (potentially cumbersome) or import it into a temporary database, drop everything else, and dump the table back into a file.
复制+粘贴它（可能很麻烦）或将其导入临时数据库，删除其他所有内容，然后将表转储回文件。
回答by Marc B
If the dump was done with the extended insert syntax, then the actual table data will be done as a single line within the dump file:
如果转储是使用扩展插入语法完成的，那么实际的表数据将作为转储文件中的一行完成：
INSERT INTO tablename (field, ...) VALUES (data, ...), (data, ...), (etc..)
which you could simply grep out. Extracting the actual table definition will be harder, though it SHOULD be immediately above the data line. Don't have access to a proper shell at the moment, but going off the top of my head, something like this might do the trick (in pseudo-code):
你可以简单地grep出来。提取实际的表定义会更困难，尽管它应该紧挨着数据线的上方。目前无法访问适当的外壳，但在我的脑海中，像这样的事情可能会起作用（在伪代码中）：
# retrieve line # that data insertion for the wanted table occurs on
DATALINE=`grep -l 'INSERT INTO tablename' dumpfile.sql`
# limiting ourselves to the part of the file up to the data line, find the line number
# of the last CREATE TABLE, which SHOULD be the one creating the table for the data
CREATELINE=`head -$DATALINE|grep -l 'CREATE TABLE'|tail -1|awk '{"print "}'`
The you can extract the creation DDL with judicious head/tail and the line numbers we just retrieved:
您可以使用明智的头/尾和我们刚刚检索到的行号提取创建 DDL：
CREATE=`head -$CREATELINE dumpfile.sql|tail -$($CREATELINE - $DATALINE - 1)`
bear in mind that I'm going off the top of my head on this, so it's almost guaranteed to not work, but should be enough to get you started.
请记住，我对此感到非常头疼，因此几乎可以肯定它不起作用，但应该足以让您入门。
回答by Pierre de LESPINAY
I found this nice solution, you have to download this scripton your server and type
我找到了这个不错的解决方案，你必须在你的服务器上下载这个脚本并输入
$> ./MyDumpSplitter.sh yourfile.sql your_table_name
This will extract your table into your_table_name.sql
这会将您的表提取到 your_table_name.sql
Optionnal
可选
Now you can rename it using this type of command
现在您可以使用这种类型的命令重命名它
$> sed -i 's/`your_table_name`/`your_table_name2`/g' your_table_name.sql
And re-injecting it with
并重新注入它
mysql> source your_table_name.sql;
回答by charlesbridge
I ran into that problem a while ago and wrote a Perl script. It worked well, but it was an older version of MySQL. Call it like:extract.pl -table=TABLENAME mysqldumpfile.sql > recovered_table.sql
不久前我遇到了这个问题并编写了一个 Perl 脚本。它运行良好，但它是旧版本的 MySQL。像这样称呼它：extract.pl -table=TABLENAME mysqldumpfile.sql > recovered_table.sql
#!/usr/bin/perl -s -wnl
#extract a single table from a mysql dump
BEGIN {
$table or warn
"Usage: $ sed -n -e '/CREATE TABLE.*products/,/CREATE TABLE/p' mysql.sql > products.sql
-table=TABLE_TO_EXTRACT mysqldumpfile.sql"
and exit 1;
}
/^DROP TABLE IF EXISTS `$table`/ .. /^UNLOCK TABLES;$/ and print;
回答by Alaa
simply you can run stream editor for filtering and transforming text command like below:
只需运行流编辑器来过滤和转换文本命令，如下所示：
sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' dumpfile > dbname.sql 2>error
回答by Steven Lizarazo
If you want to extract a database you could use:
如果要提取数据库，可以使用：
sed -n '/^-- Current Database: `blogs`/,/^-- Current Database: `/p' dump.sql > blogs.sql 2>error
For example:
例如：
##代码##