how to query two mysql databases from two servers?

blackhorse

How to query two mysql databases from two servers?

We can query two databases on the same server if the user has the access to both databases. We just use databasename.tablename etc.

But

how about the databases are on two different servers?

And the user name and password to access these database are different?

How can we query them in one query script?

dagon

create a new connection to the 2nd server and use that

$connectA= mysql_connect(...);
$connectB= mysql_connect(...);

blackhorse

For example that I want to select from database 1 and insert the results to database 2. I will use subquery

	//db1
	$db1_host = "remote.remotehost.com";

$db1_username = "mysqluser1";
$db1_password = "mypassword1"; // encrypted

$db1_name="database1";

$db1_conn = mysql_connect( $db1_host, $db1_username, $db1_password );
//$db1_selected = mysql_select_db($db1_name, $db1_conn);

//get the data from database1
$sql_select_records_from_databse1="SELECT field_1, field_2 FROM database1.table1";

//db2
$db2_host = "localhost";

$db2_username = "mysqluser2";
$db2_password = "mypassword2"; // encrypted

$db2_name="database2";

$db2_conn = mysql_connect( $db2_host, $db2_username, $db2_password );
$db2_selected = mysql_select_db($db2_name, $db2_conn);


$sql_insert_records_to_database2="INSERT INTO table_a (field_a, field_b) $sql_select_records_from_databse1";

Do you mean that I can do something like this? Even 1 database is on local machine, the other database is on remote machine?

Even the user name and password are different for the two databases? (I can set up localhost user name and password the same as the remote host name and password if it will make difference.)

Thanks!

dagon

blackhorse;10928869 wrote:
Do you mean that I can do something like this? Even 1 database is on local machine, the other database is on remote machine?

does not matter where the servers are as long as you have the right credentials to connect. Nor does user name or password matter, each connector is separate.

blackhorse

But I cannot merge them in one query like I did above.

I got error like

SELECT command denied to user 'mysqluser2'@'localhost' for table 'table1'

Something was wrong in my codes?

I found this thread

solution for remote server
Submitted by Anonymous (not verified) on Tue, 06/02/2009 - 11:56.

first add the server to your local sql server (from where you want to run the query)

exec sp_addlinkedserver [192.168.1.130]

now run the query as

select * from [192.168.1.130].[pubs].[dbo].[authors]

Will this work for my case in mysql and php?

blackhorse

If the subquery insert (using two databases from two servers) can work, then that will be better!

If not, I will just select from one database insert into the second database.

dagon

didn't see the sub querry, that is different, and you will need as you read to do the full server ip thing, or break it in to 2 , select from a then insert in to b.