How do I delete a table from a mysqldump

11,368

Solution 1

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*

Source: gist.github.com/1608062

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

Solution 2

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?

Change the grep command to include "Dumping data for table":

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:

sed '66,75 d' dump.sql > cleandump.sql
Share:
11,368
rubo77
Author by

rubo77

SCHWUPPS-DI-WUPPS

Updated on July 21, 2022

Comments

  • rubo77
    rubo77 over 1 year

    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?

    I found this: 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?