Mysql+Keepalived实现双主热备高可用
安装环境：
DB1：Centos6.9系统、IP：10.0.3.177、MYSQL版本：5.6
DB2：Centos6.9系统、IP：10.0.3.178、MYSQL版本：5.6
虚拟IP：10.0.3.176（虚拟IP同样占用一个IP，先确保虚拟IP未被使用）
实现步骤：
1.先正常安装DB1，DB2两台mysql数据库，可参考我另外一篇文章
链接：https://lcryblog.lofter.com/post/1e131973_11d44974
2.双主同步复制配置
修改配置文件：(两个mysql都要修改，以DB1为例)
vi /usr/local/mysql/my.cnf
在[mysqld]配置区域添加下面内容：
server-id = 1
#节点标示，主从节点不能相同，必须全局唯一,DB1设置1，DB2设置2
log-bin=mysql-bin
sync_binlog = 1
relay-log = mysql-relay-bin
slave-skip-errors = all
binlog_checksum = none
binlog_format = mixed
重启mysql： service mysql restart
3.创建复制用户并授权（注：在执行主主互备之前要保证两台server上数据一致）
首先在DB1的mysql库中创建复制用户：
登录mysql：mysql -u root -p
grant replication slave on *.* to 'tongbu'@'10.0.3.178' identified by 'tongbu123';
以上创建为用户tongbu，密码tongbu123
刷新系统权限表：flush privileges;
查看下log bin日志和pos值位置：show master status;
记录上图圈的数值，后面会用到，两个mysql都需要
然后在DB2上创建复制用户：
登录mysql：mysql -u root -p
grant replication slave on *.* to 'tongbu'@'10.0.3.177' identified by 'tongbu123';
以上创建为用户tongbu，密码tongbu123
刷新系统权限表：flush privileges;
查看下log bin日志和pos值位置：show master status;
在此可能DB1、DB2一样，但是重启重启过服务这个会变，一定DB1,DB2都查一下。
4.同步数据
在DB1的mysql库中将DB2设为自己的主服务器
mysql>
CHANGE MASTER TO
MASTER_HOST='10.0.3.178',
MASTER_USER='tongbu',
MASTER_PASSWORD='tongbu123',
MASTER_LOG_FILE='mysql-bin.000001', #这里是上面查询到的DB2的log bin
MASTER_LOG_POS= 120; #这里是上面查询到的DB2的position
在DB2的mysql库中将DB1设为自己的主服务器
mysql>
CHANGE MASTER TO
MASTER_HOST='10.0.3.177',
MASTER_USER='tongbu',
MASTER_PASSWORD='tongbu123',
MASTER_LOG_FILE='mysql-bin.000005', #这里是上面查询到的DB1的log bin
MASTER_LOG_POS= 120; #这里是上面查询到的DB1的position
5.验证：
mysql> start slave;
mysql> show slave status \G;
如果同步不成功，Mysql/data/auto.cnf（两台服务器）要删掉，然后按照步骤重新同步:
验证是否同步，DB1插入更新数据看DB2是否有同步即可
6、安装Keepalived
安装keepalived并将其配置成系统服务。DB1和DB2两台机器上都需要
1）安装依赖
yum install -y pcre-devel openssl-devel popt-devel
2）下载Keepalived
wget https://www.keepalived.org/software/keepalived-1.2.7.tar.gz
3）解压
tar zxvf keepalived-1.2.7.tar.gz
4）进入目录
cd keepalived-1.2.7
5）编译指定安装目录，我这里为/usr/local/keepalived
./configure --prefix=/usr/local/keepalived
6）安装
make && make install
7）将keepalived配置成系统服务
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/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/
chkconfig --add keepalived
chkconfig keepalived on
7.配置DB1上的Keepalived
vi /etc/keepalived/keepalived.conf //清空，复制粘贴下面，在修改一下即可
! Configuration File for keepalived
global_defs {
notification_email {
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id mysql-1
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
10.0.3.176 #这里填VIP虚拟IP
}
}
virtual_server 10.0.3.176 3306 { #这里填VIP虚拟IP
delay_loop 2
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP
real_server 10.0.3.177 3306 { #这里填本机IP
weight 3
notify_down /usr/local/keepalived/mysql.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
8.编写脚本，并赋予权限。（DB1和DB2都要编写）
vi /usr/local/keepalived/mysql.sh
#!/bin/bash
pkill keepalived
chmod +x /usr/local/keepalived/mysql.sh //赋予权限执行权限
/etc/init.d/keepalived start // 启动Keepalived
echo "/etc/init.d/keepalivedstart " >> /etc/rc.d/rc.sysinit //添加开机自启
9.配置DB2上的Keepalived，同DB1，只需要修改以下即可地方：
vi /etc/keepalived/keepalived.conf
priority为90 //优先级小于DB1
nopreempt //不设置删除该行
real_server 设置本地IP
同样编写脚本，赋予权限，再启动Keepalived即可。
10.测试VIP连接地址
在其他服务器连接数据库
mysql -h10.0.3.176 -uroot -p
关闭DB1或DB2中一台服务器，再用其他服务器连接数据库
到此安装完毕。
常见问题：
1. keepalived配置lvs不生效
执行yum -y install ipvsadm
再执行ipvsadm
2.如何修改mysql密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('新密码');
3.查看mysql主机名
show variables like "%hostname%";
4.防火墙如何配置
1）允许组播，两台设备上都需要执行
2）修改：vi /etc/sysconfig/iptables
适当位置添加以下内容：
-A INPUT -d 224.0.0.18 -j ACCEPT
参考文章：
https://blog.csdn.net/oguro/article/details/52905169