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.

Tonicity

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.)

Statistical

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;

Hardee

does it have to have the prefix in the table definitions ? annoying. – Cohby

@zinking: Does it matter, when your application code can just use the VIEW? – Hardee

sounds like you have to have 2 slightly different table creation script. – Cohby

Answer 3

You can set up MariaDB in cloud server and have LONDON and DUBLIN servers as masters. Multi master replication is available in MariaDB. I assume that the masters have different database names.

https://mariadb.com/kb/en/mariadb/multi-source-replication/

Electorate

Answer 4

Presto is useful here as well.

you can deploy presto on the cloud machine, using mysql connectors connect to different geo databases, create different catalog schemas.

write queries to combine result from both database. something like:

select * from dublin.A
union all
select * from london.A

Some Random Links on this

Cohby

Answer 5

Have two webservices for each database location. The webservices queries the respective databases on a periodical interval and insert the data into cloud database.

Cade

A bespoke solution always works. However, was thinking that there might be an off-the-self solution. – Tonicity