MySQL高可用基础之keepalived+双主复制

环境：
MySQL-VIP：192.168.1.3
MySQL-master1：192.168.1.1
MySQL-master2：192.168.1.2


OS版本：CentOS release 6.4 (Final) Linux 2.6.32-358.el6.x86_64
MySQL版本：5.6.14
Keepalived版本：1.2.13

先安装依赖包：

yum -y install gcc pcre-devel zlib-devel openssl-devel

一、MySQL master-master配置


1、修改MySQL配置文件/etc/my.cnf   
# Server1配置
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_UNSIGNED_SUBTRACTION 
port = 6603
server_id = 1 
lower_case_table_names = 1
character_set_server = utf8
autocommit = off
innodb_flush_log_at_trx_commit = 1
skip_name_resolve = ON
innodb_strict_mode = ON
log_bin = mysql-bin
innodb_data_home_dir = /usr/local/mysql/data
innodb_log_group_home_dir = /usr/local/mysql/data
auto_increment_increment=2
auto_increment_offset=1


# Server2配置
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_UNSIGNED_SUBTRACTION 
port = 6603
server_id = 2 
lower_case_table_names = 1
character_set_server = utf8
autocommit = off
innodb_flush_log_at_trx_commit = 1
skip_name_resolve = ON
innodb_strict_mode = ON
log_bin = mysql-bin
innodb_data_home_dir = /usr/local/mysql/data
innodb_log_group_home_dir = /usr/local/mysql/data
auto_increment_increment=2
auto_increment_offset=2


2、将192.168.1.1设为192.168.1.2的主服务器
# 在Server1上执行
grant replication slave on *.* to 'repl'@'%' identified by 'repl'; 
show master status; 


# 在Server2上执行
change master to 
master_host='192.168.1.1',
master_port=6603,
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000027',
master_log_pos=120; 
start slave;
show slave status\G


3、将192.168.1.2设为192.168.1.1的主服务器
# 在Server2上执行
grant replication slave on *.* to 'repl'@'%' identified by 'repl'; 
show master status; 


# 在Server1上执行
change master to 
master_host='192.168.1.2',
master_port=6603,
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000010',
master_log_pos=351; 
start slave;
show slave status\G


4、MySQL双Master同步测试




二、keepalived安装及配置


1、192.168.1.1服务器上keepalived安装及配置
安装keepalived
# tar -zxvf keepalived-1.2.13.tar.gz
# cd keepalived-1.2.13
# ./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.32-358.el6.x86_64
# make
# make install


配置keepalived
新建一个配置文件，默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件
# mkdir /etc/keepalived
# vi /etc/keepalived/keepalived.conf

global_defs {
    router_id V_DB
    script_user root
    enable_script_security
}

vrrp_script check_mysql {
    script "/etc/keepalived/failover.sh"
    interval 3
    weight -2
    fall 2
    rise 1
}

vrrp_instance SandBox_DB {
    state BACKUP

    unicast_src_ip 10.120.251.105
    unicast_peer {
        10.120.251.89
    }

    interface eth0
    virtual_router_id 51
    priority 100
    nopreempt

    authentication {
        auth_type PASS
        auth_pass 1111
    }

    virtual_ipaddress {
        10.120.251.53
    }

   track_script {
       check_mysql
   }
}
 

编写检测服务down后所要执行的脚本
# vi /usr/local/mysql/bin/failover.sh

#!/bin/bash

count=1

while true; do
    mysql -h127.0.0.1 -uadmin -p123456  -e "select 1;" > /dev/null 2>&1
    i=$?

    ps aux | grep mysqld | grep -v grep > /dev/null 2>&1
    j=$?

    if [ $i -eq 0 ] && [ $j -eq 0 ]; then
            echo 'OK'
        exit 0
    elif [ $count -lt 5 ]; then
        let count++
        continue
    else
        systemctl stop keepalived
                echo "db fail "
        exit 1
    fi
done

# chmod +x /usr/local/mysql/bin/failover.sh


注：此脚本是上面配置文件notify_down选项所用到的，keepalived使用notify_down选项来检查real_server的服务状态，当发现real_server服务故障时，便触发此脚本；我们可以看到，脚本就一个命令，通过pkill keepalived强制杀死keepalived进程，从而实现了MySQL故障自动转移。另外，我们不用担心两个MySQL会同时提供数据更新操作，因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP，而不是两台MySQL的IP+VIP

启动keepalived
# /usr/local/keepalived/sbin/keepalived -D
# ps aux | grep keepalived 


测试
找一台局域网PC，然后去ping MySQL的VIP，这时候MySQL的VIP是可以ping的通的
停止MySQL服务，看keepalived健康检查程序是否会触发我们编写的脚本


keepalived配置成服务并开机启动
# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
# chkconfig --add keepalived 
# chkconfig --level 345 keepalived on


2、192.168.1.2上keepalived安装及配置
安装keepalived
# tar -zxvf keepalived-1.2.13.tar.gz
# cd keepalived-1.2.13
# ./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.32-358.el6.x86_64
# make
# make install


配置keepalived
 
# mkdir /etc/keepalived
# vi /etc/keepalived/keepalived.conf
 

     

global_defs {
    router_id V_DB
    script_user root
    enable_script_security
}

vrrp_script check_mysql {
    script "/etc/keepalived/failover.sh"
    interval 3
    weight -2
    fall 2
    rise 1
}

vrrp_instance SandBox_DB {
    state BACKUP

    unicast_src_ip 10.120.251.89
    unicast_peer {
        10.120.251.105
    }

    interface eth0
    virtual_router_id 51
    priority 100
    nopreempt

    authentication {
        auth_type PASS
        auth_pass 1111
    }

    virtual_ipaddress {
        10.120.251.53
    }

   track_script {
       check_mysql
   }
}
 

编写检测服务down后所要执行的脚本
# vi /usr/local/mysql/bin/failover.sh

#!/bin/bash

count=1

while true; do
    mysql -h127.0.0.1 -uadmin -p123456  -e "select 1;" > /dev/null 2>&1
    i=$?

    ps aux | grep mysqld | grep -v grep > /dev/null 2>&1
    j=$?

    if [ $i -eq 0 ] && [ $j -eq 0 ]; then
            echo 'OK'
        exit 0
    elif [ $count -lt 5 ]; then
        let count++
        continue
    else
        systemctl stop keepalived
                echo "db fail "
        exit 1
    fi
done

启动keepalived
# /usr/local/keepalived/sbin/keepalived -D
# ps aux | grep keepalived 


测试
停止MySQL服务，看keepalived健康检查程序是否会触发我们编写的脚本


keepalived配置成服务并开机启动
# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
# chkconfig --add keepalived 
# chkconfig --level 345 keepalived on

修改keepalived日志位置

1.在主从keeplived节点上编译/etc/sysconfig/keepalived文件

# vi /etc/sysconfig/keepalived

# Options for keepalived. See `keepalived --help' output and keepalived(8) and

# keepalived.conf(5) man pages for a list of all options. Here are the most

# common ones :

#

# --vrrp -P Only run with VRRP subsystem.

# --check -C Only run with Health-checker subsystem.

# --dont-release-vrrp -V Dont remove VRRP VIPs & VROUTEs on daemon stop.

# --dont-release-ipvs -I Dont remove IPVS topology on daemon stop.

# --dump-conf -d Dump the configuration data.

# --log-detail -D Detailed log messages.

# --log-facility -S 0-7 Set local syslog facility (default=LOG_DAEMON)

#

KEEPALIVED_OPTIONS="-D -d -S 0"

##参数解释都在#注释里面
2. 修改主从节点日志配置文件/etc/rsyslog.conf
#vi /etc/rsyslog.conf
加入如下配置：
#keepalived -S 0
local0.* /var/log/keepalived.log
3.重启日志服务
# /etc/init.d/rsyslog restart
4.检查/var/log/keepalived.log文件是否存在

三、测试
1、MySQL远程登录测试
使用客户端登录VIP测试


2、keepalived故障转移测试
客户端一直去ping VIP，然后关闭192.168.1.1上的keepalived，正常情况下VIP就会切换到192.168.1.2上面去
开启192.168.1.1上的keepalived，关闭192.168.1.2上的keepalived，看是否能自动切换，正常情况下VIP又会属于192.168.1.1
注：keepalived切换速度还是非常块的，整个切换过程只需1-3秒


3、MySQL故障转移测试
在192.168.1.1上关闭MySQL服务，看VIP是否会切换到192.168.1.2上
开启192.168.1.1上的MySQL和keepalived，然后关闭192.168.1.2上的MySQL，看VIP是否会切换到192.168.1.1上
客户端连接的MySQL的VIP，在切换时执行了一个MySQL查询命令




这个方案可以在一定程度上解决MySQL高可用的问题，即应用访问VIP，当一个MySQL Server出现问题，会自动切换到另一个，切换过程很快，对应用透明。但这种简单配置只能有一台服务器工作，另一个备用，这样无法扩展读写，也没法做负载均衡。目前MySQL负载均衡方案一般是一个HA（keepalived、MHA等）+ 一个负载均衡器（LVS、haproxy等）。

修改keepalived日志位置

1.在主从keeplived节点上编译/etc/sysconfig/keepalived文件

# vi /etc/sysconfig/keepalived

# Options for keepalived. See `keepalived --help' output and keepalived(8) and

# keepalived.conf(5) man pages for a list of all options. Here are the most

# common ones :

#

# --vrrp -P Only run with VRRP subsystem.

# --check -C Only run with Health-checker subsystem.

# --dont-release-vrrp -V Dont remove VRRP VIPs & VROUTEs on daemon stop.

# --dont-release-ipvs -I Dont remove IPVS topology on daemon stop.

# --dump-conf -d Dump the configuration data.

# --log-detail -D Detailed log messages.

# --log-facility -S 0-7 Set local syslog facility (default=LOG_DAEMON)

#

KEEPALIVED_OPTIONS="-D -d -S 0"

##参数解释都在#注释里面
2. 修改主从节点日志配置文件/etc/rsyslog.conf
#vi /etc/rsyslog.conf
加入如下配置：
#keepalived -S 0
local0.* /var/log/keepalived.log
3.重启日志服务
# /etc/init.d/rsyslog restart
4.检查/var/log/keepalived.log文件是否存在