简单原理
1、在两台服务器上分别部署双主keepalived，主keepalived会在当前服务器配置虚拟IP用于mysql对外提供服务
2、在两台服务器上分别部署主主mysql，用于故障切换
3、当mysql服务器挂掉后，主keepalived会降低当前机器权重，备keepalived服务器会把虚拟IP抢过来配置在备服务器上，使备服务器的mysql能接替工作继续对外提供服务
4、由于keepalived只能检测服务器是否宕机来实现故障自动切换，但不能针对应用级别（mysql）的检测，因此，需要编写脚本实时监测mysql服务是否运行正常，当检测mysql运行不正常时就降低权重，来实现故障自动切换
角色分配：
| IP地址 | 部署应用 |
|---|---|
| 192.168.1.200 | mysql001+keepalived01 |
| 192.168.1.201 | mysql002+keepalived02 |
虚拟IP192.168.1.100初始配置在keepalived01，无需手动配置，keepalived会自动配置
准备工作：
#关闭iptables
service iptables stop
chkconfig iptables off
#关闭selinux
setenforce 0
修改/etc/selinux/config文件，将SELINUX=enforcing改为SELINUX=disabled
#同步主机时间
ntpdate 202.120.2.101
=====================================================================
一、配置mysql双主服务
#用yum安装mysql服务
yum install MySQL-shared-compat-5.6.23-1.el6.x86_64.rpm
yum install MySQL-server-5.6.23-1.el6.x86_64.rpm
yum install MySQL-client-5.6.23-1.el6.x86_64.rpm
yum install MySQL-devel-5.6.23-1.el6.x86_64.rpm
yum install MySQL-shared-5.6.23-1.el6.x86_64.rpm
#创建数据目录
mkdir -pv /home/mydata/data
chown -R mysql.mysql /home/mydata
chmod -R +w /home/mydata
#修改主配置文件
vi /etc/my.cnf
#在mysql001上
[mysqld]
datadir = /home/mydata/data #mysql的数据存放位置
port = 3306 #mysql的端口号
socket = /var/lib/mysql/mysql.sock
log-bin = master-bin
server_id = 1 #mysql的ID号
log-bin = mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table = mysql.%
replicate-wild-ignore-table = test.%
replicate-wild-ignore-table = information_schema.%
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
pid-file = /home/mydata/data/mysql.pid
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
innodb_file_per_table = on
thread_concurrency = 8
skip_name_resolve = on
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#在mysql002上
[mysqld]
datadir = /home/mydata/data
port = 3306
socket = /var/lib/mysql/mysql.sock
log-bin = master-bin
server_id = 2
log-bin = mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table = mysql.%
replicate-wild-ignore-table = test.%
replicate-wild-ignore-table = information_schema.%
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
pid-file = /home/mydata/data/mysql.pid
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
innodb_file_per_table = on
thread_concurrency = 8
skip_name_resolve = on
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#初始化mysql
/usr/bin/mysql_install_db --datadir=/home/mydata/data --user=mysql
#相互为对方主机授权复制账号
grant replication slave on *.* to 'repl_user'@'192.168.%.%' identified by 'repl_passwd';
#查看master的状态
show master status;
#授权给check用户，用来检测mysql
grant replication client on *.* to 'check'@'localhost' identified by 'check';
#配置对方为自己的master
在mysql001上：
change master to
master_host='192.168.1.201',
master_user='repl_user',
master_password='repl_passwd',
master_log_file='mysql-bin.000003',
master_log_pos=333; 【这个数字是上面show master status;】
在mysql002上：
change master to
master_host='192.168.1.200',
master_user='repl_user',
master_password='repl_passwd',
master_log_file='mysql-bin.000003',
master_log_pos=333;
#启动服务，两台机器上都执行
start slave;
#查看是否运行正常
show slave statusG;
二、配置keepalived
在mysql001上：
vi /etc/keepalived/keepalived.conf
#######################################################
! Configuration File for keepalived
global_defs {
notification_email {
2011820123@qq.com
}
notification_email_from keepalived@localhost
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_DEVEL
}
vrrp_script check_mysqld { #设置检测脚本
script "/etc/keepalived/check.sh" #指定检测脚本的存放位置
interval 2 #间隔时间
weight -51 #权重，降权51
}
vrrp_instance VI_1 {
state BACKUP #注意，这里两台服务器都要是BACKUP
interface em1
virtual_router_id 60
priority 100
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
check_mysqld
}
virtual_ipaddress {
192.168.1.100 #虚拟IP
}
}
#######################################################
在mysql002上：
vi /etc/keepalived/keepalived.conf
#######################################################
! Configuration File for keepalived
global_defs {
notification_email {
2011820123@qq.com
}
notification_email_from keepalived@localhost
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_DEVEL
}
vrrp_script check_mysqld {
script "/etc/keepalived/check.sh"
interval 2
weight -51
}
vrrp_instance VI_1 {
state BACKUP #注意，这里两台服务器都要是BACKUP
interface em1
virtual_router_id 60
priority 90
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
check_mysqld
}
virtual_ipaddress {
192.168.1.100
}
}
#######################################################
"state BACKUP”，在这里两台服务器要都设为BACKUP，如果一台是MASTER，另一台是BACKUP，那么当MASTER恢复数据之后，主keepalived要切换到MASTER上，这样BACKUP上的数据就会丢失。因此在这两台mysql服务器上都设置成BACKUP，那么当MASTER恢复后，不会自动切换回去
#重启keepalived服务
service keepalived restart
五、编辑mysql监控脚本
vi /etc/keepalived/check.sh
#######################################################
#/bin/bash
live=`ss -tnlp | grep 3306 | wc -l` #检查mysql的3306端口是否存在
yes=`mysql -ucheck -pcheck -e "show slave statusG" | head -13 | tail -2 | awk -F: '{print $2}' | grep Yes | wc -l` #检查‘show slave status’是否正常
if [ $live -ge 1 ];then #如果$live大于等于1,执行下步操作
if [ $yes -eq 2 ];then #如果‘show slave status’出现两个yes，就退出
exit 0
else
/etc/init.d/keepalived restart #否则，重启keepalived服务，退出
exit 1
fi
else
/etc/init.d/keepalived restart #如果$live小于1，那么重启keepalived服务
exit 1
fi
#######################################################
#给脚本执行权限
chmod +x /etc/keepalived/check.sh
转自：
mysql双主+keepalived - 今日头条(www.toutiao.com)
http://www.toutiao.com/i6384270119261962753/