mysql-proxy實現讀寫分離
MySQL Proxy是一個處于你的client端和MySQL server端之間的簡單程序，它可以監測、分析或改變它們的通信。它使用靈活，沒有限制，常見的用途包括：負載平衡，故障、查詢分析，查詢過濾和修改等等。
MySQL Proxy就是這么一個中間層代理，簡單的說，MySQL Proxy就是一個連接池，負責將前臺應用的連接請求轉發給后臺的數據庫，并且通過使用lua腳本，可以實現復雜的連接控制和過濾，從而實現讀寫分離和負載平衡。對于應用來說，MySQL Proxy是完全透明的，應用則只需要連接到MySQL Proxy的監聽端口即可。當然，這樣proxy機器可能成為單點失效，但完全可以使用多個proxy機器做為冗余，在應用服務器的連接池配置中配置到多個proxy的連接參數即可。
MySQL Proxy更強大的一項功能是實現“讀寫分離”，基本原理是讓主數據庫處理事務性查詢，讓從庫處理SELECT查詢。數據庫復制被用來把事務性查詢導致的變更同步到集群中的從庫。
1、安裝mysql-proxy
此處下載安裝包http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-Proxy/
如果是編譯安裝依賴包有 libevent2 1.x lua 5.1.x glibc2 2.6.0 pkg-config libtool 1.5
這里使用glibc的二進制包解壓即可mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz
|
1
2
|
tar -zxvf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz mv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy |
2、創建好mysql主從復制
|
1
2
|
master 192.168.216.133：3306slave 192.168.216.132：3306 |
3、配置mysql-proxy
創建mysql-proxy配置文件，配置文件中的所有選擇都不能加引號
|
1
|
vim /usr/local/mysql-proxy/mysql-proxy.conf |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
[mysql-proxy] daemon=true #以后臺守護進程方式啟動 keepalive=true #當進程故障后自動重啟 log-level=debug #設置日志級別為debug，可以在調試完成后改成info log-file=/var/log/mysql-proxy.log #設置日志文件路徑 basedir=/usr/local/mysql-proxy #設置mysql-proxy的家目錄 proxy-address=192.168.216.132:4040 #指定mysql-proxy的監聽地址 proxy-backend-addresses=192.168.216.133:3306 #設置后臺主服務器 proxy-read-only-backend-addresses=192.168.216.132:3306 #設置后臺從服務器 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua #設置讀寫分離腳本路徑 admin-address=192.168.216.132:4041 #設置mysql-proxy管理地址，需要家長admin插件 admin-username=admin #設置登錄管理地址用戶 admin-password=admin #設置管理用戶密碼 admin-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua #設置管理后臺lua腳本路徑，改腳本默認沒有要自動定義 |
配置完mysql-proxy.conf后需要確保該文件的權限是600，并確保包含個lua腳本
通過配置文件啟動mysql-proxy
|
1
2
3
4
|
/usr/local/mysql-proxy/bin/mysql-proxy --plugins=proxy --plugins=admin --defaults-file=mysql-proxy.conf --plugins=proxy #指定proxy插件，該配置寫入配置文件無法啟動 --plugins=admin #指定admin插件 --defaults-file=mysql-proxy.conf #指定配置文件 |
4、啟動測試
登錄管理地址查看當前狀態
|
1
|
mysql -uadmin -padmin -h192.168.216.132 -P4041 |
兩個后端服務器當前狀態為unknown是因為沒有用戶通過mysql-proxy連接到后端
mysql-proxy不對用戶做身份驗證，而是下身份驗證交予后端服務器進行驗證的，因此需要在后端服務器上對mysql-proxy開放權限
下面是自定義的admin.lua
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
function set_error(errmsg) proxy.response = { type = proxy.MYSQLD_PACKET_ERR, errmsg = errmsg or "error" } endfunction read_query(packet) if packet:byte() ~= proxy.COM_QUERY then set_error("[admin] we only handle text-based queries (COM_QUERY)") return proxy.PROXY_SEND_RESULT end local query = packet:sub(2) local rows = { } local fields = { } if query:lower() == "select * from backends" then fields = { { name = "backend_ndx",type = proxy.MYSQL_TYPE_LONG }, { name = "address",type = proxy.MYSQL_TYPE_STRING }, { name = "state",type = proxy.MYSQL_TYPE_STRING }, { name = "type",type = proxy.MYSQL_TYPE_STRING }, { name = "uuid",type = proxy.MYSQL_TYPE_STRING }, { name = "connected_clients",type = proxy.MYSQL_TYPE_LONG }, } for i = 1, #proxy.global.backends do local states = { "unknown", "up", "down" } local types = { "unknown", "rw", "ro" } local b = proxy.global.backends[i] rows[#rows + 1] = { i, b.dst.name, -- configured backend address states[b.state + 1], -- the C-id is pushed down starting at 0 types[b.type + 1], -- the C-id is pushed down starting at 0 b.uuid, -- the MySQL Server's UUID if it is managed b.connected_clients -- currently connected clients } end elseif query:lower() == "select * from help" then fields = { { name = "command",type = proxy.MYSQL_TYPE_STRING }, { name = "description",type = proxy.MYSQL_TYPE_STRING }, } rows[#rows + 1] = { "SELECT * FROM help", "shows this help" } rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" } else set_error("use 'SELECT * FROM help' to see the supported commands") return proxy.PROXY_SEND_RESULT endproxy.response = { type = proxy.MYSQLD_PACKET_OK, resultset = { fields = fields, rows = rows } } return proxy.PROXY_SEND_RESULT end |
5、相關問題解決
（1）、如果日志中提示 (debug) [network-mysqld.c:1134]: error on a connection (fd: -1 event: 0). closing client connection.
可以修改 rw-splitting.lua中的min_idle_connections = 4和max_idle_connections = 8的只，將其調大
（2）、如果遇到亂碼需要調整后端mysql的設置的字符集
|
1
2
3
4
|
[mysqld] skip-character-set-client-handshake init-connect = 'SET NAMES utf8' character_set_server = utf8 |
mysqlnd_ms實現mysql讀寫分離
mysqlnd_ms是mysqlnd的一個插件，該插件實現了連接保存和切換、負載均衡、讀寫分離的功能。要想使用mysqlnd_ms的讀寫分離功能必須在安裝php時使用–with-mysqlnd。mysqlnd實現的功能是可以不需要在php服務器上安裝mysql，在php5.3之前編譯安裝php需要通過–with-mysql=/path/to/mysql指定mysql的安裝路徑。
1、安裝mysqlnd_ms模塊
|
1
2
3
4
5
|
tar -zxvf mysqlnd_ms-1.5.2.tgz cd mysqlnd_ms-1.5.2 /usr/local/php/bin/phpize ./configure --with-php-config=/usr/local/php/bin/php-config make && make install |
出現以下類似提示，記錄下面的路徑需要擁有配置php.ini
|
1
2
|
Installing shared extensions: /usr/local/php/lib/php/extensions/no-debug-non-zts-20121212/Installing header files: /usr/local/php/include/php/</span> |
2、編輯 /usr/local/php/etc/php.ini
|
1
2
3
|
extension = /usr/local/php/lib/php/extensions/no-debug-non-zts-20121212/mysqlnd_ms.so mysqlnd_ms.enable = On mysqlnd_ms.config_file = /usr/local/php/etc/mysqlnd_ms_plugin.ini |
3、創建mysqlnd_ms_plugin.ini配置文件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
{ "myapp": { "master": { "master_0": { "host": "192.168.6.135", "socket": "\/tmp\/mysql.sock" } "slave": { "slave_0": { "host": "192.168.6.136", "port": "3306" "slave_1": { "host": "192.168.6.137", "port": "3306" "filters": { "random": { "sticky": "1" } } }} |
這里使用到了1主2從的mysql服務器
filters是定義訪問從服務器的策略，random是隨機選擇一臺服務器，strick參數設置成1是指將一次請求都指向一臺服務器
4、測試
使用wordpress進行測試，編輯配置文件wp-config.php
|
1
2
|
/** MySQL主機 */ define('DB_HOST', 'myapp'); #這的myapp是在mysqlnd_ms_plugin.ini中定義的 |
