bash 如何从 mysqldump 中删除表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9895995/
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
How do I delete a table from a mysqldump
提问by rubo77
How do I delete the output for one big table inside a mysqldump with lots of tables in it?
如何删除包含大量表的 mysqldump 中的一个大表的输出？
I have a dump of a database that is 6 GB large, but 90% of it is only one logging-table "cache_entries", that I don't need anymore inside my backup.
我有一个 6 GB 大的数据库转储，但其中 90% 只是一个日志记录表“cache_entries”，我的备份中不再需要它。
How can I easily remove that bit inside the dump, that describes the large logging-table?
如何轻松删除转储中描述大型日志记录表的那个位？
I found this: http://gtowey.blogspot.de/2009/11/restore-single-table-from-mysqldump.html
我发现了这个：http: //gtowey.blogspot.de/2009/11/restore-single-table-from-mysqldump.html
Example:
例子：
grep -n 'Table structure' dump.sql
and then for example:
然后例如：
sed -n '40,61 p' dump.sql > t2.sql
But how can I change that for my needs?
但是我怎样才能根据我的需要改变它呢？
回答by HighKing
You could use 'n,n d' to remove certain lines. I guess in your case you do want to have the table in question, but don't want the data?
您可以使用 'n,nd' 删除某些行。我想在您的情况下，您确实想要有问题的表格，但不想要数据？
Change the grep command to include "Dumping data for table":
更改 grep 命令以包含“为表转储数据”：
grep -n 'Table structure\|Dumping data for table' dump.sql 19:-- Table structure for table `t1` 37:-- Dumping data for table `t1` 47:-- Table structure for table `t2` 66:-- Dumping data for table `t2` 76:-- Table structure for table `t3` 96:-- Dumping data for table `t3`
Now, if you don't want the data for t2, you could use:
现在，如果您不想要 t2 的数据，您可以使用：
sed '66,75 d' dump.sql > cleandump.sql
回答by rubo77
I found this bash script, that splits a dump of one database into separate filed for each table, using csplit(that splits a file into sections determined by context lines):
我找到了这个 bash 脚本，它将一个数据库的转储拆分为每个表的单独文件，使用csplit（将文件拆分为由上下文行确定的部分）：
#!/bin/bash
####
# Split MySQL dump SQL file into one file per table
# based on http://blog.tty.nl/2011/12/28/splitting-a-database-dump
####
if [ $# -ne 1 ] ; then
echo "USAGE ubuntu@ubuntu:~$ grep -n [34] a.txt
3:3
4:4
ubuntu@ubuntu:~$ cat a.txt
1
2
3
4
5
6
ubuntu@ubuntu:~$ grep [34] a.txt
3
4
ubuntu@ubuntu:~$ sed '3,4d' a.txt > b.txt
ubuntu@ubuntu:~$ cat b.txt
1
2
5
6
ubuntu@ubuntu:~$
DUMP_FILE"
fi
csplit -s -ftable "/-- Table structure for table/" {*}
mv table00 head
for FILE in `ls -1 table*`; do
NAME=`head -n1 $FILE | cut -d$'\x60' -f2`
cat head $FILE > "$NAME.sql"
done
rm head table*
Source: gist.github.com/1608062
and a bit enhanced: How do I split the output from mysqldump into smaller files?
并有所增强： 如何将 mysqldump 的输出拆分为较小的文件？
once, you have separate files for each table, you can delete the unwanted tables and glue them together if needed with
一次，每个表都有单独的文件，如果需要，您可以删除不需要的表并将它们粘合在一起
cat table* >glued_sqldump.sql
cat table* >glued_sqldump.sql
回答by johnshen64
you need to find the create table statement of your table, and find the next create table statement. say they are n1 and n2.
你需要找到你的表的create table语句，然后找到下一个create table语句。说它们是 n1 和 n2。
then you can just delete them with sed as above. sed 'n1,n2d' dump.sql > new.sql
然后你可以像上面一样用 sed 删除它们。sed 'n1,n2d' dump.sql > new.sql
you can just grep create table and note the line numbers for your prework.
你可以只 grep create table 并记下你准备工作的行号。
here is a demo.
这是一个演示。
##代码##