在 MySQL 中跳过从属设备上的某些列复制
我在主站和从站上有相同的表。两者都有许多包含 price 的列。是否有可能为从站中的 price 列设置单独的值，以便复制不会覆盖它们？
我们的系统适用于许多有单独价格的国家，但我们也想分享中央数据库中的一些通用数据。
我想出了一些解决方案：
- 将所有国家/地区特定列提取到不会复制的其他表中。 This would require a lot of changes in our source code.
- 创建一些仅包含将从主数据库复制的公共共享列的代理数据库，然后将其设置为具有完整列定义的基于国家/地区的数据库的主数据库。 It looks ugly to me.
还有更好的吗？我将不胜感激任何帮助。
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.
- 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.
This would require a lot of changes in our source code.
It looks ugly to me.
Is there something better? I would appreciate any help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我真的不认为复制是为了像您所要求的那样进行“几乎复制”而设计的。我认为你不能只复制表的一部分。
您的第一个解决方案对我来说听起来最合理。
一般来说，我会想出一个无需复制或复制所有表的软件解决方案。然后在生产中添加复制以增加冗余或性能。其他任何事情听起来开发和测试都会相当复杂。
（我：虽然我已经设置过几次 MySQL 复制，但我并不完全是 MySQL 专家。）
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.)
Percona Server 5.1 中有一个从属跳过列补丁。您可能想看一下。
There is a slave-skip-columns patch in Percona Server 5.1. You might want to take a look.
这是一个老问题，但首先在 Google 中出现，所以我认为这是相关且有用的： https://dev.mysql.com/doc/refman/5.5/en/replication-features-differing-tables.html
简而言之，如果你的公共列在主服务器和从服务器上的表具有相同的类型和相同的顺序，那么即使主服务器或从服务器的末尾有额外的列，复制也可以工作。对于不匹配的数据类型，有一些规则。
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.