mysql com table dump,如何从mysqldump中删除表

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

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?

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?

解决方案

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*

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