准备
1. 双主 master1 192.168.199.49
master2 192.168.199.50
VIP 192.168.199.52 //虚拟IP
2.环境 master:nginx + php + mysql + keepalived
VIP:只要和master在一个局域网内即可。
3. 服务器之间网络通畅，可以互相ping通。
4. 2个服务器的mysql版本要一致。数据库密码一致
5. 防火墙增加允许组播和允许VRRP（虚拟路由器冗余协）通信，这样主服务器在故障恢复后才能抢回资源
-A INPUT -s 182.148.15.0/24 -d 224.0.0.18 -j ACCEPT
-A INPUT -s 182.148.15.0/24 -p vrrp -j ACCEPT
重启生效：service iptables reload
配置服务器
服务器1： 在my.cnf文件的[mysqld]配置区域添加下面内容：
log-bin=mysql-bin #记录二进制文件
binlog_format=mixed #mysql默认采用的二进制格式
server-id = 2 #服务号，必须是唯一的，一般取IP的后8位
expire_logs_days = 10 #binlog过期清理时间 binlog-do-db = db1 #需要服务的数据库。
#replicate-ignore-db = db3,mysql,test,information_schema,performance_schema,transfer_new #不需要复制的数据库
#replicate-wild-ignore-table=garbage.% #有问题可以尝试使用此项配置
sync_binlog = 1 #表示binlog日志在每1次写入后与硬盘同步，设置1最安全也最慢
auto-increment-increment = 2 #服务器个数
auto-increment-offset = 2 #自增偏移量，如果有2台，则一台设置1，另一台设置2
slave-skip-errors = all #从库复制时跳过所有的错误
服务器2： log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
binlog-do-db = db1
#replicate-ignore-db = db3,mysql,test,information_schema,performance_schema,transfer_new
#replicate-wild-ignore-table=garbage.% #有问题可以尝试使用此项配置
sync_binlog = 1
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all
2台服务器都重启mysql服务：
service mysql restart
同步配置(2台服务器分别执行)
给对方授权复制权限 //对方服务器60.205.182.26允许复制本服务器数据库，用户名repl 密码ocnt-123
mysql> grant replication slave,replication client on *.* to repl@'60.205.182.26' identified by "ocnt-123";
//生效
mysql> flush privileges;
//锁定表
mysql> flush tables with read lock;
同步数据
1.锁定表
mysql> flush tables with read lock;
2.同步数据，可使用navicat
同步操作(2台服务器分别执行)
//查看各自服务器当前二进制日志文件名称和记录位置
mysql> show master status; <!-----------------------服务器1同步------------------------------------>
//解锁表
mysql> unlock tables;
//停止slave
mysql> slave stop;
//开始同步: IP、用户名和密码为服务器2刚才grant授权配置的,日志文件和pos值得位置通过服务器2 show master status查看得来
mysql> change master to master_host='101.200.63.35',master_user='repl',master_password='',master_log_file='mysql-bin.000016',master_log_pos=363;
//开启slave
mysql> start slave; <!-----------------------服务器2同步------------------------------------>
//解锁表
mysql> unlock tables;
//停止slave
mysql> slave stop；
<!--开始同步-->
change master to master_host='60.205.182.26',master_user='repl',master_password='',master_log_file='mysql-bin.000028',master_log_pos=107;
<!--开启slave-->
mysql> start slave;
查看同步状态，如下出现两个“Yes”，表明同步成功！（Slave_IO_Running和Slave_SQL_Running）
mysql> show slave status \G;
配置到此处就完成了双主热备。
keepalived故障自动切换配置
master1:192.168.199.49 主机1
master2:192.168.199.50 主机2 备份
VIP:192.168.199.52 虚拟IP
### 2台服务器安装keepalived [root@localhost ]#yum install -y openssl-devel
[root@localhost src]#cd /usr/local/src/
[root@localhost src]# wget http://www.keepalived.org/software/keepalived-1.3.5.tar.gz
[root@localhost src]# tar -zvxf keepalived-1.3.5.tar.gz
[root@localhost src]# cd keepalived-1.3.5
[root@localhost keepalived-1.3.5]# ./configure --prefix=/usr/local/keepalived
[root@localhost keepalived-1.3.5]# make && make install [root@localhost keepalived-1.3.5]# cp /usr/local/src/keepalived-1.3.5/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/
[root@localhost keepalived-1.3.5]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@localhost keepalived-1.3.5]# mkdir /etc/keepalived/
[root@localhost keepalived-1.3.5]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
[root@localhost keepalived-1.3.5]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
[root@localhost keepalived-1.3.5]# echo "/etc/init.d/keepalived start" >> /etc/rc.local
master1服务器配置
[root@master1 ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@master1 ~]# vim /etc/keepalived/keepalived.conf ＃删除所有内容，重新配置如下 ! Configuration File for keepalived
global_defs {
notification_email {
412140451@qq.com
} notification_email_from 412140451@qq.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MASTER-HA
} vrrp_script chk_mysql_port { #检测mysql服务是否在运行
script "/opt/chk_mysql.sh" #这里通过脚本监测
interval 3 #脚本执行间隔，每3s检测一次
weight -5 #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5
fall 2 #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）
rise 1 #检测1次成功就算成功。但不修改优先级
} vrrp_instance VI_1 {
state MASTER
interface eth0 #指定虚拟ip的网卡接口
mcast_src_ip 192.168.199.49 #本机master的 IP地址
virtual_router_id 100 #路由器标识，MASTER和BACKUP必须是一致的
priority 100 #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来
advert_int 1
authentication { #加密协议
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.199.52 #虚拟IP地址 VIP
} track_script {
chk_mysql_port
}
}
master2服务器配置 修改 mcast_src_ip priority
global_defs {
notification_email {
412140451@qq.com
}
notification_email_from 412140451@qq.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MASTER-HA
}
vrrp_script chk_mysql_port {
script "/opt/chk_mysql.sh"
interval 3
weight -5
fall 2
rise 1
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
mcast_src_ip 192.168.199.50
virtual_router_id 100
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.199.52
}
track_script {
chk_mysql_port
}
}
2个服务器都编写检测切换mysql脚本
KeepAlived做心跳检测，如果Master的MySQL服务挂了(3306端口挂了),那么它就会选择自杀。Slave的KeepAlived通过心跳检测发现这个情况，就会将VIP的请求接管
vim /opt/chk_mysql.sh #!/bin/bash
CHECK_TIME=3 #Mysql
host='localhost'
port=''
user='root'
password='ocnt-123'
mysqlclient="mysql" #mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
function check_mysql_helth (){
#NEW=$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1
$mysqlclient --host=$host --port=$port --user=$user --password=$password -e "show databases;" > /dev/null 2>&1 if [ $? == 0 ]
then
MYSQL_OK=1
else
MYSQL_OK=0
fi
# echo $MYSQL_OK
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
pkill keepalived
exit 1
fi
sleep 1
done
赋权限
chmod 755 /opt/chk_mysql.sh
启动服务
关闭防火墙
service iptables stop
启动服务
/etc/init.d/keepalived start
数据库测试
使用局域网内的客户端连接VIP
mysql -h192.168.199.52 -uroot -pocnt-123
测试是否可用
故障切换检测
默认情况下，vip是在master1上的。使用"ip addr"命令查看vip切换情况
ip addr
查找
inet 192.168.199.49/32 scope global eth0 //这个32位子网掩码的vip地址表示该资源目前还在master1机器上，而master2机器上没有
同时查看 tail -f /var/log/message 会发现
Dec 13 17:52:38 localhost Keepalived_vrrp[65545]: VRRP_Instance(VI_1) Entering MASTER STATE
Dec 13 17:52:38 localhost Keepalived_vrrp[65545]: VRRP_Instance(VI_1) setting protocol VIPs.
Dec 13 17:52:38 localhost Keepalived_vrrp[65545]: Sending gratuitous ARP on eth0 for 192.168.199.51 关掉master1的mysql服务
service mysql stop
查看 master1的日志 tail -f /var/log/message 会发现
Dec 13 18:52:44 localhost Keepalived_vrrp[65545]: Stopped
Dec 13 18:52:44 localhost Keepalived[65542]: Stopped Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2 查看master2的日志
Dec 13 18:52:45 localhost Keepalived_vrrp[28451]: VRRP_Instance(VI_1) Transition to MASTER STATE
Dec 13 18:52:46 localhost Keepalived_vrrp[28451]: VRRP_Instance(VI_1) Entering MASTER STATE
Dec 13 18:52:46 localhost Keepalived_vrrp[28451]: VRRP_Instance(VI_1) setting protocol VIPs.
Dec 13 18:52:46 localhost Keepalived_vrrp[28451]: Sending gratuitous ARP on eth0 for 192.168.199.51 此时就已经转移到master2上，
恢复master1 的mysql服务
service mysql start
恢复master1 的keepalive
/etc/init.d/keepalived start 查看master1的日志
Dec 13 18:54:41 localhost Keepalived_vrrp[71002]: VRRP_Instance(VI_1) Transition to MASTER STATE
Dec 13 18:54:42 localhost Keepalived_vrrp[71002]: VRRP_Instance(VI_1) Entering MASTER STATE
Dec 13 18:54:42 localhost Keepalived_vrrp[71002]: VRRP_Instance(VI_1) setting protocol VIPs.
Dec 13 18:54:42 localhost Keepalived_vrrp[71002]: Sending gratuitous ARP on eth0 for 192.168.199.51 查看master2的日志
Dec 13 18:54:41 localhost Keepalived_vrrp[28451]: VRRP_Instance(VI_1) Received advert with higher priority 100, ours 90
Dec 13 18:54:41 localhost Keepalived_vrrp[28451]: VRRP_Instance(VI_1) Entering BACKUP STATE
Dec 13 18:54:41 localhost Keepalived_vrrp[28451]: VRRP_Instance(VI_1) removing protocol VIPs. 说明服务已经转移到master2上了。
至此完成
完成
其它：删除主从复制
mysql> stop slave;
mysql> slave reset;
mysql> reset master;
keepalived
启动
/etc/init.d/keepalived start
异常解决办法
如果不能正常复制，查看状态
sql> show slave status \G;
然后可以看到错误信息，一般情况下，重启一下slave就好了
sql> stop slave;