用于回退场景的 MySQL 复制
当我有两台 mysql 服务器，它们有不同的作业（持有不同的数据库），但希望能够在另一台出现故障时使用其中一台来插入，您会建议我如何保持两台服务器上的数据相等“关闭”实时”？
显然，不可能每 x 分钟进行一次完整的数据库转储。
我读过二进制日志，是这样吗？我需要走哪条路？ 这不会大大减慢后备服务器的速度吗？ 有没有办法不将某些表包含在二进制日志中 - 数据已更改并不重要？
When I have two mysql servers that have different jobs (holding different databases) but want to be able to use one of them to slip in when the other one fails, what would you suggest how I keep the data on both of them equal "close to realtime"?
Obviously it's not possible to make a full database dump every x minutes.
I've read about the Binary Log, is that the way that I need to go? Will that not slow down the fallback server a lot? Is there a way to not include some tables in the binary log - where it doesn't matter that the data has changed?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可能需要考虑主-主复制方案，但略有不同。 您可以指定要复制的数据库并限制每个服务器的复制。
对于 server1，我将添加
--replicate-do-db=server_2_db和 server2--replicate-do-db=server_1_db到您的 my.cnf （或 my.ini在 Windows 上）。 这意味着只有 server_1_db 的语句才会被复制到 server2，反之亦然。另请确保定期执行完整备份，而不仅仅是依赖复制，因为它不能提供针对意外 DROP DATABASE 语句或类似语句的安全性。
You may want to consider the master-master replication scenario, but with a slight twist. You can specify which databases to replicate and limit the replication for each server.
For server1 I would add
--replicate-do-db=server_2_dband on server2--replicate-do-db=server_1_dbto your my.cnf (or my.ini on Windows). This would mean that only statements for the server_1_db would be replicated to server2 and vice verse.Please also make sure that you perform full backups on a regular basis and not just rely on replication as it does not provide safety from accidental
DROP DATABASEstatements or their like.二进制日志绝对是最佳选择。 然而，您应该意识到，使用MySQL，您不能像这样在服务器之间来回切换。
一台服务器将成为主服务器，另一台服务器将成为从服务器。 您可以向主服务器写入/读取，但只能从从服务器读取。 如果您曾经写入从属设备，它们将不同步，并且没有简单的方法可以让它们再次同步（基本上，您必须交换它们，以便主设备成为新的从设备，但这是一个繁琐的手动过程）。
如果您需要真正的热插拔备份数据库，您可能必须使用 MySQL 以外的系统。 如果您想要的只是一个只读的实时备份，可以在最坏的情况下立即使用（主数据库被永久破坏），那么二进制日志将非常适合您。
Binary log is definitely the way to go. However, you should be aware that with MySQL you can't just flip back and forth between servers like that.
One server will be the master and the other will be the slave. You write/read to the master, but can only read from the slave server. If you ever write to the slave, they'll be out of sync and there's no easy way to get them to sync up again (basically, you have to swap them so the master is the new slave, but this is a tedious manual process).
If you need true hot-swappable backup databases you might have to go to a system other than MySQL. If all you want is a read-only live backup that you can use instantly in the worst-case scenario (master is permanently destroyed), Binary Log will suit you just fine.