/ Register

Discover Centmin Mod today
Register Now

Creating High Availability Mariadb Clustering with Haproxy Load Balancing

Discussion in 'Centmin Mod User Tutorials & Guides' started by brijendrasial, Dec 16, 2019.

Previous Thread Next Thread
Loading...
Page 1 of 2
  1. Dec 16, 2019 #1
    brijendrasial

    brijendrasial Active Member

    205
    153
    43
    Mar 21, 2018
    Ratings:
    +235
    Local Time:
    6:06 PM
    1.13.9
    10.0.22-MariaDB
    First Create 3 Nodes of Centminmod Installation

    Node1:192.168.1.1 (CMM Installed) (MariaDB Cluster 1)
    Node2:192.168.1.2 (CMM Installed) (MariaDB Cluster 2)
    Node3:192.168.1.3 (CMM Installed) (MariaDB Cluster 3)
    Node4:192.168.1.4 (CMM Installed) (Haproxy)

    Open These ports 4567,4568,4444,3306 under TCP in your firewall (CSF or IPTABLES) on All MariaDB Cluster nodes.


    On 192.168.1.1 (CMM Installed) (MariaDB Cluster 1)

    Edit my.cnf file and at the end add lines below

    Must change
    Replace wsrep_node_address="192.168.1.1" with your node1 IP address.
    Replace wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3 with your node1,node2,node3 IP respectively.

    Alternatively you can also
    Replace wsrep_cluster_name="bullten" as you want but do remember it should be same on all nodes.
    Replace wsrep_node_name="node1" to any name you want.

    Code:
    vi /etc/my.cnf
    Stop MariaDB
    Code:
    systemctl stop mariadb
    Start Galera Cluster
    Code:
    galera_new_cluster
    Change the permission
    Code:
    chown -R mysql:mysql /var/lib/mysql

    On 192.168.1.2 (CMM Installed) (MariaDB Cluster 2)

    Edit my.cnf file and at the end add lines below

    Must change
    Replace wsrep_node_address="192.168.1.2" with your node2 IP address.
    Replace wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3 with your node1,node2,node3 IP respectively.

    Alternatively you can also
    Replace wsrep_node_name="node2" to any name you want.

    Code:
    vi /etc/my.cnf
    Change the permission
    Code:
    chown -R mysql:mysql /var/lib/mysql
    Stop MariaDB
    Code:
    systemctl stop mariadb
    Start MariaDB
    Code:
    systemctl start mariadb

    On 192.168.1.3 (CMM Installed) (MariaDB Cluster 3)

    Edit my.cnf file and at the end add lines below

    Must change
    Replace wsrep_node_address="192.168.1.3" with your node3 IP address.
    Replace wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3 with your node1,node2,node3 IP respectively.

    Alternatively you can also
    Replace wsrep_node_name="node3" to any name you want.

    Code:
    vi /etc/my.cnf
    Change the permission
    Code:
    chown -R mysql:mysql /var/lib/mysql
    Stop MariaDB
    Code:
    systemctl stop mariadb
    Start MariaDB
    Code:
    systemctl start mariadb
    Now your Node is connected to check cluster size run the below command on any MariaDB node
    Code:
    SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';
    [​IMG]

    wsrep_cluster_size = 3 shows three nodes are inter connected.


    Node4:192.168.1.4 (CMM Installed) (Haproxy)

    Install Haproxy

    Create Haproxy Service
    Code:
    vi /usr/lib/systemd/system/haproxy.service

    Reload systemd Deamon
    Code:
    systemctl daemon-reload


    Create haproxy.cfg file and replace 192.168.1.1,192.168.1.2,192.168.1.3 with your respected ip's


    Install Percona-clustercheck on all MariaDB node(node1,node2,node3).

    Start Mysqlchk
    Code:
    systemctl enable mysqlchk.socket
systemctl start mysqlchk.socket
    Now there is script error for MYSQL_USERNAME and MYSQL_PASSWORD that needs to be fixed for correct functioning.

    Code:
    vi  /usr/bin/clustercheck
    Find the below lines and change it to look like same below.
    Grant Access to any one node and it will be copied to all

    Now run clustercheck and you will see output as below. Try to run and check on all 3 nodes

    [​IMG]

    Open TCP port 9200 on all MariaDB nodes so that haproxy can interact with it

    Now We allow haproxy IP to interact with MariaDB cluster.

    On Main MariadDB Node run the below command. 192.168.1.4 is haproxy host IP. You can change haproxy_user and haproxy_pass as you want.

    Now lets test if haproxy is working on port 13305 and changing host on request

    on Haproxy node run the below command multiple times to see if value changes everytime

    mysql -u haproxy_user -phaproxy_pass -h 192.168.1.4 -P 13305 -e "SELECT @@hostname"

    [​IMG]

    Haproxy Stats Access
    Code:
    http://192.68.1.4:9000/haproxy_stats
User:Pass: haproxy:haproxy
    Now you can install wordpress on a new vps and for mysql host just use 192.168.1.4:13305(Change ip as per your haproxy node IP) and it will use haproxy as load balancer. You can stop mariadb on any node and check if site is working.

    You can also look to my regular testing stats.
    Code:
    http://51.255.140.203:9000/haproxy_stats
user:haproxy pass:haproxy
     
  2. Dec 16, 2019 #2
    pamamolf

    pamamolf Premium Member Premium Member

    4,068
    427
    83
    May 31, 2014
    Ratings:
    +832
    Local Time:
    3:36 PM
    Nginx-1.25.x
    MariaDB 10.3.x
    Great tutorial !

    The only problem that i had is if i use Cloudflare in front of Haproxy and using tcp mode.

    Haproxy can see only the Cloudflare ip's and if i want to use stick mode to not let user jump from one server to another to avoid session issues then the load balancing is working per Cloudflare ip and it doesn't work well...
     
  3. Dec 16, 2019 #3
    wmtech

    wmtech Active Member

    167
    44
    28
    Jul 22, 2017
    Ratings:
    +125
    Local Time:
    2:36 PM
    Bad HA setup. Your HAProxy node is a single point of failure. Also I would use ProxySQL instead of HAProxy.

    Better: Create a MariaDB cluster and install ProxySQL at each application server needing to connect to MySQL. Setup ProxySQL to use one of the cluster nodes as a dedicated MySQL Writer and use all MySQL cluster nodes as Readers.
     
  4. Dec 17, 2019 #4
    eva2000

    eva2000 Administrator Staff Member

    53,247
    12,117
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,655
    Local Time:
    10:36 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    Very nice @brijendrasial thanks for sharing your tutorial - always nice to see fellow Centmin Mod users share their knowledge :) For folks not familiar with MariaDB Galera Clustering, start by reading https://mariadb.com/kb/en/library/galera-cluster/ and ensure you know how to upgrade Galera clusters as well as backup MySQL data from Galera Clusters and troubleshoot common issues and Galera's limitations.

    @pamamolf You'd ideally don't want your MySQL load balancers like Haproxy, ProxySQL or MaxScale exposed to the public facing internet - internal private lan IP is the way. Though I haven't tried myself there is Cloudflare Spectrum for Cloudflare Enterprise plans https://developers.cloudflare.com/spectrum/getting-started/
    And yeah @wmtech any single web or MySQL load balancer is a single point of failure so they also need a 2nd or 3rd instance configured for automated failover. But the above I guess would be an introduction guide. Before MaxScale and ProxySQL came into existence, Haproxy was how folks load balanced web and MySQL servers. But yes MaxScale and ProxySQL are better used for MySQL load balance/proxying.
     
  5. Dec 17, 2019 #5
    pamamolf

    pamamolf Premium Member Premium Member

    4,068
    427
    83
    May 31, 2014
    Ratings:
    +832
    Local Time:
    3:36 PM
    Nginx-1.25.x
    MariaDB 10.3.x
    @wmtech
    I am talking about web app servers and not Mysql servers.
    Yes for sure ProxySQL and Maxscale are better solutions for the database.

    Then your write node will be a single point of failure....

    @eva2000
    In front of web servers we can have 2 or more HAproxy to avoid a single point of failure but i think that we will need a floating ip for that to work ... or not?
     
  6. Dec 17, 2019 #6
    eva2000

    eva2000 Administrator Staff Member

    53,247
    12,117
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,655
    Local Time:
    10:36 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    yes you would or use Cloudflare Load Balancer with auto failover https://www.cloudflare.com/en-au/load-balancing/ :)

    For web server load balancer proxy such as Haproxy you can configure it to get the real IP of visitor instead of Cloudflare proxy IPs just as you can for Nginx. I'll let you do the research into Haproxy documentation for that - a good exercise to do ;) :)
     
  7. Dec 17, 2019 #7
    wmtech

    wmtech Active Member

    167
    44
    28
    Jul 22, 2017
    Ratings:
    +125
    Local Time:
    2:36 PM
    No, because ProxySQL will take care of that and automatically choose another writer if one goes down. ;-)
     
  8. Dec 17, 2019 #8
    pamamolf

    pamamolf Premium Member Premium Member

    4,068
    427
    83
    May 31, 2014
    Ratings:
    +832
    Local Time:
    3:36 PM
    Nginx-1.25.x
    MariaDB 10.3.x
    Are you sure that you can do that is tcp mode?

    I don't think that it is possible in tcp mode as it just gets the packet and forward it ....

    Good. Hope to see a ProxySQL tutorial here then as the most in the web are a bit complicated.

    That's why i am checking most the MaxScale.

    @brijendrasial

    I like your tutorials. No questions. Easy to understand :) Good job !
     
  9. Dec 17, 2019 #9
    pamamolf

    pamamolf Premium Member Premium Member

    4,068
    427
    83
    May 31, 2014
    Ratings:
    +832
    Local Time:
    3:36 PM
    Nginx-1.25.x
    MariaDB 10.3.x
    @eva2000

    I have two solutions but both require http mode:
    Code:
    acl from_cf    src -f /etc/haproxy/cloudflare_ips.lst
acl cf_ip_hdr  req.hdr(CF-Connecting-IP) -m found

http-request set-header X-Forwarded-For %[req.hdr(CF-Connecting-IP)] if from_cf cf_ip_hdr
    or
    Code:
    acl  is_cf src -f /etc/haproxy/cloudflare_ips.lst

http-request set-header X-Client-IP %[src] if !is_cf
http-request set-header X-Client-IP %[hdr(cf-connecting-ip)] if is_cf
     
  10. Dec 17, 2019 #10
    eva2000

    eva2000 Administrator Staff Member

    53,247
    12,117
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,655
    Local Time:
    10:36 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    you'd only want tcp mode for mysql load balancer for web server load balancing not required to use tcp mode in haproxy

    keep reading :)
     
  11. Dec 17, 2019 #11
    pamamolf

    pamamolf Premium Member Premium Member

    4,068
    427
    83
    May 31, 2014
    Ratings:
    +832
    Local Time:
    3:36 PM
    Nginx-1.25.x
    MariaDB 10.3.x
    For ssl pass through that i want to do as i prefer ssl to be terminated at the web servers i don't think it is possible :)

    I was checking for 2-3 days for a solution for this so i read a lot ha ha ha

    About Cloudflare it will be very expensive for me :(

    I have 800.000.000 requests per month...

    You may want to release the Centminmod HAproxy addon then? :D

    More than 2 years in preview status :whistle:
     
    Last edited: Dec 17, 2019
  12. Dec 17, 2019 #12
    brijendrasial

    brijendrasial Active Member

    205
    153
    43
    Mar 21, 2018
    Ratings:
    +235
    Local Time:
    6:06 PM
    1.13.9
    10.0.22-MariaDB
    proxymysql is indeed better option but I was playing to balancing load for mariadb and nginx which is most probably possible with haproxy at same time and thought to paste the tutorial. Yes this setup is with single point of failure and not much config is needed to create a new haproxy node and add to HA.
     
  13. Dec 17, 2019 #13
    eva2000

    eva2000 Administrator Staff Member

    53,247
    12,117
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,655
    Local Time:
    10:36 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    Haproxy TCP mode with SSL pass through can still support passing on real visitor IP - 2 ways I know of, but I'll let you do the research - always learn better when you do some leg work ;) :) A hint, one of the methods added support from haproxy 1.5+ onwards ;) Backend servers also need to support this method and known backends that support it via haproxy TCP mode are Nginx, Postfix, Exim, Squid, Stunnel, Stud etc

    Your mean haproxy addon at https://community.centminmod.com/threads/addons-haproxy-sh-preview.12641/#post-80220 ? Yes perfection takes time :D Again, I only releases features I am willing to provide some form of support for. Haproxy addon isn't at that stage yet with all other Centmin Mod development working taking priority.

    Yeah Cloudflare load balancer charges on number of origins/regions and health checks and requests. Requests alone are first 500k is free and every 500k requests after is US$0.50 so that 800 million would be 799.5 x $1 = US$799.5/month though as you scale higher it might work out cheaper than forking out for server load balancers when you take into account you need multiple server load balancer as failover.

    So non-Cloudflare load balancer via haproxy you may need 2-5x haproxy load balancer servers to handle your load and provide failover in case one fails. At say US$150-400/month per server, that 2-5x would be between US$300-2,000 per month just for haproxy load balanacers once you reach much higher concurrent user/traffic. But depends on price of each server and what type of network capacity you want them to handle i.e. more expensive for 10-40Gbps network capable servers than 1Gbps standard servers.

    So depending on server hardware/configuration and size of data requests served, for every 1Gbps network capacity you may be able to handle say 1,000 to 10,000 concurrent users for HTTPS traffic. So to handle 100,000 concurrent users would need between 10-100Gbps network capacity based servers - those are expensive and you'd need multiples of them 2-5x to allow haproxy load balancer to failover.

    With Cloudflare load balancer you won't have those costs at massive concurrent user scale loads.

    So what are you currently using to serve that 800 million requests/month ?

    Much appreciated still - always good to see folks share their knowledge :)
     
  14. Dec 17, 2019 #14
    pamamolf

    pamamolf Premium Member Premium Member

    4,068
    427
    83
    May 31, 2014
    Ratings:
    +832
    Local Time:
    3:36 PM
    Nginx-1.25.x
    MariaDB 10.3.x
    I am using an Invision forum ...

    I think that i didn't explain well the issue....

    The problem is not that i can't see the real ip's in Nginx using tcp mode.

    The problem is that HAproxy in tcp mode and ssl pass through is routing the traffic according to Cloudflare ip's as it doesn't process the https connections and don't know the real ip's...

    It just forward the packets ...
     
  15. Dec 19, 2019 #15
    rdan

    rdan Well-Known Member

    5,439
    1,398
    113
    May 25, 2014
    Ratings:
    +2,187
    Local Time:
    8:36 PM
    Mainline
    10.2
    Guys, How many is your peak and average concurrent visitors that you need HA setup?
    Just curious :)
     
  16. Dec 19, 2019 #16
    eva2000

    eva2000 Administrator Staff Member

    53,247
    12,117
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,655
    Local Time:
    10:36 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    You mean you have Haproxy configured for geo IP dependent routing ? As otherwise Haproxy wouldn't care what IP it sees, it just pass-through real visitor IP to backends = nginx
     
  17. Dec 19, 2019 #17
    pamamolf

    pamamolf Premium Member Premium Member

    4,068
    427
    83
    May 31, 2014
    Ratings:
    +832
    Local Time:
    3:36 PM
    Nginx-1.25.x
    MariaDB 10.3.x
    No i didn't set a Geo ip routing.

    Just a HAproxy server in front of 3 web servers for some testing...

    I am using tcp mode to pass through the ssl to web servers. So i am using stick mode to keep the user in the first server that will connect to. It does the same as Cloudflare's affinity mode. So there will be no issues with user sessions...

    The problem is that HAproxy server do the routing to the web servers according to Cloudflare ip's that change every one or two clicks and not based to the real ip of the user.

    That's the tcp mode, get the packet and push it to a web server. But the user that way will jump from one server to another as the routing is based to the Cloudflare ip's...

    HAproxy server can't route the requests in tcp mode according to real ip users. That's the issue.
     
  18. Dec 19, 2019 #18
    eva2000

    eva2000 Administrator Staff Member

    53,247
    12,117
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,655
    Local Time:
    10:36 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    Easy don't create stick tables based on tracking IP and use a different stick table type ;)
     
  19. Dec 19, 2019 #19
    pamamolf

    pamamolf Premium Member Premium Member

    4,068
    427
    83
    May 31, 2014
    Ratings:
    +832
    Local Time:
    3:36 PM
    Nginx-1.25.x
    MariaDB 10.3.x
    Can you please recommend me a stick table type for my scenario case?

    Thank you
     
    Last edited: Dec 19, 2019
  20. Dec 19, 2019 #20
    eva2000

    eva2000 Administrator Staff Member

    53,247
    12,117
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,655
    Local Time:
    10:36 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    Up to you to do your own research but others can chime in if they want. Or just save yourself the hassle and instead of tcp mode, use http mode for web serving and leave tcp mode for MySQL like above example. My custom built Haproxy 2.1.1 binaries for HTTP/2 HTTPS h2load load testing on local virtualbox install shows Haproxy in http mode terminating SSL at Haproxy with 1 thread + Haproxy static file cache setup had between 10-50% better performance than direct Centmin Mod Nginx HTTP/2 HTTPS default config (can be tuned for better performance though) h2load load testing with 2 cpu threads for a simple png image. Of course all depends on environment/configuration tested etc.
     
Previous Thread Next Thread
Loading...
Page 1 of 2

.