MySQL 双主 + keepalived
参考：https://www.cnblogs.com/kevingrace/p/6710136.html
MySQL双主复制 + keepalived 可以实现数据高可用。双主即互为master-slave，其中任意一个改变，另外一个会跟着改变(与主从不同，主从是单向)；可以实现数据库服务器的热备，但是一个Master宕机后不能实现动态切换。使用Keepalived，可以通过虚拟IP，实现双主对外的统一接口以及自动检查、失败切换机制，从而实现MySQL数据库的高可用方案。
一、双主配置
1、服务器规划
master1：192.168.116.128
master2：192.168.116.130
vip：192.168.116.129
2、mysql 配置
(1) master1(192.168.116.128) mysql服务器配置
进入mysql配置文件：
cd /etc/my.cnf
log-bin=mysql-bin
binlog_format=mixed
server-id=128
auto-increment-increment=2
auto-increment-offset=1
log-slave-updates
sync-binlog=1
黑色的是之前主从复制配置信息，红色的是主主配置需要新增的配置信息。
(2) master2(192.168.116.130) mysql服务器配置
进入mysql配置文件：
cd /etc/my.cnf
log-bin=mysql-bin
binlog_format=mixed
server-id=130
auto-increment-increment=2
auto-increment-offset=2
log-slave-updates
sync-binlog=1
黑色的是之前主从复制配置信息，红色的是主主配置需要新增的配置信息。
(3) master1，master2 分别创建复制账号
master1 执行下列语句：
grant replication slave on *.* to 'latiny130'@'192.168.116.130' identified by '123456';
创建的账号是给作为从服务器master2使用
master2 执行下列语句：
grant replication slave on *.* to 'latiny128'@'192.168.116.128' identified by '123456';
创建的账号是给作为从服务器master1使用
(4) 登录到命令行分别查看master1，master2作为主数据库的状态
master1
File为mysql-bin.000001，Position为154才是初始状态，如果不是这两个值，最好重置一下，输入reset master即可。
master2
change master to master_host='192.168.116.130',master_user='latiny130',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;
启动master1的slave线程
start slave;
change master to master_host='192.168.116.128',master_user='latiny128',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;
启动master2的slave线程
start slave;
[root@master1 ~]# yum install -y openssl-devel [root@master1 ~]# cd /usr/local/src/ [root@master1 src]# wget http://www.keepalived.org/software/keepalived-1.3.5.tar.gz [root@master1 src]# tar -zvxf keepalived-1.3.5.tar.gz [root@master1 src]# cd keepalived-1.3.5 [root@master1 keepalived-1.3.5]# ./configure --prefix=/usr/local/keepalived [root@master1 keepalived-1.3.5]# make && make install [root@master1 keepalived-1.3.5]# cp /usr/local/src/keepalived-1.3.5/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/ [root@master1 keepalived-1.3.5]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ [root@master1 keepalived-1.3.5]# mkdir /etc/keepalived/ [root@master1 keepalived-1.3.5]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/ [root@master1 keepalived-1.3.5]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ [root@master1 keepalived-1.3.5]# echo "/etc/init.d/keepalived start" >> /etc/rc.local
bal_defs { notification_email { ops@wangshibo.cn tech@wangshibo.cn } notification_email_from ops@wangshibo.cn smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MASTER-HA } #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等 vrrp_script chk_mysql_port { #这里通过脚本监测 script "/opt/chk_mysql.sh" #脚本执行间隔，每2s检测一次 interval 2 #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5 weight -5 #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间） fall 2 #检测1次成功就算成功。但不修改优先级 rise 1 } vrrp_instance VI_1 { state MASTER #指定虚拟ip的网卡接口 interface eth0 mcast_src_ip 192.168.116.128 #路由器标识，MASTER和BACKUP必须是一致的 virtual_router_id 51 #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP priority 101 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.116.129 } track_script { chk_mysql_port } }
编写切换脚本 vim /opt/chk_mysql.sh
MYSQL=/usr/bin/mysql MYSQL_HOST=localhost MYSQL_USER=root MYSQL_PASSWORD=123456 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 pkill keepalived exit 1 fi sleep 1 done
对脚本授权：chmod 755 /opt/chk_mysql.sh
启动keepalived：/etc/init.d/keepalived start
bal_defs { notification_email { ops@wangshibo.cn tech@wangshibo.cn } notification_email_from ops@wangshibo.cn 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 2 weight -5 fall 2 rise 1 } vrrp_instance VI_1 { state BACKUP interface eth0 mcast_src_ip 191.168.116.130 virtual_router_id 51 priority 99 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.116.129 } track_script { chk_mysql_port } }
编写切换脚本 vim /opt/chk_mysql.sh
MYSQL=/usr/bin/mysql MYSQL_HOST=localhost MYSQL_USER=root MYSQL_PASSWORD=123456 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 pkill keepalived exit 1 fi sleep 1 done
对脚本授权：chmod 755 /opt/chk_mysql.sh
启动keepalived：/etc/init.d/keepalived start
4、两台服务器授权允许root用户或者其用户(chk_mysql.sh里配置的)，用户在客户端登录测试
登录命令行：grant all on *.* to root@'192.168.116.129' identified by "123456";
千万注意：
nopreempt这个参数只能用于state为backup的情况，所以在配置的时候要把master和backup的state都设置成backup，这样才会实现keepalived的非抢占模式！
也就是说：
(1) 当state状态一个为master，一个为backup的时候，加不加nopreempt这个参数都是一样的效果。即都是根据priority优先级来决定谁抢占vip资源的，是抢占模式！
(2) 当state状态都设置成backup，如果不配置nopreempt参数，那么也是看priority优先级决定谁抢占vip资源，即也是抢占模式。
(3) 当state状态都设置成backup，如果配置nopreempt参数，那么就不会去考虑priority优先级了，是非抢占模式！即只有vip当前所在机器发生故障，另一台机器才能接管vip。即使优先级高的那一台机器恢复 后也不会主动抢回vip，只能等到对方发生故障，才会将vip切回来。
