I would to copy OLTP data from original tables to a Reporting table which is join of 5 tables. I know Percona doesn’t offer materialized views like Oracle or PostgreSQL. But I am wondering about other methods to perform a similar kind of data copy, though possibly more of a workaround. Long term we’ll most likely move reporting tables to a separate database/warehouse. In both cases we still need an efficient method to replicate this data to Reporting tables which are not a one for one copy in terms of schema. Regarding lag time or eventual consistency, we could allow ~ 3-5 minutes for data replication to occur.
Is there any way to use Percona’s built-in replication or other tools to replicate to a table of different names and schema structures? Let’s say using a query to define what will be replicated and to where?
If not possible, what other ideas or workarounds might be recommended. I can think of creating some triggers on master tables (onInsert, onUpdate, onDelete). But I’m looking for other options beyond trigger based transactions, if available. And ideas?
These are the ways I know to do that:
Justin Swanhart’s FlexViews
MariaDB allows to run triggers on a slave
With Percona Server (or vanilla MySQL) and replication, yes you can theoretically do that on a slave, but you would need to use statement based replication, which is slow and unsafe.
You can however use triggers on the master with any MySQL flavor.
Thank you, Federico. Something like FlexViews is the type of functionality I’m looking for. I’m just not sure it would be Production grade/capable or not. There is not a lot of activity on that particular project.
One of the challenges to using triggers with statements in an HA environment is that we are running in a multi-master replicated cluster using Percona XtraDB. So we don’t want to fire duplicate triggers from those master tables within the cluster. It looks like MariaDB had to address that somehow with their slave triggers when dealing with clusters. That’s propbably a moot point for us since we’re running Percona rather MariaDB.
I think FlexViews has been used in production, and probably is. My only problem with that project is that it requires a lot of different technologies.
About triggers, I really don’t recommend to use statement-based replication. It’s unsafe and inherently slow (because each statement is re-executed in the slave). MariaDB allows to run triggers for RBR events without logging their effects in the binlog.