I have a cloud-based MySQL instance (slave) and am planning a solution where a remote instance (master) will be based on a ship. The ship will go in and out of international waters and therefore will have an intermittent terrestrial connection. When there is no connection, data will be stored locally. When the vessel arrives in an area with a terrestrial connection, I want the vessel database to synchronize with the cloud-based instance.

This scenario will be duplicated with multiple vessels, with their own instances, and each vessel will need to send local changes to the cloud-based instance.

We have looked at 3'rd party open-source tools like symmetric DS and wanted to 'ask-the-experts' the preferred approach. Is there a best-in-breed 3'rd party tool, or is the out-of-the-box MySQL solution sufficient? Thank you in advance.

  • Not sure why you've tagged MySQL-5.5 on what seems to be a relatively new project. For long term maintenance picking something newer up front will save pain later – danblack Feb 15 at 0:46

Classical MySQL replication replication should work:

Assumptions made:

  • your cloud instance is read only.

The master won't be able to trigger the replication however by default a slave will retry every 60 seconds (configurable CHANGE MASTER .. MASTER_CONNECT_RETRY=).

Ensure your expire_binlog_days is large on the ship based master. Account for the space required carefully on your master and cloud instance.

I'd recommend the ship based master connect via a VPN and the slave connects to the VPN address.

For multiple vessels, you can use use a multisource replication available in MySQL-5.7+ or MariaDB-10.0+. Keep each vessel in its own database for simplicity.

Choose a binlog_format that minimises your traffic volume.

  • 1
    There is a big "if" here- if the total data is comparable to the amount of changes, it maybe easier (and safer) to sync the whole db- specially for long periods of time. The actual solution requires more details to reliably answer it. – jynus Feb 15 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.