How to Export and Import SQL Tables Remotely Without Direct Server Access?

Exporting SQL Tables Remotely

Accessing data from remote MySQL tables without direct server access or phpMyAdmin poses a challenge. One effective method to overcome this obstacle is by leveraging SQL commands within a PHP script.

To export a table, follow these steps:

1. Create an SQL query: Utilize a query like SELECT * INTO OUTFILE '$file' FROM ##table##`, where $file` represents the export file's location on the server.
2. Execute the query: Use mysql_query to initiate the export process to the specified file.

Once the data is exported, you can import it back into your local database using a similar SQL query:

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

Alternatively, you can employ mysqldump to export data via a system command in PHP:

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

In this command, --opt optimizes the dump, -h specifies the remote host, -u and -p provide authentication credentials, ##database is the source database, and gzip compresses the output into the specified file.

By implementing these methods, you can efficiently export and import data between remote and local MySQL servers without direct server access.

The above is the detailed content of How to Export and Import SQL Tables Remotely Without Direct Server Access?. For more information, please follow other related articles on the PHP Chinese website!