mysql 复制 - 表锁定？
我目前在一家公司工作，该公司有一个运行 mysql/php 的网站（所有表也使用 MYISAM 表类型）。
我们想实现复制，但我在 mysql 文档和互联网上的其他地方读到，这将在写入二进制日志（从属数据库最终从中读取）时锁定表。
这些锁是否会在写入量相当大的实时站点上造成问题？ 另外，有没有一种方法可以在不锁定表的情况下启用复制？
I am currently working for a company that has a website running mysql/php (all tables are also using the MYISAM table type).
We would like to implement replication, but I have read in the mysql docs and elsewhere on the internet that this will lock the tables when doing the writes to the binary log (which the slave dbs will eventually read from).
Will these locks cause a problem on a live site that is fairly write-heavy? Also, is there a way to enable replication without having to lock the tables?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果将表类型更改为 innodb，则会使用行级锁定。 此外，您的复制将更加稳定，因为更新将是事务性的。 MyISAM 复制是一个长期的痛苦。
确保您的服务器版本匹配，并且始终确保在关闭从属服务器之前关闭主服务器。 关闭从站后，您可以立即重新启动主站，但您必须将其关闭。
另外，请确保为 InnoDB 使用适当的自动扩展选项。 而且，当您使用它时，您可能希望从 float 和 double 迁移到“十进制”（这意味着 mysql 5.1。）这将为您节省一些复制麻烦。
这可能比你要求的多一点。 享受。
PS，是的，myisam 锁可能会导致问题。 另外，innodb 比 myisam 慢，除非 myisam 阻塞了一个巨大的选择。
If you change your table types to innodb, row level locking is used. Also, your replication will be more stable, as updates will be transactional. MyISAM replication is a long-term pain.
Be sure that your servers are version-matched, and ALWAYS be sure to shut down the master before shutting down the slaves. You can bring the master up again immediately after shutting down the slaves, but you do have to take it down.
Also, make sure you use appropriate autoextend options for InnoDB. And, while you're at it, you'll probably want to migrate away from float and double to 'decimal' (which means mysql 5.1.) That will save you some replication headaches.
That's probably a bit more than you asked for. Enjoy.
P.s., yes the myisam locks can cause problems. Also, innodb is slower than myisam, unless myisam is blocking for a huge select.
根据我对写入密集型站点进行 DBA 的经验，写入二进制日志不会给主服务器上的锁定或性能带来明显的问题。 如果您想对其进行基准测试，只需打开二进制日志记录即可。 我真的不认为表被锁定以将查询写入二进制日志。
然而，从属设备上的表锁定完全是另一回事。 复制是串行的：每个查询在从属运行下一个查询之前运行完成。 如此长的更新会导致复制暂时落后。 如果您的应用程序打算使用复制进行横向扩展，则它需要知道如何适应这一点。
In my experience DBAing a write-heavy site, writing a binary log adds no perceivable problems with locking or performance on the master. If you want to benchmark it, simply turn binary logging on. I really don't think tables are locked to write queries to the binary log.
Table locking on the slave is quite another thing, however. Replication is serial: each query runs to completion before the slave runs the next one. So long updates will cause replication to fall behind temporarily. If your application is intending to use replication for scale-out, it needs to know how to accomodate this.
myisam 表类型的解决方案并不是“更好”。 不过，你可以接受它。
您能做的最好的事情就是确保您的从服务器和主服务器在相同的硬件上运行（FPU 差异可能会导致复制错误），并确保您在 MySQL 服务器上运行相同的版本号。
以下链接回答了您的问题。 具体来说，如果没有进行删除操作，MyISAM 表中的锁阻止写入的可能性较小。 因此，没有删除孔的表在复制设置中执行速度会更快。
http://dev.mysql.com/doc/refman/ 5.1/en/internal-locking.html
您可以通过在计划停机期间（特别是在批量删除之后）定期进行 DBA 导出/导入来减轻“漏洞”的影响。此外，请确保您的从数据库不会下线时master仍在运行。 这将为您节省很多很多问题。
The solution with the myisam table type is not 'better'. However, you can get by with it.
The best you can do, is make sure your slave and master run on the same hardware (FPU differences can create replication errors), as well as making sure you are running the same version numbers on your MySQL servers.
The following link answers your questions. Specifically, locks in MyISAM tables have less of a chance of blocking writes if there are no deletes going on. So a table that doesn't have delete holes in it will perform faster in a replicated setup.
http://dev.mysql.com/doc/refman/5.1/en/internal-locking.html
You can mitigate the effect of 'holes' by have a DBA export/import periodically during scheduled downtimes (especially after mass deletes.) Also, make sure your slave databases don't go down with the master still running. That will save you many, many issues.