How to dump result of a sql query into a file

Question

Ok so I am in a situation where I need to dump the results of a sql query into a file and this is for backup purpose I tried running th following from my terminal :

mysql -e "select * from products where brand_id=5" -u root -p database_name > dumpfile.sql

this dumps the data but with the "insert into statements", so if later on I want to dump this data back into my database I will be re-composing it. And that is not the correct approach. So please suggest me how do I dump query results for back up purpose ?

Answer 1

The following command line (using --where option) should work:

mysqldump -u root -p database_name products --where="brand_id=5" > dumpfile.sql

More information about the mysqldump command: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

Answer 2

use mysqldump

mysqldump -u root -p database_name products --where "brand_id=5"> dump.sql

For more read: http://mechanics.flite.com/blog/2012/11/15/3-methods-to-extract-a-subset-of-your-data-using-mysqldump/