MariaDB高可用+负载均衡

MariaDB高可用+负载均衡

配置映射

172.129.33.51 node1
172.129.33.52 node2
172.129.33.53 node3

 

配置YUM源

# cat /etc/yum.repos.d/local.repo 
[mairadb]
name=mariadb
baseurl=file:///opt/mariadb-repo
gpgcheck=0

 

安装服务

# yum install MariaDB-server MariaDB-client galera -y
# systemctl start mariadb

 

修改配置文件并启动

# mysqladmin  -uroot password 000000
# vim /etc/my.cnf.d/server.cnf 
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://node1,node2,node3
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

node2-3：
多取消一个注释：
bind-address=0.0.0.0


# mysql -uroot -p000000
> grant all privileges on *.* to root@'%' identified by'000000'; 
# systemctl stop mariadb

 

开启高可用

node1:
# galera_new_cluster
node2-3:
# systemctl start mariadb

# netstat -ntpl
tcp6       0      0 192.168.200.11:3306                 :::*                    LISTEN     
tcp        0      0 0.0.0.0:4567            0.0.0.0:*               LISTEN      11910/mysqld   

 

配置负载均衡

# yum install -y haproxy
node1：
# cat /etc/haproxy/haproxy.cfg 
global
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon
    stats socket /var/lib/haproxy/stats

defaults
    log                     global
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout check           10s
    maxconn                 4000

listen stats
    bind        172.129.33.51:9000
    mode        http
    stats       enable
    stats       uri     /stats 
    stats       auth    admin:admin
    stats       admin   if TRUE

listen mariadb
    balance     roundrobin
    mode        tcp
    option      tcplog
    option      tcpka
    bind        172.129.33.51:3307
    server node1 172.129.33.51:3306 check weight 1
    server node2 172.129.33.52:3306 check weight 1
    server node3 172.129.33.53:3306 check weight 1

# systemctl start haproxy
# netstat -ntpl
tcp        0      0 172.129.33.51:9000      0.0.0.0:*               LISTEN      1745/haproxy    

可以看见192.168.200.11:3307和192.168.200.11:9000端口启动，可以通过web界面查看HAProxy服务的状态。
使用网页访问192.168.200.11:9000/stats，页面如下所示：（登录用户名/密码为admin/admin）

设置三个节点数据库不同的server_id
# mysql -uroot -p000000
> SET GLOBAL server_id=51;
> SET GLOBAL server_id=52;
> SET GLOBAL server_id=53;