全部产品
云服务器 ECS

利用MySQL读写分离,提升应用数据吞吐性能

文档提供方:上海驻云信息科技有限公司    更新时间:2017-04-12 15:04:07   分享:   

背景

一般情况下,对数据库的读和写都在同一个数据库服务器中操作时,业务系统性能会降低。为了提升业务系统性能,优化用户体验,可以通过读写分离来减轻主数据库的负载。本篇文章分别从应用层和系统层来介绍读写分离的实现方法。

应用层实现方法:

应用层中直接使用代码实现,在进入Service之前,使用AOP来做出判断,是使用写库还是读库,判断依据可以根据方法名判断,比如说以query、find、get等开头的就走读库,其他的走写库。

优点:

1、多数据源切换方便,由程序自动完成。

2、不需要引入中间件。

3、理论上支持任何数据库。

缺点:

1、由程序员完成,运维参与不到。

2、不能做到动态增加数据源。

系统层实现方法:

方式一:使用DRDS实现

https://help.aliyun.com/document_detail/29681.html

方式二:使用中间件MySQL-proxy实现

本教程使用MySQL-proxy实现读写分离。

MySQL-proxy介绍:

MySQL Proxy是一个处于Client端和MySQL server端之间的简单程序,它可以监测、分析或改变它们的通信。它使用灵活,没有限制,常见的用途包括:负载平衡,故障、查询分析,查询过滤和修改等等。

MySQL-proxy原理:

1

MySQL Proxy是一个中间层代理,简单的说,MySQL Proxy就是一个连接池,负责将前台应用的连接请求转发给后台的数据库,并且通过使用lua脚本,可以实现复杂的连接控制和过滤,从而实现读写分离和负载平衡。对于应用来说,MySQL Proxy是完全透明的,应用则只需要连接到MySQL Proxy的监听端口即可。当然,这样proxy机器可能成为单点失效,但完全可以使用多个proxy机器做为冗余,在应用服务器的连接池配置中配置到多个proxy的连接参数即可。

优点:

1、源程序不需要做任何改动就可以实现读写分离。

2、动态添加数据源不需要重启程序。

缺点:

1、程序依赖于中间件,会导致切换数据库变得困难。

2、由中间件做了中转代理,性能有所下降。

操作步骤

环境说明:

主库IP:121.40.18.26

从库IP:101.37.36.20

MySQL-proxy代理IP:116.62.101.76

前期准备:

1、新建3台ECS,并安装mysql。

2、搭建主从,必须保证主从数据库数据一致。

主环境

1.修改mysql配置文件。

  1. vim /etc/my.cnf
  2. [mysqld]
  3. server-id=202 #设置服务器唯一的id,默认是1
  4. log-bin=mysql-bin # 启用二进制日志
从环境
  1. [mysqld]
  2. server-id=203

2.重启主从服务器中的MySQL服务。

  1. /etc/init.d/mysqld restart

3.在主服务器上建立帐户并授权slave。

  1. mysql -uroot -p95c7586783
  2. grant replication slave on *.* to 'syncms'@'填写slave-IP' identified by '123456';
  3. flush privileges;

4.查看主数据库状态。

  1. mysql> show master status;

2

5.配置从数据库。

  1. change master to master_host='填写master-IP', master_user='syncms', master_password='123456', master_log_file='mysql-bin.000005', master_log_pos=602;

6.启动slave同步进程并查看状态。

  1. start slave;
  2. show slave status\G

3

7.验证主从同步。

主库上操作:
  1. mysql> create database testproxy;
  2. mysql> create table testproxy.test1(ID int primary key,name char(10) not null);
  3. mysql> insert into testproxy.test1 values(1,'one');
  4. mysql> insert into testproxy.test1 values(2,'two');
  5. mysql> select * from testproxy.test1;

4

从库操作:

从库中查找testproxy.test1表的数据,与主库一致,主从同步成功

  1. select * from testproxy.test1;

5

读写分离配置

1.安装MySQL-Proxy。

  1. wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
  2. mkdir /alidata
  3. tar xvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
  4. mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/ /alidata/mysql-proxy-0.8.5

2.环境变量设置。

  1. vim /etc/profile #加入以下内容
  2. PATH=$PATH:/alidata/mysql-proxy-0.8.5/bin
  3. export $PATH
  4. source /etc/profile #使变量立即生效
  5. mysql-proxy -V

6

3.读写分离设置。

  1. cd /alidata/mysql-proxy-0.8.5/share/doc/mysql-proxy/
  2. vim rw-splitting.lua

MySQL Proxy会检测客户端连接, 当连接没有超过min_idle_connections预设值时, 不会进行读写分离默认最小4个(最大8个)以上的客户端连接才会实现读写分离, 现改为最小1个最大2个,便于读写分离的测试,生产环境中,可以根据实际情况进行调整。

调整前:

7

调整后:

8

4.将lua管理脚本(admin.lua)复制到读写分离脚本(rw-splitting.lua)所在目录。

  1. cp /alidata/mysql-proxy-0.8.5/lib/mysql-proxy/lua/admin.lua /alidata/mysql-proxy-0.8.5/share/doc/mysql-proxy/

授权

1.主库中操作授权,因主从同步的原因,从库也会执行。

  1. mysql -uroot -p95c7586783
  2. grant all on *.* to 'mysql-proxy'@'填写MySQL Proxy IP' identified by '123456';
  3. flush privileges;

2.开启MySQL-Proxy。

  1. mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins=proxy -b 填写master-IP:3306 -r 填写slave-IP:3306 --proxy-lua-script="/alidata/mysql-proxy-0.8.5/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/alidata/mysql-proxy-0.8.5/share/doc/mysql-proxy/admin.lua"

3.启动MySQL-Proxy之后,查看端口和相关进程。

  1. netstat -tpln

9

  1. ps -ef | grep mysql

10

测试读写分离

1.关闭从复制

  1. stop slave;

2.MySQL-Proxy上操作,登录mysql-proxy后台管理。

  1. mysql -u admin -padmin -P 4041 -h MySQL-Proxy-IP
  2. select * from backends; #查看状态

11

第一次连接,会连接到主库上。

  1. mysql -umysql-proxy -p123456 -h 116.62.101.76 -P 4040
  2. insert into testproxy.test1 values(3,'three'); #新增一条数据,由于测试需要,关闭了从复制,因此该数据在主库中存在,在从库中不存在

12

多开几个连接进行测试,当查询testproxy.test1表的数据显示是从库的数据时,读写分离成功。

  1. mysql -umysql-proxy -p123456 -h 116.62.101.76 -P 4040
  2. select * from testproxy.test1;

13

本文导读目录
本文导读目录
以上内容是否对您有帮助?