Solved

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

Posted on 2011-02-17
18
936 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 76

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 76

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 76

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 76

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 76

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 76

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 76

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduction We as admins face situation where we need to redirect websites to another. This may be required as a part of an upgrade keeping the old URL but website should be served from new URL. This document would brief you on different ways ca…
The purpose of this article is to demonstrate how we can use conditional statements using Python.
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.:
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…

760 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

20 Experts available now in Live!

Get 1:1 Help Now