![\"\"](\"https:\/\/icicimov.com\/blog\/wp-content\/uploads\/2014\/09\/keepalived.jpg\")
<\/a><\/p>\nIf the software talking to MySQL database can take multiple instances as input parameter then the dual master setup is all that we need (taken the above mentioned limitations don’t apply). This setup might suit the purpose of Tomcat HA shared sessions storage for example which is simple enough not to cause any issues. I also have MySQL Master-Master mode cluster with circular replication running as WordPress backend database in production without any issues as well. For others we need to make sure the client writes to one instance only and read from all or in case of slow replication write and read to one instance only and have the second one as standby. That’s where keepalived comes into play.<\/p>\n
Setup<\/h1>\n
The hosts have been setup with two network interfaces, one on a public Although we have MySQL replication set as Master-Master we have to insure we write to only one server at all times. We use Keepalived with floating VIP for that purpose. It will elect one of the MySQL backends upon client request and establish permanent connection so all consecutive requests go to the same instance. The following kernel setting are needed before we start:<\/p>\n that will enable the services, MySQL in this case, to bind to non-local IP address, enable asymmetric routing on the host (requests might come via one interface but leave via another) and set the appropriate ARP level on the network interfaces. Then after installing Keepalived as simple as:<\/p>\n we need to create the main configuration file On host02:<\/p>\n Set the health check user we will use in Keepalived mysql script on one of the hosts (the change will get replicated to the other one):<\/p>\n Next we set a simple health check script Another point we need to solve is the port redirect on the backup server. On the backup server MySQL needs to serve requests coming to the VIP which does not exist on this box. To solve this we set redirect rule on the firewall:<\/p>\n We need to do this automatically on fail over and startup so we modify our config little bit. On both servers we create the following script and tell the LB to execute it on fail over as we did in the above configuration file. This script will add the firewall rule on the BACKUP node and remove it on the MASTER one.<\/p>\n That’s it, both servers are setup as a load balancers listening on VIP and sending the incoming traffic only to the host that the VIP is bound to (effect of We need to test couple of scenarios for HA and failover.<\/p>\n In case one of the MySQL db’s fails the LVS LB will detect that and remove that instance from the LB route:<\/p>\n which we can see in the VS routing table as well:<\/p>\n and when it comes back:<\/p>\n The failover on the client side will look like this:<\/p>\n In case of the MySQL client, the client retries the connection and reconnects to the other server by itself. In case of some custom client, the client should be able to do the same ie keep retrying the connection until it succeeds.<\/p>\n In case the MASTER node fails, simulating by stopping the Keepalived service:<\/p>\n the BACKUP takes the role of MASTER:<\/p>\n takes over the VIP and removes the firewall rule by executing the so the client will reconnect after renewing the connection to the db:<\/p>\n And when the old MASTER rejoins the cluster it takes the role of BACKUP which is exactly what we want in order to avoid the flip-flop effect and interrupt the clients again (the nopreempt option in our config):<\/p>\n on the current MASTER we will see:<\/p>\n The main problem in AWS is that this provider is blocking the multicast traffic in the VPC’s. To circumvent this we need to switch to unicast for the LVS\/IPVS cluster communication. Another issue is the challenge of the virtual environment it self, more specific the VIP failover. In the virtual world it is not enough to move the VIP from one host to another but we also need to inform the physical host Hypervisor platform (Xen,KVM etc) about the change so the traffic can be correctly routed to the new destination via its SDN (Software Defined Network).<\/p>\n The solution of the first problem is using the What we want to achieve here is have a MySQL HA two nodes cluster in Master-Master mode and load balance the instances using as less hardware as possible. The role of the LB will be given to Keepalived that will…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,9],"tags":[],"class_list":["post-160","post","type-post","status-publish","format-standard","hentry","category-database","category-high-availability"],"_links":{"self":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=160"}],"version-history":[{"count":9,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/160\/revisions"}],"predecessor-version":[{"id":168,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/160\/revisions\/168"}],"wp:attachment":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}192.168.100.0\/24<\/code> network that will be used for incoming client connections and cluster communication and one on the private 10.10.1.0\/24<\/code> network that will be used for the replication traffic only. We will start by setting the MySQL service on the nodes first.<\/p>\nKeepalived<\/h2>\n
net.ipv4.ip_nonlocal_bind=1\nnet.ipv4.ip_forward=1\nnet.ipv4.conf.default.arp_ignore=1\nnet.ipv4.conf.default.arp_announce=2\nnet.ipv4.conf.all.arp_ignore=1\nnet.ipv4.conf.all.arp_announce=2\nnet.ipv4.conf.default.rp_filter=0\nnet.ipv4.conf.all.rp_filter=0\nnet.ipv4.tcp_syncookies=1\nnet.ipv4.conf.all.log_martians=1\n<\/code><\/pre>\n$ sudo aptitude install keepalived\n<\/code><\/pre>\n\/etc\/keepalived\/keepalived.conf<\/code> on each host. On host01:<\/p>\nglobal_defs {\n lvs_id lvs_host01\n notification_email {\n igorc@encompasscorporation.com\n }\n notification_email_from loadbalancer1\n smtp_server mail.bigpond.com\n smtp_connect_timeout 5\n router_id lb1\n}\nvrrp_instance VI_1 {\n interface eth0\n state BACKUP\n lvs_sync_daemon_interface eth0\n virtual_router_id 50\n priority 102\n advert_int 3\n track_interface {\n eth0\n }\n authentication {\n auth_type PASS\n auth_pass password\n }\n virtual_ipaddress {\n 192.168.100.91 label eth0:1\n }\n notify_master \"\/etc\/keepalived\/iptables.sh 192.168.100.91 master\"\n notify_backup \"\/etc\/keepalived\/iptables.sh 192.168.100.91 backup\"\n}\nvirtual_server 192.168.100.91 3306 {\n delay_loop 6\n nopreempt\n lb_algo rr\n lb_kind DR\n protocol TCP\n real_server 192.168.100.89 3306 {\n weight 10\n MISC_CHECK {\n misc_path \"\/etc\/keepalived\/mysql-check.sh 192.168.100.89\"\n misc_timeout 15\n }\n }\n real_server 192.168.100.90 3306 {\n weight 10\n MISC_CHECK {\n misc_path \"\/etc\/keepalived\/mysql-check.sh 192.168.100.90\"\n misc_timeout 15\n }\n }\n}\n<\/code><\/pre>\nglobal_defs {\n lvs_id lvs_host02\n notification_email {\n igorc@encompasscorporation.com\n }\n notification_email_from loadbalancer2\n smtp_server mail.bigpond.com\n smtp_connect_timeout 5\n router_id lb2\n}\nvrrp_instance VI_1 {\n interface eth0\n state BACKUP\n lvs_sync_daemon_interface eth0\n virtual_router_id 50\n priority 101\n advert_int 3\n track_interface {\n eth0\n }\n authentication {\n auth_type PASS\n auth_pass password\n }\n virtual_ipaddress {\n 192.168.100.91 label eth0:1\n }\n notify_master \"\/etc\/keepalived\/iptables.sh 192.168.100.91 master\"\n notify_backup \"\/etc\/keepalived\/iptables.sh 192.168.100.91 backup\"\n}\nvirtual_server 192.168.100.91 3306 {\n delay_loop 6\n nopreempt\n lb_algo rr\n lb_kind DR\n protocol TCP\n real_server 192.168.100.89 3306 {\n weight 10\n MISC_CHECK {\n misc_path \"\/etc\/keepalived\/mysql-check.sh 192.168.100.89\"\n misc_timeout 15\n }\n }\n real_server 192.168.100.90 3306 {\n weight 10\n MISC_CHECK {\n misc_path \"\/etc\/keepalived\/mysql-check.sh 192.168.100.90\"\n misc_timeout 15\n }\n }\n}\n<\/code><\/pre>\n\nmysql> grant select on *.* to 'hcheck'@'localhost' identified by 'password';\nQuery OK, 0 rows affected (0.00 sec)\n \nmysql> grant select on *.* to 'hcheck'@'192.168.100.89' identified by 'password';\nQuery OK, 0 rows affected (0.00 sec)\n \nmysql> grant select on *.* to 'hcheck'@'192.168.100.90' identified by 'password';\nQuery OK, 0 rows affected (0.00 sec)\n \nmysql> flush privileges;\nQuery OK, 0 rows affected (0.00 sec)\n<\/pre>\n
\/etc\/keepalived\/mysql-check.sh<\/code> on both hosts. We don’t want to just check the TCP connection to port 3306 but also check if the server is alive and responding:<\/p>\n#!\/bin\/bash\nmysql --host=$1 --user=hcheck --password=password -Nse \"select 1 from dual\"\n<\/code><\/pre>\n$ sudo iptables -t nat -A PREROUTING -d 192.168.100.91 -p tcp -j REDIRECT\n<\/code><\/pre>\n\/etc\/keepalived\/iptables.sh<\/code>:<\/p>\n#!\/bin\/bash\ncase $2 in\nbackup)\n\/sbin\/iptables -t nat -A PREROUTING -d $1 -p tcp -j REDIRECT\n;;\nmaster)\n\/sbin\/iptables -t nat -D PREROUTING -d $1 -p tcp -j REDIRECT\n;;\nesac\n<\/code><\/pre>\nlb_kind DR<\/code>). We have HA for the MySQL database and the LB its self, in case the MASTER fails the VIP will move to the BACKUP one which will take over the VIP and the MASTER role.<\/p>\n\n# ipvsadm -Ln\nIP Virtual Server version 1.2.1 (size=4096)\nProt LocalAddress:Port Scheduler Flags\n -> RemoteAddress:Port Forward Weight ActiveConn InActConn\nTCP 192.168.100.91:3306 rr\n -> 192.168.100.89:3306 Route 10 1 0 \n -> 192.168.100.90:3306 Route 10 2 0\n<\/pre>\n
Testing<\/h2>\n
MySQL instance failure<\/h3>\n
Sep 23 09:35:44 host02 Keepalived_healthcheckers: Misc check to [192.168.100.89] for [\/etc\/keepalived\/mysql-check.sh 192.168.100.89] failed.\nSep 23 09:35:44 host02 Keepalived_healthcheckers: Removing service [192.168.100.89]:3306 from VS [192.168.100.91]:3306\n<\/code><\/pre>\n\nroot@host01:~# ipvsadm -Ln\nIP Virtual Server version 1.2.1 (size=4096)\nProt LocalAddress:Port Scheduler Flags\n-> RemoteAddress:Port Forward Weight ActiveConn InActConn\nTCP 192.168.100.91:3306 rr\n-> 192.168.100.90:3306 Route 10 1 0\n<\/pre>\n
Sep 23 10:58:40 host02 Keepalived_healthcheckers: Misc check to [192.168.100.89] for [\/etc\/keepalived\/mysql-check.sh 192.168.100.89] success.\nSep 23 10:58:40 host02 Keepalived_healthcheckers: Adding service [192.168.100.89]:3306 to VS [192.168.100.91]:3306\n<\/code><\/pre>\n\nmysql> show tables;\nERROR 2006 (HY000): MySQL server has gone away\nNo connection. Trying to reconnect...\nConnection id: 60\nCurrent database: sessions\n+--------------------+\n| Tables_in_sessions |\n+--------------------+\n| tomcat_sessions |\n+--------------------+\n1 row in set (0.03 sec)\nmysql>\n<\/pre>\n
LVS MASTER failure<\/h3>\n
root@host01:~# service keepalived stop\n<\/code><\/pre>\nSep 23 11:10:01 host02 Keepalived_vrrp: VRRP_Instance(VI_1) Transition to MASTER STATE\nSep 23 11:10:04 host02 Keepalived_vrrp: VRRP_Instance(VI_1) Entering MASTER STATE\nSep 23 11:10:04 host02 Keepalived_vrrp: Opening script file \/etc\/keepalived\/iptables.sh\n<\/code><\/pre>\n\/etc\/keepalived\/iptables.sh<\/code> script:<\/p>\nroot@host02:~# iptables -t nat -nvL\nChain PREROUTING (policy ACCEPT 34 packets, 3046 bytes)\npkts bytes target prot opt in out source destination\n\nChain INPUT (policy ACCEPT 2 packets, 128 bytes)\npkts bytes target prot opt in out source destination\n\nChain OUTPUT (policy ACCEPT 52 packets, 3144 bytes)\npkts bytes target prot opt in out source destination\n\nChain POSTROUTING (policy ACCEPT 52 packets, 3144 bytes)\npkts bytes target prot opt in out source destination\n<\/code><\/pre>\n\nmysql> show tables;\nERROR 2013 (HY000): Lost connection to MySQL server during query\nmysql> show tables;\nERROR 2006 (HY000): MySQL server has gone away\nNo connection. Trying to reconnect...\nConnection id: 153\nCurrent database: sessions\n+--------------------+\n| Tables_in_sessions |\n+--------------------+\n| tomcat_sessions |\n+--------------------+\n1 row in set (0.19 sec)\n<\/pre>\n
root@host01:~# service keepalived start\n[ ok ] Starting keepalived: keepalived.\n<\/code><\/pre>\nroot@host02:~# tail -f \/var\/log\/syslog\n...\nSep 23 11:15:49 host02 kernel: [79474.736124] IPVS: sync thread started: state = BACKUP, mcast_ifn = eth0, syncid = 50\n<\/code><\/pre>\nMaking it work on AWS<\/h2>\n
unicast_src_ip<\/code> and unicast_peer<\/code> options to tell Keepalived to use unicast for communication. For the second one, VIP failover which in case of AWS will be EIP, we need to modify the notify_master script and implement this function via AWS CLI utilities. The technical details of this setup can be found in VIP(EIP) fail over with Keepalived in Amazon VPC across availability zones<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"