MySQL:写入从节点 [英] MySQL: Writing to slave node

问题描述

让我们说我拥有汽车数据库.我有品牌和型号(FK品牌).我计划让用户跟踪他们的汽车.每辆车都有FK来建模.现在，我有很多用户，我想拆分数据库以分配负载. Makes和Models表变化不大，但需要在各个分片之间共享.我的想法是使用MySQL从品牌和型号的主数据库复制到每个从数据库.我的问题是:如果不写主数据库上的那些表，是否可以安全地写从数据库?

Lets say I have a datbase of Cars. I have Makes and Models (FK to Makes). I plan on having users track their cars. each Car has a FK to Model. Now, I have a lot of users, and I want to split up my database to distribute load. The Makes and Models tables don't change so much, but they need to be shared across shards. My thought is to use MySQL replication from a master DB of makes and models to each slave database. My question is: Can I safely write to the slave databases assuming I don't write to those tables on the master?

在讨论这个问题时，是否仍然可以保证一个从属数据库具有最新数据?例如，某人刚刚添加了"Taurus"品牌，然后想要添加他们的汽车.我可以确保他们正在使用的从数据库具有最新的主数据吗?

And while on the subject, is there anyway to guarantee one slave database has the latest data? For example, someone just added the 'Taurus' make, and then wants to add their car. Can I ensure that the slave database they are using has the latest master data?

推荐答案

是的，通常，您可以安全地写入未在主服务器上写入的从服务器上的表.如果您独立地执行诸如在从属服务器和主服务器上插入auto_increment行的操作，那么您当然会遇到问题.实际上，您应该配置该表以将其完全排除在复制之外.

Yes, in general you can safely write to a table on the slaves that is not being written on the master. If you do things like insert auto_increment rows on the slaves and on the master, independently, you will of course have problems. You should configure that table to be excluded from replication entirely, really.

为检查是否有最新数据，SHOW SLAVE STATUS包含一个字段Seconds_Behind_Master，该字段告诉您从站是否最新.显然，您希望它为零.当然，要确定存在插入和复制的数据，您需要等待一秒钟，然后然后看到Seconds_Behind_Master为零.

For checking whether you have the latest data, SHOW SLAVE STATUS includes a field Seconds_Behind_Master that tells you whether the slave is up to date. Obviously you want it to be zero. To be certain that inserted and replicated data is present, of course, you need to wait a second and then see that Seconds_Behind_Master is zero.

这篇关于MySQL:写入从节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！