How to Export and Import MySQL Table Data Remotely without Direct Access?

Exporting and Importing MySQL Table Data Remotly Without Direct Access

Exporting and importing data from a MySQL table on a remote server without direct access or phpMyAdmin can be a challenge. This article presents an efficient solution using PHP scripts.

Exporting Data

To export data from the remote MySQL table, you can leverage SQL and PHP. Here's the code:

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

This command creates an output file ('backups/mytable.sql') containing the data from the specified table ('##table##'). You can then retrieve this file using a browser or FTP client.

Importing Data

To import the exported data into your local MySQL database, use the following code:

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

Alternative Method

If PHP's mysql functions are unavailable, you can invoke the 'mysqldump' command using PHP's system function:

<code class="php">$file = 'backups/mytable.sql';
system("mysqldump --opt -h ##databaseserver## -u ##username## -p ##password## ##database | gzip > ".$file);</code>

This command dumps the specified database ('##database') to a gzipped SQL file ('backups/mytable.sql'). You can then transfer this file to your local server and import it using the same mysqli_query() command in a PHP script.

The above is the detailed content of How to Export and Import MySQL Table Data Remotely without Direct Access?. For more information, please follow other related articles on the PHP Chinese website!