如何保证MySQL复制SLAVE与复制MASTER完全同步？
使用一主一从的简单复制设置，如何确保从站和主站完全同步？
现在是的，它们都从完全相同的映像开始，并且复制正在工作并报告一切正常但是： * 曾经发生过停止复制时出现错误，然后必须停止复制然后再恢复的情况。 * 也许 SLAVE 上意外发生了变化，然后它就不再与 MASTER 相同了。 * 其他可能破坏同步的情况。
虽然可以对两个数据库进行大型 mysqldump 并比较文件，但我对一种可以更轻松实现并且可以自动检查以确保所有内容同步的方法感兴趣。
谢谢
Using simple replication settings with one MASTER and one SLAVE, how can one ensure that the SLAVE and MASTER are fully synchronized?
Now yes, they both started from the exact same image and replication is working and reporting that everything is okay BUT:
* It has happened that there were errors stopping the replication and then the replication had to be stopped and later resumed.
* Perhaps a change accidentally occurred on the SLAVE and then it's not the same as the MASTER anymore.
* Other whichever scenarios that might break sync.
While it's possible to do a big mysqldump of both database and compare the files I would be interested in a method that can be implemented more easily and also can be checked automatically to ensure all is in sync.
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您尝试过 Percona Toolkit（以前称为 Maatkit）吗？您可以使用他们的工具之一，即 pt-table-checksum对于你的情况。您也可以在他们的网站上查看其他工具。
Have you tried Percona Toolkit (formerly known as Maatkit)? You can use one of their tools which is pt-table-checksum for your case. You can check other tools too at their website.
如果您拥有 MySQL Server 版本 5.6.14 或更高版本，则可以使用 MySQL 复制同步检查器。它包含在 MySql 服务器包中。专门设计用于支持全局事务标识符 (GTID) 且 gtid_mode=ON 的服务器。
有关详细信息，请参阅 MySQL 文档
If you have MySQL Server versions 5.6.14 or higher, you can use the MySQL Replication Synchronization Checker. It's included in the MySql server package. Is designed to work exclusively for servers that support global transaction identifiers (GTIDs) and have gtid_mode=ON.
See MySQL Documentation for more information
您对看似健康的主/从复制设置的怀疑是正确的！当我们突然收到来自 check_mk 的警报时，我们运行得很好，该警报涉及我们的主服务器上存在的数据库，而我们的从服务器上不存在......但是主服务器和从服务器的状态输出都很好！这有多令人不安？证明过程完整性的方法是使用校验和来验证数据。
我在互联网上看到很多推荐 pt-table-checksum 。然而，它的局限性被证明是太繁重了，让我们感到不舒服。最重要的是，它需要甚至设置基于语句的复制（请参阅 pt-table-checksum 链接）。正如mysql 5.6在线文档中所述，（对于基于行的复制...）“所有更改都可以复制。这是最安全的复制形式。”基于语句的复制还有其他缺点，让我们的开发人员感到紧张，因为某些功能无法正确复制；请参阅文档以获取列表。
我们已经遇到过使用基于语句的复制的主服务器和从服务器的问题，因此我们特别努力避免它。
我们将尝试 mysqlrplsync，它特别提到它 " 独立工作二进制日志格式（行、语句或混合）”。然而，它还提到必须打开 gtid-mode，并且它需要 MySQL 5.6.14 及更高版本...我相信，这意味着 RHEL7/CentOS 7 附带的 MySQL 至少已经过时了。您需要获取 MySQL Community Edition，该版本留给读者作为练习但是您可以此处获取软件包 或此处获取存储库，包括 RHEL 衍生品和 Debian。
You are right to be suspicious of a seemingly healthy master/slave replication setup! We were running fine when suddenly we got alerts from check_mk concerning a database that existed on our master that did not exist on our slave... but the master and slave status outputs were good! How unnerving is that? The way to prove integrity of the process is to use checksums to verify the data.
I have seen a lot of chatter on the Internet recommending pt-table-checksum . However, its limitations proved to be too onerous for us to be comfortable with. Most importantly, it requires and even sets statement-based replication (see the pt-table-checksum link). As it says in the mysql 5.6 online documentation, (for row-based replication...) "all changes can be replicated. This is the safest form of replication." There are other disadvantages to statement-based replication that make our developers nervous because some functions cannot be replicated properly; see the doc for a list.
We have already experienced issues with a master and slave using statement-based replication so we're specifically trying to avoid it.
We are going to try mysqlrplsync which specifically mentions that it "works independently of the binary log format (row, statement, or mixed)". It also mentions, however, that gtid-mode must be on and it requires MySQL 5.6.14 and higher... which means, I believe, that the MySQL delivered with RHEL7/CentOS 7 at least is out. You'll need to get the MySQL Community Edition, which is left as an exercise for the reader but you can go here for the packages or here for the repos, including RHEL derivatives and Debian.