【MySQL Database】通过keepalived实现数据库高可用集群

[root@wallet01 ~]# mysql -uroot -pabcd.1234
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.202
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

[root@wallet02 ~]# mysql -uroot -pabcd.1234
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.201
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000008
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

[root@wallet01 ~]# yum install -y libnl libnl-devel
[root@wallet01 ~]# cd /usr/local/src
[root@wallet01 src]# tar zxvf keepalived-1.2.19.tar.gz
[root@wallet01 src]# cd keepalived-1.2.19
[root@wallet01 keepalived-1.2.19]# ./configure --sysconf=/etc --with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64
[root@wallet01 keepalived-1.2.19]# make && make install 
[root@wallet01 keepalived-1.2.19]# ln -s /usr/local/sbin/keepalived /sbin/
[root@wallet01 ~]# chkconfig --add keepalived
[root@wallet01 ~]# chkconfig --level 35 keepalived on
[root@wallet01 ~]# chkconfig --list |grep keepalived
keepalived      0:off   1:off   2:off   3:on    4:off   5:on    6:off

[root@wallet01 ~]# vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id wallet01
}
vrrp_script check_mysql {
        script "/usr/local/mysql/scripts/check_mysql.sh"
        interval 2
}
vrrp_instance v01 {
    state BACKUP
    interface eth1
    virtual_router_id 200
    priority 100
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.200 dev eth0
    }
        track_script {
        check_mysql
    }
}

[root@wallet01 ~]# vi /usr/local/mysql/scripts/check_mysql.sh 
#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=abcd.1234
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "show status;" >/dev/null 2>&1
if [ $? == 0 ]
then
    echo " $host mysql login successfully "
    exit 0
else
    echo " $host mysql login faild"
    exit 1
fi
[root@wallet01 ~]# chmod a+x /usr/local/mysql/scripts/check_mysql.sh

[root@wallet01 ~]# service keepalived start
Starting keepalived:                                       [  OK  ]

[root@wallet01 ~]# service keepalived status
keepalived (pid  3452) is running...

[root@wallet01 ~]# ip addr
2: eth0:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 08:00:27:2b:d6:17 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.201/24 brd 192.168.1.255 scope global eth0
    inet 192.168.1.200/32 scope global eth0
    inet6 fe80::a00:27ff:fe2b:d617/64 scope link 
       valid_lft forever preferred_lft forever

[root@wallet02 ~]# yum install -y libnl libnl-devel
[root@wallet02 ~]# cd /usr/local/src
[root@wallet02 src]# tar zxvf keepalived-1.2.19.tar.gz
[root@wallet02 src]# cd keepalived-1.2.19
[root@wallet02 keepalived-1.2.19]# ./configure --sysconf=/etc --with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64
[root@wallet02 keepalived-1.2.19]# make && make install 
[root@wallet02 keepalived-1.2.19]# ln -s /usr/local/sbin/keepalived /sbin/
[root@wallet02 ~]# chkconfig --add keepalived
[root@wallet02 ~]# chkconfig --level 35 keepalived on
[root@wallet02 ~]# chkconfig --list |grep keepalived
keepalived      0:off   1:off   2:off   3:on    4:off   5:on    6:off

[root@wallet02 ~]# vi /etc/keepalived/keepalived.conf 
! Configuration File for keepalived
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id wallet02
}
vrrp_script check_mysql {
        script "/usr/local/mysql/scripts/check_mysql.sh"
        interval 2
}
vrrp_instance v01 {
    state BACKUP
    interface eth1
    virtual_router_id 200
    priority 80
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.200 dev eth0
    }
        track_script {
        check_mysql
    }
}

[root@wallet02 ~]# vi /usr/local/mysql/scripts/check_mysql.sh 
#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=abcd.1234
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "show status;" >/dev/null 2>&1
if [ $? == 0 ]
then
    echo " $host mysql login successfully "
    exit 0
else
    echo " $host mysql login faild"
    exit 1
fi
[root@wallet02 ~]# chmod a+x /usr/local/mysql/scripts/check_mysql.sh

[root@wallet02 ~]# service keepalived start
Starting keepalived:                                       [  OK  ]

[root@wallet02 ~]# ip addr
2: eth0:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 08:00:27:17:92:69 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.202/24 brd 192.168.1.255 scope global eth0
    inet6 fe80::a00:27ff:fe17:9269/64 scope link 
       valid_lft forever preferred_lft forever	 

[root@wallet01 ~]# service mysqld stop
Shutting down MySQL....                                    [  OK  ]

[root@wallet01 ~]# ip addr
2: eth0:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 08:00:27:46:e9:d6 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.201/24 brd 192.168.1.255 scope global eth0
    inet6 fe80::a00:27ff:fe46:e9d6/64 scope link 
       valid_lft forever preferred_lft forever
       	   
[root@wallet02 ~]# ip addr
2: eth0:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 08:00:27:7b:14:12 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.202/24 brd 192.168.1.255 scope global eth0
    inet 192.168.1.200/32 scope global eth0
    inet6 fe80::a00:27ff:fe7b:1412/64 scope link 
       valid_lft forever preferred_lft forever