Client 存取 | +-------+-----------+ | MySQL Proxy | | 10.10.10.134:3306 | +----------------+--+ / \ /(寫) \ (讀) +----------+--------+ +---------------------+ | MySQL Master |(同步) | MySQL Slaver | | 10.10.10.137:3306 |----->| 10.10.10.135:3306 | +-------------------+ +---------------------+
延續 MySQL Master-Slaver Replication 讓資料庫資料有備援 環境
MySQL Proxy 是一套 Lua 程式語言寫的,用來讓應用程式存取資料庫時,由它來判斷寫入時送給那一台主機,讀則從另一台主機。這個好處是可以把負載分散,也可以把它想成是負載平衡系統,Master 負責寫入,Slaver 負責讀取,Master 同步到 Slaver。
在 proxy 那台用 yum 安裝 mysql proxy
yum -y install mysql-proxy
版本為 mysql-proxy-0.8.5
[mysql-proxy] daemon = true pid-file = /var/run/mysql-proxy.pid log-file = /var/log/mysql-proxy.log log-level = debug max-open-files = 1024 plugins = admin,proxy user = mysql-proxy # # Proxy Configuration proxy-address = 0.0.0.0:3306 proxy-backend-addresses = 10.10.10.137:3306 # 寫入 proxy-read-only-backend-addresses = 10.10.10.135:3306 # 唯讀 proxy-lua-script = /usr/lib64/mysql-proxy/lua/proxy/rw-splitting.lua #proxy-skip-profiling = true # # Admin Configuration admin-address = 0.0.0.0:4041 admin-lua-script = /usr/lib64/mysql-proxy/lua/admin.lua admin-username = admin admin-password = admin
當中的 proxy-lua-script = /usr/lib64/mysql-proxy/lua/proxy/rw-splitting.lua 是在 mysql-proxy-0.8.5.tar.gz lib/ 裡拿來用的 (compiling 額外需要 glib2-devel lua-devel libevent-devel 等套件)
rw-splitting.lua 裡的 min_idle_connections (預設 4) 與 max_idle_connections (預設 8),達到這數值時才會開始作讀寫分散,測試時可以降低它
接著修改 discuz/config/config_global.php 的
$_config['db']['1']['dbhost'] = '10.10.10.134';
讓 discuz 透過 proxy 去寫讀資料庫,再由 proxy 控制寫到那台,讀從那台
並記得在 master 及 slaver 開放可以讓 proxy 使用 discuz_user@10.10.10.134 存取權限
測試 discuz 時,會有亂碼,所以要加 (for mysql v 5.5)
character_set_server=utf8 collation_server=utf8_general_ci init_connect='SET NAMES utf8'
我們可連 proxy admin,查看連線狀態
mysql -u admin -padmin -h 10.10.10.134 -P 4041
輸入
SELECT * FROM help
也只有兩個可用
+------------------------+------------------------------------+ | command | description | +------------------------+------------------------------------+ | SELECT * FROM help | shows this help | | SELECT * FROM backends | lists the backends and their state | +------------------------+------------------------------------+
+-------------+-------------------+---------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+-------------------+---------+------+------+-------------------+ | 1 | 10.10.10.137:3306 | unknown | rw | NULL | 0 | | 2 | 10.10.10.135:3306 | unknown | ro | NULL | 0 | +-------------+-------------------+---------+------+------+-------------------+
我用 ab 測試連線,rw 與 ro 的 connect 各自有數值
+-------------+-------------------+-------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+-------------------+-------+------+------+-------------------+ | 1 | 10.10.10.137:3306 | up | rw | NULL | 3 | | 2 | 10.10.10.135:3306 | up | ro | NULL | 7 | +-------------+-------------------+-------+------+------+-------------------+
我更測試在 master 與 slaver 打開 log,發現 INSTER 是會在 master,但 slaver 還是有 UPDATE
所以這支 rw-splitting.lua 的讀寫分開與我的認知是有落差的嗎 !!
還有還有 console 會一直出現
server default db: client default db: discuz syncronizing
mysql 說 Adding ‘c.default_db ~= “”‘ for default DB check seems to solve the problem:
-- if client and server db don't match, adjust the server-side -- -- skip it if we send a INIT_DB anyway if cmd.type ~= proxy.COM_INIT_DB and c.default_db and c.default_db ~= "" and c.default_db ~= s.default_db then print(" server default db: '" .. s.default_db .. "'") print(" client default db: '" .. c.default_db .. "'") print(" syncronizing") proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db) end
但我修了還是沒用,不過其實不影響分散功能,是否有其它問題,尚未發現
關閉 STDOUT,不會一直跳出訊息,在 print 前後加入 if is_debug then end 即可,除非 is_debug = true
if is_debug then print(" server default db: " .. s.default_db) print(" client default db: " .. c.default_db) print(" syncronizing") proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db, { resultset_is_needed = true }) end
留言