Connecting remote MySQL database to local MySQL database?

Question

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?

Answer 1

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;

Answer 2

Replication Method is good for this case, Why, Here you is the advantages of replication in MySQL include:

Scale-out solutions - spreading the load among multiple replicas to improve performance. In this environment, all writes and updates must take place on the replication source server. Reads, however, may take place on one or more replicas. This model can improve the performance of writes (since the source is dedicated to updates), while dramatically increasing read speed across an increasing number of replicas.

Data security - because data is replicated to the replica, and the replica can pause the replication process, it is possible to run backup services on the replica without corrupting the corresponding data on the source.

Analytics - live data can be created on the source, while the analysis of the information can take place on the replica without affecting the performance of the source.

Long-distance data distribution - if a branch office would like to work with a copy of your main data, you can use replication to create a local copy of the data for their use without requiring permanent access to the source.