一、环境介绍:

1.1服务器角色配置:

mysql-master 10.0.0.101(外)  172.168.1.101(内) 
mysql-slave  10.0.0.103 (外) 172.168.1.103(内)
mysql-router01   10.0.0.102 (外) 172.168.1.102(内)
mysql-router02   10.0.0.104 (外) 172.168.1.104(内)
jumpserver      10.0.0.128 (外) 172.168.1.128(内)

1.2配置数据库的一主两从库

Slave01 和master都是允许读写的,slave02提供只读
同时MySQL的主从复制都是事先配置成功的(此环境是开启GTID模式的复制)

1.3部署web应用jumpserver

jumpserver web 10.0.0.128(外) 172.168.1.128(内)
jumpsever配置文件如下:

[root@localhost jumpserver]# cat /opt/jumpserver/jumpserver.conf 
[base]
url = http://10.0.0.128
key = f1tty6elu8h03x2k
ip = 0.0.0.0  ###默认监听任何IP
port = 8000  ###为jumpsever的默认的web端口
log = debug

[db]
engine = mysql
host = 172.168.1.20  ####为虚拟VIP
port = 7001     ####为mysqlroute读写模式的默认端口
####jumpserver的连接数据库的地址
user = jumpserver
password = jumpserver
database = jumpserver

[mail]
mail_enable = 1
email_host = smtp@163.com
email_port = 25
email_host_user = tofox@163.com
email_host_password = weuidnre
email_use_tls = False
email_use_ssl = False

[connect]
nav_sort_by = ip

启动jumpsever服务:

[root@localhost jumpserver]# /opt/jumpserver/service.sh stop
[root@localhost jumpserver]# /opt/jumpserver/service.sh start
[root@localhost jumpserver]# /opt/jumpserver/service.sh status
jumpserver is running...                                   [  OK  ]
[root@localhost jumpserver]#
[root@localhost jumpserver]# ps -ef|grep python
root       2443   2441  0 May14 ?        00:00:00 /bin/bash -c ulimit -S -c 0 >/dev/null 2>&1 ; python ./run_server.py
root       2444   2443  0 May14 ?        00:00:04 python ./run_server.py
root       9081   8974  0 14:50 pts/0    00:00:00 grep python

1.4安装keepalived和mysql-router

mysql-router01 10.0.0.102 mysql-router02 10.0.0.104
在10.0.0.102 机器上安装keepalived 和mysql-router
在10.0.0.104 机器上安装keepalived 和mysql-router

[root@master01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
slave01 172.168.1.103
master01 172.168.1.101
router01  172.168.1.102
router02  172.168.1.104
slave02 172.168.1.105

重要提示:
为快速实现环境达到演示效果,建议6台台虚拟机先关闭selinux和iptables,保证服务器的系统时间都是一致的,必须保证iptables不是开机自启动,安装keepalived和mysql-router后,要保证这2个服务是开启自启动的。

二、keepalived相关原理介绍:

Keepalived 是一种高性能的服务器高可用或热备解决方案,Keepalived可以用来防止服务器单点故障(单点故障是指一旦某一点出现故障就会导致整个系统架构的不可用)的发生,通过配合Nginx可以实现web前端服务的高可用。以下本文会介绍keepalived结合mysql-router实现数据库得高可用

Keepalived实现的基础是VRRP协议,Keepalived就是巧用VRRP协议来实现高可用性(HA)的.

VRRP(Virtual Router Redundancy Protocol)协议是用于实现路由器冗余的协议,VRRP协议将两台或多台路由器设备虚拟成一个设备,对外提供虚拟路由器IP(一个或多个),而在路由器组内部,如果实际拥有这个对外IP的路由器如果工作正常的话就是MASTER,或者是通过算法选举产生,MASTER实现针对虚拟路由器IP的各种网络功能,如ARP请求,ICMP,以及数据的转发等;其他设备不拥有该IP,状态是BACKUP,除了接收MASTER的VRRP状态通告信息外,不执行对外的网络功能。当主机失效时,BACKUP将接管原先MASTER的网络功能。

VRRP协议使用多播数据来传输VRRP数据,VRRP数据使用特殊的虚拟源MAC地址发送数据而不是自身网卡的MAC地址,VRRP运行时只有MASTER路由器定时发送VRRP通告信息,表示MASTER工作正常以及虚拟路由器IP(组),BACKUP只接收VRRP数据,不发送数据,如果一定时间内没有接收到MASTER的通告信息,各BACKUP将宣告自己成为MASTER,发送通告信息,重新进行MASTER选举状态。

三.Mysql-router相关原理介绍:

3.1.什么是mysql route

MySQL Router是处于应用client和dbserver之间的轻量级代理程序,它能检测,分析和转发查询到后端数据库实例,并把结果返回给client。是mysql-proxy的一个替代品。其架构图和功能如下:
keepalived + mysqlroute +mysql实现mysql高可用
(1)Router实现读写分离,程序不是直接连接数据库IP,而是固定连接到mysql router。MySQL Router对前端应用是透明的。应用程序把MySQL Router当作是普通的mysql实例,把查询发给MySQL Router,而MySQL Router会把查询结果返回给前端的应用程序。

(2)从数据库服务器故障,业务可以正常运行。由MySQL Router来进行自动下线不可用服务器。程序配置不需要任何修改。

(3)主数据库故障,由MySQL Router来决定主从自动切换,业务可以正常访问。程序配置不需要做任何修改。

3.2.读写分离原理

MySQL Router接受前端应用程序请求后,根据不同的端口来区分读写,把连接读写端口的所有查询发往主库,把连接只读端口的select查询以轮询方式发往多个从库,从而实现读写分离的目的。读写返回的结果会交给MySQL Router,由MySQL Router返回给客户端的应用程序。

3.3.Mysql router用途

MySQL Router的主要用途是读写分离,主主故障自动切换,负载均衡,连接池等。

3.4.Mysql router主主故障自动切换的坑

Mysql router主主故障切换功能经过测试没有问题,但是有一个比较大的坑需要注意
Mysql router的主主故障切换如果是运行在一主一从的情况下,从库作为主库的备份,这种情况是使用mysql router主主故障切换是没有问题的,但是在一主多从的情况下使用主主故障切换就会处在如下问题:mysql主库挂掉了,从库slave01和slave02却一直是同步的原来的主库master的IP地址,导致slave上的IO线程一直是connecting状态,导致slave02复制失败。

四.软件具体安装部署

10.0.0.102机器上安装mysql-router 和keepalived
此机器上的keepalived是主

yum -y install openssl-devel
 wget http://www.keepalived.org/software/keepalived-1.4.0.tar.gz
 tar xf keepalived-1.4.0.tar.gz  -C /usr/local/
 cd /usr/local/keepalived-1.4.0/
 ./configure
 make
 make install
  mkdir /etc/keepalived
 find /usr/local/keepalived-1.4.0/ -name "keepalived.conf"
 cp /usr/local/keepalived-1.4.0/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/ 
 find / -name "keepalived"
 cp /usr/local/etc/sysconfig/keepalived  /etc/sysconfig/
 cp /usr/local/keepalived-1.4.0/keepalived/etc/init.d/keepalived /etc/init.d/
 chmod +x /etc/init.d/keepalived 
 chkconfig keepalived on
 cp /usr/local/sbin/keepalived /usr/sbin/
 which keepalived
 cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.ori

下面的配置文件是master主keepalived的配置文件

vim /etc/keepalived/keepalived.conf
 [root@master01 ~]# cat /etc/keepalived/keepalived.conf
global_defs {
   notification_email {
   3056977536@qq.com
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   **router_id LVS_01**
}

vrrp_instance VI_1 {
   ** state MASTER**
    **interface eth1**
    virtual_router_id 51
    **priority 120**
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
    **172.168.1.20/24**
    }
}

 /etc/init.d/keepalived status
 /etc/init.d/keepalived start

同样的方式在10.0.0.104机器上安装mysql-router 和keepalived

此机器上的keepalived是从
下面是从keepalived机器的配置文件介绍:

[root@router02 ~]# cat /etc/keepalived/keepalived.conf
global_defs {
   notification_email {
   3056977536@qq.com
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
  ** router_id LVS_02**
}

vrrp_instance VI_1 {
**    state BACKUP
    interface eth1**
    virtual_router_id 51
   ** priority 80**
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
   ** 172.168.1.20/24**
    }
}

主从keepalived参数介绍:
先是主keepalived服务器:

global_defs
{
notification_email    #通知email,根据实际情况配置
{
admin@example.com
}
notification_email_from admin@example.com
smtp_server 127.0.0.1
stmp_connect_timeout 30
router_id node1         #节点名标识,主要用于通知中
}

vrrp_instance VI_NODE {
  state MASTER          #配置为主服务器
  interface eth0        #通讯网卡
  virtual_router_id 100 #路由标识
  priority 200          #优先级,0-254
  advert_int 5          #通知间隔,实际部署时可以设置小一点,减少延时

  authentication {
    auth_type PASS
    auth_pass 123456    #验证密码,用于通讯主机间验证
  }

  virtual_ipaddress {
    192.168.1.206       #虚拟ip,可以定义多个
  }
}

接下是从服务器设置:

global_defs {
  notification_email {
    admin@example.com
  }
  notification_email_from admin@example.com
  smtp_server 127.0.0.1
  stmp_connect_timeout 30
  router_id node2
}

vrrp_instance VI_NODE {
  state BACKUP           #与主服务器对应
  interface eth0         #从服务器的通信网卡
  virtual_router_id 100  #路由标识,和主服务器相同
  priority 100           #优先级,小于主服务器即可
  advert_int 5           #这里是接受通知间隔,与主服务器要设置相同

  authentication {
   auth_type PASS
    auth_pass 123456     #验证密码,与主服务器相同
  }

  virtual_ipaddress {
    192.168.1.206        #虚拟IP,也要和主服务器相同
  }
}

上面的设置是最基础的设置,实现的功能是如果主服务器的Keepalived停止服务(一般情况下服务器宕机),则将虚拟IP切换至从服务器,主服务器恢复后从新切换回主服务器。

4.1、Keepalived的配置文件介绍来实现虚拟VIP漂移

注意:1、两个keepalived.conf配置文件中添加的虚拟网卡要一样,并且不要和已使用服务器的内外网卡一样这样就实现不了心跳了。
可以是同一网段局域内网卡,也可以是同一网段外网网卡
2、当主keepalived应用开启的时候,是可以看到虚拟IP地址的

[root@router01 ~]# ps -ef|grep keepalived
root       9265      1  0 07:24 ?        00:00:00 keepalived -D
root       9267   9265  0 07:24 ?        00:00:00 keepalived -D
root       9268   9265  0 07:24 ?        00:00:05 keepalived -D
root       9517   9492  0 10:02 pts/3    00:00:00 grep keepalived
[root@router01 ~]# ip addr|grep 172.168.1.20
inet 172.168.1.20/24 scope global secondary eth1

这时从keepalived程序也开启,但是在从本地是grep不到虚拟IP地址的

[root@router02 ~]#  ps -ef|grep keepalived
root      13115      1  0 07:08 ?        00:00:00 keepalived -D
root      13117  13115  0 07:08 ?        00:00:00 keepalived -D
root      13118  13115  0 07:08 ?        00:00:01 keepalived -D
root      13271  13232  0 10:02 pts/2    00:00:00 grep keepalived
[root@router02 ~]# ip a|grep 172.168.1.20
[root@router02 ~]#

一旦主keepalived程序关闭或者是服务器挂掉,这时虚拟VIP就可以实现跳跃到从keepalived机器上。

[root@router01 ~]# /etc/init.d/keepalived stop
Stopping keepalived:                                   [  OK  ]
[root@router01 ~]# ps -ef|grep keepalived
root       9540   9492  0 10:21 pts/3    00:00:00 grep keepalived
[root@router01 ~]#

[root@router02 ~]# ip a|grep 172.168.1.20
    inet 172.168.1.20/24 scope global secondary eth1
[root@router02 ~]#

提示:
如果从负载均衡服务器grep不到虚拟网卡原因有:
1、本地服务器keepalived程序可能关闭,得restart从启才行,才能实现虚拟网卡的跳跃。
2、就是主负载均衡服务器keepalived程序没有关闭而导致
3、主负载均衡服务器只要keepalived服务启动就不会出现上述的问题。
4.服务器的iptables没关闭导致,或者是selinxu没关闭也有可能导致

4.2.安装mysql-router

10.0.0.102 10.0.0.104两个机器安装mysql-router

采用二进制安装mysql-router:

tar xf mysql-router-2.1.6-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local/
cd /usr/local/
ln -s mysql-router-2.1.6-linux-glibc2.12-x86-64bit mysql-router
mkdir /etc/mysql-route/
mkdir /data/log/mysql-route -p
cp /usr/local/mysql-router/share/doc/mysqlrouter/sample_mysqlrouter.conf /etc/mysql-route/mysqlrouter.conf
cp  /etc/mysql-route/mysqlrouter.conf  /etc/mysql-route/mysqlrouter.conf.ori

4.3配置文件介绍如下:

[root@router01 mysql-route]# cat mysqlrouter.conf
[DEFAULT]
##日志存放目录
logging_folder = /data/log/mysql-route
##插件存放目录
plugin_folder = /usr/local/mysql-router/lib/mysqlrouter
###配置文件存放目录
config_folder = /etc/mysql-route
###运行目录
runtime_folder = /var/run

[logger]
###日志运行级别
level = INFO

#[fabric_cache]
#address = your_fabric_node.example.com:32275
#user =
###主节点故障转移 
[routing:basic_failover]
#To be more transparent, use MySQL Server port 3306
##写节点地址
bind_address=172.168.1.20  ##{此IP地址为虚拟VIP}
##写节点端口
bind_port = 7001
##主库为读写模式
mode = read-write
###172.168.1.101为mysql 主 172.168.1.103为mysql从
#### 主节点地址:默认情况下第一台主数据库为写主库,当第一台主数据库DOWN机后,第二台数据库被提升为主库
destinations = 172.168.1.101:3306,172.168.1.103:3306

[routing:balancing]
bind_address=172.168.1.20
bind_port = 7002
connect_timeout = 3
max_connections = 1024
###为mysql从库(此处只负责读)
destinations = 172.168.1.105:3306
mode = read-only
#[routing:homepage_reads_fabric]
#bind_port = 7002
#destinations = fabric+cache:///group/homepage_group?allow_primary_reads=yes
#mode = read-only

#If no plugin is configured which starts a service, keepalive
#will make sure MySQL Router will not immediately exit. It is
#safe to remove once Router is configured.
[keepalive]
interval = 60

启动mysqlrouter服务

/usr/local/mysql-router/bin/mysqlrouter -c /etc/mysql-route/mysqlrouter.conf &

关闭iptables 保证mysql-router开机自启动

[root@router01 ~]# chkconfig --list|grep iptables
iptables        0:off   1:off   2:off   3:off   4:off   5:off   6:off
[root@router01 ~]# chkconfig  iptables off
[root@router01~]# vim /etc/rc.local 
#!/bin/sh
touch /var/lock/subsys/local
/usr/local/mysql-router/bin/mysqlrouter -c /etc/mysql-route/mysqlrouter.conf &
[root@router02 ~]# chkconfig --list|grep iptables
iptables        0:off   1:off   2:off   3:off   4:off   5:off   6:off
[root@router02 ~]# chkconfig  iptables off
[root@router02 ~]# vim /etc/rc.local 
#!/bin/sh
touch /var/lock/subsys/local
/usr/local/mysql-router/bin/mysqlrouter -c /etc/mysql-route/mysqlrouter.conf &

4.4创建测试账户进行测试:

登录mysql master主库进行创建后,账户会同步到各个slave上

mysql> grant all on jumpserver.* to jumpserver@'172.168.1.%' identified by 'jumpserver';

[root@master01 ~]#  mysql -h172.168.1.20 -P7001 -ujumpserver -pjumpserver -e "show variables like 'hostname';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| hostname      | master01 |

[root@router01 ~]# mysql -h172.168.1.20 -P7001 -ujumpserver -pjumpserver -e "show variables like 'hostname';"
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| hostname      | master01 |

[root@slave01 ~]# mysql -h172.168.1.20 -P7001 -ujumpserver -pjumpserver -e "show variables like 'hostname';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| hostname      | master01 |

4.4.1第一台主库DOWN后,MySQL Router如何处理?

当第一台主数据库DOWN机后,第二台slave备份数据库被提升为主库,所以此时查看到的主机名字变为了slave01

[root@master01 ~]# /etc/init.d/mysql stop
Shutting down MySQL.............. SUCCESS! 
[root@master01 ~]# mysql -h172.168.1.20 -P7001 -ujumpserver -pjumpserver -e "show variables like 'hostname';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| hostname      | slave01 |
+---------------+---------+
[root@master01 ~]#
[root@router02 ~]# mysql -h172.168.1.20 -P7001 -ujumpserver -pjumpserver -e "show variables like 'hostname';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| hostname      | slave01 |
+---------------+---------+
[root@router02 ~]#
[root@router01 ~]# mysql -h172.168.1.20 -P7001 -ujumpserver -pjumpserver -e "show variables like 'hostname';"
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| hostname      | slave01 |
+---------------+---------+
[root@router01 ~]#

4.4.2.稍后如果第一台主库被修复重启后,连接的数据库又会是那个呢?

如果第一台主库被修复重启后,那么默认仍然连接第二台为主库进行读写,不会自动切回到第一台主库。
重新启动主库master测试:

[root@master01 ~]# /etc/init.d/mysql start
Starting MySQL.................... SUCCESS! 
[root@master01 ~]# mysql -h172.168.1.20 -P7001 -ujumpserver -pjumpserver -e "show variables like 'hostname';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| hostname      | slave01 |
+---------------+---------+

4.4.3.如果第一台主数据库被修复后,又希望切换回第一台主库,怎么办?

可以重启MySQL Router 此时需要把router01和router02机器上的mysql-router服务都重新启动下,才可以切换回原来的主库

[root@router01 ~]# kill -9  9598 
[root@router01 ~]# /usr/local/mysql-router/bin/mysqlrouter -c /etc/mysql-route/mysqlrouter.conf &
[1] 9791
[root@router02 ~]# kill -9 13571
[1]+  Killed                  /usr/local/mysql-router/bin/mysqlrouter -c /etc/mysql-route/mysqlrouter.conf
 [root@router02 ~]# /usr/local/mysql-router/bin/mysqlrouter -c /etc/mysql-route/mysqlrouter.conf &
[1] 13627

[root@router02 ~]# mysql -h172.168.1.20 -P7001 -ujumpserver -pjumpserver -e "show variables like 'hostname';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| hostname      | master01 |
+---------------+----------+
[root@router02 ~]#
[root@router01 ~]#
[root@router01 ~]# mysql -h172.168.1.20 -P7001 -ujumpserver -pjumpserver -e "show variables like 'hostname';"
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| hostname      | master01 |
+---------------+----------+

五、通过jumpserver的web页面程序演示

5.1首先是安装完成jumpserver后要能够正常的登录

keepalived + mysqlroute +mysql实现mysql高可用
keepalived + mysqlroute +mysql实现mysql高可用

5.2关闭10.0.0.102keepalive主机器router01测试高可用

keepalived + mysqlroute +mysql实现mysql高可用

VIP172.168.1.20飘到10.0.0.104机器上

[root@router02 ~]# ip a|grep 172.168.1.20
inet 172.168.1.20/24 scope global secondary eth1
[root@router02 ~]#

root@router02 ~]# mysql -h172.168.1.20 -P7001 -ujumpserver -pjumpserver -e "show variables like 'hostname';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| hostname      | master01 |
+---------------+----------+

此时访问jumpserver web看是否可以正常登录?
经测试是没问题的,jumpserver web可以正常打开的

重启10.0.0.102机器mysql-router01
VIP再次又票到10.0.0.102 机器上

[root@router01 ~]# ip a|grep 172.168.1.20
    inet 172.168.1.20/24 scope global secondary eth1
[root@router01 ~]# mysql -h172.168.1.20 -P7001 -ujumpserver -pjumpserver -e "show variables like 'hostname';"
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| hostname      | master01 |
+---------------+----------+
[root@router01 ~]#

Jumpserver web依然可以继续打开

keepalived + mysqlroute +mysql实现mysql高可用

到此处,基于mysql的一主多从的高可用演示完毕,当然此方案和MHA相比,还没完全做到MHA的智能高可用