.png)
透過MySQL Proxy實現讀寫分離
既上一篇文章MYSQL REPLICATION (MASTER SLAVE) 建置之後，若要實現讀寫分離則需要透過SQL Proxy來實現，以下分享安裝與設定方法以及過程中遇到的問題解決方法和需注意的事項
環境:
OS: Ubuntu 14.10
MySQL: 5.5
MySQL-Proxy 安裝與設定
1. 安裝 mysql-proxy
$ sudo apt-get install mysql-proxy
|
2. 安裝 lua script (proxy實現讀寫分離需透過rw-splitting.lua script，故需安裝lua)
$ sudo apt-get install lua5.2
|
3. 編輯設定
$ sudo vim /etc/default/mysql-proxy
ENABLED="true"
OPTIONS="--defaults-file=/etc/mysql/mysql-proxy.cnf"
|
$ sudo vim /etc/mysql/mysql-proxy.cnf
[mysql-proxy]
daemon = true user = mysql proxy-skip-profiling = true keepalive = true max-open-files = 2048 event-threads = 50 pid-file = /var/run/mysql-proxy.pid log-file = /var/log/mysql-proxy.log log-level = debug
#主控台 IP、PORT ex. 192.168.0.100:4401
admin-address = [Proxy-Server IP]:[Port] #用於登入主控台的帳號 admin-username = [account] #特別注意密碼需與連結的sql master密碼一致 admin-password = [password] admin-lua-script = /usr/lib/mysql-proxy/lua/admin.lua
#供web連結使用 ex. 192.168.0.100:3307
proxy-address = [Proxy-Server IP]:[Port]
#master node IP、PORT，mysql預設port:3306
proxy-backend-addresses = [Master-Server IP]:[Port]
#slave node IP、PORT，多台可用逗點隔開 ex. 192.168.0.102:3306,
192.168.0.103:3306 proxy-read-only-backend-addresses=[Slave-Server IP]:[Port]
#設定讀寫分離腳本
proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua |
4. 啟動mysql-proxy
$ sudo service mysql-proxy start
|
proxy主控台登入
$ mysql -h [proxy-server ip] -P [port] -u [account] -p #ex. mysql -h 192.168.0.100 -P 4401 -u root -p
|
目前mysql-proxy主控台僅支援兩個指令
透過指令 select * from backends 可檢視目前所有node狀態
啟用分流的連線數設定，編輯rw-splitting.lua檔
$ sudo vim /usr/share/mysql-proxy/rw-splitting.lua
|
設定min、max_idle_connections數值:
常見問題:
1. Web Application透過proxy讀取slave資料時會出現亂碼
解決方法在每台node的my.cnf設定檔內的 [mysqld] 加入以下設定:
$ sudo vim /etc/mysql/my.cnf
[mysqld]
skip-character-set-client-handshake
character-set-server = utf8 collation-server = utf8_general_ci init-connect = SET NAMES utf8 |
0 意見: