Geek Answers Handbook

Delete insert data from specific table from mysqldump (sed?) Output

I have huge mysqldump output and you want to exclude inserts for a specific table.

The file is as follows:

-- -- Dumping data for table `big_table` -- INSERT INTO `big_table` ... INSERT INTO `big_table` ... -- -- Table structure for table `next_table` --

How can I cut out those inserts that are between "Dumping data for table big_table" and the next "Table structure for table". The file is too large to be inserted into a text editor.

+4
source share
3 answers

One solution using sed . It searches for all rows between literals -- Dumping data for table 'big_table' and -- Table structure for table . And comment on those lines that do not begin with -- .

Assuming the contents of infile :

 1 2 3 4 -- -- Dumping data for table `big_table` -- INSERT INTO `big_table` ... INSERT INTO `big_table` ... -- -- Table structure for table `next_table` -- 1 2 3 4 5 6

Launch command:

 sed -e ' /-- Dumping data for table `big_table`/,/-- Table structure for table/ { /^--/! s/^/--/ } ' infile

With the following output:

 1 2 3 4 -- -- Dumping data for table `big_table` -- -- --INSERT INTO `big_table` ... --INSERT INTO `big_table` ... -- -- -- -- Table structure for table `next_table` -- 1 2 3 4 5 6
+4
source

I overlooked the fact that all inserts, of course, start with the name of the table. So i can just use

  grep -v "INSERT INTO \` big_table \ `" dump.sql> dump_stripped.sql
+8
source

How about a workaround:

  • Backing up a table that you do not want to update (rename and then create, possibly an empty version)
  • Run dump
  • Restore the table and override the one that was dumped

Hope this helps.

0
source

More articles:


All Articles