MySQL架构之keepalived+haproxy+mysql 实现MHA中slave集群负载均衡的高可用（原创）

MySQL的高可用方案一般有如下几种：
keepalived+双主，MHA，PXC，MMM，Heartbeat+DRBD等，比较常用的是keepalived+双主，MHA和PXC。

HAProxy是一款免费的提供高可用性、负载均衡以及基于TCP（第四层）和HTTP（第七层）应用的代理软件，借助HAProxy可以快速并且可靠的提供基于TCP和HTTP应用的代理解决方案。
Keepalived主要作用是LoadBalance master和LoadBalance backup之间的健康检查，实现故障转换。
Mysql Replication主要作用是提高mysql并处理数据的能力以及实现容灾备份的作用。

架构规划：
keepalived+haproxy+mysql

服务器及其IP地址规划：
240 mysql_b2 192.168.0.240
242 mysql_b1 192.168.0.242
247 haproxy1 192.168.0.247
249 haproxy2 192.168.0.249
243 VIP          192.168.0.243
248 client       192.168.0.248

软件版本：
mysql:8.0.12
keepalived:2.0.8
haproxy:1.8.14
mydumper:0.9.1

软件下载链接：
wget http://fossies.org/linux/misc/haproxy-1.8.14.tar.gz
wget http://www.keepalived.org/software/keepalived-2.0.8.tar.gz

服务器准备：

docker network create --subnet=192.168.0.0/16 staticnet
docker run -d --privileged -v `pwd`/mysql_data:/data -p 3000:3306 --name mysql_b2 --hostname mysql_b2 --net staticnet --ip 192.168.0.240 eiki/mysql:8.0.12 /usr/sbin/init
docker run -d --privileged -v `pwd`/mysql_data:/data -p 4000:3306 --name mysql_b1 --hostname mysql_b1 --net staticnet --ip 192.168.0.242 eiki/mysql:8.0.12 /usr/sbin/init
docker run -d --privileged -v `pwd`/mysql_data:/data -p 5001:8899 --name haproxy1 --hostname haproxy1 --net staticnet --ip 192.168.0.247 eiki/centos:7.5 /usr/sbin/init
docker run -d --privileged -v `pwd`/mysql_data:/data -p 5002:8899 --name haproxy2 --hostname haproxy2 --net staticnet --ip 192.168.0.249 eiki/centos:7.5 /usr/sbin/init

Mysql主从复制配置：

过程略，参考请见https://www.cnblogs.com/EikiXu/p/9811764.html
MyDumper的安装，参考https://www.cnblogs.com/EikiXu/p/9816058.html

1.在master服务器上创建mysql用户（授权复制账户）：
192.168.0.242：

create user 'replica'@'%' IDENTIFIED BY 'rooT_000';
grant REPLICATION SLAVE ON *.* TO 'replica'@'%';

2.编辑master服务器的mysql配置文件my.cnf：

[mysql]
prompt = [\\u@\\h][\\d]>\\_
port = 3306
socket = /usr/local/mysql/data/mysql.sock

[mysqld]
server-id = 1 ###指定服务器的ID
port = 3306
mysqlx_port = 33060
mysqlx_socket = /usr/local/mysql/data/mysqlx.sock
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/data/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
log-error = error.log
slow-query-log = 1
slow-query-log-file = slow.log
long_query_time = 0.2
log-bin = mysql-bin ###开启二进制日志
relay-log = relay.log
binlog_format =ROW
relay_log_recovery = 1
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect ='SET NAMES utf8mb4'
innodb_buffer_pool_size = 1G
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
log_timestamps = SYSTEM
lower_case_table_names = 1
default-authentication-plugin =mysql_native_password
max_allowed_packet = 500M ###控制其通信缓冲区的最大长度


plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = 0
rpl_semi_sync_master_timeout = 1000


auto_increment_increment=2
auto_increment_offset=1

binlog-ignore = mysql //忽略mysql和information_schema 数据库
binlog-ignore = information_schema
binlog-do-db = blog //同步数据库，默认同步所有数据库

replicate-do-db=test

3.查看master状态；

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1642 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.04 sec)

4.在slave端创建数据库blog,导出master端的blog库，导入到此库，并修改mysql主配置文件my.cnf server-id= 10 重启mysql数据库
192.168.0.240：
mydumper -h 192.168.0.242 --regex="test.*" -u root -p 'abc123' -r 300000 -G -E -R -v 3 -t 5 -o /data/backup20181031 > /data/mydumper20181031.log 2>&1 &
myloader -S /usr/local/mysql/data/mysql.sock -u root -p 'abc123' -v 3 -t 10 -e y -d /data/backup20181031 > /data/myloader20181031.log 2>&1 &

CHANGE MASTER TO
MASTER_HOST='192.168.0.242',
MASTER_USER='replica',
MASTER_PASSWORD='rooT_000',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1642;

5.mysql主从同步测试，show slave status\G;能看到Slave_IO_Running和Slave_SQL_Running都为YES即可。

[root@localhost][(none)]> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.242
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 441
Relay_Log_File: relay.000007
Relay_Log_Pos: 655
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test,test
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
Last_Errno: 0
Last_Error: 
Skip_Counter: 0
Exec_Master_Log_Pos: 441
Relay_Log_Space: 1020
Until_Condition: None
Until_Log_File: 
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File: 
Master_SSL_CA_Path: 
Master_SSL_Cert: 
Master_SSL_Cipher: 
Master_SSL_Key: 
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 
Replicate_Ignore_Server_Ids: 
Master_Server_Id: 1
Master_UUID: 3b27e7d5-d5d9-11e8-8004-0242ac110002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind: 
Last_IO_Error_Timestamp: 
Last_SQL_Error_Timestamp: 
Master_SSL_Crl: 
Master_SSL_Crlpath: 
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0
Replicate_Rewrite_DB: 
Channel_Name: 
Master_TLS_Version: 
Master_public_key_path: 
Get_master_public_key: 0
1 row in set (0.01 sec)

Haproxy安装及其配置，master和backup安装完全一样的，配置部分差异：

参考来源：https://blog.csdn.net/qq_36663951/article/details/80492092

创建haproxy程序用户以及配置文件目录

useradd -M -s /sbin/nologin haproxy 

下载安装包、授权并解压

wget https://www.haproxy.org/download/1.8/src/haproxy-1.8.14.tar.gz
tar-zxvf haproxy-1.8.14.tar.gz
cd haproxy-1.8.14
make TARGET=linux2628 USE_PCRE=1 USE_OPENSSL=1 USE_ZLIB=1 PREFIX=/usr/local/haproxy
make install PREFIX=/usr/local/haproxy
cd /usr/local/haproxy
mkdir conf logs //在此目录下面建立conf，logs目录分别存放HAproxy的配置文件，PID文件和日志文件。

创建用户haproxy
grant all privileges on *.* to 'haproxy'@'%' identified by 'haproxy';
flush privileges;

vim /usr/local/haproxy/conf/haproxy.cfg

global
log /dev/log local0 info
log /dev/log local1 notice
user haproxy
group haproxy
defaults
log global
retries 2
timeout connect 3000
timeout server 5000
timeout client 5000
listen mysql-cluster
bind 0.0.0.0:3306
mode tcp
#option mysql-check user haproxy
option tcp-check
balance roundrobin
server mysql1 192.168.0.242:3306 check weight 2
server mysql2 192.168.0.240:3306 check weight 2
listen mysql-clusterstats
bind 0.0.0.0:8899
mode http
stats enable
stats uri /
stats realm Strictly\ Private
stats auth status:mypass
stats auth admin:mysqladmin #状态查看页面登陆帐号密码

vim /etc/rsyslog.d/haproxy.conf

if ($programname == 'haproxy' and $syslogseverity-text == 'info') then -/var/log/haproxy181/haproxy181-info.log
& ~
if ($programname == 'haproxy' and $syslogseverity-text == 'notice') then -/var/log/haproxy181/haproxy181-notice.log
& ~

/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.cfg -c ###验证参数文件配置有效性

/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.cfg ###启动haproxy

Haproxy开启系统日志支持。

vim /etc/syslog.conf

#添加：
local3.* /var/log/haproxy.log  #注意：local数字 该值与haproxy.conf中的配置保持一致
local0.* /var/log/haproxy.log  #注意：local数字 该值与haproxy.conf中的配置保持一致
vim /etc/sysconfig/syslog
#修改：
SYSLOGD_OPTIONS="-r -m 0" #注释：-c 2 使用兼容模式，默认是 -c 5,-r开启远程日志,-m 0标记时间戳。单位是分钟，为0时，表示禁用该功能

#重新启动syslog服务

/etc/init.d/syslogrestart

[root@vm05 haproxy-1.8.9]# ss -antulp | grep haproxy
配置haproxy开机自启动
# chmod +x /etc/rc.d/rc.local
echo '/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.cfg' >> /etc/rc.d/rc.local

为了方便系统在开机时加载，还可以创建启动脚本：
vim /etc/rc.d/init.d/haproxy

#!/bin/sh
#
# haproxy
#
# chkconfig:   - 85 15
# description:  HAProxy is a free, very fast and reliable solution \
#               offering high availability, load balancing, and \
#               proxying for TCP and  HTTP-based applications
# processname: haproxy
# config:      /etc/haproxy/haproxy.cfg
# pidfile:     /var/run/haproxy.pid
 
# Source function library.
. /etc/rc.d/init.d/functions
 
# Source networking configuration.
. /etc/sysconfig/network
 
# Check that networking is up.
[ "$NETWORKING" = "no" ] && exit 0
 
exec="/usr/sbin/haproxy"
prog=$(basename $exec)
 
[ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog
 
cfgfile=/etc/haproxy/haproxy.cfg
pidfile=/var/run/haproxy.pid
lockfile=/var/lock/subsys/haproxy
 
check() {
    $exec -c -V -f $cfgfile $OPTIONS
}
 
start() {
    $exec -c -q -f $cfgfile $OPTIONS
    if [ $? -ne 0 ]; then
        echo "Errors in configuration file, check with $prog check."
        return 1
    fi
 
    echo -n $"Starting $prog: "
    # start it up here, usually something like "daemon $exec"
    daemon $exec -D -f $cfgfile -p $pidfile $OPTIONS
    retval=$?
    echo
    [ $retval -eq 0 ] && touch $lockfile
    return $retval
}
 
stop() {
    echo -n $"Stopping $prog: "
    # stop it here, often "killproc $prog"
    killproc $prog
    retval=$?
    echo
    [ $retval -eq 0 ] && rm -f $lockfile
    return $retval
}
 
restart() {
    $exec -c -q -f $cfgfile $OPTIONS
    if [ $? -ne 0 ]; then
        echo "Errors in configuration file, check with $prog check."
        return 1
    fi
    stop
    start
}
 
reload() {
    $exec -c -q -f $cfgfile $OPTIONS
    if [ $? -ne 0 ]; then
        echo "Errors in configuration file, check with $prog check."
        return 1
    fi
    echo -n $"Reloading $prog: "
    $exec -D -f $cfgfile -p $pidfile $OPTIONS -sf $(cat $pidfile)
    retval=$?
    echo
    return $retval
}
 
force_reload() {
    restart
}
 
fdr_status() {
    status $prog
}
 
case "$1" in
    start|stop|restart|reload)
        $1
        ;;
    force-reload)
        force_reload
        ;;
    check)
        check
        ;;
    status)
        fdr_status
        ;;
    condrestart|try-restart)
        [ ! -f $lockfile ] || restart
        ;;
    *)
        echo $"Usage: $0 {start|stop|status|restart|try-restart|reload|force-reload}"
        exit 2
esac

[root@vm05 src]# chmod +x /etc/rc.d/init.d/haproxy

[root@vm05 src]# chkconfig --add haproxy

登录地址：
http://192.168.0.247:8899
查看全部服务器是不是都是UP
-------测试是否是负载平衡
for i in $(seq 1 10); do mysql -uroot -pabc123 -h192.168.0.243 -e 'select @@server_id;'; done | egrep '[0-9]'

停止服务：
# killall haproxy

keepalived 安装：

安装依赖包：
yum -y install kernel kernel-devel* popt popt-devel libssl-dev libnl libnl-devel openssl openssl-* ipvsadm libnfnetlink-devel

详细安装步骤：
tar xzvf keepalived-2.0.8.tar.gz -C /usr/local/src
cd /usr/local/src/keepalived-2.0.8/
./configure --prefix=/usr/local/keepalived --sysconf=/etc
make && make install
cp /usr/local/src/keepalived-2.0.8/keepalived/etc/init.d/keepalived /etc/init.d/
cp /usr/local/src/keepalived-2.0.8/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/src/keepalived-2.0.8/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

chkconfig --add keepalived
chkconfig --level 2345 keepalived on
vi /etc/keepalived/keepalived.conf

route add 192.168.0.0 mask 255.255.255.0 10.200.22.128

keepalived主：

global_defs {
notification_email {
root@localhost
}
notification_email_from root@localhost
smtp_server root
smtp_connect_timeout 30
router_id NodeA
}
vrrp_script check_haproxy {
script "/etc/keepalived/scripts/check_haproxy.sh"
interval 3 #脚本执行间隔
weight 2 #执行脚本后优先级变更：5表示优先级+5；-5则表示优先级-5
}

vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.0.243
}
track_script {
check_haproxy
}
notify_master /etc/keepalived/scripts/state_master.sh
notify_backup /etc/keepalived/scripts/state_backup.sh
notify_fault /etc/keepalived/scripts/state_fault.sh
}

　　

keepalived从：

global_defs {
notification_email {
root@localhost
}
notification_email_from root@localhost
smtp_server root
smtp_connect_timeout 30
router_id NodeA
}
vrrp_script check_haproxy {
script "/etc/keepalived/scripts/check_haproxy.sh"
interval 3 #脚本执行间隔
weight 2 #执行脚本后优先级变更：5表示优先级+5；-5则表示优先级-5
}

vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.0.243
}
track_script {
check_haproxy
}
notify_master /etc/keepalived/scripts/state_master.sh
notify_backup /etc/keepalived/scripts/state_backup.sh
notify_fault /etc/keepalived/scripts/state_fault.sh
}

　　

vi /etc/keepalived/scripts/check_haproxy.sh

#!/bin/bash
#nginx="/usr/local/haproxy/sbin/haproxy"

PID=`ps -C haproxy --no-heading|wc -l`
if [ "${PID}" = "0" ];
then
/etc/init.d/haproxy start
sleep 1
LOCK=`ps -C haproxy --no-heading|wc -l`
if [ "${LOCK}" = "0" ];
then
/etc/init.d/keepalived stop
fi
fi

 

vi /etc/keepalived/scripts/state_master.sh

#!/bin/bash
echo -e >> $LOGFILE
host=haproxy01 #设置当前的主机名
LOGFILE="/var/log/keepalived-state.log"
echo "[Master]" >> $LOGFILE
date >> $LOGFILE
echo "The ${host} Starting to become master server...." >> $LOGFILE 2>&1

echo "Please run the “ipvsadm -Ln” check the keepalived state ..." >> $LOGFILE
echo ".........................................................................!">> $LOGFILE
echo >>$LOGFILE

vi /etc/keepalived/scripts/state_backup.sh

#!/bin/bash
echo -e >> $LOGFILE
host=haproxy02 #设置当前的主机名
LOGFILE="/var/log/keepalived-state.log"
echo "[Backup]" >> $LOGFILE
date >> $LOGFILE
echo "The ${host} Starting to become Backup server...." >> $LOGFILE 2>&1

echo "Please run the “ipvsadm -Ln” check the state ..." >> $LOGFILE
echo "........................................................................!">> $LOGFILE
echo >> $LOGFILE

vi /etc/keepalived/scripts/state_fault.sh

#!/bin/bash
echo -e >> $LOGFILE
host=haproxy01 #设置当前的主机名
LOGFILE="/var/log/keepalived-state.log"
echo "[fault errot ]" >> $LOGFILE
date >> $LOGFILE
echo "The ${host} is fault error...." >> $LOGFILE 2>&1
echo "Please check the server state ..." >> $LOGFILE
echo "........................................................................!">> $LOGFILE
echo >> $LOGFILE

 参考来源：

https://blog.csdn.net/bbwangj/article/details/82969078

https://blog.csdn.net/wzb56_earl/article/details/49048787?utm_source=blogxgwz42

https://blog.csdn.net/weixin_41004350/article/details/78493035?utm_source=blogxgwz30

https://blog.csdn.net/qq_36276335/article/details/69942101?utm_source=blogxgwz20

https://blog.csdn.net/bbwangj/article/details/80346179?utm_source=blogxgwz1