Dear beloved people at ServerFault. I'm trying (and failing) to setup a load balanced MySQL server array. Please enlighten me, show me the error in my ways.
Current setup: two MySQL servers with dedicated fixed IP's (10.116.219.47 & 10.116.219.48) and one virtual IP that is shared using keepalived (10.116.219.12). This setup is successful in providing failover. DB2-server quickly takes over if we turn off DB1-server. It's only when adding a virtual_server block to the config, that things start to break.
DB1
vrrp_instance VI_2 {
state MASTER
interface eth0
virtual_router_id 60
priority 150
advert_int 1
authentication {
auth_type PASS
auth_pass 3S83hbt200SbwY6
}
virtual_ipaddress {
10.116.219.12
}
}
DB2
vrrp_instance VI_2 {
state BACKUP
interface eth0
virtual_router_id 60
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 3S83hbt200SbwY6
}
virtual_ipaddress {
10.116.219.12
}
}
We've been trying to add load balancing to the keepalived config, but here DB2 stops responding.
virtual_server 10.116.219.12 3306 {
delay_loop 2
lb_algo rr
lb_kind DR
protocol TCP
real_server 10.116.219.47 3306 {
weight 10
TCP_CHECK {
connect_port 3306
connect_timeout 1
}
}
real_server 10.116.219.48 3306 {
weight 10
TCP_CHECK {
connect_port 3306
connect_timeout 1
}
}
}
Trying to connect to the virtual ip (10.116.219.12) will alternate on a successful response from DB1, or a time-out from DB2.
Debugging so far:
- MySQL connecting to the fixed ip's works on both DB1 and DB2. Firewall/MySQL binding is not blocking incoming connections.
- Set net.ipv4.ip_forward=1 on both DB1 and DB2
- Listening for TCP inbound connections on DB2 will show up the attempt. (20:46:08.385786 IP 10.116.219.44.46211 > 10.116.219.12.mysql)
So it appears that DB2 MySQL-server refuses to respond, my guess is because the incoming packet destination (the shared virtual ip) is not known to the DB2 server, because DB1 is carrying it as keepalived master.