Can you use MySQL to replicate data over a network from several sites?
Is MySQL a good fit for replicating from several hundred point-of-sale database sites to a single database over a network that is not always reliable? This is an interim project as we are waiting for a decision that would replace all of our disparate POS systems with a single solution, however in the mean time the data has to be collected and analyzed.
replication is used to push data out, not pull it in. For example, it is common to have MySQL run a single master with multiple slaves
, and it does not support a single MySQL instance running as a slave to multiple masters.
That being said, it depends on what you want to do. If you are looking for a
central backup of multiple MySQL servers, you can run a single machine with
multiple instances, each acting as a slave to an individual remote server.
If you want a central analysis machine without two-way replication (data just comes from the remote servers), you could script a process where each remote server ships data to the central machine, or makes it available for pulling. Then the data can be loaded into a central MySQL instance which is used for analysis.
If your systems are pretty disparate, you may need to find a common format like
CSV and ship the data to the central server for loading using FTP, Rsync, or the like.
This was first published in June 2005