通过读写分离提升数据吞吐性能_实例配置_最佳实践_云服务器 ECS_阿里云技术文档

一般情况下，对数据库的读和写都在同一个数据库服务器中操作时，业务系统性能会降低。为了提升业务系统性能，优化用户体验，可以通过读写分离来减轻主数据库的负载。本教程主要介绍如何使用中间件MySQL-proxy实现读写分离。

前提条件

使用本教程进行操作前，请确保您已经注册了阿里云账号。如还未注册，请先完成账号注册。

背景信息

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

优点：

多数据源切换方便，由程序自动完成。
不需要引入中间件。
理论上支持任何数据库。

缺点：

由程序员完成，运维参与不到。
不能做到动态增加数据源。

系统层的实现方法包括以下两种：

使用分布式关系型数据库DRDS实现读写分离。
使用中间件MySQL-proxy实现读写分离。

本教程介绍如何使用中间件MySQL-proxy实现读写分离。

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

MySQL-proxy的原理如下图：
通过读写分离提升数据吞吐性能_实例配置_最佳实践_云服务器 ECS 阿里云技术文档第1张

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

优点：

源程序不需要做任何改动就可以实现读写分离。
动态添加数据源不需要重启程序。

缺点：

源程序依赖于中间件，会导致切换数据库变得困难。
由中间件做了中转代理，性能有所下降。

操作步骤

使用中间件MySQL-proxy实现读写分离的操作步骤如下：

步骤一：完成准备工作
步骤二：配置读写分离
步骤三：授权
步骤四：验证读写分离

步骤一：完成准备工作

环境说明如下：

主库IP：121.40.18.26
从库IP：101.37.36.20
MySQL-proxy代理IP：116.62.101.76

完成以下操作，做好准备工作：

新建3台ECS，并安装mysql。
搭建主从环境，必须保证主从数据库数据一致。

修改主从环境的mysql配置文件。

主环境：

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

从环境：
```
[mysqld]
server-id=203
```

重启主从服务器中的MySQL服务。
```
/etc/init.d/mysqld restart
```

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

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

查看主数据库状态。
```
mysql> show master status;
```

配置从数据库。

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

启动slave同步进程并查看状态。
```
start slave;
show slave status\G
```

验证主从同步。

在主数据库的表testproxy.test1中写入数据。

mysql> create database testproxy;
mysql> create table testproxy.test1(ID int primary key,name char(10) not null);
mysql> insert into testproxy.test1 values(1,'one');
mysql> insert into testproxy.test1 values(2,'two');
mysql> select * from testproxy.test1;

通过读写分离提升数据吞吐性能_实例配置_最佳实践_云服务器 ECS 阿里云技术文档第4张

在从数据库中运行以下命令，查找表testproxy.test1的数据。
```
select * from testproxy.test1;
```
如果表testproxy.test1的内容与主数据库的一致，则主从同步成功。

步骤二：配置读写分离

完成以下操作，配置读写分离：

安装MySQL-Proxy。

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

设置环境变量。

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

通过读写分离提升数据吞吐性能_实例配置_最佳实践_云服务器 ECS 阿里云技术文档第6张

设置读写分离。
```
cd /alidata/mysql-proxy-0.8.5/share/doc/mysql-proxy/
vim  rw-splitting.lua
```
MySQL Proxy会检测客户端连接，当连接没有超过min_idle_connections预设值时，不会进行读写分离。默认最小4个（最大8个）以上的客户端连接才会实现读写分离。现改为最小1个（最大2个），便于读写分离的测试。生产环境中，可以根据实际情况进行调整。

调整前：

调整后：

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

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

步骤三：授权

完成以下操作，进行授权：

在主库中操作授权。因主从同步的原因，从库也会执行。

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

开启MySQL-Proxy。

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"

查看端口和相关进程。
```
netstat -tpln
```
```
ps -ef | grep mysql
```

步骤四：验证读写分离

完成以下操作，验证读写分离：

关闭从复制。
```
stop slave;
```

在MySQL-Proxy上操作，登录mysql-proxy后台管理。

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

通过读写分离提升数据吞吐性能_实例配置_最佳实践_云服务器 ECS 阿里云技术文档第11张

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

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

通过读写分离提升数据吞吐性能_实例配置_最佳实践_云服务器 ECS 阿里云技术文档第12张

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

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

通过读写分离提升数据吞吐性能_实例配置_最佳实践_云服务器 ECS 阿里云技术文档第13张

通过读写分离提升数据吞吐性能_实例配置_最佳实践_云服务器 ECS

通过读写分离提升数据吞吐性能