Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
@DTest thanks for edit –  Shashank Jun 28 '12 at 14:53

1 Answer 1

up vote 2 down vote accepted

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;
share|improve this answer
    
Thanks for the valuable answer... –  Shashank Jun 28 '12 at 11:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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