Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I occasionally need to extract a single record from a mysqlbackup

To do this, I first extract the single table I want from the backup...

sed -n -e '/CREATE TABLE.*usertext/,/CREATE TABLE/p' 20120930_backup.sql > table.sql

In table.sql, the records are batched using extended inserts (with maybe 100 records per insert before it creates a new line starting with INSERT INTO), so they look like...

INSERT INTO usertext VALUES (1, field2 etc), (2, field2 etc),

INSERT INTO usertext VALUES (101, field2 etc), (102, field2 etc), ...

Im trying to extract record 239560 from this, using...

sed -n -e '/(239560.*/,/)/p' table.sql > record.sql

Ie.. start streaming when it finds 239560, and stop when it hits the closing bracket

But this isnt working as I hoped, it just results in the full insert batch being output.

Please can someone give me some pointers as to where Im going wrong?

Would I be better off using awk for extracting segments of lines, and use sed for extracting lines within a file?

share|improve this question
Could you please show us an example of input and the expected output? – quanta Oct 7 '12 at 16:08
up vote 2 down vote accepted

Based on Iain's answer I've come up with this sed trick...

$ sed  's/\(INSERT[^(]*\).*\((239560,[^)]*)\),.*/\1\2/' sample.sql | tee 1-record.sql
INSERT INTO usertext VALUES (239560, 2, 3, 4)

-- Variation without using sed... You can extract values of some row/insert that match just that one number.

$ cat sample.sql
INSERT INTO usertext VALUES (101, field2 etc), (102, field2 etc), (239560, 2, 3, 4), (5, 8, 9)
INSERT INTO usertext VALUES (101, field2 etc), (102, field2 etc), (1239560, 2, 3, 4), (5, 8, 9)
$ egrep -o '(\(239560\,[^)]+\))' sample.sql        
(239560, 2, 3, 4)

rest of insert can be scripted easily if you use -n switch with grep to catch line of match and then just cut everything up to first (

share|improve this answer
Many thanks :-) Both your and Iains sed were equally useful, but your egrep seems to be exactly what I was looking for – carpii Nov 16 '12 at 3:35

Try something like this which may be tuned to your example but hopefully will get you on the right track

given an input file like this

INSERT INTO usertext VALUES (101, field2 etc), (102, field2 etc), (103,filed2, etc), ...

sed  's/.*\((102,[^)]*)\),.*/\1/' f
(102, field2, etc)

What this does is split your data into 3 sections, remembers the important section using (...) and substitutes this for the whole line.

  • .* everything before the text you want
  • \((102,[^)]*)\) your interesting text
  • ,.* everything else
share|improve this answer
(102.*)) will capture everything till last block of ()s not just one block containing 102 and related row values. I tried this using GNU sed 4.2.1 and got different match from one in your code block – Hrvoje Špoljar Oct 7 '12 at 21:17
@Hrvojepoljar: Fixed - Thanks - I should have added another block of data to test, like I said ... tuned to the example. – Iain Oct 7 '12 at 21:56

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.