数据库主从复制(3)

interface eth0

virtual_router_id 82 priority 100 advert_int 5

authentication { auth_type PASS auth_pass 1111 }

track_script { vs_mysql_82 }

virtual_ipaddress { 192.168.0.34 } }

shell > cat /etc/keepalived/checkMySQL.sh 脚本： #!/bin/bash

MYSQL=/usr/local/mysql/bin/mysql MYSQL_HOST=127.0.0.1 MYSQL_USER=repluser

MYSQL_PASSWORD=replpass MYSQL_PORT=3306

# 日志文件

LOG_FILE=/etc/keepalived/check_mysql.log # 检查次数

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} -P${MYSQL_PORT} -e \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 \-= 1\ check_mysql_helth

if [ $MYSQL_OK = 1 ] ; then CHECK_TIME=0

#echo `date --date=today +\%H:%m:%S\- [INFO] - mysql available: success[$MYSQL_OK] >> $LOG_FILE exit 0 fi

if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ] then

/etc/init.d/keepalived stop

echo `date --date=today +\%H:%m:%S\- [INFO] - mysql invaild. keepalived stop. >> $LOG_FILE exit 1 fi

sleep 1 done

添加执行权限：

shell > chmod +x /etc/keepalived/checkMySQL.sh

手动执行下这个脚本，看看返回是否是0，如果是1，请检查mysql连不上的原因： shell > /etc/keepalived/checkMySQL.sh shell > echo $? 0

B：master2：

shell > cat /etc/keepalived/keepalived.conf vrrp_script vs_mysql_82 {

script \ interval 60 }

vrrp_instance VI_82 { state BACKUP nopreempt interface eth0

virtual_router_id 82 priority 90 advert_int 5

authentication { auth_type PASS auth_pass 1111 }

track_script { vs_mysql_82 }

virtual_ipaddress { 192.168.0.34 } }

checkMySQL.sh脚本内容与master1一样，并添加执行权限，手动测试执行

3.master1和master2都启动keepalived： shell > service keepalived start shell > chkconfig keepalived on

4.测试

在master1上查看下VIP：

shell > ip addr

1: lo: mtu 65536 qdisc noqueue state UNKNOWN

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host

valid_lft forever preferred_lft forever

2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000

link/ether 00:0c:29:e0:62:11 brd ff:ff:ff:ff:ff:ff

inet 192.168.0.30/24 brd 192.168.0.255 scope global eth0 inet 192.168.0.34/32 scope global eth0 inet6 fe80::20c:29ff:fee0:6211/64 scope link

valid_lft forever preferred_lft forever

在master2上查看下VIP，正常情况master2是没有VIP的：

1: lo: mtu 65536 qdisc noqueue state UNKNOWN

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host

valid_lft forever preferred_lft forever

2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000

link/ether 00:0c:29:53:cb:0a brd ff:ff:ff:ff:ff:ff

inet 192.168.0.32/24 brd 192.168.0.255 scope global eth0 inet6 fe80::20c:29ff:fe53:cb0a/64 scope link valid_lft forever preferred_lft forever

在192.168.0.%网段的任意主机连接192.168.0.34，看看能否成功访问，这里如果想要看到VIP连到了哪个服务器，可以使用show slave status\\G或者show processlist;来判断，查看到的是主库信息，即就说明VIP连接的是这个主库的从库： shell > mysql -urepl -p'123456' -h 192.168.0.34 -P3306 mysql > \\s --查看下相关信息

--------------

mysql Ver 14.14 Distrib 5.6.25, for linux-glibc2.5 (x86_64) using EditLine wrapper

Connection id: 821790 Current database:

Current user: repl@192.168.0.37 #登录用户 SSL: Not in use Current pager: stdout

Using outfile: '/data/mysql/query.log' Using delimiter: ;

Server version: 5.5.24-log Source distribution Protocol version: 10

Connection: 192.168.0.34 via TCP/IP #连接IP

Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306

Uptime: 31 days 17 hours 21 min 59 sec

Threads: 2 Questions: 3560094 Slow queries: 1 Opens: 91 Flush tables: 1 Open tables: 25 Queries per second avg: 1.298

--------------

现在把master1的mysqld停掉（大概等待一分半钟再次看看两个master的vip信息，因为keepalived.conf里配置的检测间隔是60s，如果发现mysql连不上会重试3次）： master1，发现VIP没了： shell > ip addr

1: lo: mtu 65536 qdisc noqueue state UNKNOWN

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host

valid_lft forever preferred_lft forever

2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000

link/ether 00:0c:29:e0:62:11 brd ff:ff:ff:ff:ff:ff

inet 192.168.0.30/24 brd 192.168.0.255 scope global eth0 inet6 fe80::20c:29ff:fee0:6211/64 scope link

valid_lft forever preferred_lft forever

master2，发现VIP有了： shell > ip addr

1: lo: mtu 65536 qdisc noqueue state UNKNOWN

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host

valid_lft forever preferred_lft forever

2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000

link/ether 00:0c:29:53:cb:0a brd ff:ff:ff:ff:ff:ff

inet 192.168.0.32/24 brd 192.168.0.255 scope global eth0 inet 192.168.0.34/32 scope global eth0 inet6 fe80::20c:29ff:fe53:cb0a/64 scope link valid_lft forever preferred_lft forever

再次远程连接VIP：

shell > mysql -urepl -p'123456' -h 192.168.0.34 -P3306

把master1的mysqld启动起来，然后把master2的关掉，看看VIP能否切换到master1上，过程跟上面的类似，只需要按照上面的过程再次验证下能否正常访问即可。这里要注意，要先启动mysqld，并检测主从数据是否一致，如果发现数据不一致，那么就先修复好数据，然后再启动keepalived，否则贸然启动keepalived可能发生VIP切换到问题主库上导致悲催的事情发生（注：在mysqld实例停止或无法访问时，keepalived中的checkMySQL.sh脚本检测到问题时候，会自动把keepalived关掉）。