MySQL Replication - IP Failover

Question

So, I setup master-slave replication in MySQL on two virtual machines in two different physical servers. The problem that I am scratching my head at now, is, when the master goes down, how do the clients switch to the slave to do reads?

For example:

Master (192.168.1.1) mysql1.mydomain.com
Slave  (192.168.1.2) mysql2.mydomain.com

All clients currently connect to mysql1.mydomain.com, how in the world do I configure this? Seems like I need IP-Failover, or some DNS magic. A DNS magic record that was mysql-cluster.mydomain.com that always pointed to the MySQL server that is up or master, would be perfect.

Answer 1

You can use Heartbeat : It can manage a virtual IP address between your two nodes. Your clients will use this vip to connect to mysql - If the master fails, heartbeat detects it and ups the vip on the second node. Be careful not to set the auto-failover on the primary node when it comes back, because obviously his data will be outdated

Answer 2

If you want to go for "DNS magic", then choose Round Robin DNS.

This is only a partial solution: what about MySQL updates since the slave is read-only? Maybe MySQL master-master replication is a better solution in this situation.