mysql-proxy實作MySQL讀寫分離
使用二進制格式的mysql-proxy的安裝配置
- mysql-proxy本身并不能進行讀寫分離,要實作讀寫分離要依賴于lua,是以要先檢視是否安裝了lua,如果沒有安裝使用yum install安裝上即可
~]# rpm -q lua lua-5.1.4-4.1.el6.x86_64
- 建立系統使用者
useradd -r mysql-proxy
- 解壓包建立連結檔案
tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/ ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
- 導出二進制程式
~]# vim /etc/profile.d/mysql-proxy.sh 内容為: export PATH=/usr/loca/mysql-proxy/bin/:$PATH ~]# source /etc/profile.d/mysql-proxy.sh
- 檢視mysql-proxy用法
~]# mysql-proxy --help-all #比較重要的選項有以下幾個 --daemon #讓mysql-proxy在背景工作 --user=<user> #指定運作mysql-proxy程序的屬主 --proxy-backend-addresses=<host:port> #後端伺服器 --proxy-read-only-backend-addresses=<host:port> #隻允許讀的後端伺服器 --log-level=(error|warning|info|message|debug) #指定日志級别 --log-file=<file> #指定日志檔案路徑 --plugins=<name> #要加載的插件名,插件路徑在/usr/local/mysql-proxy/lib/mysql-proxy/plugins,該路徑下有一個libadmin.so的插件,可以用來管理後端伺服器,還有libproxy.so的插件,啟用proxy的功能 --keepalive #如果proxy崩潰了,就會嘗試去重新開機proxy --proxy-lua-script=<file> #指定要使用的lua腳本檔案,lua腳本路徑在/usr/local/mysql-proxy/share/doc/mysql-proxy,該路徑下有一個實作讀寫分離的lua腳本檔案rw-splitting.lua --defaults-file #指定配置檔案路徑
- 啟用mysql-proxy
~]# mysql-proxy --daemon \ > --user=mysql-proxy \ > --log-level=debug \ > --log-file=/var/log/mysql-proxy.log \ > --plugins=proxy \ > --plugins=admin \ > --proxy-backend-addresses=172.25.78.2:3306 \ > --proxy-read-only-backend-addresses=172.25.78.3:3306 \ > --keepalive=true \ > --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua \ > --admin-username=admin \ > --admin-password=adminpass \ > --admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
- 檢視啟用的端口
]# netstat -tan | grep :40 tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN #4041是admin管理接口的端口,4040是mysql-proxy的端口
連接配接測試
- 在代理伺服器上(172.25.78.4)連接配接到管理接口檢視有哪些後端伺服器
mysql -uadmin -padminpass -h172.25.78.4 --port=4041 MySQL [(none)]> SELECT * FROM backends; +-------------+------------------+---------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+------------------+---------+------+------+-------------------+ | 1 | 172.25.78.2:3306 | unknown | rw | NULL | 0 | | 2 | 172.25.78.3:3306 | unknown | ro | NULL | 0 | +-------------+------------------+---------+------+------+-------------------+
-
MariaDB [(none)]> GRANT SELECT,INSERT,UPDATE ON *.* TO 'test'@'172.25.78.%' IDENTIFIED BY 'testpass';
-
~]# mysql -utest -ptestpass -h172.25.78.4 -P4040 MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | MYDB | | S_SC_C | | db_user | | discuz | | dvwa | | hello | | mysql | | performance_schema | | test | | testdb | +--------------------+ MariaDB [(none)]> USE S_SC_C; MariaDB [S_SC_C]> INSERT INTO S_1(sname,sdept) VALUES ('H','AA'); MariaDB [S_SC_C]> SELECT * FROM S_1; +-----+-------+-------+ | sid | sname | sdept | +-----+-------+-------+ | 1 | HELLO | A | | 2 | HE | A | | 3 | H | AA | +-----+-------+-------+
-
MariaDB [S_SC_C]> SELECT * FROM S_1; +-----+-------+-------+ | sid | sname | sdept | +-----+-------+-------+ | 1 | HELLO | A | | 2 | HE | A | | 3 | H | AA | +-----+-------+-------+
- 在slave上檢視
MariaDB [S_SC_C]> SELECT * FROM S_1; +-----+-------+-------+ | sid | sname | sdept | +-----+-------+-------+ | 1 | HELLO | A | | 2 | HE | A | | 3 | H | AA | +-----+-------+-------+