I want to create a reporting server to move huge load from transaction server into it. The problem is that there is so many insert/updates in transaction database that simple replication of entire database won’t give us much. I heard partly replication is possible so we could replicate only tables which are used by reports.
Here is big picture/use case of what I’m trying to acheive with mysql replication. Assume we have table “logs” and “summary”. Every hour about 2GB of data is imported into “logs” table. Every hour that data is summarized into “summary” table using some kind of “INSERT INTO summary … SELECT COUNT(*)… FROM logs WHERE …”. There are more tables like this one and they are summarized from many other tables using JOINS.
I’m wondering if it’s possible to replicate only the “summary” table on slave server? How it works in that situation if we will ignore logs table? Will mysql replication just send new and updated rows inside “summary” table to slave? Or is it neccessary that “logs” table have to exists on slave in order to summarize the data from it on slave as well?
Thanks in advance for any tips