前言

由于最近要使用Mysql数据库，而目前公司服务器与业务有限，于是只使用了一台Mysql。所以，问题很明显，如果这台Mysql坏了，那么将会影响整个公司的业务，所以考虑做Mysql的高可用方案。目前，Mysql的高可用方案很多，这里选择Keepalived+Mysql实现高可用。

环境介绍



Mysql主主配置

# 在Master和Slave执行[root@node1 ~]# yum install -y mysql-server mysql #安装Mysql[root@node1 ~]# service mysqld start[root@node1 ~]# mysqladmin -u root password 123.com #为Mysql的root用户设置密码[root@node1 ~]# vim /etc/my.cnf #编辑Mysql配置文件，加入如下内容[mysqld]server-id = 1 #Slave这台设置2log-bin = mysql-binbinlog-ignore-db = mysql,information_schema #忽略写入binlog日志的库auto-increment-increment = 2 #字段变化增量值auto-increment-offset = 1 #初始字段ID为1slave-skip-errors = all[root@node1 ~]# service mysqld restart

注：先查看Master和Slave上的log bin日志和pos值位置

[root@node1 ~]# mysql -u root -p123.commysql> show master status;

[root@node2 ~]# mysql -u root -p123.commysql> show master status;

注：在Master上操作如下：[root@node1 ~]# mysql -u root -p123.commysql> grant all on *.* to '
root'
@'
%'
identified by '
123.com'
with grant option;
mysql> flush privileges;
mysql> change master to -> master_host='
192.168.1.160'
, -> master_user='
root'
, -> master_password='
123.com'
, -> master_log_file='
mysql-bin.000001'
, # 根据对端实际的log bin填写 -> master_log_pos=106;
# 根据对端实际的Pos值填写

注：在Slave上操作如下：[root@node2 ~]# mysql -u root -p123.commysql> grant all on *.* to '
root'
@'
%'
identified by '
123.com'
with grant option;
mysql> flush privileges;
mysql> change master to -> master_host='
192.168.1.159'
, -> master_user='
root'
, -> master_password='
123.com'
, -> master_log_file='
mysql-bin.000001'
, # 根据对端实际的log bin填写 -> master_log_pos=335;
# 根据对端实际的Pos值填写

主主同步配置完毕，在Master和Slave上分别查看同步状态，Slave_IO和Slave_SQL是YES说明主主同步成功。mysql> show slave statusG

主主同步测试

使用客户端连接任意一台Mysql，进行创库、创表、插入字段、删除表、删库等操作，另外一台Mysql都能进行同步。

在这里，进行简单一些的测试，先在Master上创建一个新库，名为main

然后到Slave上查看是否有名为main的库

接下来在slave把名为main的库删了，会发现master上的main也没有了。到此，主主同步完成，

Keepalived高可用配置

注： 在Master和Slave执行，Keepalived必须和Mysql安装在同一台机器上# 安装Keepalived[root@node1 ~]# yum install -y pcre-devel openssl-devel popt-devel[root@node1 ~]# wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz[root@node1 ~]# tar zxvf keepalived-1.2.7.tar.gz[root@node1 ~]# cd keepalived-1.2.7[root@node1 ~]# ./configure --prefix=/usr/local/keepalived[root@node1 ~]# make && make install# 将Keepalived配置成系统服务[root@node1 ~]# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/[root@node1 ~]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/[root@node1 ~]# mkdir /etc/keepalived/[root@node1 ~]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/[root@node1 ~]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

# 修改Master上的Keepalived配置文件[root@node1 ~]# vim /etc/keepalived/keepalived.conf! Configuration File for keepalivedglobal_defs {
router_id MYSQL_HA}
vrrp_instance VI_1 {
state BACKUP # 两台配置此处均是BACKUP interface eth0 virtual_router_id 51 priority 100 # 优先级，另一台改为90 advert_int 1 nopreempt # 不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置 authentication {
auth_type PASS auth_pass 1111 }
virtual_ipaddress {
192.168.1.208 # VIP }
}
virtual_server 192.168.1.208 3306 {
# VIP的具体设置 delay_loop 2 # 每个2秒检查一次real_server状态 #lb_algo wrr # LVS算法 #lb_kind DR # LVS模式 persistence_timeout 60 # 会话保持时间 protocol TCP real_server 192.168.1.159 3306 {
weight 3 notify_down /usr/local/keepalived/mysql.sh #检测到服务down后执行的脚本 TCP_CHECK {
connect_timeout 10 # 连接超时时间 nb_get_retry 3 # 重连次数 delay_before_retry 3 # 重连间隔时间 connect_port 3306 # 健康检查端口 }
}
}

# 修改Slave上的Keepalived配置文件[root@node2 ~]# vim /etc/keepalived/keepalived.conf! Configuration File for keepalivedglobal_defs {
router_id MYSQL_HA}
vrrp_instance VI_1 {
state BACKUP interface eth0 virtual_router_id 51 priority 90 advert_int 1 authentication {
auth_type PASS auth_pass 1111 }
virtual_ipaddress {
192.168.1.208 }
}
virtual_server 192.168.1.208 3306 {
delay_loop 2 #lb_algo wrr #lb_kind DR persistence_timeout 60 protocol TCP real_server 192.168.1.160 3306 {
weight 3 notify_down /usr/local/keepalived/mysql.sh TCP_CHECK {
connect_timeout 10 nb_get_retry 3 delay_before_retry 3 connect_port 3306 }
}
}

# 配置故障切换脚本,Master和Slave上执行[root@node1 ~]# vim /usr/local/keepalived/mysql.sh#!/bin/shpkill keepalived[root@node1 ~]# chmod +x /usr/local/keepalived/mysql.sh

# 此前两台Mysql已经启动，这里只用启动Keepalived(Master和Slave上执行)[root@node1 ~]# service keepalived start

高可用性测试

# 在Master上查看VIP是否存在[root@node1 ~]# ip addr

# 停止Master上的Keepalived，查看VIP是否漂移到了Slave上[root@node1 ~]# service keepalived stop[root@node2 ~]# ip addr

# 开启Master上的Keepalived，查看Master是否抢占了VIP，没有抢占Slave的VIP说明成功[root@node1 ~]# service keepalived start

# 停止Slave上的Mysql,发现Slave上Keepalived会自动停止，并且VIP已经漂移到Master上[root@node2 ~]# service mysqld stop[root@node2 ~]# ps -ef |grep mysqldroot 4589 2159 0 06:00 pts/2 00:00:00 grep mysqld[root@node2 ~]# ps -ef |grep keepalivedroot 4591 2159 0 06:00 pts/2 00:00:00 grep keepalived[root@node1 ~]# ip addr

 <喎"/kf/ware/vc/" target="_blank" class="keylink">vcD4NCjxoMyBpZD0="总结">总结

到此，基于Keepalived实现Mysql高可用全部完成。这个方案可以为Mysql提供容错，缺点也很明显，只有一台Mysql对外提供服务，另外一台只能起到备份的功能，无法实现类似于负载均衡的效果。当然，也可以再加一些中间件完成负载均衡，读写分离等功能，这里就不详解说明了……