MySQL dump by query

not mysqldump, but mysql cli...

mysql -e "select * from myTable" -u myuser -pxxxxxxxxx mydatabase

you can redirect it out to a file if you want :

mysql -e "select * from myTable" -u myuser -pxxxxxxxx mydatabase > mydumpfile.txt

Update: Original post asked if he could dump from the database by query. What he asked and what he meant were different. He really wanted to just mysqldump all tables.

mysqldump --tables myTable --where="id < 1000"

This should work

mysqldump --databases X --tables Y --where="1 limit 1000000"

If you want to dump specific fields from a table this can be handy

1/ create temporary table with your query.

create table tmptable select field1, field2, field3 from mytable where filter1 and fileter2 ;

2/ dump the whole temporary table. then you have your dump file with your specific fields.

mysqldump -u user -p mydatabase tmptable > my-quick-dump.sql

To dump a specific table,

mysqldump -u root -p dbname -t tablename --where="id<30" > post.sql

here is my mysqldump to select the same relation from different tables:

 mysqldump --defaults-file=~/.mysql/datenbank.rc -Q -t -c --hex-blob \
 --default-character-set=utf8 --where="`cat where-relation-ids-in.sql`" \
 datenbank table01 table02 table03 table04 > recovered-data.sql

where-relation-ids-in.sql:

relation_id IN (6384291, 6384068, 6383414)

~/.mysql/datenbank.rc

[client]
user=db_user
password=db_password
host=127.0.0.1

Remark: If your relation_id file is huge, the comment of the where clause will be cut in the dump file, but all data is selected correct ;-)

I hope it helps someone ;-)