MySQL Proxy簡介

標籤：

zhanhailiang 日期：2014-12-31

MySQL Proxy是介於MySQL Client端和MySQL Server端之間的中介軟體，可以監測、分析或改變它們的通訊。由於其MySQL Proxy實現MySQL C/S通訊協定，所以其對應用是透明，即應用把MySQL Proxy當成MySQL Server，只需要將原先直連的MySQL host:port修改成MySQL Proxy host:port即可；

其主要應用情境：

1. 充當MySQL串連池。
2. 通過Lua監測、分析或改變SQL DML，如串連控制，過濾，實現讀寫分離和負載平衡。其中，MySQL Proxy實現“讀寫分離”的基本原理是讓主要資料庫處理事務性查詢，讓從庫處理SELECT查詢。資料庫複寫被用來把事務性查詢導致的變更同步到叢集中的從庫。

接下來介紹如何安裝與使用MySQL Proxy。

1. 安裝

下載源碼包，擷取Lua測試指令碼

wget http://cdn.mysql.com/Downloads/MySQL-Proxy/mysql-proxy-0.8.5.tar.gztar zxvf mysql-proxy-0.8.5.tar.gz

下載二進位包，安裝

wget http://cdn.mysql.com/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gztar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gzmv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/cd /usr/local/ln -s mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy

2. 配置

添加環境變數到~/.bashrc

PATH=/usr/local/mysql-proxy/bin:$PATH

建立MySQL Proxy設定檔 /usr/local/mysql-proxy/etc/master.conf， 注意將master.conf檔案許可權修改成0660

[mysql-proxy]log-file = /var/log/mysql-proxy.loglog-level = messageproxy-backend-addresses = 127.0.0.1:3306

3. 測試

啟動Mysql Proxy，添加--daemon將以守護進程方式運行，預設MySQL Proxy監聽4040連接埠：

mysql-proxy --defaults-file=/usr/local/mysql-proxy/etc/master.conf --proxy-lua-script=/root/software/mysql-proxy-0.8.5/examples/tutorial-query-time.lua

通過MySQL用戶端直連MySQL Proxy即可：

[[email protected]~/software/mysql-proxy-0.8.5/examples]# /usr/local/mysql/bin/mysql --host=127.0.0.1 --port=4040 -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 65Server version: 5.5.39-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql>

測試Lua指令碼，測試指令碼test.php如下：

$db = mysql_connect(‘127.0.0.1:4040‘, ‘root‘, ‘******‘);if (!$db) {    exit(-1);}mysql_select_db(‘test‘, $db);$result = mysql_query(‘SELECT * FROM test.test‘, $db);while ($row = mysql_fetch_assoc($result)) {    var_dump($row);}

執行test.php：php test.php，其輸出如下：

array(1) {  ["id"]=>  string(1) "1"}array(1) {  ["id"]=>  string(1) "2"}

查看MySQL Proxy日誌如下：

[root@/usr/local]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/etc/master.conf --proxy-lua-script=/root/software/mysql-proxy-0.8.5/examples/tutorial-query-time.luawe got a normal query: SELECT * FROM test.testquery-time: 0.3msresponse-time: 0.31ms

4. 更多閱讀

1. MySQL Proxy
2. Appendix A MySQL Proxy FAQ
3. 轉MySQL Proxy的幾篇文章

MySQL Proxy簡介