dump query on multiple table

Question

Im trying to have a sql output file from a query between three tables. Below is my code:

mysqldump --user=user --password=password --host=localhost dbname --where="SELECT v.ITEM_ID , v.CODE ,\
s.SENSOR , d.DESTINATIONCODE \
FROM  V_TABLE v, S_TABLE s ,D_TABLE d \
WHERE s.ITEM_ID = v.ITEM_ID \
AND s.CREATIONDATETIME <  '2014-2-16 00:00:00'\
AND v.DESTINATION_ID=d.ID" > /var/www/dumps/output_15_12_2017.sql

this give me an error:

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `COMMANDSPOOL` WHERE SELECT v.ITEM_ID , v.CODE ,s.SENSOR , d.DESTINATIONCODE FROM  V_TABLE v, S_TABLE s ,D_TABLE d  WHERE s.ITEM_ID = v.ITEM_ID AND s.CREATIONDATETIME <  '2014-2-16 00:00:00' AND v.DESTINATION_ID=d.ID': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT v.ITEM_ID , v.CODE ,s.SENSOR , d.DESTINATIONCODE FROM  V_TABLE' at line 1 (1064         )

maybe it is an error in using \

Possible duplicate of How to use mysqldump for a portion of a table? — Martin Schneider
– Martin Schneider, Commented Dec 15, 2017 at 10:10
--where can only provide the where clause, not a complete select. use SELECT * INTO OUTFILE 'output_14_12_2017.sql' for that. — Martin Schneider
– Martin Schneider, Commented Dec 15, 2017 at 10:11
sorry, i don't understand, i wrote: --where="SELECT v.ITEM_ID , v.CODE ... or you mean in > /var/www/dumps/output_15_12_2017.sql ? — pikimota
– pikimota, Commented Dec 15, 2017 at 10:14
look at the answer from @jeremy-jones, it's explained in there :-) — Martin Schneider
– Martin Schneider, Commented Dec 15, 2017 at 10:23

Jeremy Jones · Accepted Answer · 2017-12-15 10:18:54Z

2

To use a query like that you could send it into the mysql client, (not the mysqldump utility) and then redirect that output to the file instead:

echo "SELECT v.ITEM_ID , v.CODE ,\
s.SENSOR , d.DESTINATIONCODE \
FROM  V_TABLE v, S_TABLE s ,D_TABLE d \
WHERE s.ITEM_ID = v.ITEM_ID \
AND s.CREATIONDATETIME <  '2014-2-16 00:00:00'\
AND v.DESTINATION_ID=d.ID\
" | mysql --user=user --password=password --host=localhost dbname > /var/www/dumps/output_15_12_2017.sql

If you're going this route then the --batch and --table options can be useful.

answered Dec 15, 2017 at 10:18

Jeremy Jones

5,7414 gold badges23 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pikimota Over a year ago

this worked for me. First I log into mysql mysql -u name -p dbname and then run the query select ..... into outfile '....sql';

Collectives™ on Stack Overflow

dump query on multiple table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related