使用mysql-router工具实现数据库读写分离，数据库负载均衡

研究公司oFrame框架发现，我们框架内大量的sql扫表语句严重的影响了数据查询速度和产品使用的各种问题，为此我准备先从第一步入手去增数据的性能，让我们一起学一下吧.

一、环境准备

mysql-router 简单的负载均衡组件
mysql5.6 mysql
centos7
Vmware

mysql-router 简介

MySQL的路由器是轻量级的中间件，提供您的应用程序和任何后端MySQL服务器之间的透明路由。它可用于各种各样的使用情况，如通过有效的路由数据库流量到合适的后端MySQL服务器提供高可用性和可扩展性。可插拔的体系结构还使开发者来扩展MySQL的路由器自定义的用例。

故障转移

典型地，高度可用的MySQL设置由一个单一的主站和多个从机，并且它是由应用程序来处理故障切换，在情况下，MySQL主变得不可用。使用MySQL路由器，应用程序连接将被透明基于负载平衡策略路由，不实现自定义应用程序代码。

负载均衡

MySQL的路由器通过在服务器池分配数据库的连接提供了额外的可扩展性和性能。举例来说，如果你有一个复制的一套MySQL服务器的，MySQL的路由器可以在一个循环的方式分发应用程序连接到他们。

可插入式架构

MySQL的路由器的可插入式架构使MySQL的开发人员能够轻松地与附加功能延伸产品，以及提供MySQL用户能够创建提供了无限的可能性自己的自定义插件的能力。MySQL的路由器目前包括一些核心插件，其中包括：

连接路由插件，它确实基于连接的路由，这意味着它转发MySQL的数据包发送到后端服务器，而不检查或修改它们，从而提供最大的吞吐量。
的元数据高速缓存插件，它提供了透明的客户端负载平衡，路由和故障转移到组复制和InnoDB群集。

mysql-router结构示意图

二、环境搭建

这里centos搭建和Vmware不再阐述，mysql的安装也不在讲解，重点讲解配置步骤和实现mysql-router功能

安装mysql-router

进入官网下载页面：点我去往下载页面

mysql-router下载页面

这里我选择Red Hat Enterprise Linux 7 / Oracle Linux 7也就是上图中的第一个rpm包进行下载

为了在虚拟机中能够方便的复制，我这里下载过程中复制下载地址到虚拟机中使用[ wget ]命令进行下载

[root@localhost ~]# wget https://cdn.mysql.com//Downloads/MySQL-Router/mysql-router-2.1.4-1.el7.x86_64.rpm

--2017-07-28 14:57:02--  https://cdn.mysql.com//Downloads/MySQL-Router/mysql-router-2.1.4-1.el7.x86_64
.rpm
Resolving cdn.mysql.com (cdn.mysql.com)... 2.17.63.195
Connecting to cdn.mysql.com (cdn.mysql.com)|2.17.63.195|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1947576 (1.9M) [application/x-redhat-package-manager]
Saving to: ‘mysql-router-2.1.4-1.el7.x86_64.rpm’

100%[============================================================>] 1,947,576    773KB/s   in 2.5s   

2017-07-28 14:57:10 (773 KB/s) - ‘mysql-router-2.1.4-1.el7.x86_64.rpm’ saved [1947576/1947576]

[root@localhost ~]# rpm -i mysql-router-2.1.4-1.el7.x86_64.rpm

经过上面的步骤mysql-router已经安装完毕，这里mysql安装不在叙述

三、负载均衡架构规划

我由于使用的是虚拟机我这里设定以下几个服务器

1.Mysql Middleware 安装着mysql-router专门用于mysql入口负责机器的主从分配等问题

2.Mysql Master 数据库主库，用于数据的读写

3.Mysql Salve 数据库从库

我对结构做了如下划分和IP分配，详情见图:

结构规划图

四、配置服务

1.Mysql Middleware (router主机)

默认rpm安装位置在/usr/bin/mysqlrouter 为了个人意愿我把日志logs和配置文件mysqlrouter.ini都存放在/usr/local/mysql-router下

下面开始新建配置文件，设置port:8000为读写端口 port:8001为只读端口

[root@localhost mysql-router]# mkdir logs
[root@localhost mysql-router]# vi mysqlrouter.ini

[DEFAULT]              
logging_folder=usr/local/mysql-router/logs                                                         

[logger]                                                                  
level=INFO                                                                                    
[routing:basic_failover]                                                                              
bind_address=192.168.1.186                                                                          
bind_port=000                                                                                      
destinations=192.168.1.189:3306                                                                     
mode=read-write                                                                                     
max_connections=1024                                                                                
max_connect_errors=100                                                                              
client_connect_timeout=9                                                                        
[routing:balancing]
# 绑定router的IP即可                                               
bind_address=192.168.1.186                                                #绑定端口                
bind_port=8001                                                            #数据库地址，会轮训访问                      
destinations = 192.168.1.193:3306
#指定模式为只读
mode=read-only                                                            #设置最大连接数        
max_connections=65535
max_connect_errors=100
client_connect_timeout = 9            
[keepalive]                                                               
interval = 60

以上是配置文件内容，注释了一部分

启动mysqlrouter(设置自己的配置文件即可，我已经在配置文件目录了就按照下方启动即可)

1	[root@localhost mysql-router]# mysqlrouter -c ./mysqlrouter.ini &

验证mysqlrouter是否启动（下图情况为启动成功）

1
2
3

[root@localhost mysql-router]# netstat -tunlp|grep mysqlrouter                               
tcp        0      0 0.0.0.0:8000            0.0.0.0:*               LISTEN      71271/mysqlrouter     
tcp        0      0 0.0.0.0:8001            0.0.0.0:*               LISTEN      71271/mysqlrouter

经过以上步骤已经完成router配置了，如果主库从库都可以的话即可使用客户端通过8000端口访问测试，如果不能访问通过下面这组命令查看错误日志

1	[root@localhost mysql-router]# tail logs/mysqlrouter.log

2.Mysql Master 数据库主库

配置从库复制账号

1
2
3


MySQL [(none)]> grant replication scale on *.* to '账号名'@'主机域' identifed by '账号密码'；
MySQL [(none)]> flush privileges;

配置主库的主从复制文件my.ini主要增加或修改一下选项


#服务器ID唯一标识，错了，无法复制
server-id = 1
#指定复制库
binlog-do-db=text
#以下数据库不复制
binlog-ignore-db=mysql

经过修改以后从其mysql主库的任务就完成了，接下来配置从库

Mysql Salve 数据库从库(设定主库信息进行读写配置)

登入数据库后查看服务器ID不可以和主库相同，切记！

MySQL [(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.02 sec)

查询主库配置信息

MySQL [(none)]> show master status \G

             File: mysql-bin.000006
         Position: 2647
     Binlog_Do_DB: text
 Binlog_Ignore_DB: mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)

获取File和Position即可

写入主库信息

MySQL [(none)]> change master to
    ->master_host='192.168.1.189',//主库地址
    ->master_port=3306,          //主库端口      
    ->master_user='master',      //主库同步账户名             
    ->master_password='master',  //同步账号密码
    ->master_log_file='mysql-bin.000005',//对应主库File
    ->master_log_pos=3195;//对应主库Position                                                 
Query OK, 0 rows affected, 2 warnings (0.02 sec)

MySQL [(none)]> start slave;   //启动同步
Query OK, 0 rows affected (0.00 sec)

为了保证数据同步，请一定要空表操作，避免报错

如果同步请使用MySQL [(none)]> show slave status \G 查看具体错误

其它从库全部遵循这个配置原则即可完成，多少个从库都可以

五、总结

通过本次学习我对数据库负载均衡有了很大的了解，我以后也会继续学习更深入的集群化知识，为以后架构师的梦想做知识筹备，也希望我出的这个经验分享能给大家一个很好的参考，也是目前能提高公司产品服务器这块速度慢，在并发情况下发生数据库罢工的解决方案，目前这个演示仅仅是一个主库和从库，大家回去练习可以多建立从库进行大并发访问测试，今天的分享就到这里，祝大家工作顺利。

少年当自强