MySQL负载均衡

实现MySQL负载均衡

环境说明

类型	IP地址
RS1	192.168.236.129
ld	192.168.236.131
RS2	192.168.236.131

实验步骤
//在RS1上安装MySQL，

//安装mariadb
[root@localhost ~]# yum -y install mariadb mariadb-common mariadb-devel mariadb-server
//启动  并设置开机自动启动
[root@RS1 ~]# systemctl enable --now mariadb
[root@RS1 ~]# systemctl  status mariadb

//在RS2上安装MySQL，然后创建数据库并授权

[root@RS2 ~]# yum -y install mariadb mariadb-common mariadb-devel mariadb-server
[root@RS2 ~]# systemctl enable --now mariadb

在dl安装mysql

[root@ld ~]# yum -y install mariadb mariadb-common mariadb-devel mariadb-server

在ld安装haproxy安装

//安装依赖包
[root@ld ~]# yum -y install make gcc pcre-devel bzip2-devel openssl-devel systemd-devel
//创建账户
[root@ld ~]# useradd -r -M -s /sbin/nologin haproxy
//解压
[root@ld ~]# tar xf haproxy-2.3.0.tar.gz 

[root@ld ~]# cd haproxy-2.3.0
[root@ld haproxy-2.3.0]# make clean //清除临时文件; 清理临时文件

//编译安装
[root@ld haproxy-2.3.0]# make -j $(grep 'processor' /proc/cpuinfo |wc -l)  \
TARGET=linux-glibc  \
USE_OPENSSL=1  \
USE_ZLIB=1  \
USE_PCRE=1  \
USE_SYSTEMD=1
[root@ld ~]# 
//安装
[root@ld haproxy-2.3.0]# make install PREFIX=/usr/local/haproxy
[root@ld haproxy-2.3.0]# cp haproxy /usr/sbin/

配置各个负载的内核参数

[root@ld haproxy-2.3.0]# echo 'net.ipv4.ip_nonlocal_bind = 1' >>  /etc/sysctl.conf
[root@ld haproxy-2.3.0]# echo 'net.ipv4.ip_forward = 1' >> /etc/sysctl.conf
[root@ld haproxy-2.3.0]# sysctl -p
net.ipv4.ip_nonlocal_bind = 1
net.ipv4.ip_forward = 1

提供配置文件

[root@ld haproxy-2.3.0]#  mkdir /etc/haproxy  //创建一个haproxy目录
 [root@ld haproxy-2.3.0]# cat > /etc/haproxy/haproxy.cfg <<EOF
#--------------全局配置----------------
global
    log 127.0.0.1 local0  info
    #log loghost local0 info
    maxconn 20480
#chroot /usr/local/haproxy
    pidfile /var/run/haproxy.pid
    #maxconn 4000
    user haproxy
    group haproxy
    daemon
#---------------------------------------------------------------------
#common defaults that all the 'listen' and 'backend' sections will
#use if not designated in their block
#---------------------------------------------------------------------
defaults
    mode http
    log global
    option dontlognull
    option httpclose
    option httplog
    #option forwardfor
    option redispatch
    balance roundrobin
    timeout connect 10s
    timeout client 10s
    timeout server 10s
    timeout check 10s
    maxconn 60000
    retries 3
#--------------统计页面配置------------------
listen admin_stats
    bind 0.0.0.0:8189
    stats enable
    mode http
    log global
    stats uri /haproxy_stats
    stats realm Haproxy\ Statistics
    stats auth admin:admin
    #stats hide-version
    stats admin if TRUE
    stats refresh 30s
#---------------web设置-----------------------
listen webcluster
    bind 0.0.0.0:80
    mode http
    #option httpchk GET /index.html
    log global
    maxconn 3000
    balance roundrobin
# cookie SESSION_COOKIE insert indirect nocache   
  server web01 192.168.11.148:80 check inter 2000 fall 5
    server web01 192.168.146:80 cookie web01 check inter 2000 fall 5
    
[root@ld ~]# vim /etc/haproxy/haproxy.cfg 
    cookie SESSION_COOKIE insert indirect nocache
    server web01 192.168.11.148:80 check inter 2000 fall 5
    server web01 192.168.146:80 cookie web01 check inter 2000 fall 5

EOF

haproxy.service文件编写

[root@ld haproxy-2.3.0]# vim /usr/lib/systemd/system/haproxy.service 

[Unit]
Description=HAProxy Load Balancer
After=syslog.target network.target

[Service]
ExecStartPre=/usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg   -c -q
ExecStart=/usr/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg  -p /var/run/haproxy.pid

[Install]
WantedBy=multi-user.target

[root@ld ~]# systemctl  daemon-reload //重新加载

启用日志

[root@ld ~]# vim /etc/rsyslog.conf 
local0.  /var/log/boot.log

[root@ld ~]# systemctl restart rsyslog 
[root@ld ~]# systemctl status rsyslog
● rsyslog.service - System Logging Service
   Loaded: loaded (/usr/lib/systemd/system/rsyslog.service; enabled; vendor preset: enabled)
   Active: active (running) since Sat 2020-11-21 14:39:08 CST; 12s ago
     Docs: man:rsyslogd(8)
           https://www.rsyslog.com/doc/
 Main PID: 20748 (rsyslogd)
    Tasks: 3 (limit: 12320)
   Memory: 1.2M
   CGroup: /system.slice/rsyslog.service
           └─20748 /usr/sbin/rsyslogd -n

Nov 21 14:38:52 ld systemd[1]: Stopped System Logging Service.
Nov 21 14:38:52 ld systemd[1]: Starting System Logging Service...
Nov 21 14:39:08 ld rsyslogd[20748]: [origin software="rsyslogd" swVersion="8.1911.0-3.el8" x-pid="20748" x-info="http>
Nov 21 14:39:08 ld systemd[1]: Started System Logging Service.
Nov 21 14:39:08 ld rsyslogd[20748]: imjournal: journal files changed, reloading...  [v8.1911.0-3.el8 try https://www.>
lines 1-16/16 (END)

修改配置文件

//修改haproxy配置文件
[root@ld ~]# cat /etc/haproxy/haproxy.cfg 
#--------------全局配置----------------
global
    log 127.0.0.1 local0  info
    #log loghost local0 info
    maxconn 20480
#chroot /usr/local/haproxy
    pidfile /var/run/haproxy.pid
    #maxconn 4000
    user haproxy
    group haproxy
    daemon
#---------------------------------------------------------------------
#common defaults that all the 'listen' and 'backend' sections will
#use if not designated in their block
#---------------------------------------------------------------------
defaults
    mode tcp
    log global
    option dontlognull
    option httpclose
    option httplog
    #option forwardfor
    option redispatch
    balance roundrobin
    timeout connect 10s
    timeout client 10s
    timeout server 10s
    timeout check 10s
    maxconn 60000
    retries 3
#--------------统计页面配置------------------
listen admin_stats
    bind 0.0.0.0:8189
    stats enable
    mode http
    log global
    stats uri /haproxy_stats
    stats realm Haproxy\ Statistics
    stats auth admin:admin
    #stats hide-version
    stats admin if TRUE
    stats refresh 30s
#---------------web设置-----------------------
listen webcluster
    bind 0.0.0.0:3306
    mode tcp
    #option httpchk GET /index.html
    log global
    maxconn 3000
    balance roundrobin
# cookie SESSION_COOKIE insert indirect nocache   
    server web01 192.168.236.133:3306 check inter 2000 fall 5
   server web02 192.168.236.129:3306 check inter 2000 fall 5

//启动
[root@ld ~]# systemctl  restart haproxy

[root@rs2 ~]# mysql -uroot  //创建数据库并授权
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database rs2;
Query OK, 1 row affected (0.282 sec)

MariaDB [(none)]>  grant all on *.* to 'test'@'192.168.236.%' identified by '123456';
Query OK, 0 rows affected (0.319 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.284 sec)

MariaDB [(none)]> 




[root@rs1 ~]# mysql -uroot  //然后创建数据库并授权
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database RS1;
Query OK, 1 row affected (0.271 sec)

MariaDB [(none)]> grant all on *.* to ‘test’@'192.168.236.%' identified by '123456';
Query OK, 0 rows affected (0.317 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

//在dl上测试

[root@ld ~]# mysql -utest  -p123456 -h192.168.236.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| rs2                |
+--------------------+
[root@ld ~]# mysql -utest  -p123456 -h192.168.236.
+--------------------+
| Database           |
+--------------------+
| RS1                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+