MysqL的高可用方案一般有如下几种:
keepalived+双主,MHA,MMM,Heartbeat+DRBD,PXC,galera Cluster
比较常用的是keepalived+双主,MHA和PXC。
对于小公司,一般推荐使用keepalived+双主,简单。
下面来部署一下
配置环境:
角色 主机IP 主机名 *** 作系统版本 软件版本
VIP 192.168.244.10
master1 192.168.244.145 master1 CentOS7.1 MysqL 5.6.26,Keepalived v1.2.13
master2 192.168.244.146 master2 CentOS7.1 MysqL 5.6.26,Keepalived v1.2.13
一、 配置MysqL双主复制环境
1. 修改配置文件
master1中有关复制的配置如下:
[MysqLd]log-bin=MysqL-binserver-ID=1log_slave_updates1
master2
2read_only1
2. 创建复制用户
master1中创建:
CREATE USER 'repl'@192.168.244.146' IDENTIFIED BY MysqL';GRANT REPliCATION SLAVE ON *.* TO ';
master2中创建:
192.168.244.145';
3. 执行CHANGE MASTER TO语句
因是从头搭建MysqL主从复制集群,所以不需要获取全局读锁来得到二进制日志文件的位置,直接根据show master status的输出来确认。
master1上执行:
CHANGE MASTER TO MASTER_HOST=,MASTER_USERMysqL-bin.00000464729;
master2上执行:
CHANGE MASTER MysqL-bin.00000368479;
4. 分别在两个节点上执行start slave语句并通过show slave status\G查看复制是否搭建成功。
成功标准:
Slave_IO_Running: YesSlave_sql_Running: Yes
二、 配置Keepalived
1. 安装Keepalived
# yum install -y keepalived
当然,也可直接编译官方的源码包。
2. 修改Keepalived的配置文件
master1
[root@master1 ~]# vim /etc/keepalived/keepalived.conf
vrrp_script chk_MysqL { script "/etc/keepalived/check_MysqL.sh" interval 30 #设置检查间隔时长,可根据自己的需求自行设定}vrrp_instance VI_1 { state BACKUP #通过下面的priority来区分MASTER和BACKUP,也只有如此,底下的nopreempt才有效 interface eno16777736 virtual_router_ID 51 priority 100 advert_int nopreempt #防止切换到从库后,主keepalived恢复后自动切换回主库 authentication { auth_type PASS auth_pass 1111 } track_script { chk_MysqL } virtual_ipaddress { 192.168.244.10/24 }}
关于keepalived的参数的详细介绍,可参考:LVS+Keepalived搭建MyCAT高可用负载均衡集群
其中,/etc/keepalived/check_MysqL.sh内容如下:
#!/bin/bash ###判断如果上次检查的脚本还没执行完,则退出此次执行if [ `ps -ef|grep -w ""grep|grep -v wc2 -l` -gt then ]; exit 0fi MysqL_con'=MysqL -uroot -p123456error_log/etc/keepalived/logs/check_MysqL.err=" ###定义一个简单判断MysqL是否可用的函数function excute_query { ${MysqL_con} select 1;-e "2 ${error_log}} ###定义无法执行查询,且MysqL服务异常时的处理函数>> service_error { echo`date -e "` -----MysqL service error,Now stop keepalived-----+%F %H:%M:%s" ${error_log} service keepalived stop >> ${error_log} &>>echoDB1 keepalived 已停止 "DB1 keepalived 已停止,请及时处理!|mail -s "126 slowtech@\n---------------------------------------------------------\n.com ${error_log}} ###定义无法执行查询,但MysqL服务正常的处理函数 query_error { ` -----query error,but MysqL service ok,retry after 30s-----sleep30 excute_query if0 [ $? -ne then ];` -----still can't execute query----- ${error_log} ###对DB1设置read_only属性 ` -----set read_only = 1 on DB1----- ${error_log} MysqL_con set global read_only = 1;-e ${error_log} ###kill掉当前客户端连接 ` -----kill current clIEnt thread----- ${error_log} rmkill -f /tmp/null.sql &>/dev/ ###这里其实是一个批量kill线程的小技巧 MysqL_con select concat("kill ",ID,";") from information_schema.PROCESSList where command="query" or command="Execute" into outfile "/tmp/kill.sql";-e MysqL_con source /tmp/kill.sql-e "2 ###给kill一个执行和缓冲时间 ###关闭本机keepalived ` -----stop keepalived----- ${error_log} service keepalived stop else&>>` -----query ok after 30s----- } ###检查开始: 执行查询excute_query service MysqLd status null&>/dev/ service_error else query_error fififor
通过具体的查询语句来判断数据库服务的可用性,如果查询失败,则判断MysqLd进程本身的状态,如果不正常,则直接停止当前节点的keepalived,将VIP转移到另外一个节点,如果正常,则等待30s,再次执行查询语句,还是失败,则将当前的master节点设置为read_only,并kill掉当前的客户端连接,然后停止当前的keepalived。
master2
[root@master2 ~]# vim /etc/keepalived/keepalived.conf
! Configuration file keepalived vrrp_instance VI_1 { state BACKUP interface eno16777736 virtual_router_ID 51901 authentication { auth_type PASS auth_pass 1111 } notify_master sh/etc/keepalived/notify_master_MysqL. #此条指令告诉keepalived发现自己转为MASTER后执行的脚本 virtual_ipaddress { 192.168244.10.24/ }}bash###当keepalived监测到本机转为MASTER状态时,执行该脚本 change_log
其中,/etc/keepalived/notify_master_MysqL.sh的内容如下:
#!/bin/state_change.logMysqL_con=/etc/keepalived/logs/'=` -----keepalived change to MASTER----- $change_log slave_info() { ###统一定义一个函数取得slave的position、running、和log_file等信息 ###根据函数后面所跟参数来决定取得哪些数据 if1 [ $ slave_stat = slave_status ];show slave status\G;=`${MysqL_con} -e egrepSlave_IO_Running|Slave_sql_Running -` Slave_IO_Runningecho=`awk $slave_stat|{print } ` Slave_sql_Running{print }=`` elif1 [ $2 = log_file -a $ log_file_pos = pos ];Master_Log_file|Read_Master_Log_Pos|Relay_Master_Log_file|Exec_Master_Log_Pos=`${MysqL_con} -e ` Master_Log_fileecho=`` Read_Master_Log_Pos $log_file_pos|` Relay_Master_Log_file=`{print }=`` Exec_Master_Log_Pos{print }=`} action() { ###经判断应该&可以切换时执行的动作 ` -----set read_only = 0 on DB2----- $change_log ###解除read_only属性 ${MysqL_con} set global read_only = 0;-e $change_log DB2 keepalived转为MASTER状态,线上数据库切换至DB2DB2 keepalived change to MASTER\ slowtech@---------------------------------------------------------\n $change_log} slave_info slave_statusif i [ $Slave_sql_Running = Yes ];0= #一个计数器 slave_info log_file pos ###判断从master接收到的binlog是否全部在本地执行(这样仍无法完全确定从库已追上主库,因为无法完全保证io_thread没有延时(由网络传输问题导致的从库落后的概率很小) until $Exec_Master_Log_Pos ] [ $Master_Log_file = $Relay_Master_Log_file -a $Read_Master_Log_Pos =doif 10 [ $i -lt #将等待exec_pos追上read_pos的时间限制为10s ];` -----Relay_Master_Log_file=$Relay_Master_Log_file,Exec_Master_Log_Pos=$Exec_Master_Log_Pos is behind Master_Log_file=$Master_Log_file,Read_Master_Log_Pos=$Read_Master_Log_Pos,wait......"read_pos i >> $change_log #输出消息到日志,等待exec_pos=)) =$(($i+ slave_info log_file pos elseThe waits time is more than 10s,Now force change. Master_Log_file=$Master_Log_file Read_Master_Log_Pos=$Read_Master_Log_Pos Relay_Master_Log_file=$Relay_Master_Log_file Exec_Master_Log_Pos=$Exec_Master_Log_Pos $change_log action exit 0fi done action slave_info log_file pos fiDB2's slave status is wrong,1)"> $change_log action当原主恢复正常后,如何将VIP从master2切回到master1中呢?
整个脚本的逻辑是让从的Exec_Master_Log_Pos尽可能的追上Read_Master_Log_Pos,它给了10s的限制,如果还是没有追上,则直接将master2设置为主(通过解除read_only属性),其实这里面还是有待商榷的,譬如10s的限制是否合理,还是一定需要Exec_Master_Log_Pos=Read_Master_Log_Pos才切换。
bash###手动执行将主库切换回DB1的 *** 作 MysqL_con
#!/bin/'=` -----change to BACKUP manually----- "state_change.log >> /etc/keepalived/logs/` -----set read_only = 1 on DB2-----state_change.log$MysqL_con 2-e state_change.log ###kill掉当前客户端连接>> /etc/keepalived/logs/###这里其实是一个批量kill线程的小技巧$MysqL_con $MysqL_con -e ###给kill一个执行和缓冲时间 ###确保DB1已经追上了,下面的repl为复制所用的账户,-e h后跟DB1的内网IPlog_file_pos-168.244=`MysqL -urepl -pMysqL -h192.145.`Master_Log_file -e `Read_Master_Log_Pos=``Relay_Master_Log_file=``Exec_Master_Log_Pos=``=`until $Relay_Master_Log_file ] [ $Read_Master_Log_Pos = $Exec_Master_Log_Pos -a $Master_Log_file =do1 ` -----DB1 Exec_Master_Log_Pos($exec_pos) is behind Read_Master_Log_Pos($read_pos),1)">state_change.log ###然后解除DB1的read_only属性` -----set read_only = 0 on DB1-----ssh244.145 MysqL -uroot -p123456 -e "set global read_only = 0;" && /etc/init.d/keepalived start 'state_change.log ###重启DB2的keepalived使VIP漂移到DB1 ` -----make VIP move to DB1-----state_change.logstate_change.log /sbin/service keepalived restart &>> /etc/keepalived/logs/DB2 keepalived转为BACKUP状态,线上数据库切换至DB1DB2 keepalived change to BACKUP--------------------------------------------------\n"总结: >> /etc/keepalived/logs/state_change.log
25
1. /etc/keepalived/check_MysqL.sh和/etc/keepalived/notify_master_MysqL.sh必须加可执行权限。
如果前者没有加可执行权限,则master1上将不会绑定VIP,日志直接提示如下信息:
May 14 37:09:3165 master1 Keepalived_vrrp[]: VRRP_Instance(VI_1) Entering BACKUP STATEMay 31652]: VRRP sockpool: [ifindex(112),proto(0),unicast(10),fd(11,)]May 503165 master1 Keepalived_vrrp[in]: VRRP_Instance(VI_1) Now -D -d -S 0 FAulT state
2. 在Keepalived中有两种模式,分别是master->backup模式和backup->backup模式,这两种模式有什么区别呢?
在master->backup模式下,一旦主库宕掉,虚拟IP会自动漂移到从库,当主库修复后,keepalived启动后,还会把虚拟IP抢过来,即使你设置nopreempt(不抢占)的方式抢占IP的动作也会发生。在backup->backup模式下,当主库宕掉后虚拟IP会自动漂移到从库上,当原主恢复之后重启keepalived服务,并不会抢占新主的虚拟IP,即使是优先级高于从库的优先级别,也不会抢占IP。为了减少IP的漂移次数,生产中我们通常是把修复好的主库当做新主库的备库。
3. 本文是在MySQL主库高可用 -- 双主单活故障自动切换方案 基础上,结合自己对于MysqL的理解整理的。原文的脚本直接执行有点问题,思路有有点瑕疵,于是结合自己的实际环境,重新修改了一把。
4. 在测试的过程中,有以下几点需要注意:
1> master1检测脚本的逻辑是如果MysqL的服务不可用,则通过service keepalived stop命令来关闭keepalived,但在实际测试的过程中,却出现了即便执行了service keepalived stop命令,keepalived进程依然没有停止,导致MysqL的服务虽然不可用了,但VIP并不没有漂移到master2上。
优化方案:在执行service keepalived stop后,等待5s,再次检测keepalived的状态,如果keepalived没有关闭,则直接kill掉。
2> keepalived的日志默认是输出到/var/log/messages中,这样不便于查看。如何自定义keepalived的日志输出文件呢?
如果是用service启动的,修改/etc/sysconfig/keepalived文件
KEEPAliVED_OPTIONS="0
如果不是,则启动的时候指定以上参数,如:
/usr/local/keepalived/sbin/keepalived -d -D -S local0.
修改/etc/syslog.conf
# keepalived -S * /var/log/keepalived.log
重启syslog
RHEL 5&6:service syslog restart
RHEL 7:service rsyslog restart
总结
以上是内存溢出为你收集整理的基于keepalived搭建MySQL的高可用集群全部内容,希望文章能够帮你解决基于keepalived搭建MySQL的高可用集群所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)