database｜How to determine（判断） master in mysql master-slave

I am setting up MySQL Master-slave replication and I am trying to figure out how to handle the failover situation where I promote the slave to master (in the event that the master goes down).

我正在设置MySQL主从复制，并且我正在尝试弄清楚如何处理将从站提升为主站（如果主站出现故障）的故障转移情况。

My application server needs to direct all writes to the current master, but I cannot use server level HA between the master and slave (heartbeat, keepalived) since the two db servers are on completely different subnets in different physical locations.

我的应用程序服务器需要将所有写入操作定向到当前主服务器，但我不能在主服务器和从服务器（检测信号，keepalived）之间使用服务器级HA，因为两个数据库服务器位于不同物理位置的完全不同的子网上。

I think this is something that I need to handle at the application level. I can query the two servers and ask which one is a master, then perform all queries to that one.

我认为这是我需要在应用程序级别处理的事情。我可以查询这两台服务器并询问哪一台是主服务器，然后对该服务器执行所有查询。

Is there a query in MySQL to see if the current server is a master in a master-slave replica?

MySQL中是否有查询以查看当前服务器是否是主从副本中的主服务器？

@RolandoMySQLDBA has answered the question accurately ... but he also pointed out that his solution was "quick-and-dirty."

And that is a very true statement. :)

@RolandoMySQLDBA准确地回答了这个问题...但他也指出，他的解决方案是“快速而随意的”。

这是一个非常真实的说法。:)

The thing that concerns me here is not with that answer, but rather is that the original question appears to make an incorrect assumption:

我在这里关心的不是这个答案，而是最初的问题似乎做出了不正确的假设：

I can query the two servers and ask which one is a master, then perform all queries to that one.

我可以查询这两台服务器并询问哪一台是主服务器，然后对该服务器执行所有查询。

The problem is that in MySQL replication, the master is never truly aware that it is the master.

问题在于，在MySQL复制中，主节点永远不会真正意识到它是主节点。

The concept of "promotion to master" is not really a concept in MySQL asynchronous replication. "Promoting" a MySQL server to the master role is something that happens "external to" MySQL servers, as opposed to something that happens "internal to" MySQL servers.

“提升为主”的概念在MySQL异步复制中并不是一个真正的概念。将MySQL服务器“提升”为主角色是发生在MySQL服务器“外部”的事情，而不是“内部”发生在MySQL服务器内部的事情。

"Promotion to master" not done by any kind of server provisioning, because, technically speaking, every MySQL server that has binary logging enabled is a master, even if it never has a slave. SHOW MASTER STATUS works exactly the same way and returns exactly the same result, slaves or not, and a master with 2 slaves is no more or less a master than a master with 1 slave or 0 slaves. Similarly, a master whose slaves are all offline is still just as much a master, because when the slaves come back online, they'll pick up replicating where they left off.

“提升为主服务器”不是由任何类型的服务器配置完成的，因为从技术上讲，每个启用了二进制日志记录的MySQL服务器都是主服务器，即使它从未有过从服务器。SHOW MASTER STATUS的工作方式完全相同，并返回完全相同的结果，无论是否为从站，并且具有2个从站的主站与具有1个从站或0个从站的主站不比具有1个从站或0个从站的主站或多或少。同样，一个从站都处于离线状态的主人仍然同样是一个主站，因为当从站重新上线时，他们会从他们离开的地方开始复制。

In a sense, the only "awareness" on the part of either server is not whether it is a master, but rather, whether it is a slave (or "not").

从某种意义上说，任何一个服务器的唯一“意识”不是它是否是主服务器，而是它是否是从库（或“不是”）。

That's what Rolando's solution asks: "are you a slave?" If the answer is no, then the assumption is that this must be the master... which he also pointed out as a flawed assumption if STOP SLAVE; is issued. But a stopped slave is still a slave, so "not a slave" (at any moment in time) doesn't equate to "being a master."

这就是Rolando's的解决方案所问的：“你是从库吗？如果答案是否定的，那么假设这一定是主人......他还指出，如果停止从库，这是一个有缺陷的假设;已颁发。但是，被阻止的仍然是从库，因此“不是从库”（在任何时刻）都不等于“成为主人”。

A similar test could be done on the presumed master:

可以在假定的主服务器上执行类似的测试：

SELECT COUNT(1) FROM information_schema.processlist WHERE user = 'the_username_used_by_the_slave'; SELECT COUNT(1) FROM information_schema.processlist WHERE command = 'binlog dump';

If the value is zero, then the slave's IO thread is not connected.This test has a similar defect, in that if the slave is disconnected administratively,isolated, or failed, then it won't be connected.So this doesn't really solve anything either.

如果该值为零，则从库的IO线程未连接。此测试具有类似的缺陷，因为如果从库以管理方式断开连接，隔离或失败，则不会连接。所以这也不能真正解决任何问题。

Worse still (for either of these scenarios) the information_schema.processlist "table"is a virtual table that gets materialized every time it is selected from,and this takes time and costs resources. The busier your server, the more it costs,because each thread's activity has to be peered-into.

更糟糕的是（对于这两种情况中的任何一种），information_schema.processlist“表”是一个虚拟表，每次从中选择它时都会实现，这需要时间和成本资源。服务器越繁忙，成本就越高，因为必须对每个线程的活动进行对等互连。

lightweight solution would be:

更轻量级的解决方案是：

SELECT @@global.read_only;

On a slave, you could/should set the global variable read_onlyso that users without the SUPER privilege cannot unintentionally write to it (and your applicationshould not have SUPER). If you manually "promote" the slaveto the master role, you SET GLOBAL read_only = OFFto enable writes. (Replication can always write to the slave,no matter how this is set).

在从属服务器上，您可以/应该设置全局变量read_only，以便没有 SUPER 权限的用户不会无意中写入它（并且您的应用程序不应该具有 SUPER）。如果手动将从属角色“提升”为主角色，则设置全局read_only = OFF 以启用写入。（复制始终可以写入从库，无论如何设置）。

But this still, I think, misses an important point:

但我认为，这仍然遗漏了一个重要的观点：

I would propose that the application should not be making this decision heuristically in a master/slave setup, and certainly not on a connection-by-connection basis. The application should use either a hard configuration option,or the application should remain unaware and have the database connectiondestination handled by something else.

我建议应用程序不应该在主/从设置中以启发式方式做出此决定，当然也不应该在逐个连接的基础上做出此决定。应用程序应使用硬配置选项，或者应用程序应保持不知情状态，并由其他设备处理数据库连接目标。

Or, at minimum, the application should never switch over until the master fails, and then it should never switch back on its own.

或者，至少，应用程序在主服务器失败之前不应切换，然后它永远不应自行切换回去。

Here's why I say that: once the "decision" is made -- by whoever or whatever -- to make another server the master, the application cannot be allowed for any reason to switch back to the original master, even after it comes back online, without intervention

这就是为什么我这么说：一旦“决定”（由任何人或什么人）做出使另一台服务器成为主服务器，就不能以任何理由允许应用程序切换回原始主服务器，即使它重新联机，没有干预。

Let's say you hit a bug and there's a software-forced crash; mysqld_safe dutifully restarts mysqld, and InnoDB crash recovery performs flawlessly. But that takes a few minutes.

假设您遇到了一个错误，并且有一个软件强制崩溃;mysqld_safe尽职尽责地重新启动mysqld，InnoDB崩溃恢复完美无缺地执行。但这需要几分钟。

Meanwhile, the master is down so your application has switched to the slave. Transactions have been created, orders placed, funds transferred, comments posted, blogs edited, whatever your system does.

同时，主站已关闭，因此您的应用程序已切换到从站。无论您的系统做什么，都已创建交易，已下订单，已转移资金，已发布评论，已编辑博客。

Now, the original master comes back online.

现在，原始主库重新联机。

If your application switches back to the original master, you are in an absolute world of hurt, because the very next thing that is likely to happen is that replication stops due to an inconsistency, because your application has changed data on the slave in the mean time. You now have two database servers with inconsistent data that you will have to reconcile manually. If there are dollars or points or credits involved, you now have mismatched balances.

如果您的应用程序切换回原始主服务器，那么您将处于一个绝对受伤害的世界，因为接下来可能发生的事情是复制由于不一致而停止，因为您的应用程序在此期间更改了从站上的数据。现在，您有两个数据库服务器的数据不一致，您必须手动协调这些数据库服务器。如果涉及美元、积分或积分，您现在的余额不匹配。

So it's critical that the application not be allowed to switch back to the original master without your intervention.

因此，在没有您干预的情况下，不允许应用程序切换回原始主服务器至关重要。

Wait, did you just find the problem with this scenario as I described it? The master has failed but your application won't be using the slave, because it thinks the slave is still the slave and not the master... the

information_schema.processlist query on the slave will still return non-zero even if the master server is powered down.

等等，你刚刚发现我所描述的这个场景的问题了吗？主站已失败，但您的应用程序不会使用从站，因为它认为从站仍然是从站而不是主站...从服务器上的 information_schema.processlist 查询仍将返回非零，即使主服务器已关闭电源。

So there isn't much point in the application discovering anything, since you'll have to manually STOP SLAVE for that test to be useful.

因此，应用程序发现任何东西都没有多大意义，因为您必须手动停止SLAVE才能使该测试有用。

Perhaps a better approach if you want the application to be able to switch would be to configure the servers with circular replication.

如果您希望应用程序能够切换，也许更好的方法是使用循环复制配置服务器。

Circular replication has inherent problems of its own, but as long as your application is only always ever writing to one server at a time, most of those issues become non-issues. In other words, both machines are always and simultaneously both master and slave, in a replication sense, but your application, via some mechanism, is always only pointing to one machine at a time as the "master" to which it can and should write.

循环复制本身也有固有的问题，但只要您的应用程序一次只写入一台服务器，大多数这些问题就不再是问题。换句话说，在复制意义上，两台机器始终同时是主机和从机，但是您的应用程序通过某种机制，始终一次只指向一台机器作为它可以并且应该写入的“主机”。

You can't deploy HA tools on the MySQL servers due to their separation, but you could implement it with HAProxy running on the application server(s). The application connects to "MySQL" on localhost, which isn't MySQL at all, but is actually HAProxy ... and it forwards the TCP connection to the appropriate MySQL machine.

由于 MySQL 服务器分离，您无法在 MySQL 服务器上部署 HA 工具，但您可以使用在应用程序服务器上运行的 HAProxy 来实现它。该应用程序连接到localhost上的“MySQL”，它根本不是MySQL，而实际上是HAProxy ...它将TCP连接转发到相应的MySQL机器。

HAProxy can test the connections to the MySQL servers and only offer traffic to a MySQL machine that is accepting connections and allowing authentication.

HAProxy可以测试与MySQL服务器的连接，并且只向接受连接并允许身份验证的MySQL机器提供流量。

The combination of HAProxy running on the application server (its demand for resources won't be substantial compared to everything else the application server has to do -- it's pretty much just tying sockets together and ignoring their payload) ... and MySQL circular replication would be the approach I would probably take in this instance, based on what's known from the question.

在应用程序服务器上运行的HAProxy的组合（与应用程序服务器必须执行的其他所有操作相比，它对资源的需求并不大 - 它几乎只是将套接字绑定在一起并忽略它们的有效负载）......MySQL循环复制将是我在这种情况下可能采用的方法，基于从问题中了解到的情况。

Or, for a strictly manual setup, go with something much simpler than "discovery," like an entry in the app server's /etc/hosts file with a hostname that the application uses to connect to MySQL, which you could manually update -- assuming promotion of slave to master is intended to be a manual process.

或者，对于严格的手动设置，请使用比“发现”简单得多的设置，例如应用程序服务器的/etc/hosts文件中的条目，其中包含应用程序用于连接到MySQL的主机名，您可以手动更新 - 假设将从站提升为主站是一个手动过程。

Or, something more complex, using Percona XtraDB Cluster. For this, though, you'd want to add a third server, because with 3 nodes in PXC, if 2 servers can see each other but get isolated from 1 server (if all three are still running) the 2 servers keep running happily but the 1 server curls up into a little ball and refuses to do anything since it realizes it must be the odd one out. This works because the 2 realize they still constitute a majority of the nodes that were online before the network split and the 1 realizes that it isn't. With PXC, it doesn't really matter which server your application connects to.

或者，更复杂的是，使用Percona XtraDB Cluster。但是，为此，您需要添加第三台服务器，因为PXC中有3个节点，如果2台服务器可以看到彼此，但与1台服务器隔离（如果所有三台服务器仍在运行），则2台服务器保持快乐运行，但1台服务器蜷缩成一个小球并拒绝做任何事情，因为它意识到它必须是奇怪的。这是有效的，因为2意识到它们仍然构成了网络分裂之前在线的大多数节点，而1意识到它不是。使用PXC，您的应用程序连接到哪个服务器并不重要。

I say all of this is to say "don't have the application poll the servers to see which one is the master" because it will bite you sooner or later and it will nibble away at your performance up until the day it bites.

我说所有这些都是说“不要让应用程序轮询服务器以查看哪一个是主服务器”，因为它迟早会咬你，它会蚕食你的表现，直到它咬人的那一天。

If you are using Master/Slave only, here is something quick-and-dirty:

如果您仅使用主/从，这里有一些简单随意的方式：

SELECT COUNT(1) SlaveThreadCount FROM information_schema.processlist WHERE user='system user';

What does this tell you?

这说明什么？

If SlaveThreadCount= 0, you have the Master

If SlaveThreadCount> 0, you have the Slave

CAVEAT（警告） : This works as long as you do not run STOP SLAVE; 只要您不运行STOP SLAVE;，这就有效

Another thing to try is this: If you disable binary logging on the Slave and you run

SHOW MASTER STATUS;, the Master gives you the current binary log. The Slave gives you nothing.

另一个要尝试的事情是：如果您在从属服务器上禁用二进制日志记录并运行SHOW MASTER STATUS;，则Master会为您提供当前的二进制日志。从主机什么也没给你。

https://dba.stackexchange.com/questions/46011/how-to-determine-master-in-mysql-master-slave