Hi,
How do i exact to my local machine?
select * into outfile 'C:\Info\table1.txt' fields terminated by ',' from table1;
This query creates under info folder as table1.txt on the server, how do i specify my local machine path here?
Thanks.
+1
A:
This query creates under info folder as table1.txt on the server, how do i specify my local machine path here?
If you are accessing the remote machine using SSH, you can't, at least not directly. As far as I know, it is not possible to pipe the result of a INTO OUTFILE operation.
Suggestions for workarounds:
Create a network share on your local machine that is reachable by the server (if possible)
Create a network share (or FTP account... or WebDAV account...) on the server, store the dump there and fetch it from there
Pekka
2010-09-20 21:56:31
@Pekka - Thanks, but i don't have permission to access server folder, do we have any other way?
Sharpeye500
2010-09-20 21:58:26
@Pekka - The DB is in a remote server, which can be accessed using todd, i am trying to run the query to export to my local machine's path (c:\somefolder\filename.txt).
Sharpeye500
2010-09-20 22:07:53
@Sharpeye I don't know what todd is. Neither does Wikipedia: http://en.wikipedia.org/wiki/Todd
Pekka
2010-09-21 08:18:04
@Pekka - http://en.wikipedia.org/wiki/Toad_for_MySQL
Sharpeye500
2010-09-21 16:59:08
A:
You can't do it directly. The OUTFILE syntax only applies to the server itself. However, you can do :
c:\> mysql -u username -h nameofserver -p -e "SELECT ... FROM database.table WHERE ..." > c:\info\table1.txt
if you've configured MySQL to allow remote connections from your machine.
Marc B
2010-09-20 21:57:51
@Marc - Many thanks, do i need to run this in command prompt? or can i run it directly on server's query browser?
Sharpeye500
2010-09-20 22:01:07
@Sharpeye you need to give more information about what your server setup looks like. We still have no idea through what kind of connection you are accessing the server.
Pekka
2010-09-20 22:04:44
You have to run that from your local machine, telling the mysql monitor to connect to your MySQL server, which means MySQL has to have allow TCP connections and an account for you to access via TCP. Pekka's solution might be better - generate the file on the server with SELECT ... OUTFILE which you can then transfer by some other means.
Marc B
2010-09-20 22:13:52
@Marc - I ran that in mysql client command prompt, i get output disabled error, any thoughts?
Sharpeye500
2010-09-20 22:17:28
No write permissions on the directory or file you were trying to redirect output into? Was it a windows error, or from the mysql monitor?
Marc B
2010-09-21 00:00:13