mysql的proxy是官方提供的可以實現讀寫分離的工具

首先下載mysql-proxy軟體，可以去官網下載，但是我從官網上下載不了，據說是選擇linux平臺的時候
後臺呼叫jquery去國外了，既然翻不了牆，那就老老實實去映象網站下載了

（在實現proxy的讀寫分離前提需要配置好了主從複製，參見我另外的帖子）

1. 下載後的解壓縮及是安裝

點選(此處)摺疊或開啟

[root@localhost ~]# useradd -r mysql-proxy
[root@localhost ~]# mkdir /mysqlprox
[root@localhost ~]# chown mysql-proxy:mysql-proxy /mysqlprox/
[root@localhost ~]# chmod 777 /mysqlprox/
[root@localhost mysqlprox]# tar zvxf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
[root@localhost mysqlprox]# ll
total 11892
drwxr-xr-x 8 7161 wheel 4096 Aug 19 2014 mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit
-rw-r----- 1 root root 12155864 Jun 20 15:45 mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

2.配置bash_profile檔案

點選(此處)摺疊或開啟

[root@localhost ~]# vi .bash_profile
PATH=$PATH:$HOME/bin:/mysqlprox/proxy/bin

3. 瞭解mysql-proxy的命令
對於不知道命令是神馬，使用mysql-proxy -help或者 mysql-proxy--help-proxy來檢視

點選(此處)摺疊或開啟

[root@localhost ~]# mysql-proxy --help
Usage:
mysql-proxy [OPTION...] - MySQL Proxy
Help Options:
-?, --help Show help options
--help-all Show all help options
--help-proxy Show options for the proxy-module
Application Options:
-V, --version Show version
--defaults-file=<file> configuration file
--verbose-shutdown Always log the exit code when shutting down
--daemon Start in daemon-mode
--user=<user> Run mysql-proxy as user
--basedir=<absolute path> Base directory to prepend to relative paths in the config
--pid-file=<file> PID file in case we are started as daemon
--plugin-dir=<path> path to the plugins
--plugins=<name> plugins to load
--log-level=(error|warning|info|message|debug) log all messages of level ... or higher
--log-file=<file> log all messages in a file
--log-use-syslog log all messages to syslog
--log-backtrace-on-crash try to invoke debugger on crash
--keepalive try to restart the proxy if it crashed
--max-open-files maximum number of open files (ulimit -n)
--event-threads number of event-handling threads (default: 1)
--lua-path=<...> set the LUA_PATH
--lua-cpath=<...> set the LUA_CPATH
[root@localhost ~]# mysql-proxy --help-proxy
Usage:
mysql-proxy [OPTION...] - MySQL Proxy
proxy-module
-P, --proxy-address=<host:port> listening address:port of the proxy-server (default: :4040)
-r, --proxy-read-only-backend-addresses=<host:port> address:port of the remote slave-server (default: not set)
-b, --proxy-backend-addresses=<host:port> address:port of the remote backend-servers (default: 127.0.0.1:3306)
--proxy-skip-profiling disables profiling of queries (default: enabled)
--proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
-s, --proxy-lua-script=<file> filename of the lua script (default: not set)
--no-proxy don\'t start the proxy-module (default: enabled)
--proxy-pool-no-change-user don\'t use CHANGE_USER to reset the connection coming from the pool (default: enabled)
--proxy-connect-timeout connect timeout in seconds (default: 2.0 seconds)
--proxy-read-timeout read timeout in seconds (default: 8 hours)
--proxy-write-timeout write timeout in seconds (default: 8 hours)

點選(此處)摺疊或開啟

--help-all ：獲取全部幫助資訊；
-proxy-address-=host:port ：代理服務監聽的地址和埠；
--admin-address=host:port ：管理模組監聽的地址和埠；
--proxy-backend-addresses=host:port ：後端mysql伺服器的地址和埠；
--proxy-read-only-backend-addresses=host:port ：後端只讀mysql伺服器的地址和埠；
--proxy-lua-script=file_name ：完成mysql代理功能的Lua指令碼；
--daemon ：以守護程式模式啟動mysql-proxy；
--keepalive ：在mysql-proxy崩潰時嘗試重啟之；
--log-file=/path/to/log_file_name ：日誌檔名稱；
--log-level=level ：日誌級別；
--log-use-syslog ：基於syslog記錄日誌；
--plugins=plugin：在mysql-proxy啟動時載入的外掛；
--user=user_name ：執行mysql-proxy程式的使用者；
--defaults-file=/path/to/conf_file_name ：預設使用的配置檔案路徑；其配置段使用[mysql-proxy]標識；
--proxy-skip-profiling ：禁用profile；

瞭解了命令後

4. 啟動mysql-proxy
[root@mysql-proxy ~]# mysql-proxy --daemon --log-level=debug --user=mysql-proxy --keepalive --log-file=/mysqlprox/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.2.143:3306" --proxy-read-only-backend-addresses="192.168.2.144:3306" --proxy-lua-script="/mysqlprox/proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/mysqlprox/proxy/lib/mysql-proxy/lua/admin.lua"
具體的：

點選(此處)摺疊或開啟

[root@mysql-proxy ~]# mysql-proxy
--daemon
--log-level=debug
--user=mysql-proxy
--keepalive
--log-file=/var/log/mysql-proxy.log
--plugins=\"proxy\"
--proxy-backend-addresses=\"192.168.182.128:3306\"
--proxy-read-only-backend-addresses=\"192.168.182.129:3306\"
--proxy-lua-script=\"/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua\"
--plugins=admin --admin-username=\"admin\" --admin-password=\"admin\"
--admin-lua-script=\"/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua\"

啟動後檢視程式

點選(此處)摺疊或開啟

[root@localhost proxy]# ps -ef|grep mysql
root 6520 1 0 16:06 ? 00:00:00 /mysqlprox/proxy/libexec/mysql-proxy --daemon --log-level=debug --user=mysql-proxy --keepalive --log-file=/mysqlprox/mysql-proxy.log --plugins=proxy --proxy-backend-addresses=192.168.2.143:3306 --proxy-read-only-backend-addresses=192.168.2.144:3306 --proxy-lua-script=/mysqlprox/proxy/share/doc/mysql-proxy/rw-splitting.lua --plugins=admin --admin-username=admin --admin-password=admin --admin-lua-script=/mysqlprox/proxy/lib/mysql-proxy/lua/admin.lua
101 6521 6520 0 16:06 ? 00:00:00 /mysqlprox/proxy/libexec/mysql-proxy --daemon --log-level=debug --user=mysql-proxy --keepalive --log-file=/mysqlprox/mysql-proxy.log --plugins=proxy --proxy-backend-addresses=192.168.2.143:3306 --proxy-read-only-backend-addresses=192.168.2.144:3306 --proxy-lua-script=/mysqlprox/proxy/share/doc/mysql-proxy/rw-splitting.lua --plugins=admin --admin-username=admin --admin-password=admin --admin-lua-script=/mysqlprox/proxy/lib/mysql-proxy/lua/admin.lua
root 6529 5443 0 16:06 pts/1 00:00:00 grep mysql

點選(此處)摺疊或開啟

[root@localhost proxy]# netstat -naputl|grep mysql-proxy
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 6521/mysql-proxy
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 6521/mysql-proxy
4040是proxy埠；4041是admin埠；也就是管理

5. 對mysql資料庫賦權能夠進入

點選(此處)摺疊或開啟

在master\\slave上給存在的使用者賦權
master:
mysql> grant all on *.* to root@\'%\' identified by \'root\';
slave：
配置了同步上面會同步過去

6. 測試連線：
測試到4040埠的連線

點選(此處)摺疊或開啟

[root@mysqldb1 binlog1]# mysql -uroot -proot -h192.168.2.145 -P4040
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 14
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| nns |
| performance_schema |
| rep |
| test |
| tts |
+--------------------+
7 rows in set (0.01 sec)
mysql> exit
Bye

測試到4041埠的連線

點選(此處)摺疊或開啟

[root@mysqldb1 binlog1]# mysql -uadmin -padmin -h192.168.2.145 -P4041
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
mysql> show databases;
ERROR 1105 (07000): use \'SELECT * FROM help\' to see the supported commands
mysql> select * from help;
+------------------------+------------------------------------+
| command | description |
+------------------------+------------------------------------+
| SELECT * FROM help | shows this help |
| SELECT * FROM backends | lists the backends and their state |
+------------------------+------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM backends;
+-------------+--------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+--------------------+---------+------+------+-------------------+
| 1 | 192.168.2.143:3306 | up | rw | NULL | 0 |
| 2 | 192.168.2.144:3306 | unknown | ro | NULL | 0 |
+-------------+--------------------+---------+------+------+-------------------+
2 rows in set (0.00 sec)

這裡發現144資料庫上的state為unknown的狀態，這是因為rw-splitting.lua指令碼預設有4個連結才啟用分離；所以多開啟幾個終端；
或者可以去修改裡面的相關值；

點選(此處)摺疊或開啟

mysql> SELECT * FROM backends;
+-------------+--------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+--------------------+---------+------+------+-------------------+
| 1 | 192.168.2.143:3306 | up | rw | NULL | 0 |
| 2 | 192.168.2.144:3306 | up | ro | NULL | 0 |
+-------------+--------------------+---------+------+------+-------------------+
2 rows in set (0.00 sec)

mysql proxy 安裝及配置實現讀寫分離

相關文章