I’m stuck. I basically want to create a LOCAL data file (csv file) from a remote database using the OUTFILE command.
I am basically, pulling data.. and want to create it on my local file server vs. creating the outfile on the remote server. I’m limited on space remotely, thus I want to create the file locally. What am I missing on how to do this? Thanks!
This is my working syntax so far on the command line (it is creating the file I want, but on the remote server)
mysql -u test -pfoo --database test -h testdb201.name.host.com --port 3306 -ss -e "SELECT 'a','b','c' UNION SELECT col1, col2, col3 INTO OUTFILE '/tmp/mytest.csv' FIELDS TERMINATED BY ',' FROM tst_p000 limit 10"
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
According to the MySQL Select syntax, You can’t use OUTFILE to output to a file outside the server itself.
You would need to converted the tab-delimited output of the query to CSV format like this (sed command credited here).
mysql -u test -pfoo --database test -h testdb201.name.host.com --port 3306 -ss -e "SELECT 'a','b','c' UNION SELECT col1, col2, col3 " | sed 's/t/","/g;s/^/"/;s/$/"/;s/n//g' > myDump.csv
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0