MySQL主主+Keepalived高可用(一)：解决单点故障-低调大师

MySQL主主+Keepalived高可用(一)：解决单点故障

2017-11-15 604

在企业中，一般系统架构的瓶颈会出现在数据库这一部分，Mysql主从架构在很大程度上解决了这部分瓶颈，但是在Mysql主从同步的架构也存在很多问题。比如：

1. 关于数据写入部分(也就是主库)往往很难做到扩展，虽然很多大公司在逻辑业务方面就进行对数据的拆分，比如商品库存按照区域去拆分(一个区域走一个库存也就是一个主库,然后定时同步总的库存)，按照商品类型去划分(一个类型的商品走一套数据库)，但是这对于很多中小型公司来说实现起来还是比较困难的；

2. 主从同步一般都是一个主库，一旦主库出现问题,就有可能直接导致整个主从同步架构崩盘，虽然发现后也是可以慢慢恢复的，但是这个恢复时间对于很多公司来说是难以接受的。

今天的这篇博文主要给解决主库单点故障这个问题提供一个思路：

一台主库(我们称之为master-01)提供服务,只负责数据的写入；
拿出一台数据库服务器(我们称之为Master-02)资源做master-01主库的从库(之间做主从同步)；
两台主库之间做高可用,可以采用keepalived等方案(一定要保证master-01同时也要作为keepalived的主)；
程序在调用主库IP地址的地方写为高可用的VIP地址；
所有提供服务的从服务器与master-02进行主从同步；
建议采用高可用策略的时候,当master-01出现问题切换到master-02的时候,即使master-01恢复了,也不要让它去自动承接VIP地址,否则可能造成数据的混写

这样做可以在一定程度上保证主库的高可用，在一台主库down掉之后,可以在极短的时间内切换到另一台主库上(尽可能减少主库宕机对业务造成的影响)，减少了主从同步给线上主库带来的压力；但是也有几个不足的地方：

master-02可能会一直处于空闲状态(其实完全可以让它承担一部分从库的角色来负责一部分查询请求的)；
这样真正提供服务的从库要等master-02先同步完了数据后才能去master-02上去同步数据，这样可能会造成一定程度的同步延迟时间的加长；
如果master-01一旦恢复正常，会不会导致数据写入混乱(这个可以在keepalived中设置响应的规则,让其不”夺权”，我们认为的去调整操作即可。

架构的简易图如下：

方案简介

由keepalive实现VIP转移，从而实现高可用

软件环境

OS Version：Red Hat Enterprise Linux Server release 6.3 (Santiago)

MySQL Version: MySQL-server-5.6.21-1.el6.x86_64

KeepAlived Version: keepalived-1.2.13

DownLoad URL:http://www.keepalived.org/software/keepalived-1.2.13.tar.gz

部署步骤

1) 配置MySQL主主

详细步骤省略…………..

Master A>show processlist\G

*************************** 1. row ***************************

Id: 1

User: system user

Host:

db: NULL

Command: Connect

Time: 9654

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

*************************** 2. row ***************************

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 12773

State: Waiting for master to send event

Info: NULL

*************************** 3. row ***************************

Id: 1677

User: slave

Host: 192.168.56.102:34325

db: NULL

Command: Binlog Dump

Time: 9678

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

Master B>show processlist \G

*************************** 1. row ***************************

Id: 1

User: system user

Host:

db: NULL

Command: Connect

Time: 9771

State: Waiting for master to send event

Info: NULL

*************************** 2. row ***************************

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 9770

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

*************************** 3. row ***************************

Id: 12

User: slave

Host: 192.168.56.101:46709

db: NULL

Command: Binlog Dump

Time: 9746

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

2) 安装KeepAlived

#A和B主机都安装

]# tar -zxvf keepalived-1.2.13.tar.gz -C /opt/

]# cd /opt/keepalived-1.2.13/

]# mkdir /usr/local/keepalived

]# ./configure --prefix=/usr/local/keepalived/

]# make;make install

问题:configure报错

configure: error:

!!! OpenSSL is not properly installed on your system. !!!

!!! Can not include OpenSSL headers files. !!!

]# rpm -ivh keyutils-libs-devel-1.4-4.el6.x86_64.rpm

]# rpm -ivh libcom_err-devel-1.41.12-12.el6.x86_64.rpm

]# rpm -ivh pkgconfig-0.23-9.1.el6.x86_64.rpm

]# rpm -ivh libselinux-devel-2.0.94-5.3.el6.x86_64.rpm

]# rpm -ivh krb5-devel-1.9-33.el6.x86_64.rpm

]# rpm -ivh zlib-devel-1.2.3-27.el6.x86_64.rpm

]# rpm -ivh openssl-devel-1.0.0-20.el6_2.5.x86_64.rpm

3) 配置KeepAlived

Create Control File

默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件

#A主机和B主机都操作

]# mkdir /etc/keepalived

#cp安装生成的conf模板到/etc/keepalived

]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

Master A配置

a) Master A配置文件

#Master A配置文件

~]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

#全局配置

global_defs {

#表示keepalived在发生诸如切换操作时发送Email给哪些地址，邮件地址可以多个，每行一个

notification_email {

jixiang.yu@trekiz.com

}

#表示发送通知邮件时邮件源地址是谁

notification_email_from Alexandre.Cassen@firewall.loc

#表示发送email时使用的smtp服务器地址，这里可以用本地的sendmail来实现

smtp_server 127.0.0.1

#连接smtp连接超时时间

smtp_connect_timeout 30

#机器标识

router_id MySQL_HA

}

vrrp_instance MySQL-HA{

state BACKUP #state指定instance的初始状态，但这里指定的不算，还是得通过优先级竞选来确定。两台配置此处均是BACKUP。

interface eth1 #实例绑定的网卡，因为在配置虚拟IP的时候必须是在已有的网卡上添加的

virtual_router_id 51 #这里设置VRID，这里非常重要，相同的VRID为一个组，他将决定多播的MAC地址

priority 100 #设置本节点的优先级，优先级高的为master，如另外一个节点配置为90，那此节点就是master

advert_int 1 #检查间隔，默认为1秒

nopreempt #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress { #这里设置的就是VIP，也就是虚拟IP地址

192.168.56.111

}

virtual_server 192.168.56.111 3306{

delay_loop 2 #每个2秒检查一次real_server状态

lb_algo wrr

lb_kind DR

persistence_timeout 50 #会话保持时间

protocol TCP

real_server 192.168.56.101 3306{

weight 3

notify_down /usr/local/MySQL/bin/MySQL.sh #检测到服务down后执行的脚本

TCP_CHECK {

connect_timeout 3 #连接超时时间

nb_get_retry 3 #重连次数

delay_before_retry 3 #重连间隔时间

connect_port 3306 #健康检查端口

}

b) Master A编写notify_down脚本

~]# mkdir -p /usr/local/MySQL/bin/

~]# vim /usr/local/MySQL/bin/MySQL.sh

#!/bin/sh

pkill keepalived

注：此脚本是上面配置文件notify_down选项所用到的，keepalived使用notify_down选项来检查real_server的服务状态，当发现real_server服务故障时，便触发此脚本；我们可以看到，脚本就一个命令，通过pkill keepalived强制杀死keepalived进程，从而实现了MySQL故障自动转移。另外，我们不用担心两个MySQL会同时提供数据更新操作，因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP，而不是两台MySQL的IP+VIP

c) Master A启动KeepAlived服务

~]# /usr/local/keepalived/sbin/keepalived –D

~]# ps aux|grep keep

root 29423 0.0 0.1 39484 764 ? Ss 20:27 0:00 /usr/local/keepalived/sbin/keepalived -D

root 29424 0.0 0.4 43660 2056 ? S 20:27 0:00 /usr/local/keepalived/sbin/keepalived -D

root 29425 0.0 0.2 43660 1368 ? S 20:27 0:00 /usr/local/keepalived/sbin/keepalived -D

root 29439 0.0 0.1 103240 840 pts/2 S+ 20:27 0:00 grep keep

#可以看到VIP已经飘在eth1上

~]# ip a|grep eth1

3: eth1:< BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000

inet 192.168.56.101/24 brd 192.168.56.255 scope global eth1

inet 192.168.56.111/32 scope global eth1

d) Master A测试keepalived服务

#停止MySQL服务

~]# /etc/init.d/mysql stop

Shutting down MySQL..... [ OK ]

#查看vip，发现VIP已经不存在

~]# ip a|grep eth1

3: eth1:< BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000

inet 192.168.56.101/24 brd 192.168.56.255 scope global eth1

#查看keepalive进程消失

~]# ps -ef|grep keep

root 29537 4894 0 20:34 pts/2 00:00:00 grep keep

#监控日志

~]# tail -f /var/log/messages

Dec 3 20:34:08 MySQL1 Keepalived_healthcheckers[29424]: TCP connection to [192.168.56.101]:3306 failed !!!

Dec 3 20:34:08 MySQL1 Keepalived_healthcheckers[29424]: Removing service [192.168.56.101]:3306 from VS [192.168.56.111]:3306

Dec 3 20:34:08 MySQL1 Keepalived_healthcheckers[29424]: Executing [/usr/local/MySQL/bin/MySQL.sh] for service [192.168.56.101]:3306 in VS [192.168.56.111]:3306

Dec 3 20:34:08 MySQL1 Keepalived_healthcheckers[29424]: Lost quorum 1-0=1 > 0 for VS [192.168.56.111]:3306

Dec 3 20:34:08 MySQL1 Keepalived_healthcheckers[29424]: Remote SMTP server [127.0.0.1]:25 connected.

Dec 3 20:34:09 MySQL1 Keepalived[29423]: Stopping Keepalived v1.2.13 (12/03,2014)

Dec 3 20:34:09 MySQL1 Keepalived_vrrp[29425]: VRRP_Instance(MySQL-HA{) sending 0 priority

Dec 3 20:34:09 MySQL1 Keepalived_vrrp[29425]: VRRP_Instance(MySQL-HA{) removing protocol VIPs.

Dec 3 20:34:09 MySQL1 Keepalived_healthcheckers[29424]: Netlink reflector reports IP 192.168.56.111 removed

Dec 3 20:34:09 MySQL1 avahi-daemon[1430]: Server startup complete. Host name is MySQL1-62.local. Local service cookie is 1197773774.

Dec 3 20:34:09 MySQL1 avahi-daemon[1430]: Withdrawing address record for 192.168.56.111 on eth1.

Dec 3 20:34:10 MySQL1 avahi-daemon[1430]: Service "MySQL1-62" (/services/ssh.service) successfully established.

Master B配置

a) Master B配置文件

#跟DB1基本一致，但有三个地方不同：优先级为90、无抢占设置、real_server为本机IP

DB2~]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

notification_email {

jixiang.yu@trekiz.com

}

notification_email_from jixiang.yu@trekiz.com

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id MySQL_HA

}

vrrp_instance MySQL-HA{

state BACKUP

interface eth1

virtual_router_id 51

priority 90

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.56.111

}

virtual_server 192.168.56.111 3306{

delay_loop 2

lb_algo wrr

lb_kind DR

persistence_timeout 50

protocol TCP

real_server 192.168.56.102 3306{

weight 3

notify_down /usr/local/MySQL/bin/MySQL.sh

TCP_CHECK {

connect_timeout 10

nb_get_retry 3

delay_before_retry 3

connect_port 3306 #健康检查端口

}

b) Master B编写notify_down脚本

~]# mkdir -p /usr/local/MySQL/bin/

~]# vim /usr/local/MySQL/bin/MySQL.sh

#!/bin/sh

pkill keepalived

c) Master B启动KeepAlived服务

~]# /usr/local/keepalived/sbin/keepalived –D

~]# ps aux|grep keep

root 2855 0.0 0.1 39484 764 ? Ss 09:56 0:00 /usr/local/keepalive/sbin/keepalived -D

root 2856 0.0 0.3 41588 1992 ? S 09:56 0:00 /usr/local/keepalive/sbin/keepalived -D

root 2857 0.0 0.2 41588 1288 ? S 09:56 0:00 /usr/local/keepalive/sbin/keepalived -D

root 2865 0.0 0.1 103240 840 pts/2 S+ 09:56 0:00 grep keep

#可以看到VIP已经飘在eth1上

~]# ip a|grep eth1

3: eth1:< BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000

inet 192.168.56.102/24 brd 192.168.56.255 scope global eth1

inet 192.168.56.111/32 scope global eth1

d) Master B测试keepalived服务

#停止MySQL服务

~]# /etc/init.d/mysql stop

Shutting down MySQL..... [ OK ]

#查看vip，发现VIP已经不存在

~]# ip a|grep eth1

3: eth1:< BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000

inet 192.168.56.101/24 brd 192.168.56.255 scope global eth1

#查看keepalive进程消失

~]# ps -ef|grep keep

root 29537 4894 0 20:34 pts/2 00:00:00 grep keep

#监控日志

~]# tail -f /var/log/messages

Dec 4 10:35:53 MySQL2 Keepalived_healthcheckers[6733]: TCP connection to [192.168.56.102]:3306 failed !!!

Dec 4 10:35:53 MySQL2 Keepalived_healthcheckers[6733]: Removing service [192.168.56.102]:3306 from VS [192.168.56.111]:3306

Dec 4 10:35:53 MySQL2 Keepalived_healthcheckers[6733]: Executing [/usr/local/MySQL/bin/MySQL.sh] for service [192.168.56.102]:3306 in VS [192.168.56.111]:3306

Dec 4 10:35:53 MySQL2 Keepalived_healthcheckers[6733]: Lost quorum 1-0=1 > 0 for VS [192.168.56.111]:3306

Dec 4 10:35:53 MySQL2 Keepalived_healthcheckers[6733]: Remote SMTP server [127.0.0.1]:25 connected.

Dec 4 10:35:53 MySQL2 Keepalived[6732]: Stopping Keepalived v1.2.13 (11/24,2014)

Dec 4 10:35:53 MySQL2 Keepalived_vrrp[6734]: VRRP_Instance(MySQL-HA{) sending 0 priority

Dec 4 10:35:53 MySQL2 Keepalived_vrrp[6734]: VRRP_Instance(MySQL-HA{) removing protocol VIPs.

Dec 4 10:35:53 MySQL2 Keepalived_healthcheckers[6733]: Netlink reflector reports IP 192.168.56.111 removed

Dec 4 10:35:54 MySQL2 avahi-daemon[1387]: Withdrawing address record for 192.168.56.111 on eth1.

4) MySQL Client使用VIP连接数据库

~]# mysql -u trekiz -h 192.168.56.111 -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2622

Server version: 5.6.21-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

5) 配置init.d/keepalived脚本

cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/

cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

cp /usr/local/keepalived/sbin/keepalived /sbin/

~]# /etc/init.d/keepalived -h

Usage: /etc/init.d/keepalived {start|stop|reload|restart|condrestart|status}

6) KeepAlived Failover Test

a) Master A,B都启动keepalived,vip在A上,此时A主机pkill keepalived

mysql> show master status;

+---------------------+----------+--------------+------------------+-------------------+

+---------------------+----------+--------------+------------------+-------------------+

| master_slave.000031 | 532 | | | |

+---------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> show master status;

ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> show master status;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 168

Current database: *** NONE ***

+-----------------+----------+--------------+------------------+-------------------+

+-----------------+----------+--------------+------------------+-------------------+

| master_a.000021 | 532 | | | |

+-----------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

结果

VIP就会切换到Master B上面去

b) 启动Master A的keepalived,vip在B上,B主机pkill keepalived

mysql> show master status;

+-----------------+----------+--------------+------------------+-------------------+

+-----------------+----------+--------------+------------------+-------------------+

| master_a.000021 | 532 | | | |

+-----------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> show master status;

ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> show master status;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 2845

Current database: *** NONE ***

+---------------------+----------+--------------+------------------+-------------------+

+---------------------+----------+--------------+------------------+-------------------+

| master_slave.000031 | 532 | | | |

+---------------------+----------+--------------+------------------+-------------------+

1 row in set (0.08 sec)

结果

VIP就会切换到Master A上面去

c) Master A,B都启动keepalived,vip在A上,此时A主机stop mysql

结果

VIP就会切换到Master B上面去

d) 启动Master A的keepalived,vip在B上,B主机stop mysql

结果

VIP就会切换到Master A上面去

本文转自ljianbing51CTO博客，原文链接：http://blog.51cto.com/ljianbing/1617272 ，如需转载请自行联系原作者

微信关注我们

原文链接：https://yq.aliyun.com/articles/547599

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

Exchange2013 企业部署解决方案-Part 2

我们之前已经准备好了Exchange2013服务器前期安装架构准备工作。在这一部分，我们将Exchange 2013部署　配置工作使用GUI和命令行。我们将首先安装邮箱角色，其次由CAS服务器。部署Exchange 2013 RTM：命令行安装运行CMD （第一个邮件服务器MBX1） 1 Setup.exe /mode:Install /role:Mailbox /OrganizationName:HEWcantgis /InstallWindowsComponents /MdbName:DB1 /DbFilePath:”G:\Databases\DB1” /LogFilePath:”G:\Databases\DB1” /IAcceptExchangeServerLicenseTerms 命令解析： “使用/ InstallWindowsComponents”将继续与Exchange 2013的安装所有必需的功能。 “/ MdbName的”指定名称的第一个邮箱数据库（使用此参数，如果你不希望安装程序创建的数据库了随机名称，如“邮箱数据库0981237234”） “/ DBFileP...

2017-11-16

488

在当前经济形势下，可以量化的“投资回报”无疑是最具说服力的。所以在IMPACT 2009的演讲和专访中，Sandy Carter都列举了一系列具体的数据来说明IBM在SOA市场取得的成绩以及坚定不移的态度，同时也通过典型客户的案例来说明IBM提出的SOA解决方案不仅易于实践，而且可以帮助企业“转危为机”。 Sandy Carter表示，凭借8,000多名客户和7,400个商业合作伙伴，IBM 继续巩固其在 SOA 市场的领导地位。行业分析机构 Wintergreen 也在近期宣布，IBM 在全球 SOA 市场上占有70%的份额，较上年增长了6%。作为“Smarter Work Require Smart SOA”这一话题的佐证，IBM还宣布了这样一组调查数据：低效率的工作流程将导致企业中的每位员工每周浪费5.3小时，三分之二的员工认为同行可以帮助他们更好地完成工作，但不知道如何找到他们；42%的人至少每周会有一次是在错误信息的指导下做出决策；91%的CEO表示他们需要变革企业的运作方式。在51CTO推出的IMPACT 2009报道专题 http://www.51cto.com/ex...

2017-11-16

531

资源下载

更多资源

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。