updating data from slaves to master in mysql

Question

I'm running a master mysql server with multiple slaves. The replication process from master to slaves works fine. But there's one problem: I need to push the contents from one table from the slaves to the master.

That same table is also used by the master itself, but each mysql server has their own data in that table. Therefore I need to ADD the data from the slaves to the master.

I thought about keeping track of the queries at each slave and then pushing the queries out to the master, but it seems messy and whenever I would get an error I'd be stuck.

The table looks something like this:

Server ID (key field) - Date (key field) - Statistic

Each server keeps track of the statistics at his server and the master needs to consolidate all this for reporting purposes.

Any ideas?

Do you really need to consolidate this data from slaves to same table on the master? What do you think about excluding this table from replication and consolidate data (send from master to slaves and back) using crontab+bash? — ravnur
i did exclude it from replication already. I need to update ONLY the data from the slaves into the master for that table. I'm now looking into doing mysqldump on the slaves and then using crontab to push it back to the master with REPLACE instead of INSERT — user1914292
if you can't avoid of writing into slaves you can go with consolidation, it is fine. also you can take a look on Tungsten replicator. You can setup slave-many masters easily (in this case you master will be the slave and all slaves will be the master). You go with it also — ravnur

user1914292 user1914292 · Accepted Answer · 2013-11-16T22:19:07

I ended up dumping the table with mysqldump and then adding it to the master database over crontab.

I realize it's important to use mysqldump with the replace option in this case, because it's possible that I already had entries in there. Note that the key I'm using makes sure that entries only come from a certain slave/master so no server is in another server's way.

solution:

mysqldump -u xxx -pyyyy database table | mysql -h host -u xxxx -p yyy database

updating data from slaves to master in mysql

1 Answers