I just configured two different server machines to run FPMI/FSQL/MySQL etc. They are on different networks, but can obviously access one another via the internet. Currently they are running independently. I plan on setting up both FactoryPMI gateways as a cluster which is pretty straight forward. However, although there is a redundancy feature in FactorySQL, a few potential issues came to mind.
How can I easily keep the historical data synchronized in both databases? Basically if the primary goes down, what is the best way to sync the two databases so both the primary and backup have no missing data.
Also, if the secondary goes down, how can I ensure that the secondary database will be updated with records that were collected in the primary?
Finally, how can I ensure that any changes to the primary database that my application makes will be carried over to the secondary?
It seems that all three of these issues are probably rectified by one solution. Circular Replication was mentioned to me, but I am not incredibly familiar with it so I’m not sure if this is the best solution.
As always, thanks in advance.