Is it possible to have a MySQL set-up that is running on a 2 Active master running over a VIP/Keep alive. Then having a multiple slaves that connecting to the VIP of the 2 master for the replication?

(The application is only connected to the VIP of that 2 master and any write can accept on any node, if master1 is busy, then master 2 will take the new write to the database and replicate it to the master1).

So, that if ever that there's an issue with the master1 still the master2 is running and the replication is also running smoothly as it is connected with the VIP of the 2 master.

Any inputs are really and highly appreciated. Thank you very much in advance!


Do not connect a Slave to a pair of Masters via a VIP.

There is nothing to keep the binlogs synced up, so a Slave must communicate with only one Master. (Multi-source Slave is a different topic.)

You could, however, have half your slaves connected to one Master, and half to the other. Then if either Master goes down, you lose only half the Slaves.

If you are trying to limit the configuration to only 3 servers, then consider Galera (in PXC or MariaDB). It has a lot more advantages than M<->M->S.

Even with multiple Slaves, consider Galera with 3 nodes, then evenly distribute the slaves off the Cluster nodes. Now a single Master failure will lead to losing only 1/3 of the Slaves.

(Actually, you don't lose the Slaves of a dead Master, but they do get stale.)

  • That's make sense. So, the only way is to create a cluster set-up for HA solution? Not with the master-master-multiple slaves set-up? Jan 25 '18 at 2:16
  • @DatabaseAdmin - There are several ways. My opinions: M-M (with single-writer) was the best yesterday. MHA helped with failover. I think Galera is the best today. Group Replication may like a strong competitor tomorrow. Each of those allows for multiple Slaves for read scaling.
    – Rick James
    Jan 25 '18 at 2:24
  • actually, what I'm trying to prevent here is the downtime with the master, as we encountered about 2 days ago, that the master is down for almost 30 minutes due to a hardware issues. Jan 25 '18 at 2:27
  • I want something like, even the master1 is down, master 2 will still continue and since it is over a VIP, the application will still send insert through the VIP. And the master 2 still, continue to feed data to their slaves. Jan 25 '18 at 2:30
  • @DatabaseAdmin - The binlog position (see SHOW SLAVE STATUS) and other things are not consistent between the two Masters. A CHANGE MASTER with carefully filled in values is needed to point a Slave to a different Master. If it worked for you, it was pure luck
    – Rick James
    Jan 25 '18 at 2:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.