MySQL高可用负载均衡集群

  • A+
所属分类:Linux

使用MySQL时随着时间的增长,用户量以及数据量的逐渐增加,访问量更是剧增,最终将会使MySQL达到某个瓶颈,那么MySQL的性能将会大大降低。那么如何跨过这个瓶颈,提高MySQL的并发量呢?方法有很多,分布式数据库、读写分离、高可用负载均衡、增加缓存服务器等等。其中实现高可用负载均衡的方法有很多,例如LVS+keepalived组合实现、haproxy+keepalived组合实现等等,这里我们采用haproxy+keepalived组合实现MySQL高可用负载均衡这一技术。

主机名 IP 安装服务
Mysql-Master 192.168.2.34 Mysql
Myysql-Slave 192.168.2.35 Mysql
HA-1 192.168.2.36 Haproxy+Keepalived
HA-2 192.168.2.37 Haproxy+Keepalived
VIP 192.168.2.30
一.Mysql
1.安装
2.修改配置文件
Mysql-master
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
max_connections=1000
symbolic-links=0
slow_query_log=on
slow_query_log_file=/var/log/mysql-slow
long_query_time=5

server-id = 1 #backup这台设置2
log-bin = mysql-bin
binlog-ignore-db = mysql,information_schema #忽略写入binlog日志的库
auto-increment-increment = 2 #字段变化增量值
auto-increment-offset = 1 #初始字段ID为1
slave-skip-errors = all #忽略所有复制产生的错误

character-set-server=utf8
collation-server=utf8_bin
default-storage-engine=INNODB
max_allowed_packet=256M
innodb_log_file_size=2GB
sql_mode = NO_AUTO_VALUE_ON_ZERO
transaction-isolation=READ-COMMITTED
binlog_format=row
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Mysql-slave:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
max_connections=1000
symbolic-links=0
slow_query_log=on
slow_query_log_file=/var/log/mysql-slow
long_query_time=5

server-id = 2 #backup这台设置2
log-bin = mysql-bin
binlog-ignore-db = mysql,information_schema #忽略写入binlog日志的库
auto-increment-increment = 2 #字段变化增量值
auto-increment-offset = 1 #初始字段ID为1
slave-skip-errors = all #忽略所有复制产生的错误

character-set-server=utf8
collation-server=utf8_bin
default-storage-engine=INNODB
max_allowed_packet=256M
innodb_log_file_size=2GB
sql_mode = NO_AUTO_VALUE_ON_ZERO
transaction-isolation=READ-COMMITTED
binlog_format=row
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
3.重启服务并添加端口
systemctl restart mysqld
firewall-cmd --zone=public --add-port=3306/tcp --permanent;firewall-cmd --reload
4.创建同步帐号(Master和Slave)
 mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.2.%' IDENTIFIED BY 'sync@123';
mysql> flush privileges; 
也可测试
Mysql-master:
mysql -h 192.168.2.35 -u sync -p
Mysql-slave
mysql -h 192.168.2.34 -u sync -p
5.查看log bin和pos值并配置
Mysql-master:
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000005 | 417 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
Mysql-slave:
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000003 | 417 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
Mysql-master: (log bin pos写slave的)
mysql> change master to master_host='192.168.2.35', master_user='sync',master_password='sync@123',master_log_file='mysql-bin.000003',master_log_pos=417;
mysql> start slave; 
Mysql-slave: (log bin pos写master的)
mysql> change master to master_host='192.168.2.34', master_user='sync',master_password='sync@123',master_log_file='mysql-bin.000005',master_log_pos=417;
mysql> start slave;
6.查看是否搭建成功
mysql> show slave status \G
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
二.Haproxy
Haproxy是一个开源的高性能的反向代理或者说是负载均衡服务软件之一,它支持双机热备、虚拟主机、基于TCP和HTTP应用代理等功能。其配置简单,而且拥有很好的对服务器节点的健康检查功能(相当于keepalived健康检查),当其代理的后端服务器出现故障时,Haproxy会自动的将该故障服务器摘除,当服务器的故障恢复后Haproxy还会自动将RS服务器。
1.安装(HA-1、HA-2配置一样)
yum install -y haproxy
2.修改配置文件
vim /etc/haproxy/haproxy.cfg
global
    log 127.0.0.1 local2          ##日志定义级别
    chroot /var/lib/haproxy       ##工作目录
    pidfile /var/run/haproxy.pid  ##进程id,启动进程的用户必须有权限访问此文件
    maxconn 4000                  ##默认最大连接数,需考虑ulimit-n限制
    user haproxy                  ##运行程序用户
    group haproxy
    daemon                        ##后台运行
##默认配置
defaults
    mode http   ##默认模式mode { tcp|http|health },tcp是4层,http是7层,health只会返回OK
    log global  ##采用全局定义的日志
    option httplog  ##日志类别http日志格式
    option dontlognull  ##不记录健康检查的日志信息
    option redispatch   ##serverId对应的服务器挂掉后,强制定向到其他健康的服务器,以后将不支持
    retries 3        ##三次连接失败则服务器不用
    timeout connect 10s  ##连接超时
    timeout client 1m    ##客户端连接超时
    timeout server 1m    ##服务器端连接超时
    timeout http-keep-alive 10s  ##默认持久连接超时时间
    timeout check 10s    ##心跳检测
    maxconn 3000         ##最大连接数
    balance roundrobin #设置默认负载均衡方式,轮询方式
  #balance source #设置默认负载均衡方式,类似于nginx的ip_hash
  #balnace leastconn #设置默认负载均衡方式,最小连接数
listen stats
    mode http
    bind 0.0.0.0:8090 #监听端口 
    stats enable
    stats refresh 30s #统计页面自动刷新时间 
    stats uri /stats #统计页面url 
    stats realm Haproxy Manager #统计页面密码框上提示文本 
    stats auth admin:admin #统计页面用户名和密码设置 
    #stats hide-version #隐藏统计页面上HAProxy的版本信息
frontend main
    bind 0.0.0.0:3306
    default_backend mysql
backend mysql
    balance leastconn
    server Mysql-Master 192.168.2.34:3306 check port 3306 maxconn 300
    server Mysql-Slave 192.168.2.35:3306 check port 3306 maxconn 300

3.启动日志
vim /etc/rsyslog.conf
# Provides TCP syslog reception 去掉以下注释
$ModLoad imtcp
$InputTCPServerRun 514

# haproxy.log
local2.* /var/log/haproxy.log
vim /etc/sysconfig/rsyslog
SYSLOGD_OPTIONS="-r -m 0"
重启日志服务
systemctl restart rsyslog
4.启动Haproxy

! Configuration File for keepalived
global_defs {
   router_id MYSQL_HA
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens192
    virtual_router_id 51
    priority 100
    advert_int 1
    nopreempt
    authentication {
         auth_type PASS
         auth_pass 1111
     }
     virtual_ipaddress {
     192.168.1.30/24
     }
}
virtual_server 192.168.1.30 3306 {
   delay_loop 6
   lb_algo wrr
   lb_kind DR
   persistence_timeout 50
   protocol TCP
real_server 192.168.1.28 3306 {
   weight 3
   notify_down /etc/keepalived/mysql.sh
   TCP_CHECK {

        connect_timeout 10
        nb_get_retry 3
        delay_before_retry 3
        connect_port 3306

             }

       }
 
}

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: