Mysql dump into SQL format with mysql -e?

Question

I have been looking for solution to export table with specific fields/columns. I am using Ubuntu commandline to do that. Luckily MySQL dump by query help me achieve my goal with

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

But, I am getting plain text file. I want to get file dumped as SQL format. So can I get dumped file with mydumpfile.sql instead of mydumpfile.txt?

There is an updated answer in the page you mentioned, use the mysqldump command instead. — Simon MC. Cheng, Aug 10 '16 at 06:16
I assume you don't mean just changing the file extension? Do you mean insert statements? — Sami Kuhmonen, Aug 10 '16 at 06:17
@SimonMC.Cheng How to use SELECT in mysqldump? I was told it was not possible in mysqldump? — tapaljor, Aug 10 '16 at 11:43
Not sure what is your problem... if you want to have sql statement output with table schema, you can use something like... [mysqldump mydatabase mytable --where="mycolumn = myvalue > dump.sql] — Simon MC. Cheng, Aug 10 '16 at 14:38
@SimonMC.Cheng I want SELECT statement in mysqldump which is apparently not possible. — tapaljor, Aug 11 '16 at 09:04

score 1 · Answer 1 · answered Aug 10 '16 at 08:30

1

If you want full insert statements you have to use mysqldump. if you want comma separated (CSV) you have to use SELECT INTO OUTFILE from the mysql client.

mysql -e does a query on the database and shows result in the console. Your redirect '>' at the end means that display get's saved to a file. That saved file is not very usefull for programming work. It's only for humans.

answered Aug 10 '16 at 08:30

middlestump

1,035
8
22

I think CSV is only option I am left. When I try that it says access denied. Should run on commandline or inside mysql? – tapaljor Aug 10 '16 at 08:53
can do with in the console but you don't have the select into outfile permission (or so it seems) – middlestump Aug 10 '16 at 08:57

Mysql dump into SQL format with mysql -e?

1 Answers1