insert into a MySQL database on a different server

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 no开发者_如何学Ct 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

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.

$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);

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 definitely the answer.

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.

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

Try this:

// origin
$dblink1=mysql_connect('mysite1.com', 'user1', 'password1'); 
mysql_select_db('db1', $dblink1);  

// destination
$dblink2=mysql_connect('mysite2.com', 'user2', 'password2'); 
mysql_select_db('db2',$dblink2); 

$res_from = mysql_query("show tables", $dblink1); 
if($res_from === FALSE) {
    die(mysql_error()); // for error handling
}

while($table = mysql_fetch_array($res_from)) { 
echo($table[0] . "<br />"); // optional, only for show the name tables

$table=$table[0];

$tableinfo = mysql_fetch_array(mysql_query("SHOW CREATE TABLE $table  ",$dblink1)); // retrieve table structure from mysite1.com 

mysql_query(" $tableinfo[1] ",$dblink2); // use found structure to make table on mysite2.com

$res_to = mysql_query("SELECT * FROM $table  ",$dblink1); // select all content from table mysite1.com  
while ($row = mysql_fetch_array($res_to, MYSQL_ASSOC) ) {
    $sql_error = "";
    mysql_query("INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."')",$dblink2); // insert one row into new table on mysite2.com`enter code here`
    $sql_error = mysql_error($dblink2);
}
if ($sql_error) {
    echo $sql_error;
}else {
    echo "copy table ".$table." done.<br />";
}
    flush();
}

mysql_close($dblink1); 
mysql_close($dblink2);

IMPORTANT! make sure you have a user with all privileges in the mysql of mysite1.com In any case, you can make one so: CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword'; GRANT ALL ON *.* to myuser;