Easy way to export a SQL table without access to the server or phpMyADMIN

Question

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

Answer 1

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`');
$fp = fopen('php://output', 'w');
if ($fp && $result) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="export.csv"');
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        fputcsv($fp, array_values($row));
    }
    die;
}

Answer 2

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:
http://www.taw24.de/download/pafiledb.php?PHPSESSID=b48001ea004aacd86f5643a72feb2829&action=viewfile&fid=43&id=1
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:
http://snipplr.com/view/173/mysql-dump/

Answer 3

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, give the filename a timestamp for example.

To get it back in to your DB from the 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;);

Answer 4

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.

Answer 5

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.

Answer 6

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