Jump to content


Photo

Output DB to text file


  • Please log in to reply
3 replies to this topic

#1 NevadaSam

NevadaSam
  • Members
  • PipPip
  • Member
  • 26 posts
  • LocationUSA

Posted 28 September 2006 - 11:46 PM

I want to dump the contents of a table from my database into a text file. My logon (host, username, password) are all correct. And my query syntax is good.

SELECT * INTO OUTFILE 'books.txt'
FIELDS TERMINATED BY '|'
FROM books

But I get this error when I run it:
Error 1045: Access denied for user 'user'@'localhost' (using password: YES)
I was hoping that it would write the txt file to the directory from where I was running the query. But I am thinking that it is trying to write it to the MySQL server which, of course, I don't have permission to do.

How can I dump this?

Sam





#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 September 2006 - 02:53 AM

I'm pretty sure you need the LOCAL modifier so that you're not writing to the server directory.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 NevadaSam

NevadaSam
  • Members
  • PipPip
  • Member
  • 26 posts
  • LocationUSA

Posted 29 September 2006 - 11:38 PM


I tried many different variations of the file location modifier ("/public_html/save.txt" and more) but I believe it is a privilege issue. I learn more about the SELECT INTO OUTFILE command since my last post. It seems that the FILE privilege is normally not granted because of security reasons. This from my text:

The FILE privilege. This enables a user to access files on the server machine with the same privileges as the MySQL server process. This privilege is useful for executing the LOAD DATA INFILE and SELECT INTO OUTFILE statements that read from and write to server-side files. This privilege, however, can be abused as a backdoor around operating systems security and thus should be granted sparingly.


I can do the LOAD DATA INFILE on most hosting services. So when I learn more about PHP I will write a script that will SELECT all the data from a table and then I will output it to a text file that way. Thanks for taking time to read my posts.

Sam


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 September 2006 - 03:55 PM

I didn't realize that the file had to be written on the server host, and hence would require FILE permissions that are generally not granted for security reasons.  But yes, you might as well just roll-your-own export script.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users