【mysql HA】 MySql+Keepalived 实现双主高可用

主机规划：

virtual_server ip :192.168.1.212

mysql1: 192.168.1.206

mysql2: 192.168.1.210

安装mysql

# yum -y install mysql mysql-server

# rpm -qa |grep mysql
mysql-server-5.1.61-4.el6.x86_64
mysql-libs-5.1.61-4.el6.x86_64
mysql-5.1.61-4.el6.x86_64

修改mysql配置文件
192.168.1.206
# cat /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_allowed_packet=16M
user=mysql
symbolic-links=0
server-id=1
log-bin=mysql-bin
# Disabling symbolic-links is recommended to prevent assorted security risks

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
master-host =192.168.1.210
master-user=tongbu
master-pass=123456
master-port =3306
master-connect-retry=60

192.168.1.210

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
log-bin=mysql-bin
server-id = 2
# Disabling symbolic-links is recommended to prevent assorted security risks

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
master-host =192.168.1.206
master-user=tongbu
master-pass=123456
master-port =3306

master-connect-retry=60

配置同步，互相设置另一台服务器为mysql主

192.168.1.210
mysql> grant replication slave on *.* to 'tongbu'@'%' identified by '123456';
192.168.1.206
mysql> slave stop;
mysql>change master to master_host='192.168.1.210',master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=242;
mysql> slave start;


192.168.1.206
mysql> grant replication slave on *.* to 'tongbu'@'%' identified by '123456';
192.168.1.210
mysql> slave stop;
mysql>change master to master_host='192.168.1.206',master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=187;
mysql> slave start;

安装keepalived

 

# wget

# yum -y install gcc gcc-c++ openssl-devel

# tar xf keepalived-1.2.2.tar.gz

# cd keepalived-1.2.2

# ./configure --prefix=/usr/local/keepalived

# make && make install

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

# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/

# mkdir /etc/keepalived

 

keepalived配置文件

192.168.1.206

# cat /etc/keepalived/keepalived.conf 

! Configuration File for keepalived
global_defs {
   router_id MySQL-ha
}
vrrp_script check_run {
   script "/root/keepalived_check_mysql.sh"
   interval 5
}
vrrp_sync_group VG1 {
    group {
          VI_1
    }
}
vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 88
    priority 100
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 11111 
    }
    track_script {
        check_run
    }
    virtual_ipaddress {
        192.168.1.212
    }
}

192.168.1.210

# cat /etc/keepalived/keepalived.conf 

! Configuration File for keepalived
global_defs {
   router_id MySQL-ha
}
vrrp_script check_run {
   script "/root/keepalived_check_mysql.sh"
   interval 5
}
vrrp_sync_group VG1 {
    group {
          VI_1
    }
}
vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 88
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 11111
    }
    track_script {
        check_run
    }
    virtual_ipaddress {
        192.168.1.212
    }
}

检测脚本

# cat /root/keepalived_check_mysql.sh

#!/bin/bash
MYSQL=/usr/bin/mysql 
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=000000
CHECK_TIME=3

#mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0

MYSQL_OK=1

function check_mysql_helth (){
    $MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1
    if [ $? = 0 ] ;then
    MYSQL_OK=1
    else
    MYSQL_OK=0
    fi
    return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
    let "CHECK_TIME -= 1"
    check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
    CHECK_TIME=0
    exit 0
fi
if [ $MYSQL_OK -eq 0 ] &&  [ $CHECK_TIME -eq 0 ]
then
    /etc/init.d/keepalived stop
    exit 1
fi
sleep 1
done