All over the internet I can find manuals on how to replicate databases from master(s) to slaves.
What I need is to continuously keep a copy of table from 5 different servers in sync.
We have one application running at 5 locations. We need to have a copy of the system_log table from every one of those 5 servers always in sync on one new server at our 'headquarters'.
What is the best way to achieve this without using custom scripts?
Sorry. If you are trying to have many Masters and one Slave -- that is not supported. (Some third party addons may have such.) It is better to have the 5 servers all write to the 'headquarters' Master. Network latencies are probably not a serious problem.
There are multiple ways to replicate from multiple "masters" to a single slave. We have currently done this. Here are 2 options -
Before suggesting - I mean multiple masters as separate data sources and not in the typical master being replicated to another master. These masters have independent data and have no relationship with data in other masters. To be more precise - Master-1 - databases A,B,C, Master-2 databases - D,E,F.
Use tungsten replicator from continuent that provides a non-sql way to replicate from multiple masters.
MySQL 5.7 provides multi source replication , using native replication. Each master is set up as a separate channel and each channel can be started , stopped , skipped as we do with a single master prior to 5.7. However, we found that performance wise this was an issue as one of the slave threads got into a system lock and the replication speed was quite slow. However on servers where we had lower transaction rates (< 100 tx per minute) the replication was working fine. As we had a timeline and near realtime requirement for replicated data, we dismantled 5.7 and continued with Tungsten.
So far Tungsten has worked flawlessly without any issue. (FAN -IN replication topology).
HTH.
Thanks, Raghu
I would recommend looking at circular replication, where all servers are connected in a ring, and each server is slave to previous and master to the next, so any change in 1 replicates all the way through the ring. I've used this at my work to link 3 servers (one external webserver, 1 internal US wamp server, 1 Asian office wamp server). It was fairly easy to set up and the only problems I've had was when the asian office was not reachable for 2 whole days due to widespread internet outage in the area. As soon as it came back up, the replication resumed.
These 2 links were particularly helpful for me:
http://onlamp.com/onlamp/2006/04/20/advanced-mysql-replication.html
http://www.cwik.ch/2011/03/setting-up-multi-master-circular-replication-with-mysql/
as was the replication chapter of High Performance MySQL: Optimization, Backups, and Replication, which also had some different replication schemes you might find interesting if circular is not suited for your needs.
Replication is all about master-slave relationship. At any point A is a master to B(ie B is slave to A) and B is master to A(A is slave to B). If you have n servers, do a ring replication.
Maybe, you can write in master server the data and replicate to the 5 slaves. In central node you write 'system_log' and mysql reply your data to slaves servers.
