mysql的proxy是官方提供的可以实现读写分离的工具

首先下载mysql-proxy软件，可以去官网下载，但是我从官网上下载不了，据说是选择linux平台的时候
后台调用jquery去国外了，既然翻不了墙，那就老老实实去镜像网站下载了
http://mirror.bit.edu.cn/mysql/Downloads/MySQL-Proxy/

（在实现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)

ITPub博客

mysql proxy 安装及配置实现读写分离

最新文章