Sign in to follow this  

[web] Dumping a database to a text file

This topic is 4340 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hello, I'm trying to dump a copy of my database to a plain text file using MySql and PHP. I researched the various methods of doing it. The first method I tried was the mysql dump command using exec. However, when I tried this I got a "Unable to fork error". I then tried to use the command "SELECT * INTO OUTFILE '/temp/database.txt'", it says "Access denied" for the username and password that I am using. Now I assume both errors that I'm getting for these two ways are permissions related but I checked the permissions on the server and they should be set right. My question is, is there a way to fix those errors? Or is there a better way to do it?

Share this post


Link to post
Share on other sites
Where is the database? On a webhost or on a server you own? If the former, check the cpanel of your site. It usually provides a way to download a backup. If the latter, use the mysqldump command, or simply copy the files in the mysql directory (not sure if the latter is textual). You can also install phpMyAdmin (www.phpmyadmin.net) and use the export functionality.

Share this post


Link to post
Share on other sites
What I need is a way to allow certain other people to go to the .php page that will allow them to save the database to a text file. I don't need it for a backup, just as a way to allow some other people to get the database. I can't use phpAdmin either. Also, is there any other alternatives to mysqldump because if possible I would prefer not to have to use that way because of the security issues.

Thanks for the reply.

Share this post


Link to post
Share on other sites
You could simply loop through all the tables, then loop through all the columns and subsequently each row in a table and output that into a buffer, then output it. That's how phpmyadmin creates it's exports as well.

You could also get phpmyadmin, rip out the export code and modify it to suit your needs. The GPL is a wonderfull thing [smile]

Share this post


Link to post
Share on other sites
SELECT *
INTO OUTFILE "/tmp/result.text"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM table_to_dump;

Is what I use to dump a database table. It dumps to a CSV file that I can load in Excel. Make sure you have write permissions to where you are creating the outfile.

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
alternatively, why not just write a simple PHP script that echos all the data to the screen, you can then format it as you like, either as raw data comma seperated, or as an SQL INSERT, whatever you like...


$sql = "SELECT * FROM tbl_to_export";
$result = mysql_query($sql);
while($data = mysql_fetch_array($result))
{
echo $data["column1"] . "," . $data["column2"] ....... "\n";
}

Share this post


Link to post
Share on other sites
Quote:
Original post by Anonymous Poster
alternatively, why not just write a simple PHP script that echos all the data to the screen, you can then format it as you like, either as raw data comma seperated, or as an SQL INSERT, whatever you like...


$sql = "SELECT * FROM tbl_to_export";
$result = mysql_query($sql);
while($data = mysql_fetch_array($result))
{
echo $data["column1"] . "," . $data["column2"] ....... "\n";
}



That's what I am currently doing. However I would like to actually be able to save it as a text file. Is there a way to take the text in an HTML file and output it to a text file?

Share this post


Link to post
Share on other sites
Quote:
Original post by Third
Quote:
Original post by Anonymous Poster
alternatively, why not just write a simple PHP script that echos all the data to the screen, you can then format it as you like, either as raw data comma seperated, or as an SQL INSERT, whatever you like...


$sql = "SELECT * FROM tbl_to_export";
$result = mysql_query($sql);
while($data = mysql_fetch_array($result))
{
echo $data["column1"] . "," . $data["column2"] ....... "\n";
}




That's what I am currently doing. However I would like to actually be able to save it as a text file. Is there a way to take the text in an HTML file and output it to a text file?


Simply select file | Save As in your browser, and select the file type as text. Even if you save as an HTML file it wont make any difference, as the file wont contain any HTML markup, it will be 100% pure text but with an .htm extension.


BTW the prev Anon Poster was me....

EDIT to add:
An HTML file is just pure text, but with HTML formatting tags for the browser to interpret. As your echo statement doesnt echo any HTML, the resulting page will be pure text.

Share this post


Link to post
Share on other sites
Quote:
Original post by Third
Quote:
Original post by Anonymous Poster
alternatively, why not just write a simple PHP script that echos all the data to the screen, you can then format it as you like, either as raw data comma seperated, or as an SQL INSERT, whatever you like...


$sql = "SELECT * FROM tbl_to_export";
$result = mysql_query($sql);
while($data = mysql_fetch_array($result))
{
echo $data["column1"] . "," . $data["column2"] ....... "\n";
}



That's what I am currently doing. However I would like to actually be able to save it as a text file. Is there a way to take the text in an HTML file and output it to a text file?


have PHP write it to a temporary text file on the server than forward the user to a download location....

Or even better, google an existing PHP zip file class than have the server dump the tables into a formatted string(that you'd write to a text file or whatnot in any format you'd like) than zip the file using the zip class and send the header information that this is a zip file and echo the zip file the zip class makes for you...

Theres a bunch of ways

Share this post


Link to post
Share on other sites
Quote:
Original post by Arkantis

have PHP write it to a temporary text file on the server than forward the user to a download location....

Or even better, google an existing PHP zip file class than have the server dump the tables into a formatted string(that you'd write to a text file or whatnot in any format you'd like) than zip the file using the zip class and send the header information that this is a zip file and echo the zip file the zip class makes for you...

Theres a bunch of ways


No need to mess around with temporary files. Simply put all the text in a variable and echo() that to the browser. Just hake sure you use header() to set the correct headers.

As for a zip class, phpmyadmin has a good one you could re-use under the GPL.

Share this post


Link to post
Share on other sites
Rather than using INTO OUTFILE, use INTO LOCAL OUTFILE instead... your MySQL account might not have the server file access privilege.

Before assuming that /temp exists, please verify it - on Linux / Unix machines, /temp does not normally exist hence you won't be able to use that.

If you're getting "fork failed" that indicates that either there is a problem with the server, or that your user has reached its quota of processes (or similar).

It's possible that if you're running in one of these dodgy "virtual servers" (The ones which aren't virtual servers at all, just some kind of lame partitioning scheme) you're in some kind of restricted environment which prohibits spawning child processes.

Using mysqldump is the right and correct way of doing this.

PHPMyAdmin uses a lame and highly flawed approach; its backups are frequently silently truncated, or have encoding or other problems. This makes restoring them problematic (i.e. impossible)

Mark

Share this post


Link to post
Share on other sites
Quote:
Original post by markr
By the way, in case you failed to get the message from that last post,

PHPMyAdmin is a steaming pile of

Mark


That doesn't mean you shouldn't re-use parts of its code into your own product. It just shows that you need to understand that which you re-use. Also, phpMyAdmin is pretty decent if you don't have access to the back-end (i.e. a regular shared hosting account), though full backups should be made through the cpanel's interface to mysqldump.

Share this post


Link to post
Share on other sites

This topic is 4340 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this