mysql keepalive实施实例

MySQL-VIP：192.168.1.238

MySQL-master1：192.168.1.141

MySQL-master2：192.168.1.142 

OS版本：CentOS 5.4  

MySQL版本：5.0.77  

Keepalived版本：1.1.20 

一、MySQL master-master配置

1.修改/etc/my.cnf

----master1--------

log-bin=mysql-bin

server-id = 1

binlog-do-db=houhou

binlog-ignore-db=mysql

replicate-do-db=houhou

replicate-ignore-db=mysql

log-slave-updates

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1

------master2----------

log-bin=mysql-bin

server-id = 2

binlog-do-db=houhou

binlog-ignore-db=mysql

replicate-do-db=houhou

replicate-ignore-db=mysql

lo

g-slave-updates

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=2

2、将192.168.1.141设为192.168.1.142的主服务器

在192.168.1.141上新建授权用户

grant replication slave on *.* to 'replication'@'%' identified by 'replication'; 

MySQL> show master status;

+------------------+----------+--------------+------------------+  

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  

+------------------+----------+--------------+------------------+  

| MySQL-bin.000003 |      374 |              |                  |   

+-----------------

-+----------+--------------+------------------+  

在192.168.1.142上将192.168.1.141设为自己的主服务器

MySQL> change master to master_host='192.168.1.141',master_user='replication',master_password='replication',master_log_file='MySQL-bin.000003',master_log_pos=374; 

MySQL> start slave;

MySQL> show slave status\\G

  看到：

          Slave_IO_Running: Yes  

          Slave_SQL_Running: Yes  

表示配置成功。

3、将192.168.1.142设为192.168.1.141的主服务器

 在192.168.1.142上授权用户

MySQL> grant replication slave on *.* to 'replication'@'%' identified by '

replication';

MySQL> show master status;

+------------------+----------+--------------+------------------+  

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  

+------------------+----------+--------------+------------------+  

| MySQL-bin.000003 |      374 |              |                  |   

+------------------+----------+--------------+------------------+    

在192.168.1.141上，将192.168.1.142设为自己的主服务器

MySQL> change master to master_host='192.168.1.142',master_user='replication',master_password='replication',master_log_file=

'MySQL-bin.000003',master_log_pos=374; 

MySQL> start slave;

MySQL> show slave status\\G

 看到：

          Slave_IO_Running: Yes  

          Slave_SQL_Running: Yes  

表示配置成功。

4、MySQL同步测试

二、keepalived安装及配置

1、192.168.1.141服务器上keepalived安装及配置

下载keepalived  

wget http://www.keepalived.org/software/keepalived-1.1.20.tar.gz

安装keepalived

#tar zxvf keepalived-1.1.20.tar.gz  

#cd keepalived-1.1.20  

#./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-1.el5-xen-x86_

#make && make install 

配置keepalived

我们自己在新建一个配置文件，默认情况下keepalived启动时会去

/etc/keepalived目录下找配置文件

#mkdir /etc/keepalived  

#vi /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 MASTER

interface eth0

virtual_router_id 77

priority 100

advert_int 1

nopreempt

authentication {

auth_type PASS

auth_pass zhengfei 

}

track_script {

check_run

}

virtual_ipaddress {

192.168.1.238

}

}

编写检测服务down后所要执行的脚本

vi keepalived_check_mysql.sh 

#!/bin/bash

MYSQL=/usr/bin/mysql

M

YSQL_HOST=localhost

MYSQL_USER=root

MYSQL_PASSWORD=zhengfei

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

/usr/local/keepalived/etc/rc.d/init.d/keepalived s

top

exit 1

fi

sleep 1

done

启动keepalived

#/usr/local/keepalived/sbin/keepalived -D  

#ps -aux | grep keepalived 

2、192.168.1.142上keepalived安装及配置

安装keepalived

#tar zxvf keepalived-1.1.20.tar.gz  

#cd keepalived-1.1.20  

#./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-1.el5-i686  

#make && make install 

配置keepalived

这台配置和上面基本一样，但有两个地方不同：优先级为90、无抢占设置

#mkdir /etc/keepalived  

#vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

router_id MySQL-ha

}

vrrp_script check_run {

script "/root/keepalived_check_m

ysql.sh"

interval 5

}

vrrp_sync_group VG1 {

group {

VI_1

}

}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id 77

priority 80

advert_int 1

authentication {

auth_type PASS

auth_pass zhengfei

}

track_script {

check_run

}

virtual_ipaddress {

192.168.1.238

}

}

在上面部署相同的/root/keepalived_check_mysql.sh脚本

启动keepalived  

#/usr/local/keepalived/sbin/keepalived –D  

#ps -aux | grep keepalived

配置完毕