Mysql – Connecting remote MySQL database to local MySQL database

bulkcopyMySQLPHPremote

I want to write PHP code to be embedded in a Drupal7 module.

I want to call a procedure which can copy newly generated data in the local MySQL database to a remote MySQL database.

When data is inserted in table A of my local database it should be copied to the specific table B on the remote MySQL database.

Table 'A' is on local host.

Table 'B' is on remote server.

INSERT data on 'A' -> copied to 'B'

Is this possible?

Best Answer

There are 3 methods to set this up

METHOD #1 : MySQL Replication

Setup MySQL Replication where the Slave has this option

replicate_do_table=mydb.mytable

Then, any DML (INSERT, UPDATE, DELETE) or DDL (ALTER TABLE) you execute will go immediately to serverB. This makes Method #1 is the fastest and most granular approach.

METHOD #2 : Copying the table to the other server

Rather than rehash, Here is an earlier post I did May 31, 2011 for this method : How do you copy a table from MySqlServer_A to MySqlServer_B?

METHOD #3 : FEDERATED Table (MyISAM Only)

Suppose mytable on serverA looks like this

CREATE TABLE mydb.mytable
(
...
) ENGINE=MyISAM;

You can a mapping of the target table in serverB by running this on serverA like this

CREATE TABLE mydb.mytable_remote LIKE mytable;
ALTER TABLE mydb.mytable_remote ENGINE=FEDERATED
CONNECTION='mysql://username:password@serverB/mydb/mytable';

I wrote an earlier post back on Jan 4, 2012 : fetching externally hosted db's table from within the local mysql server

Then you would have to bulk copy everything in serverA to serverB running the following on serverA:

INSERT IGNORE INTO mydb.mytable_remote SELECT * FROM mydb.mytable;
Related Question