Skip some columns replication on slave in MySQL

Question

I have identical table on master and slave. Both have many columns with price among them. Is there any possibility to set individual values for price column in slave so that replication won't overwrites them?

Our system works in many countries that have individual prices, but we would also like to share some common data from the central database.

I though up some solutions:

• Extract all country specific columns to other tables that won't be replicated.
This would require a lot of changes in our source code.
• Create some proxy database with only common shared columns that will replicate from master and then set it as master to country based database with full column definition.
It looks ugly to me.

Is there something better? I would appreciate any help.

Answer 1

I really don't think replication is designed to do "almost-replication" like you're asking. I don't think you can replicate just part of a table.

Your first solution sounds the most reasonable to me.

In general, I'd come up with a software solution that works with no replication, or with all tables being replicated. Then add replication in production to add redundancy or performance. Anything else sounds like it'll be pretty complex to develop and test.

(Me: I'm not exactly a MySQL expert, though I have set up MySQL replication a couple times.)

Answer 2

There is a slave-skip-columns patch in Percona Server 5.1. You might want to take a look.

Answer 3

This is an old question, but comes up first in Google so I thought this is relevant and useful: https://dev.mysql.com/doc/refman/5.5/en/replication-features-differing-tables.html

In short, if your common columns in a table, on master and on slave, are of the same type and in the same order, then replication works even if there are extra columns at the end on either master or slave. For mismatching data types, there are some rules.