How to update MySQL data within intranet from MySQL data in cloud?

Question

I am currently using a MySQL DB located within the intranet. Only the people within the LAN can access the information.

I'm trying to re-create the MySQL DB in a cloud provider. This can be accessed by other people from a different location.

When people update the MySQL DB in the cloud, is there a way to synchronize the information to the MySQL DB located within the intranet and vice versa?

I was thinking of using MySQL Job Event, like exporting data to an insert/update script file then dumping the file onto a local intranet folder. The intranet MySQL will also have a Job Event checking for this file if there's a new dump.

I am not sure if this is the right way to do such a task.

Any helpful tips are really appreciated.

Answer 1

MySQL has a built-in functionality called MySQL replication that allows the synchronisation between 2 servers, by applying on a "slave" the operations that have been done on the "master". If no writes are done on the slave, that works pretty well and, while it has its issues, it is widely used by most MySQL users (Facebook, Twitter, etc.) for high availability, load balancing and/or testing/maintenance operations.

It is very easy to setup and has little performance impact, as it is asynchronous by default. That makes possible to perform the replication in real time or in batches, as desired.

Please note that while all "Infraestructure as a service" allows replication, some "database as a service" providers do not allow to do it outside of their servers (for example, RDS only allowed it in MySQL 5.6 servers).

If you need to write to the slave (your intranet server), that would be more complex, as you have to make a plan on how to merge the results and modify the replication settings or use an alternative technology.