How to Export MySQL Data from a Remote Server without Direct Access?

Exporting MySQL Data without Direct Server Access

When transferring data from a remote MySQL server to your local machine without direct server access or phpMyAdmin, PHP can provide a solution.

PHP-Based Export and Import

To export data from a remote table named "##table##" into a local file "mytable.sql," use the following PHP code:

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

This SQL query exports the entire table into the specified file, which can then be downloaded using a browser or FTP client.

To import the data back into your local database from the "mytable.sql" file, execute the following query:

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

System Command Method

Alternatively, 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 creates a compressed SQL dump of your chosen database and saves it to the specified file. The dump can then be imported using standard MySQL operations.

The above is the detailed content of How to Export MySQL Data from a Remote Server without Direct Access?. For more information, please follow other related articles on the PHP Chinese website!