Take the 2-minute tour ×

I need a way to easily export and then import data in a MySQL table from a remote server to my home server. I don't have direct access to the server, and no utilities such as phpMyAdmin are installed. I do, however, have the ability to put PHP scripts on the server.

How do I get at the data?

I ask this question purely to record my way to do it

share|improve this question
add comment

6 Answers

up vote 11 down vote accepted

You could use SQL for this:

$file = 'backups/mytable.sql';
$result = mysql_query("SELECT * INTO OUTFILE '$file' FROM `##table##`");

Then just point a browser or FTP client at the directory/file (backups/mytable.sql). This is also a nice way to do incremental backups, given the filename a timestamp for example.

To get it back in to your DataBase from that file you can use:

$file = 'backups/mytable.sql';
$result = mysql_query("LOAD DATA INFILE '$file' INTO TABLE `##table##`");

The other option is to use PHP to invoke a system command on the server and run 'mysqldump':

$file = 'backups/mytable.sql';
system("mysqldump --opt -h ##databaseserver## -u ##username## -p ##password## ##database | gzip > ".$file;);
share|improve this answer
In what ever script you runy the SELET * INTO OUTFILE, you can just do a header('Location :'.$file) to immediately download the file. –  Jrgns Sep 19 '08 at 9:53
add comment

You should also consider phpMinAdmin which is only one file, so its easy to upload and setup.

share|improve this answer
add comment

You might consider looking at: http://www.webyog.com This is a great GUI admin tool, and they have a really neat HTTP-Tunneling feature (I'm not sure if this is only in enterprise which costs a few bucks).

Basically you upload a script they provide into your webspace (php script) and point sqlyog manager to it and you can access the database(s). It uses this script to tunnel/proxy the requests/queries between your home client and the server.

I know at least 1 person who uses this method with great results.

share|improve this answer
add comment

I use mysqldump via the command line :

exec("mysqldump sourceDatabase -uUsername -p'password'  > outputFilename.sql");

Then you just download the resulting file and your done.

share|improve this answer
add comment

If you have FTP/SFTP access you could just go ahead and upload phpMyAdmin yourself.

I'm using this little package to make automated mysql backups from a server I only have FTP access to:
The site is in german but the download has some english documentation as well.

A quick google also turns up this, but I have not used it myself:

share|improve this answer
If uploading phpMyAdmin was an option, I would have done it. –  Jrgns Sep 17 '08 at 11:21
Love the snippet, though! –  Jrgns Sep 17 '08 at 11:22
add comment

I did it by exporting to CSV, and then importing with whatever utility is available. I quite like the use of the php://output stream.

$result = $db_con->query('SELECT * FROM `some_table`');
if ($fp && $result) {
    header('Content-Disposition: attachment; filename="export.csv"');
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        fputcsv($fp, array_values($row));
share|improve this answer
add comment

Your Answer


