-
-
Save sassyn/fb7df85d65d99169b3bccc4e3e6cc52b to your computer and use it in GitHub Desktop.
HAProxy configuration for MySQL failover and redundancy
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # HAProxy configuration - haproxy-db.cfg | |
| ## | |
| ## FRONTEND ## | |
| ## | |
| # Load-balanced IPs for DB writes and reads | |
| # | |
| frontend db_write | |
| bind 172.16.0.50:3306 | |
| default_backend cluster_db_write | |
| frontend db_read | |
| bind 172.16.0.51:3306 | |
| default_backend cluster_db_read | |
| # Monitor DB server availability | |
| # | |
| frontend monitor_db01 | |
| # | |
| # set db01_backup to 'up' or 'down' | |
| # | |
| bind 127.0.0.1:9301 | |
| mode http | |
| #option nolinger | |
| acl no_repl_db01 nbsrv(db01_replication) eq 0 | |
| acl no_repl_db02 nbsrv(db02_replication) eq 0 | |
| acl no_db01 nbsrv(db01_status) eq 0 | |
| acl no_db02 nbsrv(db02_status) eq 0 | |
| monitor-uri /dbs | |
| monitor fail unless no_repl_db01 no_repl_db02 no_db02 | |
| monitor fail if no_db01 no_db02 | |
| frontend monitor_db02 | |
| # | |
| # set db02_backup to 'up' or 'down' | |
| # | |
| bind 127.0.0.1:9302 | |
| mode http | |
| #option nolinger | |
| acl no_repl_db01 nbsrv(db01_replication) eq 0 | |
| acl no_repl_db02 nbsrv(db02_replication) eq 0 | |
| acl no_db01 nbsrv(db01_status) eq 0 | |
| acl no_db02 nbsrv(db02_status) eq 0 | |
| monitor-uri /dbs | |
| monitor fail unless no_repl_db01 no_repl_db02 no_db01 | |
| monitor fail if no_db01 no_db02 | |
| frontend monitor_db03 | |
| # | |
| # set db03 read-only slave to 'down' | |
| # | |
| bind 127.0.0.1:9303 | |
| mode http | |
| #option nolinger | |
| acl no_repl_db03 nbsrv(db03_replication) eq 0 | |
| acl no_repl_db01 nbsrv(db01_replication) eq 0 | |
| acl db02 nbsrv(db02_status) eq 1 | |
| monitor-uri /dbs | |
| monitor fail if no_repl_db03 | |
| monitor fail if no_repl_db01 db02 | |
| frontend monitor_db04 | |
| # | |
| # set db04 read-only slave to 'down' | |
| # | |
| bind 127.0.0.1:9304 | |
| mode http | |
| #option nolinger | |
| acl no_repl_db04 nbsrv(db04_replication) eq 0 | |
| acl no_repl_db01 nbsrv(db01_replication) eq 0 | |
| acl db02 nbsrv(db02_status) eq 1 | |
| monitor-uri /dbs | |
| monitor fail if no_repl_db04 | |
| monitor fail if no_repl_db01 db02 | |
| frontend monitor_db05 | |
| # | |
| # set db05 read-only slave to 'down' | |
| # | |
| bind 127.0.0.1:9305 | |
| mode http | |
| #option nolinger | |
| acl no_repl_db05 nbsrv(db05_replication) eq 0 | |
| acl no_repl_db02 nbsrv(db02_replication) eq 0 | |
| acl db01 nbsrv(db01_status) eq 1 | |
| monitor-uri /dbs | |
| monitor fail if no_repl_db05 | |
| monitor fail if no_repl_db02 db01 | |
| # Monitor for split-brain syndrome | |
| # | |
| frontend monitor_splitbrain | |
| # | |
| # set db01_splitbrain and db02_splitbrain to 'up' | |
| # | |
| bind 127.0.0.1:9300 | |
| mode http | |
| #option nolinger | |
| acl no_repl01 nbsrv(db01_replication) eq 0 | |
| acl no_repl02 nbsrv(db02_replication) eq 0 | |
| acl db01 nbsrv(db01_status) eq 1 | |
| acl db02 nbsrv(db02_status) eq 1 | |
| monitor-uri /dbs | |
| monitor fail unless no_repl01 no_repl02 db01 db02 | |
| ## | |
| ## BACKEND ## | |
| ## | |
| # Check every DB server replication status | |
| # - perform an http check on port 9201 (replication status) | |
| # - set to 'down' if response is '503 Service Unavailable' | |
| # - set to 'up' if response is '200 OK' | |
| # | |
| backend db01_replication | |
| mode tcp | |
| balance roundrobin | |
| option tcpka | |
| option httpchk | |
| server db01 172.16.0.60:3306 check port 9201 inter 1s rise 1 fall 1 | |
| backend db02_replication | |
| mode tcp | |
| balance roundrobin | |
| option tcpka | |
| option httpchk | |
| server db02 172.16.0.61:3306 check port 9201 inter 1s rise 1 fall 1 | |
| backend db03_replication | |
| mode tcp | |
| balance roundrobin | |
| option tcpka | |
| option httpchk | |
| server db03 172.16.0.63:3306 check port 9201 inter 1s rise 1 fall 1 | |
| backend db04_replication | |
| mode tcp | |
| balance roundrobin | |
| option tcpka | |
| option httpchk | |
| server db04 172.16.0.64:3306 check port 9201 inter 1s rise 1 fall 1 | |
| backend db05_replication | |
| mode tcp | |
| balance roundrobin | |
| option tcpka | |
| option httpchk | |
| server db05 172.16.0.65:3306 check port 9201 inter 1s rise 1 fall 1 | |
| # Check Master DB server mysql status | |
| # - perform an http check on port 9200 (mysql status) | |
| # - set to 'down' if response is '503 Service Unavailable' | |
| # - set to 'up' if response is '200 OK' | |
| # | |
| backend db01_status | |
| mode tcp | |
| balance roundrobin | |
| option tcpka | |
| option httpchk | |
| server db01 172.16.0.60:3306 check port 9200 inter 1s rise 2 fall 2 | |
| backend db02_status | |
| mode tcp | |
| balance roundrobin | |
| option tcpka | |
| option httpchk | |
| server db02 172.16.0.61:3306 check port 9200 inter 1s rise 2 fall 2 | |
| # DB write cluster | |
| # Failure scenarios: | |
| # - replication 'up' on db01 & db02 = writes to db01 | |
| # - replication 'down' on db02 = writes to db01 | |
| # - replication 'down' on db01 = writes to db02 | |
| # - replication 'down' on db01 & db02 = go nowhere, split-brain, cluster FAIL! | |
| # - mysql 'down' on db02 = writes to db01_backup | |
| # - mysql 'down' on db01 = writes to db02_backup | |
| # - mysql 'down' on db01 & db02 = go nowhere, cluster FAIL! | |
| # | |
| backend cluster_db_write | |
| # | |
| # - max 1 db server available at all times | |
| # - db01 is preferred (top of list) | |
| # - db_backups set their 'up' or 'down' based on results from monitor_dbs | |
| # | |
| mode tcp | |
| option tcpka | |
| balance roundrobin | |
| option httpchk GET /dbs | |
| server db01 172.16.0.60:3306 weight 1 check port 9201 inter 1s rise 2 fall 1 | |
| server db02 172.16.0.61:3306 weight 1 check port 9201 inter 1s rise 2 fall 1 backup | |
| server db01_backup 172.16.0.60:3306 weight 1 check port 9301 inter 1s rise 2 fall 2 addr 127.0.0.1 backup | |
| server db02_backup 172.16.0.61:3306 weight 1 check port 9302 inter 1s rise 2 fall 2 addr 127.0.0.1 backup | |
| # DB read cluster | |
| # Failure scenarios | |
| # - replication 'up' on db01 & db02 = reads on db01, db02, all db_slaves | |
| # - replication 'down' on db02 = reads on db01, slaves of db01 | |
| # - replication 'down' on db01 = reads on db02, slaves of db02 | |
| # - replication 'down' on db01 & db02 = reads on db01_splitbrain and db01_splitbrain only | |
| # - mysql 'down' on db02 = reads on db01_backup, slaves of db01 | |
| # - mysql 'down' on db01 = reads on db02_backup, slaves of db02 | |
| # - mysql 'down' on db01 & db02 = go nowhere, cluster FAIL! | |
| # | |
| backend cluster_db_read | |
| # | |
| # - max 2 master db servers available at all times | |
| # - max N slave db servers available at all times except during split-brain | |
| # - dbs track 'up' and 'down' of dbs in the cluster_db_write | |
| # - db_backups track 'up' and 'down' of db_backups in the cluster_db_write | |
| # - db_splitbrains set their 'up' or 'down' based on results from monitor_splitbrain | |
| # | |
| mode tcp | |
| option tcpka | |
| balance roundrobin | |
| option httpchk GET /dbs | |
| server db01 172.16.0.60:3306 weight 1 track cluster_db_write/db01 | |
| server db02 172.16.0.61:3306 weight 1 track cluster_db_write/db02 | |
| server db01_backup 172.16.0.60:3306 weight 1 track cluster_db_write/db01_backup | |
| server db02_backup 172.16.0.61:3306 weight 1 track cluster_db_write/db02_backup | |
| server db01_splitbrain 172.16.0.60:3306 weight 1 check port 9300 inter 1s rise 1 fall 2 addr 127.0.0.1 | |
| server db02_splitbrain 172.16.0.61:3306 weight 1 check port 9300 inter 1s rise 1 fall 2 addr 127.0.0.1 | |
| # | |
| # Scaling & redundancy options | |
| # - db_slaves set their 'up' or 'down' based on results from monitor_dbs | |
| # - db_slaves should take longer to rise | |
| # | |
| server db03_slave 172.16.0.63:3306 weight 1 check port 9303 inter 1s rise 5 fall 1 addr 127.0.0.1 | |
| server db04_slave 172.16.0.64:3306 weight 1 check port 9304 inter 1s rise 5 fall 1 addr 127.0.0.1 | |
| server db05_slave 172.16.0.65:3306 weight 1 check port 9305 inter 1s rise 5 fall 1 addr 127.0.0.1 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment