Synchronize local MySQL databases with a cloud database

Question

I have two databases, one in London and one in Dublin. How can I have a complete view of the data in a cloud database? Assume that the database structure allows me to use multiple locations such that there will be no collisions.

EDIT: All the changes in the databases are done locally. For example: let's say I have a sensor in Dublin that dumps the data on the Dublin database and another sensor in London which dumps its collected data in the London database. How do I get a federated view of this data in my cloud database? From an admin interface I want to query the cloud database, not the other ones.

Answer 1

Plan A: Galera cluster (as found in MariaDB) that includes the 3 servers.

Plan B: "Multi-source replication" wherein your two physical servers are Masters and the Cloud server is the Slave. Again, the requires MariaDB. (See DBHash's Answer.)

Answer 2

You can define FEDERATED tables in your "cloud" database: any queries on these tables will be transmitted from the "cloud" server to the relevant London/Dublin server over the MySQL client protocol (note that data is not copied to the "cloud" server, so it does not provide any form of backup service):

CREATE SERVER london FOREIGN DATA WRAPPER mysql OPTIONS (
  HOST 'london.mysql.example.com',
  PORT 9306,
  USER 'cloud_db_user',
  PASSWORD '...',
  DATABASE 'my_database'
);

CREATE SERVER dublin FOREIGN DATA WRAPPER mysql OPTIONS (
  HOST 'dublin.mysql.example.com',
  PORT 9306,
  USER 'cloud_db_user',
  PASSWORD '...',
  DATABASE 'my_database'
);

CREATE TABLE london_table (
    -- table definition as normal
)
ENGINE=FEDERATED
CONNECTION='london/original_table';

CREATE TABLE dublin_table (
    -- table definition as normal
)
ENGINE=FEDERATED
CONNECTION='dublin/original_table';

You could then define a VIEW that comprises the UNION of those federated tables. Unfortunately however, UNION views are neither insertable nor updateable—so if you need to commit any changes to the data you'd have to operate on the underlying (federated) table:

CREATE VIEW combined AS
  SELECT * FROM london_table
UNION ALL
  SELECT * FROM dublin_table;