How to prevent replication of certain records with mysql

Question

I have master-master replication working successfully, it works fine and all changes I make will reflect to slave also.

But I don't want that slave to reflect all changes - only such records that I want should reflect on slave. How can can do this?

Answer 1

You can prevent the slave from processing updates for particular databases, and individual tables, either as a whitelist or a blacklist - see the slave configuration options for details.

If your criteria are more complex than that, you might need to rethink.

Answer 2

The only way I can think of is to create a VIEW on the master that filters out the records you don't want to replicate, then replicate that VIEW. On the slave the "VIEW" table would be MYISAM/InnoDB instead of a VIEW. I know you can replicate to different table types (i.e. InnoDB->MyISAM), but I never tried doing it with a View.

Answer 3

Without knowing what you're trying to achieve, it is hard to say.

You can prevent changes to individual tables, or whole databases from being made through replication (for instance its recommended that you don't replicate the mysql.* tables as you might not want GRANTs getting replicated) (Paul Dixon's answer provides some more info about this)

If you want to prevent certain types of statements from being replicated (for instance DELETE queries) then you might be able to use MySQL proxy to rewrite those queries before replicating them.

If you're intending to shard the database on the slaves (eg: records starting A-M on one server and N-Z on another) then again MySQL proxy might be able to help you.