1

This is similar to another question (http://stackoverflow.com/questions/935556/mysql-dump-by-query) but I hope different enough.

I want to export a specific items from a db table so I can back it up for possible future restoration.

I'm already using something like this from another table...

mysqldump --user="user" --password="password" --opt -w"id=1" databasebname tablename

But now I need something more complex.

I have the following query that I need to use to generate the export data...

SELECT tbl2.*
FROM tbl1, tbl2
WHERE tbl2.parent = tbl1.child
AND tbl1.id = 1

Can I do this with mysqldump?

Or do I need to think of a different approach?

(If it helps, this is all being done from within a bash script)

nedlud
  • 1,820
  • 4
  • 19
  • 33

2 Answers2

2

I think this will accomplish what you're looking for:

SELECT tbl2.*
FROM tbl1, tbl2
WHERE tbl2.parent = tbl1.child
AND tbl1.id = 1
INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

This will save your data into a CSV file. You can also save into other formats. I found a helpful tutorial on this topic a while back from here

Brian Driscoll
  • 19,373
  • 3
  • 46
  • 65
  • I like mysqldump because it writes all the insert statements for me. But I'll take a CSV solution. Thanks for that. – nedlud Feb 22 '11 at 04:21
1

You can do your select statement as normal and then add to the end of it

INTO OUTPUT FILE 'path/to/file'

That file can later be used with the LOAD DATA command as a backup.

Of course, if it were me, I'd feel better just dumping the whole table.

user470714
  • 2,858
  • 1
  • 28
  • 34