Keepalived实现Mysql高可用_keepalived 怎么实现mysql高可用

---

前言

  Mysql是一个市占率相当高的关系型数据库，做技术开发基本都会涉及到。那么，保证Mysql的高可用、数据安全性是一个相当重要的问题。这里介绍较为常用的一种基于keepalived的MySQl HA

---

1 MysqlHA概述

1.1 Mysql主从复制

  MySQL的HA离不开其主从复制的技术。主从复制是指一台服务器充当主数据库服务器(master)，另一台或多台服务器充当从数据库服务器(slave)，从服务器(slave)自动向主服务器(master)同步数据。实现MySQL的HA，需使两台服务器互为主从关系。

1.2 Keepalived

  Keepalived是基于VRRP（Virtual Router Redundancy Protocol，虚拟路由器冗余协议）协议的一款高可用软件。Keepailived有一台主服务器（master）和多台备份服务器（backup），在主服务器和备份服务器上面部署相同的服务配置，使用一个虚拟IP地址对外提供服务，当主服务器出现故障时，虚拟IP地址会自动漂移到备份服务器。

---

2 Mysql主从复制实现方法

2.1 一主一从

1. 集群规划

2. 配置master

• 修改hadoop103中MySQL的/usr/my.cnf配置文件

[mysqld]

#开启binlog
log_bin = mysql-bin
#binlog日志类型
binlog_format = row
#MySQL服务器唯一id
server_id = 1
1
2
3
4
5
6
7
8

• 重启hadoop103的MySQL服务

[zhouchen@hadoop103 ~]$ sudo service mysql restart
1

• 进入mysql客户端，执行以下命令，查看master状态

mysql>show master status;
1

3. 配置slave

• 修改hadoop104中MySQL的/usr/my.cnf配置文件

[mysqld]
#MySQL服务器唯一id
server_id = 2
#开启slave中继日志
relay_log=mysql-relay
1
2
3
4
5

• 重启hadoop104的MySQL服务

[zhouchen@hadoop104 ~]$ sudo service mysql restart
1

• 进入hadoop104的mysql客户端

执行以下命令

mysql>
CHANGE MASTER TO 	
MASTER_HOST='hadoop203',
MASTER_USER='root',
MASTER_PASSWORD='zhou59420',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=120;
1
2
3
4
5
6
7

• 启动slave

mysql> start slave;
1

• 查看slave状态

mysql>show slave status \G;
1

2.2 双主

1. 集群规划

2. 配置master

• 修改hadoop104中MySQL的/usr/my.cnf配置文件

[mysqld]

#开启binlog
log_bin = mysql-bin
#binlog日志类型
binlog_format = row
#MySQL服务器唯一id
server_id = 2
#开启slave中继日志
relay_log=mysql-relay
1
2
3
4
5
6
7
8
9
10

• 重启hadoop104的MySQL服务

[zhouchen@hadoop104 ~]$ sudo service mysql restart
1

• 进入hadoop104的MySQL客户端，执行以下命令，查看master状态

mysql>show master status;
1

3. 配置slave

• 修改hadoop103中MySQL的/usr/my.cnf配置文件

[mysqld]
#MySQL服务器唯一id
server_id = 1

#开启binlog
log_bin = mysql-bin
#binlog日志类型
binlog_format = row
#开启slave中继日志
relay_log=mysql-relay
1
2
3
4
5
6
7
8
9
10

• 重启hadoop103的MySQL服务

[zhouchen@hadoop103 ~]$ sudo service mysql restart
1

• 进入hadoop103的mysql客户端
  执行以下命令

mysql>
CHANGE MASTER TO 
MASTER_HOST='hadoop204',
MASTER_USER='root',
MASTER_PASSWORD='zhou59420',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=120;
1
2
3
4
5
6
7

• 启动slave

mysql> start slave;
1

• 查看slave状态

mysql>show slave status \G;
1

---

3 Keepalived实现方法

  须在hadoop103，hadoop104两台节点上部署Keepalived

3.1 部署hadoop103

1. 通过yum方式安装Keepalived

sudo yum install -y keepalived
1

2. 修改Keepalived配置文件/etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {
    router_id MySQL-ha
}
vrrp_instance VI_1 {
    state master #初始状态
    interface eth0 #网卡
    virtual_router_id 51 #虚拟路由id
    priority 100 #优先级
    advert_int 1 #Keepalived心跳间隔
    nopreempt #只在高优先级配置，原master恢复之后不重新上位
    authentication {
        auth_type PASS #认证相关
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.100 #虚拟ip
    }
} 

#声明虚拟服务器
virtual_server 192.168.1.100 3306 {
    delay_loop 6
    persistence_timeout 30
    protocol TCP
    #声明真实服务器
    real_server 192.168.139.134 3306 {
        notify_down /var/lib/mysql/killkeepalived.sh #真实服务故障后调用脚本
        TCP_CHECK {
            connect_timeout 3 #超时时间
            nb_get_retry 1 #重试次数
            delay_before_retry 1 #重试时间间隔
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

3. 编辑脚本文件/var/lib/mysql/killkeepalived.sh

#! /bin/bash
sudo service keepalived stop
1
2

4. 加执行权限

sudo chmod +x /var/lib/mysql/killkeepalived.sh
1

5. 启动Keepalived服务

sudo service keepalived start
1

6. 设置Keepalived服务开机自启

sudo chkconfig keepalived on
1

7. 确保开机时MySQL先于Keepalived启动

• 查看MySQL启动次序

sudo vim /etc/init.d/mysql
1

• 查看Keepalived启动次序

sudo vim /etc/init.d/keepalived
1

• 若Keepalived先于MySQL启动，则需要按照以下步骤设置二者启动顺序

  a. 修改/etc/init.d/mysql

sudo vim /etc/init.d/mysql
1

  b.重新设置mysql开机自启

sudo chkconfig --del mysql
sudo chkconfig --add mysql
sudo chkconfig mysql on
1
2
3

  c.修改/etc/init.d/keepalived

sudo vim /etc/init.d/keepalived
1

  d.重新设置keepalived开机自启

sudo chkconfig --del keepalived
sudo chkconfig --add keepalived
sudo chkconfig keepalivedon
1
2
3

3.2 部署hadoop104

1. 通过yum方式安装Keepalived

sudo yum install -y keepalived
1

2. 修改Keepalived配置文件/etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {
    router_id MySQL-ha
}
vrrp_instance VI_1 {
    state master #初始状态
    interface eth0 #网卡
    virtual_router_id 51 #虚拟路由id
    priority 100 #优先级
    advert_int 1 #Keepalived心跳间隔
    authentication {
        auth_type PASS #认证相关
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.100 #虚拟ip
    }
} 

#声明虚拟服务器
virtual_server 192.168.1.100 3306 {
    delay_loop 6
    persistence_timeout 30
    protocol TCP
    #声明真实服务器
    real_server 192.168.1.104 3306 {
        notify_down /var/lib/mysql/killkeepalived.sh #真实服务故障后调用脚本
        TCP_CHECK {
            connect_timeout 3 #超时时间
            nb_get_retry 1 #重试次数
            delay_before_retry 1 #重试时间间隔
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

3. 编辑脚本文件/var/lib/mysql/killkeepalived.sh

#! /bin/bash
sudo service keepalived stop
1
2

4. 加执行权限

sudo chmod +x /var/lib/mysql/killkeepalived.sh
1

5. 启动Keepalived服务

sudo service keepalived start
1

6. 设置Keepalived服务开机自启

sudo chkconfig keepalived on
1

7. 确保开机时MySQL先于Keepalived启动

• 查看MySQL启动次序

sudo vim /etc/init.d/mysql
1

• 查看Keepalived启动次序

sudo vim /etc/init.d/keepalived
1

• 若Keepalived先于MySQL启动，则需要按照以下步骤设置二者启动顺序

  a.修改/etc/init.d/mysql

sudo vim /etc/init.d/mysql
1

  b.重新设置mysql开机自启

sudo chkconfig --del mysql
sudo chkconfig --add mysql
sudo chkconfig mysql on
1
2
3

  c.修改/etc/init.d/keepalived

sudo vim /etc/init.d/keepalived
1

  d.重新设置keepalived开机自启

sudo chkconfig --del keepalived
sudo chkconfig --add keepalived
sudo chkconfig keepalivedon
1
2
3