MySQL-Proxy之读写分离

1、理论部分

1.1、MySQL Proxy的简介

MySQL Proxy是一个中间层代理（连接池），负责将前台应用连接请求转发给后台数据库，并通过lua脚本，可以实现负载的连接控制过滤，从而实现读写分离和负载均衡。对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口。

1.2、常见用途

1）负载均衡

2）读写分离

3）故障、查询分析

4）查询过滤和修改

<a href="http://s3.51cto.com/wyfs02/M00/74/F2/wKioL1YvLeDwPIjbAAHJczWOgmw838.jpg" target="_blank"></a>

1.3、官方下载

<a href="http://www.mysql.com/" target="_blank">http://www.mysql.com/</a>

<a href="http://s3.51cto.com/wyfs02/M01/74/FE/wKiom1YwWjLQz9LCAAeJ5-I5yrY903.jpg" target="_blank"></a>

2、实验部分

2.1、主机信息

Master

ipadddress=10.168.0.103

hostname=Master

Slave

ipaddress=10.168.0.104

hostname=Slave

MySQLProxy

ipaddress=10.168.0.111

hostname=MySQLProxy

Client

ipaddress=10.168.0.8

hostname=client

2.2、实验前提

完成主备的配置，详细文档如下：

<a href="http://cmdschool.blog.51cto.com/2420395/1696474" target="_blank">http://cmdschool.blog.51cto.com/2420395/1696474</a>

2.3、yum源安装

In MySQLProxy

1

<code>yum -y </code><code>install</code> <code>lua </code><code>lsof</code>

In Client

<code>yum -y </code><code>install</code> <code>mysql</code>

2.3、实验步骤

2.3.1、安装MySQL-Proxy

2

3

4

<code>wget http:</code><code>//dev</code><code>.mysql.com</code><code>/get/Downloads/MySQL-Proxy/mysql-proxy-0</code><code>.8.5-linux-glibc2.3-x86-64bit.</code><code>tar</code><code>.gz</code>

<code>tar</code> <code>xvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.</code><code>tar</code><code>.gz -C </code><code>/usr/local</code>

<code>cd</code> <code>/usr/local/</code>

<code>ln</code> <code>-s mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/ mysql-proxy</code>

vim编辑/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

<code>min_idle_connections = 1,</code>

<code>max_idle_connections = 1,</code>

注意：以上生产环境可以不修改

2.3.2、启动MySQL-Proxy

1）先尝试检查端口与获取帮助

<code>lsof</code> <code>-i TCP:3306</code>

<code>/usr/local/mysql-proxy/bin/mysql-proxy</code> <code>--help-proxy</code>

显示如下：

5

6

7

8

9

10

11

12

13

14

15

<code>Usage:</code>

<code>  </code><code>mysql-proxy [OPTION...] - MySQL Proxy</code>

<code>proxy-module</code>

<code>  </code><code>-P, --proxy-address=&lt;host:port&gt;                         listening address:port of the proxy-server (default: :4040)</code>

<code>  </code><code>-r, --proxy-</code><code>read</code><code>-only-backend-addresses=&lt;host:port&gt;     address:port of the remote slave-server (default: not </code><code>set</code><code>)</code>

<code>  </code><code>-b, --proxy-backend-addresses=&lt;host:port&gt;               address:port of the remote backend-servers (default: 127.0.0.1:3306)</code>

<code>  </code><code>--proxy-skip-profiling                                  disables profiling of queries (default: enabled)</code>

<code>  </code><code>--proxy-fix-bug-25371                                   fix bug </code><code>#25371 (mysqld &gt; 5.1.12) for older libmysql versions</code>

<code>  </code><code>-s, --proxy-lua-script=&lt;</code><code>file</code><code>&gt;                           filename of the lua script (default: not </code><code>set</code><code>)</code>

<code>  </code><code>--no-proxy                                              don't start the proxy-module (default: enabled)</code>

<code>  </code><code>--proxy-pool-no-change-user                             don't use CHANGE_USER to reset the connection coming from the pool (default: enabled)</code>

<code>  </code><code>--proxy-connect-timeout                                 connect timeout </code><code>in</code> <code>seconds (default: 2.0 seconds)</code>

<code>  </code><code>--proxy-</code><code>read</code><code>-timeout                                    </code><code>read</code> <code>timeout </code><code>in</code> <code>seconds (default: 8 hours)</code>

<code>  </code><code>--proxy-write-timeout                                   write timeout </code><code>in</code> <code>seconds (default: 8 hours)</code>

如果你看不懂英文，大致意思如下：

<code>-P 指定Proxy服务器工作的地址和端口</code>

<code>-b 指定写服务器的地址和端口</code>

<code>-r 指定读服务器的地址和端口</code>

<code>-s 指定判断的脚本</code>

<code>--daemon 以后台进程的方式启动</code>

2）启动MySQL-Proxy

<code>/usr/local/mysql-proxy/bin/mysql-proxy</code> <code>\</code>

<code>-P 10.168.0.111 \</code>

<code>-b 10.168.0.103:3306 \</code>

<code>-r 10.168.0.104:3306 \</code>

<code>--daemon \</code>

<code>-s </code><code>/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting</code><code>.lua&amp;</code>

检查端口是否在监听

2.3.3、设置防火墙

vim编辑/etc/sysconfig/iptables并加入如下内容：

<code>-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT</code>

重启防火墙：

<code>/etc/init</code><code>.d</code><code>/iptables</code> <code>restart</code>

2.3.4、授权访问

In Master

授权代理可以访问真实数据库：

<code>mysql&gt; grant all on cmdschool.* to [email protected] identified by</code><code>'prxp'</code><code>;</code>

<code>mysql&gt; flush privileges;</code>

代理端尝试连接真实数据库：

<code>mysql -h10.168.0.103 -prxu -prxp</code>

2.3.5、检查与测试

创建测试数据库与测试表：

<code>mysql&gt; create database cmdschool;</code>

<code>mysql&gt; create table t1(</code><code>id</code> <code>varchar(255));</code>

客户端连接代理作查询、插入数据、查询对比前后结果：

<code>mysql -h10.168.0.111 -uprxu -pprxp</code>

<code>mysql&gt; </code><code>select</code> <code>* from cmdschool.t1;</code>

<code>mysql&gt; insert into cmdschool.t1 values ( </code><code>"1"</code> <code>);</code>

In Slave

停止从服务器的复制功能：

<code>mysql -p</code>

<code>mysql&gt; stop slave;</code>

插入第二条测试数据，并尝试查询：

<code>mysql&gt; insert into cmdschool.t1 values ( </code><code>"2"</code> <code>);</code>

查询主库对比代理查询的结果：

查询从库对比代理查询的结果，查询完毕后重新启动从复制：

<code>mysql&gt; start slave;</code>

通过代理插入第三条测试数据：

<code>mysql&gt; insert into cmdschool.t1 values ( </code><code>"3"</code> <code>);</code>

查询主库的结果对比代理的查询结果：

查询从库的结果对比代理的查询结果：

2.3.6、优化和自启动

1）先检查文件打开数

<code>ulimit</code> <code>-a</code>

2）调整文件打开数

<code>ulimit</code> <code>-n 10240</code>

vim编辑/etc/rc.d/rc.local并加入如下内容：

<code>--daemon</code>

本文转自 tanzhenchao 51CTO博客，原文链接：http://blog.51cto.com/cmdschool/1706742，如需转载请自行联系原作者