如何从mysqldump中删除表 [英] How do I delete a table from a mysqldump

问题描述

如何删除其中包含许多表的mysqldump中一个大表的输出?

How do I delete the output for one big table inside a mysqldump with lots of tables in it?

我的数据库转储大小为6 GB，但是其中90％只是一个日志记录表"cache_entries"，不再需要备份.

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.

如何轻松删除转储中描述大型日志记录表的那个位?

How can I easily remove that bit inside the dump, that describes the large logging-table?

我发现了这一点: http://gtowey.blogspot.de/2009/11/restore-single- table-from-mysqldump.html

I found this: http://gtowey.blogspot.de/2009/11/restore-single-table-from-mysqldump.html

示例:

grep -n 'Table structure' dump.sql

，然后例如:

sed -n '40,61 p' dump.sql > t2.sql

但是我该如何改变我的需求呢?

But how can I change that for my needs?

推荐答案

我发现了这个bash脚本，该脚本使用csplit将一个数据库的转储拆分为每个表的单独文件，上下文行):

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):

#!/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 $0 DUMP_FILE"
fi

csplit -s -ftable $1 "/-- 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*

来源: gist.github.com/1608062

并进行了一些增强: 如何拆分输出从mysqldump转换成较小的文件?

and a bit enhanced: How do I split the output from mysqldump into smaller files?

一次，每个表都有单独的文件，您可以删除不需要的表，并根据需要将它们粘合在一起

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

这篇关于如何从mysqldump中删除表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！