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，如需轉載請自行聯系原作者