mysql-proxy实现数据库读写分离

实验概述：

主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力 这样的方案来进行部署实施的。

haproxy数据库会话保持_MySQL

MySQL Proxy概述

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

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

MySQL Proxy更强大的一项功能是实现“读写分离”，基本原理是让主数据库处理事务性查询，让从库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从库。

Lua概述：

Lua 是一个小巧的脚本语言。Lua的速度是最快的。Lua由标准C编写而成，几乎在所有操作系统和平台上都可以编译，运行。Lua并没有提供强大的库，这是由它的定位决定的。所以Lua不适合作为开发独立应用程序的语言。

读写分离的好处

1）增加冗余

2）增加了机器的处理能力

3）对于读操作为主的应用，使用读写分离是最好的场景，因为可以确保写的服务器压力更小，而读又可以接受点时间上的延迟

实验

实验环境：

server1和2：安装5.7版本数据库
虚拟机：6.5版本

实验需求：

首先在server1和server2实现主从复制
实现server1:master
server2:slave
server3:mysql-proxy调度器

实现主从复制后配置server1、2的数据库并在mastser端（server1）建立库以测试
server1中mysql配置：
mysql> grant replication slave on . to repl@’172.25.10.%’ identified by ‘Wxj14370902!’;
server2中mysql配置：
mysql> set global read_only=1;

server3:

1、解压安装包，将其移动到系统目录下

[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz 
[root@server3 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy

2、设置环境变量

[root@server3 bin]# pwd
/usr/local/mysql-proxy/bin

[root@server3 conf]# vim ~/.bash_profile 
 10 PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
[root@server3 conf]# source ~/.bash_profile

3、新建日志目录

[root@server3 ~]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# mkdir logs

4、编辑lua脚本

[root@server3 ~]# cd /usr/local/mysql-proxy/share/doc/mysql-proxy/
[root@server3 mysql-proxy]# vim rw-splitting.lua
发起读写分离的条件，最小发起1个连接，最大发起2个连接 
 40                 min_idle_connections = 1,  ##40行改为1
 41                 max_idle_connections = 2,  ##41行改为2

5、编辑mysql-proxy配置文件

新建目录
[root@server3 ~]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# cd conf/
编辑配置文件
[root@server3 conf]# vim mysql-proxy.conf  ##参数可使用mysql-proxy --help查看，添加
[mysql-proxy]
user=root
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.10.1:3306     ##master端
proxy-read-only-backend-addresses=172.25.10.2:3306   ##slave端
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log   ##日志目录
log-level=debug
daemon=true        ##打入后台
keepalive=true     ##持续连接

6、赋予权限

[root@server3 conf]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

7、执行配置文件

[root@server3 conf]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

进程查看

ps ax
 1156 ?        S      0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults
 1157 ?        S      0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults

端口查看

netstat -antlp
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      1157/mysql-proxy

server1、2（master、slave）：

安装监控命令
[root@server1 ~]# yum install -y lsof
监控3306端口
[root@server1 ~]# lsof -i :3306

检测：

第一步：

真机：

外部物理机连接2次调度器   
注意：这里使用的用户要有远程连接的权限
没有需在master端配置数据库
mysql> grant all on *.* to root@'%' identified by 'Wxj14370902!';

[root@foundation10 Desktop]# mysql -h 172.25.10.3 -u root -p
MySQL [(none)]> use test
MySQL [test]> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
连接2次后在server1中监控命令查看
server1中：
[root@server1 ~]# lsof -i :3306
mysqld  1486 mysql   34u  IPv6  10253      0t0  TCP *:mysql (LISTEN)
mysqld  1486 mysql   50u  IPv6  10882      0t0  TCP server1:mysql->server3:39950 (ESTABLISHED)
mysqld  1486 mysql   59u  IPv6  10282      0t0  TCP server1:mysql->server2:57774 (ESTABLISHED)
mysqld  1486 mysql   62u  IPv6  10322      0t0  TCP server1:mysql->server3:39945 (ESTABLISHED)
mysqld  1486 mysql   63u  IPv6  10325      0t0  TCP server1:mysql->server3:39948 (ESTABLISHED)

haproxy数据库会话保持_haproxy数据库会话保持_02

第二步：

真机：

外部物理机第三次连接调度器，能够实现写操作
[root@server3 ~]# mysql -h 172.25.10.3 -u root -p
mysql> use test
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
mysql> insert into userlist values ('user2','6666'); 

在server1（master端）中查看
server1:
mysql -p
mysql> use test
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 6666     |
+----------+----------+

server2中使用监控命令查看，更新调度器连接，这时server1中的再使用监控命令查看连接不更新了，
之后再用外部物理机连接调度器都会在server2上更新信息
[root@server2 ~]# lsof -i :3306
mysqld  1462 mysql   34u  IPv6  10070      0t0  TCP *:mysql (LISTEN)
mysqld  1462 mysql   60u  IPv4  10112      0t0  TCP server2:57774->server1:mysql (ESTABLISHED)
mysqld  1462 mysql   65u  IPv6  10150      0t0  TCP server2:mysql->server3:56341 (ESTABLISHED)

haproxy数据库会话保持_mysql_03


此时在server3中自动显示出调度器信息

server3:

server default db: test
    client default db: 
    syncronizing
    server default db: 
    client default db: test
    syncronizing
    server default db: 
    client default db: test
    syncronizing

haproxy数据库会话保持_haproxy数据库会话保持_04

综上，即实现了读在master端，写在slave端的需求。