How to identify master host when you enable master-master replication

MySQL & MariaDB Other MySQL® Questions
1

Hi Team,

I have two hosts, tried to enable master-master replication. We have failed over once and not able to identify which is acting as master currently. Could you please let us know how to identify which is current master host. Will show master status give the expected results or show slave status help. Please suggest!

Thanks,
Krishna

2

Master-master replication means that both servers could act as a master and take write traffic. Do you intend to set them up with one server being active and another being passive? You said you have failed over once, so is the application actively writing/reading to the database now? If that is the case, several things you could do

  1. use the application’s connection parameter to connect to the master, and run “show variables like ‘hostname’;”
  2. or just by checking the connected users and the traffic on each host via “show processlist”.
  3. or run “show master status” to check which server’s binlog file is rolling forward.

Regarding SHOW SLAVE STATUS, if a server is not a slave of any server, the command would return an empty set, otherwise, the output would tell the master(Master_host), whether the replication is in action (Slave_IO_Running, Slave_SQL_Running), the master binlog position that slave is currently replicating and so.

The manual would be a great resource to learn about replication. [url]http://dev.mysql.com/doc/refman/5.6/en/replication.html[/url]

3

Kindly check your application to which hostname its point to. If your using VIP check ifconfig to where its failovered to. Do show processlist to see where there are active application connection.

4

If you are using VIP, i’m sure you know the VIP address. You can run " arp -n " to find out “vip_address vip_mac” , then find out the “vip_mac” is which master’s mac address, and this master is the active master.

Hope it works.

5

Hi Team,

Thanks a lot for your reply, I could get the required info from Psong reply.

Thanks again,
Krishna