Search Marketing


MySQL INTO OUTFILE on Remote Machine

Fri Nov 06, 2009 8:01 pm
<<     >>
Comments: 0 Views: 1140

Using your local machine to access a remote database server with INTO OUTFILE

If you have a database on a remote machine and want to use INTO OUTFILE to create a file with the output of the query and have it automatically sent to your local machine you might assume the following command would work:

mysql -u -p database_name -h -e"SELECT * FROM database.table INTO OUTFILE '/tmp/file.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n'; "

Then once it gives you a problem you might assume that you did not set your remote MySQL permissions correctly.

So you add a grant permission for your remote user in MySQL, but it still does not work. Then you figure that your local machine has its permissions set improperly, so you fiddle around with those.

Solution: MySQL cannot write files on remote machines. As far as I am aware unless you have a MASTER/SLAVE setup MySQL never has the capability to write files on a remote machine.

"If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE."

You should notice that a command like:
mysql -u -p database_name -h -e"SELECT * FROM database.table"
will output the correct data to your local screen but it will not be formated, since the command basically mimics the local command line access screen.

The best way to handle this situation is to write a script in perl or php that adds tabs or space and creates the file and then scp's it to your local machine.

URL: http: (ex.
Math (22 + 5)
* required

© 2019 Christonium LLC
Terms of Use