asked on

MySQL: Run query from Linux command line and output results to file

I want the results of this query on my database called `mydatabase` to be written to a file named xyz.txt

SELECT * FROM Contacts LIMIT 10 >xyz.txt

Open in new window

How can I do this? This does not work:

mysql -u myusername -pmypassword -h my.host.example.com -d mydatabase SELECT * FROM Contacts LIMIT 10 >xyz.txt

Open in new window

ASKER CERTIFIED SOLUTION

arnold

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

skij

ASKER

Thanks. If I use echo, how should I escape the " and '?

For example:

SELECT concat('"' , REPLACE(REPLACE(`Email`, '"', ''), ',', '') , '","' , REPLACE(REPLACE(`FName`, '"', ''), ',', '') , '" ###') From Contacts WHERE Email <>'' LIMIT 10

Open in new window

This does not work:

echo "SELECT concat(\'\"\' , REPLACE(REPLACE(`Email`, \'\"\', \'\'), \',\', \'\') , \'\",\"\' , REPLACE(REPLACE(`FName`, \'\"\', \'\'), \',\', \'\') , \'\" ###\') From Contacts WHERE Email <>\'\' LIMIT 10" |mysql -u username --password=password -h hostname databasename >query_results.txt

Open in new window

arnold

when you use " as the enclosing quotes for the echo, you would only need to escape the double quotes.
you have to also escape the exec (`) quotes

You seem to have an errand \'" ###\' with the single quote mismatched.
echo the line out first and see how it looks.

Can you paste the pattern that you want to replace, the commas (,) if they are part of the pattern replacement need to be escaped as well.

replace(replace (\`Fname\`,'\"',''),',',''), '\" ###')

See if you can keep track of your open/close parenthesis.
you seem to have a select replace(replace()),'","' that does not seem to make sense

replace(replace(\`Email\`,'\"',''), ',' , '')
'\",\"'

See if you group the above
echo "select concat('\"',<replace Email>,'\",\"',<replacefname>) from Contacts where Email<>'' limit 10"