1

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?

3

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.