Mysql主主复制+keepalived

1》环境

Master1 10.0.0.201

Master2 10.0.0.202

2》Master1操作授权

　　mysql>grant replication slave on *.* to 'admin'@'10.0.0.202' identified by '123456';授权

　　mysql>show master statusG;

3》Master2操作授权

　　mysql->grant replication slave on *.* to 'admin'@'10.0.0.201' identified by '123456';授权
　　mysql->show master statusG;

4》主1-201上操作

　　change master to
　　master_host='10.0.0.202',
　　master_user='admin',
　　master_password='123456',
　　master_log_file='mysql-bin.000006',
　　master_log_pos=242;
　　mysql->start slave;
　　mysql->show slave statusG;

 5》主2-202上操作

　　change master to
　　master_host='10.0.0.201',
　　master_user='admin',
　　master_password='123456',
　　master_log_file='mysql-bin.000002',
　　master_log_pos=242;
　　mysql->start slave;
　　mysql->show slave statusG;

 6》主201 和202同时安装keepalived

　　# tar -xvf keepalived-1.1.20.tar.gz
　　# cd keepalived-1.1.20
　　#./configure --prefix=/usr/local/keepalived
　　# make && make install
　　# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
　　# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
　　# mkdir /etc/keepalived
　　# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
　　# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

    7》keepalived配置文件

! 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 MYSQLHA_DEVEL
}

vrrp_script check_mysqld {
script "/etc/keepalived/mysqlcheck/check_mysql.sh" #检查MYSQL 复制状态脚本
interval 2
weight 2
}

vrrp_instance VI_1 {
state BACKUP #这里主201和主202都写上BACKUP
interface eth1 #vip对外的网络接口
virtual_router_id 80 #标识号。。主主要统一起来
priority 100 #优先级。主202上改成90
advert_int 2
nopreempt #不抢占模式。在优先级高的那台设置此参数，一般在主上设置，主的优先级高


authentication {
auth_type PASS
auth_pass 1111
}

track_script {
check_mysqld #调用MYSQL脚本的函数
}

virtual_ipaddress {
10.0.2.204/16
}
}

    当master down了，backup接管了，master再次起来，不会再成为master。否则master恢复了再接管的话。如果出现问题了，则切换两次对生站业务来说并不好。

   解决方法是:
　　state 都设置为Backup,在优先级高的那台设置参数nopreempt.

8》监控MYSQL 复制状态的脚本

#!/bin/bash
#Totle:check_mysql
#Description:Check mysql status
#system:Use Linux
#Author:Alvin
#Version:1.0
#DateTime:2014-09-09
#======================================
#Set mysql host
#每个机器改成自己的IP地址和远程授权的用户
Host_S=10.0.0.201
User_S=admintest
Pwss_S=123456
Port_S=3306
#======================================
#Function-->Check_mysql_IO
#=====================================
Check_mysql_IO()
{

Check_IO=`mysql -u$User_S -p$Pwss_S -h $Host_S -P $Port_S -e "show slave statusG" | grep "Runnin" | sed 's/ //g' | grep "IO" | awk -F: '{print $NF}' | grep "Yes" | wc -l`

Check_SQL=`mysql -u$User_S -p$Pwss_S -h $Host_S -P $Port_S -e "show slave statusG" | grep "Runnin" | sed 's/ //g' | grep "SQL" | awk -F: '{print $NF}' | grep "Yes" | wc -l`


if [ $Check_IO -ne 1 -o $Check_SQL -ne 1 ]
then
/etc/init.d/keepalived stop
return 1
else
Check_PID=`/etc/init.d/keepalived status | grep "pid" | wc -l`
if [ $Check_PID -eq 1 ]
then
echo "OK"
exit 0
else
/etc/init.d/keepalived start
if [ $? -eq 0 ]
then
echo "keepalived start ok" >/tmp/checkmysql.log
exit 0
else
echo "keepalived start fail..">/tmp/checkmysql.log
return 1
fi
fi
fi
}

#======================================
#Function-->Main
#=====================================
Main()
{

Check_mysql_IO
if [ $? -eq 1 ]
then
echo "Mysql $Host_S IO or SQL error" | mail -s "Mysql IO error" 50738846@qq.com
exit 1
fi

}
Main;

9》在201和202远程授权VIP登陆用户

　　>grant all on *.* to admintest@'%' identified by '123456';

10》最后测试

　　　　通常VIP模式是在201上面的，我们在202测试一下，。用VIP登陆是否看到是的201 ，
　　　　# mysql –u admintest –p 123456 –h 10.0.2.204 –P 3306
　　　　   mysqlàshow variables like '%hostname%';
　　　　   mysqlàshow variables like '%server_id%';

　　接下来。我们测试这个主主切换。是否OK？我们在201上。将SLAVE IO 给stop掉。
　　　　mysql->slave stop;
　　然后退出来。看看201的VIP还是否存在？如果不存在。那么202上是否已经接管了VIP？