mysql视图不能更新（mysql双主keepalived）

简单原理

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 status\G;

二、配置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 status\G" | 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

,