简介

使用MySQL时,随着时间、业务的需求、用户量以及数据量的逐渐增加,相对的,数据库的的操作更是剧增,最终将会使MySQL达到某个瓶颈,那么MySQL的性能将会大大降低,更严重的话将会影响整个系统架构的正常运行,相信看过《太空旅客》这部电影的小伙伴们都知道,飞船的系统有一处瘫痪了,其他的系统想帮忙减轻负重,但是这个工作量太大了分担不过来,接着其他的系统也瘫痪了。那么如何跨过这个瓶颈,提高MySQL的并发量呢?方法有很多,比如说分布式数据库、读写分离、高可用负载均衡、增加缓存服务器等等。但今天的主角主要是接下来要介绍的 OneProxy 数据库中间件。

OneProxy

OneProxy可以让不同应用复用到后端数据库的连接,降低数据库的并发连接数;可以即时发现和踢除不可用的后端节点,将应用请求转发到其他可用节点实现高效故障隔离, 可配合Galera Cluster或MySQL Group Replication实现快速切换; 内置的守护进程模式和HA VIP机制,可以轻松实现Proxy的单机及多机高可用,确保业务服务的稳定性;可以透明地将查询语句分发到多个MySQL备库执行, 用读写分离方案支持上千万的并发访问;也可以根据SQL语句中的值进行分库分表路由, 将写操作均匀分散到多个MySQL主库上,以构建每秒百万TPS的MySQL集群; 跨多分片的结果集合并, 极大地简化了应用程序的开发工作量;在分片的情况下可根据分片并行执行SQL,解决了MySQL在大数据量下的汇总统计性能问题。
同时。对外部请求进行安全检查,拒绝危险的DDL操作;可根据IP地址进行访问控制,只允许可信的IP地址进行访问;可以对每个SQL访问请求进行审核,实现SQL白名单机制, 彻底杜绝SQL注入式×××;可以实现前后端密码分离,应用无须使用真实密码连接数据库,实现数据库密码的保护;可以分别设定前端应用和后端数据库的SQL请求频率,实现QoS控制; 可以通过正则表达式实现敏感数据过滤和审计,也可以在日志中记录大数据量的访问和更新操作,确保数据的安全。还可以实时透明地分析流量,包括SQL语句和结果集,实时统计SQL和事务的运行时间,分析事务的SQL结构,得到各种不同维度的实时性能报告, 并可通过内置Web服务器方便直观地查看实时性能数据,弥补上层应用和MySQL实时性能监控方面的不足。使用C&C++开发,充分利用高性能的异步网络事件编程框架, 使单个OneProxy实例可支持高达40W的QPS/TPS;并可充分利用和管理内存,无Java类语言的内存回收问题,确保7x24的稳定性;内置的守护进程模式和HA VIP机制,可以轻松实现Proxy的多机高可用;

系统架构

系统架构
架构可以有很多种,比如两台OneProxy做一个Keeplive的架构,但这里搭建OneProxy+MySQL主从复制,用简单的架构说明OneProxy实现的过程,一台OneProxy做中间件,实现后端的MySQL负载,同时后端的两台MySQL使用mysqlreplicate做一个非常简单的主从复制;

利用mysqlreplicate做主从复制

安装mysql-utilities 

[root@data01 ~]# yum list | grep mysql-utilities
mysql-utilities.noarch    1.3.6-1.el6    epel  
[root@data01 ~]# yum -y install mysql-utilities.noarch

开通主库访问权限 

[root@data01 ~]# mysql -u root -p    #登录数据库
mysql> grant all privileges on *.* to master@"172.16.0.2" identified by "123456";    #允许本机以master用户使用123456作为密码访问所有库和表
Query OK, 0 rows affected (0.01 sec)
mysql> grant all privileges on *.* to slave@"172.16.0.3" identified by "123456";    #允许172.16.0.3主机以slave用户使用123456作为密码访问所有库和表
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;    #刷新数据库授权信息
Query OK, 0 rows affected (0.01 sec)

配置主从

[root@data01 ~]# mysqlreplicate --master=master:123456@172.16.0.2:3306 --slave=slave:123456@172.16.0.3:3306 --rpl-user=sync:123456 -b
WARNING: Using a password on the command line interface can be insecure.
# master on 172.16.0.2: ... connected.
# slave on 172.16.0.3: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done. 
选项解析:
--master 连接主数据库的信息 格式:用户:密码@IP地址:端口号
--slave 连接从数据库的信息   格式:用户:密码@IP地址:端口号
--rpl-user 设定同步的用户名和密码   格式:用户:密码
-b 设定从记录在第一个事件开始复制

查看主从状态

[root@data01 ~]# mysqlrplcheck --master=master:123456@172.16.0.2:3306 --slave=slave:123456@172.16.0.3:3306 -s
WARNING: Using a password on the command line interface can be insecure.
# master on 172.16.0.2: ... connected.
# slave on 172.16.0.3: ... connected.
Test Description                          Status
---------------------------------------------------------------------------
Checking for binary logging on master              [pass]
Are there binlog exceptions?                   [pass]
Replication user exists?                      [pass]
Checking server_id values                     [pass]
Checking server_uuid values                    [pass]
Is slave connected to master?                  [pass]
Check master information file                   [pass]
Checking InnoDB compatibility                   [pass]
Checking storage engines compatibility              [pass]
Checking lower_case_table_names settings              [pass]
Checking slave delay (seconds behind master)          [pass]
#
# Slave status: 
#
     Slave_IO_State : Waiting for master to send event
     Master_Host : 172.16.0.2    #主服务器的IP地址
     Master_User : sync    #同步的用户名
     Master_Port : 3306    #数据库端口
     Connect_Retry : 60    #连接重试的时间
     Master_Log_File : mysql-bin.000001    #主数据库事件日志文件
     Read_Master_Log_Pos : 384741
     Relay_Log_File : Data_01-relay-bin.000001    #从数据库事件日志文件
     Relay_Log_Pos : 384954
     Relay_Master_Log_File : mysql-bin.000001
     Slave_IO_Running : Yes    #从数据库同步状态
     Slave_SQL_Running : Yes    #从数据库的工作状态
     Replicate_Do_DB : 
     Replicate_Ignore_DB : 
     Replicate_Do_Table : 
     Replicate_Ignore_Table : 
     Replicate_Wild_Do_Table : 
     Replicate_Wild_Ignore_Table : 
     Last_Errno : 0
     Last_Error : 
     Skip_Counter : 0
     Exec_Master_Log_Pos : 384741
     Relay_Log_Space : 1743112
     Until_Condition : None
     Until_Log_File : 
     Until_Log_Pos : 0
     Master_SSL_Allowed : No
     Master_SSL_CA_File : 
     Master_SSL_CA_Path : 
     Master_SSL_Cert : 
     Master_SSL_Cipher : 
     Master_SSL_Key : 
     Seconds_Behind_Master : 0
     Master_SSL_Verify_Server_Cert : No
     Last_IO_Errno : 0
     Last_IO_Error : 
     Last_SQL_Errno : 0
     Last_SQL_Error : 
     Replicate_Ignore_Server_Ids : 
     Master_Server_Id : 1250
     Master_UUID : 1b1daad8-b501-11e6-aa21-000c29c6361d
     Master_Info_File : /data/mysql/master.info
     SQL_Delay : 0
     SQL_Remaining_Delay : None
     Slave_SQL_Running_State : Slave has read all relay log; waiting for more updates
     Master_Retry_Count : 86400
     Master_Bind : 
     Last_IO_Error_Timestamp : 
     Last_SQL_Error_Timestamp : 
     Master_SSL_Crl : 
     Master_SSL_Crlpath : 
     Retrieved_Gtid_Set : 
     Executed_Gtid_Set : 
     Auto_Position : 0
     Replicate_Rewrite_DB : 
     Channel_Name : 
     Master_TLS_Version : 
# ...done.

OneProxy实现读写分离与负载均衡

下载OneProxy的源码包

[root@oneproxy download]# wget http://www.onexsoft.com/software/oneproxy-rhel6-linux64-v6.2.0-ga.tar.gz  
--2018-06-18 07:16:45--  http://www.onexsoft.com/software/oneproxy-rhel6-linux64-v6.2.0-ga.tar.gz
Resolving www.onexsoft.com... 121.40.207.56
Connecting to www.onexsoft.com|121.40.207.56|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 7862607 (7.5M) [application/octet-stream]
Saving to: “oneproxy-rhel6-linux64-v6.2.0-ga.tar.gz”
100%[================================================= ==========>] 7,862,607    276K/s   in 27s     
2018-06-18 07:17:12 (286 KB/s) - “oneproxy-rhel6-linux64-v6.2.0-ga.tar.gz” saved [7862607/7862607]

OneProxy的配置文件

[root@oneproxy download]# tar zvxf oneproxy-rhel6-linux64-v6.2.0-ga.tar.gz -C /usr/local/
[root@oneproxy download]# cd /usr/local/oneproxy/
[root@oneproxy oneproxy]# ls
bin  conf  demo.sh  log  oneproxy.service  README  sql  testadmin.sql  testautocommit.sql  testproxy.sql  trantest.sql
文件说明:
conf 配置文件
oneproxy.service 启动脚本
log 日志文件
demo.sh 初次启动脚本

修改demo.sh脚本的OneProxy家目录

[root@oneproxy oneproxy]# vim demo.sh 
#/bin/bash
export ONEPROXY_HOME=/usr/local/oneproxy     #修改为oneproxy解压后的目录路径
ulimit -c unlimited
# valgrind --leak-check=full \
${ONEPROXY_HOME}/bin/oneproxy --defaults-file=${ONEPROXY_HOME}/conf/proxy.conf

修改启动脚本的OneProxy家目录

[root@oneproxy oneproxy]# vim oneproxy.service 
#!/bin/bash
# chkconfig: - 30 21
# description: OneProxy service.
# Source Function Library
. /etc/init.d/functions
# OneProxy Settings
ONEPROXY_HOME=/usr/local/oneproxy    #修改为oneproxy解压后的目录路径
ONEPROXY_SBIN="${ONEPROXY_HOME}/bin/oneproxy"
ONEPROXY_CONF="${ONEPROXY_HOME}/conf/proxy.conf"
ONEPROXY_PID="${ONEPROXY_HOME}/log/oneproxy.pid"
RETVAL=0
prog="OneProxy"
start() {
  echo -n $"Starting $prog ... "
  daemon $ONEPROXY_SBIN --defaults-file=$ONEPROXY_CONF
  RETVAL=$?
  echo
}
stop() {
  echo -n $"Stopping $prog ... "
  if [ -e ${ONEPROXY_PID} ]; then
    daemon kill -INT $(cat ${ONEPROXY_PID})
    RETVAL=$?
  fi
  echo
}
restart(){
  stop
  sleep 1
  start
}
case "$1" in
  start)
    start
    ;;
  stop)
    stop
    ;;
  restart)
    restart
    ;;
  *)
    echo $"Usage: $0 {start|stop|restart}"
    RETVAL=1
esac
exit $RETVAL

将密文密码加密

[root@center oneproxy]# cd /usr/local/oneproxy/bin/
[root@center bin]# ./mysqlpwd 123456
9D7E55EAF8912CCBF32069443FAC452794F8941B

配置OneProxy

[root@oneproxy bin]# vim /usr/local/oneproxy/conf/proxy.conf 

[oneproxy]

keepalive               = 1
event-threads            = 4
log-file               = log/oneproxy.log    #指定日志文件路径
pid-file               = log/oneproxy.pid    #指定PID文件路径
lck-file               = log/oneproxy.lck    #指定LCK文件路径

proxy-auto-readonly         = 0
proxy-forward-clientip       = 0
proxy-trans-debug          = 1
mysql-version            = 5.1.73    #MySQL服务版本

admin-address            = 0.0.0.0:4041
proxy-address            = 0.0.0.0:3306    #指定自身监听端口
proxy-master-addresses       = 172.16.0.2:3306@oneproxy    #指定主服务器的IP地址  格式:IP地址:端口@oneproxy组
proxy-slave-addresses        = 172.16.0.3:3306@oneproxy    #指定从服务器的IP地址  格式:IP地址:端口@oneproxy组

#用户列表   格式:用户名/密文密码@数据库名称
proxy-user-list.1          = user/9D7E55EAF8912CCBF32069443FAC452794F8941B@database01
proxy-user-list.2          = user/9D7E55EAF8912CCBF32069443FAC452794F8941Bdatabase02
proxy-user-list.3          = user/9D7E55EAF8912CCBF32069443FAC452794F8941B@database03
proxy-user-list.4           = user/9D7E55EAF8912CCBF32069443FAC452794F8941B@database04

proxy-part-template         = conf/template.txt
proxy-part-tables          = conf/part.txt  #指定分表分库的配置文件
proxy-charset            = utf8_general_ci  #指定数据库字符集

proxy-secure-client         = 127.0.0.1
proxy-license            = 32C54560E06EFF3E
proxy-httpserver          = 0.0.0.0:8080   #指定Web服务的监听端口
proxy-httpauth           = admin:admin    #指定Web访问认证信息   格式:用户名:密码
proxy-httptitle           = OneProxy       #指定Web页面名称

#设定安全级别,0默认值,1禁止DDL,2禁止不带条件的查询语句,3只允许SELECT
proxy-group-security        = oneproxy:0

#设定预定义策略
#0代表由Lua Script来决定 1代表Read Failover 2代表主节点不参与读 3代表双主结构 4代表主节点参与读操作 5代表随机读取
proxy-group-policy         = oneproxy:2

启动OneProxy

[root@oneproxy oneproxy]# ./demo.sh
[root@oneproxy oneproxy]# /usr/local/oneproxy/oneproxy.service start
Starting OneProxy ...                    [  OK  ]

查看服务监听状态

[root@oneproxy ~]# netstat -tnlp
Active Internet connections (only servers)
Proto   Recv-Q   Send-Q   Local Address     Foreign Address   State    PID/Program name         
tcp   0     0        127.0.0.1:32000  0.0.0.0:*      LISTEN  1196/java           
tcp   0     0        0.0.0.0:33890   0.0.0.0:*      LISTEN  1407/sshd                
tcp   0     0        0.0.0.0:4041    0.0.0.0:*      LISTEN  21191/oneproxy      
tcp   0      0        0.0.0.0:3306    0.0.0.0:*      LISTEN  21191/oneproxy      
tcp   0     0        0.0.0.0:8080    0.0.0.0:*      LISTEN  21191/oneproxy

访问OneProxy的Web页面

Url:http://IP地址:8080
OneProxy的Web页面

最后

OneProxy功能强大,这里如果要一一说明的话恐怕要上个一千零一夜,读者可以自行摸索摸索,这里列出几个方向OneProxy的分表分库可以帮程序猿剩下很多代码和工作量,同时借着Web的图形界面分析使用的情况,更能有效的提前发现问题,解决问题,提高效率,后续等我研究绝对透彻了,再跟大家继续探究,如果这篇文章有什么不对的地方欢迎大家指出,同时,如果在那个地方有问题欢迎在下面的评论中提出,我也非常高兴能和大家虚心探讨!

最后修改:2019 年 05 月 19 日
-