How do you work with inter-dependant multiple MySQL instances hosted on different servers?

The answer is: you'll use a MySql federated storage engine to achieve that.

The federated storage engine was developed for a specific use case. The customer was a telecommunication company that used MySQL as a data repository for high-traffic logging data that could not be handled on one instance; thus, the need to spread MySQL instances on different hosts.

The surprising truth is that, even if the table is located on a remote server, anywhere in the world, as long as it is reachable, the querying experience is precisely the same as if the database was on the local MySQL database.

You must activate the federated storage engine in the MySql configuration. Unlike other database technologies with the same feature, such as linked servers for MSSQL servers, it is not enabled per default.

Here's how to create a federated table:

CREATE TABLE federated_table(
    id  INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8mb4
CONNECTION='mysql://user@remote_host:3306/db/table';

Two things to pay attention to in the above query:

1. ENGINE:FEDERATED

2. CONNECTION:'mysql://user@remote_host:3306/db/table'

Federated is a storage engine, the same way you'd use MyISAM . The connection string informs the local MySQL instance that the physical table is on a remote instance, as defined in the connection string.

Next time you want to access or run a query on a remote MySQL instance as if it was local, consider using a federated table.

Go https://dev.mysql.com/doc/refman/5.7/en/federated-description.html to learn more about federated tables.