search

v5tech / notes

notes
https://ameizi.gitee.io/notes
MIT License
1.52k stars 378 forks source link

使用HAProxy搭建高可用MariaDB Galera Cluster #146

Open v5tech opened 8 years ago

v5tech commented 8 years ago

安装haproxy

yum install haproxy

配置/etc/haproxy/haproxy.cfg

global
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon
    stats socket /var/lib/haproxy/stats

defaults
    mode                    tcp
    log                     global
    option                  httplog
    option                  dontlognull
    option http-server-close
    option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000

listen stats 0.0.0.0:80
    mode http
    stats enable
    stats uri /stats  
    stats realm HAProxy\ Statistics 
    stats auth haproxy:haproxy
    stats admin if TRUE

listen mariadb_cluster_write 0.0.0.0:3307
    mode tcp
    server c1 192.168.64.145:3306 check port 3306
    server c2 192.168.64.146:3306 check port 3306 backup
    server c3 192.168.64.147:3306 check port 3306 backup

listen mariadb_cluster_read 0.0.0.0:3308
    mode tcp
    balance leastconn 
    server c1 192.168.64.145:3306 check port 3306
    server c2 192.168.64.146:3306 check port 3306
    server c3 192.168.64.147:3306 check port 3306

注:

  1. listen stats 0.0.0.0:80 为haproxy的stats，用户名密码haproxy:haproxy
  2. listen mariadb_cluster_write 0.0.0.0:3307，使用mysql -uroot -proot -h127.0.0.1 -P3307每次连接时都只会访问到c1即192.168.64.145，当c1宕机后会切换到c2，当c2宕机后会切换到c3
  3. listen mariadb_cluster_read 0.0.0.0:3308，使用mysql -uroot -proot -h127.0.0.1 -P3308负载轮询

    启动haproxy

service haproxy start

访问http://192.168.64.145/stats

实验测试

mysql -uroot -proot -h127.0.0.1 -P3307
MariaDB [(none)]> SELECT @@wsrep_node_name;
+-------------------+
| @@wsrep_node_name |
+-------------------+
| c1                |
+-------------------+
1 row in set (0.00 sec)

c1宕机后再次查询切换到c2

mysql -uroot -proot -h127.0.0.1 -P3307
MariaDB [(none)]> SELECT @@wsrep_node_name;
+-------------------+
| @@wsrep_node_name |
+-------------------+
| c2                |
+-------------------+
1 row in set (0.00 sec)

c2宕机后再次查询切换到c3

mysql -uroot -proot -h127.0.0.1 -P3307
MariaDB [(none)]> SELECT @@wsrep_node_name;
+-------------------+
| @@wsrep_node_name |
+-------------------+
| c3                |
+-------------------+
1 row in set (0.00 sec)

c1再次上线后切换到c1

mysql -uroot -proot -h127.0.0.1 -P3307
MariaDB [(none)]> SELECT @@wsrep_node_name;
+-------------------+
| @@wsrep_node_name |
+-------------------+
| c1                |
+-------------------+
1 row in set (0.00 sec)

mysql -uroot -proot -h127.0.0.1 -P3308
MariaDB [(none)]> SELECT @@wsrep_node_name;
+-------------------+
| @@wsrep_node_name |
+-------------------+
| c3                |
+-------------------+
1 row in set (0.00 sec)

mysql -uroot -proot -h127.0.0.1 -P3308
MariaDB [(none)]> SELECT @@wsrep_node_name;
+-------------------+
| @@wsrep_node_name |
+-------------------+
| c2                |
+-------------------+
1 row in set (0.00 sec)

mysql -uroot -proot -h127.0.0.1 -P3308
MariaDB [(none)]> SELECT @@wsrep_node_name;
+-------------------+
| @@wsrep_node_name |
+-------------------+
| c1                |
+-------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

在三台服务器上进行轮询，达到负载效果。

参考文章

http://www.fromdual.com/making-haproxy-high-available-for-mysql-galera-cluster https://mariadb.com/blog/setup-mariadb-enterprise-cluster-part-3-setup-ha-proxy-load-balancer-read-and-write-pools http://galeracluster.com/documentation-webpages/haproxy.html