I apologize first for using Sync in the title, that may be the wrong term. I am using MySQL, but can switch to Postgress or other open source platform.
I have a service that when people subscribe to we receive a lot of data from them, around ~60,000 rows that gets split up across multiple tables using a http endpoint processed by php. After the initial sync, we only receive incremental updates.
Looking at server load, we became concerned that it would become an issue as we added more and more customers and so setup a separate server that would handle all the http requests, parse the data and place it in the right tables. As load increased, we could then section off customers to other identical servers.
The question is what should I do to effectively replicate the data on those finished tables over to the production server (same network)? I could setup triggers, but that would result in tens of thousands of individual connection requests for each insert.
If I setup were to setup mirroring / master-slave, I want it to ignore all existing data because I wouldn't want it to try comparing things that have already been sent on a regular basis since that could grow to 50 million rows quickly.
It would be more like :
SELECT @rowcount:=COUNT(*) FROM production.database.table1; INSERT INTO production.table1 SELECT * FROM workhorse.database.table1 where `id` >@rowcount;
Something this simple could be run once a minute or so I would think and cut down on individual connections. I realize this is kind of messy since COUNT and ID aren't locked together, but it gets the idea across. Also, I don't know a way to run the query on a remote server.
Are there any tools/commands that would make quick work of this?