12.keepalived+mysql 双主架构

0、本次集群架构如下（先搭建好主从同步）：

集群架构图(如上）

1、分别在主、备库上创建同步复制账号：
create user 'bak'@'10.161.181.%' identified by 'root123';
grant replication slave on *.* to 'bak'@'10.161.181.%';
GRANT ALL PRIVILEGES ON *.* TO 'bak'@'%' IDENTIFIED BY 'root123' WITH GRANT OPTION;
flush privileges;

2、先在备库上10.161.181.224主机上配置同步信息：
mysql -uroot  -proot123;
change master to  master_host='10.161.181.223',master_user='bak',master_password='root123',master_auto_position=1;
start slave;

3、之后再在主库上10.161.181.223上配置主从同步信息：
change master to  master_host='10.161.181.224',master_user='bak',master_password='root123',master_auto_position=1;
start slave;
show slave status\G;

到此，双主同步建立完成。
备注：
允许root用户连接（授予下面权限）：mysql -h 10.161.181.223 -uroot -proot123 -P3306 -e "show status"
create user 'root'@'10.161.181.%' identified by 'root123';
grant select,insert,update,delete on root.* to 'root'@'10.161.181.%' identified by 'root123';
4、分别在两台主机上安装keepalived软件包:
https://keepalived.org/download.html （本次下载：keepalived-1.4.5.tar.gz）或yum源安装：yum -y install keepalived;
源码安装：
4.1先安装keepalived环境条件：
yum -y install curl gcc openssl-devel libnl3-devel net-snmp-devel
yum -y install libnl libnl-devel
yum install -y libnfnetlink-devel

4.2解压安装包:
tar -zxvf keepalived-1.4.5.tar.gz
cd keepalived-1.4.5
mkdir /usr/local/keepalived
chmod 775 /usr/local/keepalived/
./configure --prefix=/usr/local/keepalived
make && make install


make && make install如下：


4.3安装完成，生成相应文档：

cp /usr/local/keepalived/etc/sysconfig/keepalived  /etc/sysconfig/keepalived   #cp启动脚本文档到操作系统配置文件下：
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/keepalived    #添加keepalived启动程序到系统环境命令：
cp  /root/wlp/keepalived-1.4.5/keepalived/etc/init.d/keepalived /etc/init.d/keepalived  #keepalived启动脚本（源码目录下），放到/etc/init.d/目录下就可以使用service命令便捷调用
mkdir /etc/keepalived  ； #将配置文件放到默认路径下
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf

添加系统服务并为自启动：


4.4在两台主机上配置检测mysql数据库的脚本 ：
cd /etc/keepalived
vi checkmysql.sh

#!/bin/bash
#author=wlp
mysqlstr=/usr/local/mysql/bin/mysql
host=10.161.181.223
user=admin
password=root123
port=3306
##mysql service ok=1 ,else = 0
mysql_status=1

###check mysql status#####
$mysqlstr -h $host -u $user -p$password -P$port -e "show status;" >/dev/null 2>&1
if [ $? = 0 ] ; then
    echo "mysql_status=1"
    exit 0
else
    /etc/init.d/keepalived stop
fi
chmod 775 /etc/keepalived/checkmysql.sh或chmod +x /etc/keepalived/checkmysql.sh
4.5创建checkmysql.sh脚本检查用户：
创建admin管理用户：
create user 'admin'@'10.161.181.%' identified by 'root123';
grant all privileges on *.* to 'admin'@'10.161.181.%';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'root123' WITH GRANT OPTION;
flush privileges;

4.6修改两台主机keepalived配置文件：
223主机1:
vi /etc/keepalived/keealived.conf

! Configuration File for keepalived
vrrp_script vs_mysql_223 {
    script "/etc/keepalived/checkmysql.sh"
    interval 10
}

vrrp_instance VI_223 {
    state BACKUP
    nopreempt
    interface eth0
    virtual_router_id 223
    priority 223
    advert_int 5
    authentication {
        auth_type PASS
        auth_pass root123
    }
    track_script {
        vs_mysql_223
    }
    virtual_ipaddress {
        10.161.181.226
    }
}
224主机2:

! Configuration File for keepalived
vrrp_script vs_mysql_224 {
    script "/etc/keepalived/checkmysql.sh"
    interval 10
}

vrrp_instance VI_224 {
    state BACKUP
    nopreempt
    interface eth0
    virtual_router_id 223
    priority 90
    advert_int 5
    authentication {
        auth_type PASS
        auth_pass root123
    }
    track_script {
        vs_mysql_224
    }
    virtual_ipaddress {
        10.161.181.226
    }
}
4.7启动两台主机keepalived进程：




使用另外一台mysql 225，测试 vip是否可以连接到主库223：

show status\s确定连接为vip：

到此双主架构建立完成。
5.故障模拟切换：
在主库上关闭mysql服务：/usr/local/mysql/bin/mysqladmin -uroot -proot123 shutdown
查看keepalived日志情况：tail -f /var/log/messages(vip removed &&keepalived stop）
备库上查看keepalived日志：tail -f /var/log/messages (keepalived backup转换为master&& vip切换到备机上）
此上不影响用户使用，转换时会间断：mysql -uadmin -proot123 -h10.161.181.226
show status \s;



在225上通过vip验证：

再把223主机mysql服务开启：service mysqld start;
检查同步，都正常了：mysql -uroot -proot123 ; show slave status\G;
切换原主节点到223：启动223上主机keepalived:/etc/init.d/keepalived start;
关闭224上mysql：mysqladmin -uroot -proot123  shutdown;
检查vip和mysql同步：已经切换到223节点并且服务正在连接：

开启224上mysql并检查主从同步:service mysqld restart

VIP测试连接正常：