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

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

anAppBuilderAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
arnoldConnect With a Mentor Commented:
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
 
SharathData EngineerCommented:
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
 
arnoldCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
anAppBuilderAuthor Commented:
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
 
arnoldCommented:
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
 
anAppBuilderAuthor Commented:
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
 
arnoldCommented:
Create the CSV and push it as a php output.
0
 
anAppBuilderAuthor Commented:
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
 
anAppBuilderAuthor Commented:
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
 
arnoldConnect With a Mentor Commented:
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
 
anAppBuilderAuthor Commented:
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
 
arnoldCommented:
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
 
anAppBuilderAuthor Commented:
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
 
anAppBuilderAuthor Commented:
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
 
arnoldConnect With a Mentor Commented:
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
 
anAppBuilderAuthor Commented:
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
 
anAppBuilderAuthor Commented:
I did not intend to check any of my comments.  Please award all the points to arnold.
0
 
anAppBuilderAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.