insert into a MySQL database on a different server

Question

Using PHP and MySQL in a script run on mysite1.com I'm trying to copy all rows and columns from a table on mysite2.com into an identical table (that has already been created) on mysite1.com. First I connect to both the databases (I've already set up remote MySQL connections on mysite2.com).

$con1 = mysql_connect("mysite1.com", "username1", "password1");
if (!$con1) {die('Could not connect: ' . mysql_error());}
mysql_select_db("database1", $con1);

$con2 = mysql_connect("mysite2.com", "username2", "password2");
if (!$con2) {die('Could not connect: ' . mysql_error());}
mysql_select_db("database2", $con2);

I can't figure out how to format the "INSERT INTO" sql string so it will get the data from mysite2.com and put it in mysite1.com. Does anyone know how to do this?

INSERT INTO my_table1 SELECT * FROM my_table2

Answer 1

Can't be done the way you're thinking. You could set up a federated table and copy from that. Probably simpler just to use mysqldump and then load the file it creates though.

Third option is to read the data through php and generate INSERT statements, but that'l be the slowest of the three options.

Answer 2

A mysql connection handle can't be shared between different servers. It's specifically tied to the server you opened it on, and that's where it'll stay.

There's a couple options. bemace mentioned federated tables. There's also replication. Of the two, replication is probably the easiest to maintain, though it is somewhat of a pain to get up and running. Once it's up, it'll handle all the updates to the replicated tables/databases transparently, and you don't have to worry about it.

With federated tables, you have to run your queries at least twice. One for the "local" table, and one for each "remote" table. And you'll run into problems with transactions: they're not supported at all on federated tables.

Answer 3

$con1 = mysql_connect("mysite1.com", "username1", "password1");
if (!$con1) {die('Could not connect: ' . mysql_error());}
mysql_select_db("database1", $con1);

$result = mysql_query('SELECT * FROM `some_table`', $con1);
$query = array();
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    $query[] = '('.implode(',', $row).')';
}

$con2 = mysql_connect("mysite2.com", "username2", "password2");
if (!$con2) {die('Could not connect: ' . mysql_error());}
mysql_select_db("database2", $con2);

mysql_query('INSERT INTO `some_table` VALUES '.implode(',', $query).';', $con2);

Answer 4

You could use a 3rd party database synchronizer such as http://www.quest.com/toad-for-mysql/ (free)

Answer 5

MySQL makes a migration assistant tool that will do this. This functionality might be folded into the Workbench now.

You might also consider doing the following:

from mysql1 connect to mysql1: SELECT * FROM table INTO OUTFILE filename

from mysql1 connect to mysql2: LOAD DATA LOCAL INFILE filename INTO TABLE table

This will be pretty fast, but it won't be a perfect copy if you're doing inserts into mysql1 while this is going on.

If you need these to stay synchronized, replication is defiantly the answer.