Solved

In MySQL how do I "SELECT INTO OUTFILE" to a Unix passworded directory?

Posted on 2011-02-17
18
939 Views
Last Modified: 2012-06-21
I am running on shared hosting and the hosting company runs MySQL on a different server from the one I'm running on.  I want to create csv files using SELECT INTO OUTFILE and have the csv created in one of my passworded directories.  I'm using the command shown in the snippet below and I get this error

#1045 - Access denied for user '<my database user name>' (using password: YES)

Is there a way to specify the UNIX DIRECTORY user and password?
SELECT * 
INTO OUTFILE '/home/.../test.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM myTable

Open in new window

0
Comment
Question by:anAppBuilder
  • 10
  • 7
18 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 34923437
Can you check this?
SELECT * FROM myTable
INTO OUTFILE '/home/.../test.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'

Open in new window

0
 
LVL 77

Expert Comment

by:arnold
ID: 34923450
put the select data into a fil (query_file)
You need to use the mydatabase.mytable

cat query_file | mysql -h mysqlserver -u yourusername -p mydatabase

The outfile will likely be on the remote server since the directive is run on the mysql server.
Have a look at mysqldump which might be what you are looking for i.e. dump the contents of your database/tables into a file locally.
0
 

Author Comment

by:anAppBuilder
ID: 34923476
Thank you both.

Arnold, is there a way to run mysqldump from PHP?  This site is for a user who is not a computer expert.  I want to give him one button to click to backup his tables.  He should not need to use the hosting company's control panel.
0
 
LVL 77

Expert Comment

by:arnold
ID: 34923513
Does the hosting company provide an option to use phpmyadmin?
What you could do is run the query in php and output the data to the browser.

Where the user will save the data as output.
0
 

Author Comment

by:anAppBuilder
ID: 34923822
Thank you again, Arnold.  Yes, the hosting company provides phpMyAdmin, but that is not something my user can use.

The creation of the csv file needs to be transparent to the user and will probably be triggered as a side effect of something else he does plus a php program noticing that it's time to make a csv.  The user does not know anything about tables or databases.

 I want to save the csv in a passworded directory on the website.
0
 
LVL 77

Expert Comment

by:arnold
ID: 34926908
Create the CSV and push it as a php output.
0
 

Author Comment

by:anAppBuilder
ID: 34928009
Thank you, Arnold.

How do you suggest I create the csv?

I can certainly write a PHP program to read a table and write a csv.  That's what I'm trying to avoid, especially because the hosting company limits database hits per hour and that would be a lot of hits.

I want to
o  Invoke SELECT INTO OUTFILE or mysqldump (which count as one hit for the whole operation)
o  Using PHP
o  And put the csv in a passworded directory on the website

If the csv is created on the database server, I don't know how to get to it, and I suspect that the hosting company would not allow me access to it (for security reasons, since it's  shared server)



0
 

Author Comment

by:anAppBuilder
ID: 34928259
Another question.  You suggested

cat query_file | mysql -h mysqlserver -u yourusername -p mydatabase

Would I do this in a php script using a system() or exec() command?
0
 
LVL 77

Assisted Solution

by:arnold
arnold earned 500 total points
ID: 34929942
You can use php to generate the select  * from mydatabase.mytable
you would then use php  to format the data in a csv format and output the data back to the browser as Content-Type: text/CSV

You would have to make sure to handle the encoding FIELDS, Enclosed, escaped within your processing.

The user will likely be prompted to save the file.

The phpmyadmin has the backup/export option which sounds as what you want.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:anAppBuilder
ID: 34930159
Thank you again.

RE:  "The phpmyadmin has the backup/export option which sounds as what you want.  Can I invoke that backup/export option from my own php script?
0
 
LVL 77

Expert Comment

by:arnold
ID: 34930740
You could, but you would need to interact with the phpmyadmin web interface.
Sorry, did not see or remember your comment that dealt with you not wanting to generate the CSV when I made the last suggestion.
Are you able to access the mysql server externally i.e. can you from your home/office connect to the providers mysql using the mysql tools?

How often do you need to get this information of the database?
An option might be to add a column that will deal with records already obtain and you would need to add to any update record that this column is reset such that the next time you take an incremental backup of the data the updated columns will be included and not just the newly entered ones.
0
 

Author Comment

by:anAppBuilder
ID: 34935823
Thank you, Arnold.

So I think the bottom line is that I can't use either SELECT INTO OUTFILE or mysqldump in this case.  

I'll need to write my own php program to create a csv.

Arnold, please let me know if this is not correct.  I consider a confirmation that I didn't miss a way to use a MySQL feature as a "solution" for the full points.
0
 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 34936154
You could use mysqldump, but it will involve a two step process i.e. run the php code and then ftp the data of. The other issue deals with whether the web server configuration will allow for a the execution of mysqldump with the data being stored on the filesystem.
http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/using-php-to-backup-mysql-databases.aspx

http://stackoverflow.com/questions/217424/create-csv-file-for-user-in-php
http://php.net/manual/en/function.fputcsv.php
0
 

Author Comment

by:anAppBuilder
ID: 34938076
Thank you, Arnold.

RE:  You could use mysqldump, but it will involve a two step process i.e. run the php code and then ftp the data [to the server where the website is].  Unfortunately I don't have a way to read the file from my service provider's database server.
0
 
LVL 77

Assisted Solution

by:arnold
arnold earned 500 total points
ID: 34938146
What do you mean, "you do not have a way to read the file from my service provider's database server" To which file do you refer?
mysqldump is a command that should be available on your web server /usr/bin/mysqldump
the output from the command is a plain text ouput are in mysql command format
use databasename
create table
insert into

etc.

I think the fputcsv might be what you were looking for.
0
 

Author Comment

by:anAppBuilder
ID: 34939049
Thank you again, Arnold.

For some reason the dump is failing and  I'm getting a 0 length file.

I've included a code snippet and the results below.  Note that if I put in the "or die" it dies.


CODE SNIPPET
    //Verify that the DB and user info is good
    $dbLink = mysql_connect($custDBHost, $custDBUser, $custDBPswd);
    print "dbLink = $dbLink<br><br>";
    $ok = mysql_select_db($custDBName, $dbLink);
    print "ok = $ok<br><br>";
		
    $command = "mysqldump --opt --h=$custDBHost --u=$custDBUser --p=$custDBPswd $custDBName  > $backupFile";
    system($command, $retVar); // or die('could not write file');
    print "retVar=$retVar";


OUTPUT
dbLink = Resource id #5

ok = 1

retVar=3

Open in new window

0
 

Author Comment

by:anAppBuilder
ID: 34945604
I did not intend to check any of my comments.  Please award all the points to arnold.
0
 

Author Comment

by:anAppBuilder
ID: 34945638
I realize the question on the 0 length file is a separate issue.  I posted this follow up question http://www.experts-exchange.com/Database/MySQL/Q_26836933.html
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now