大型数据库表上的Mysql主从复制（如何同步初始数据）
我们有一个生产服务器和一个开发服务器。我们发现，由于我们遇到的查询量很大，在生产服务器上几乎不可能进行备份。因此，我们正在考虑以我们的开发服务器作为从属服务器来设置复制。这是理想的，因为我们可以负担得起锁定该服务器上的表，此外，为开发人员提供最新数据也很好。现在，问题。此时生产服务器无法真正被关闭或锁定，至少不容易。我们有很高的查询量和相当大的 30+ GB innodb 表。两台服务器都运行所有 innodb，并且都运行 mysql 5.1。我们可以做什么来同步数据以开始复制？我尝试了几种选择，但到目前为止，没有一个有效。
We have a production server and a dev server. We have found that backups are nearly impossible on the production server because of the query volume we experience. So, we're looking at setting up replication with our dev server being the slave. This is ideal because we can afford to lock the tables on that server and additionally it will be nice to have up to date data for the developers. Now, the issues. The production server can't really be taken down or locked at this point, at least not easily. We have a high query volume and fairly large 30+ GB innodb tables. Both servers are running all innodb and are also both on mysql 5.1. What can we do to sync the data initially to get replication started? I've tried a few options, but so far, none have worked.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您的问题似乎表明您有一个没有冗余的生产服务器，并且没有计划如何处理主数据库服务器的故障。如果这个假设成立，那么您可以采用两种路径来设置复制。
继续按照原样的架构设置复制的路径。如果您选择这条路径，那么将需要更多信息才能知道什么是最佳方法。例如，是否启用二进制日志？如果没有详细信息，我相信我能给出的最佳建议是研究 Percona XtraBackup 工具。
第二个选项是更新生产数据库架构以包括冗余和可行的故障转移计划。这将为您提供更强大的环境，并能够轻松设置额外的复制。我强烈推荐此选项，因为您最终会遇到生产数据库服务器问题，从而导致计划外停机。
Your question seems to indicate that you have a single production server with no redundancy and no plans for how to deal with a failure of the primary database server. If this assumption is true, then there are two paths you could take to set up replication.
Continue down the path of setting up replication with the architecture as is. If you choose this path, then more information would be needed to know what is the best approach. For example, are binary logs enabled? Without the details, I believe the best advice I can give is to look into the Percona XtraBackup tool.
The second option is to update your production database architecture to include redundancy and a workable fail-over plan. This would give you a more robust environment and the ability to setup additional replication easily. I would highly recommend this option since you will eventually have an issue with the production database server which will result in an unplanned outage.