0

I have the below query that I am trying to run and save the result to a file. But I don't know the exact syntax as I am new to MYSQL.

SELECT b.*,c.* FROM  tableb as b JOIN tablec as c ON c.rootid = b.id WHERE b.status = 2;

I want to run this query on a MYSQL shell inside Ubuntu terminal. And save the results in a file. Can someone guide on the exact syntax please? I am using MYSQL Ver 14.14 Dist 5.7 version on ubuntu 18.04 terminal. Thanks!

UPDATE: I tried thebelow query exactly and I see error:

mysql -u prod -p -e"SELECT tabler.* , tablec.* FROM tablec JOIN tableo ON tablec.id = tableo.id JOIN tabler ON tableo.id = tabler.id WHERE tabler.status = 4;">/tmp/tbldata;

I am seeing this error:

you have an error in your sql syntax check the manual that corresponds to your mysql server version

any help?

SECOND UPDATE my final query looks like below:

SELECT tabler.* , tablec.* INTO OUTFILE '/tmp/data.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM tablec JOIN tableo ON tablec.id = tableo.id JOIN tabler ON tableo.id = tabler.id WHERE tabler.status = 4;

I am seeing the below error:

access denied you need (at least one of) the file privilege(s) for this operation

my used prod does not have rights. How do I give rights to write to the csv file?

Rick
  • 1,392
  • 1
  • 21
  • 52

1 Answers1

2
mysql -u root -p -e"SELECT b.*,c.* FROM  tableb as b JOIN tablec as c ON c.rootid = b.id WHERE b.status = 2" > /tmp/table_data

Note: Use your username instead of root.

Another way, you can use it INTO OUTFILE

SELECT b.*,c.* 
FROM  tableb as b 
JOIN tablec as c ON c.rootid = b.id 
WHERE b.status = 2
INTO OUTFILE '/tmp/table_data.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
Dark Knight
  • 6,116
  • 1
  • 15
  • 37
  • I tried this query and I am constantly getting error. Updated my question with error. Pls help – Rick Oct 23 '20 at 14:53
  • You have included `;` in your query. Remove that. Keep space before and after `>` – Dark Knight Oct 23 '20 at 15:23
  • Thanks. Now I am seeing file permission error: access denied you need (at least one of) the file privilege(s) for this operation. Editing the question with my latest query – Rick Oct 23 '20 at 15:41
  • for access rights try this solution https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold – Dark Knight Oct 23 '20 at 16:37