ITPub博客

首页 > 数据库 > MySQL > mysql proxy 安装及配置实现读写分离

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

原创 MySQL 作者:wl365365 时间:2015-06-22 19:22:07 0 删除 编辑
mysql的proxy是官方提供的可以实现读写分离的工具

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


(在实现proxy的读写分离前提需要配置好了主从复制,参见我另外的帖子


1. 下载后的解压缩及是安装

点击(此处)折叠或打开

  1. [root@localhost ~]# useradd -r mysql-proxy
  2. [root@localhost ~]# mkdir /mysqlprox
  3. [root@localhost ~]# chown mysql-proxy:mysql-proxy /mysqlprox/
  4. [root@localhost ~]# chmod 777 /mysqlprox/
  5. [root@localhost mysqlprox]# tar zvxf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
  6. [root@localhost mysqlprox]# ll
  7. total 11892
  8. drwxr-xr-x 8 7161 wheel 4096 Aug 19 2014 mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit
  9. -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文件

点击(此处)折叠或打开

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

3. 了解mysql-proxy的命令
对于不知道命令是神马,使用mysql-proxy -help或者 mysql-proxy--help-proxy来查看

点击(此处)折叠或打开

  1. [root@localhost ~]# mysql-proxy --help
  2. Usage:
  3.   mysql-proxy [OPTION...] - MySQL Proxy

  4. Help Options:
  5.   -?, --help Show help options
  6.   --help-all Show all help options
  7.   --help-proxy Show options for the proxy-module

  8. Application Options:
  9.   -V, --version Show version
  10.   --defaults-file=<file> configuration file
  11.   --verbose-shutdown Always log the exit code when shutting down
  12.   --daemon Start in daemon-mode
  13.   --user=<user> Run mysql-proxy as user
  14.   --basedir=<absolute path> Base directory to prepend to relative paths in the config
  15.   --pid-file=<file> PID file in case we are started as daemon
  16.   --plugin-dir=<path> path to the plugins
  17.   --plugins=<name> plugins to load
  18.   --log-level=(error|warning|info|message|debug) log all messages of level ... or higher
  19.   --log-file=<file> log all messages in a file
  20.   --log-use-syslog log all messages to syslog
  21.   --log-backtrace-on-crash try to invoke debugger on crash
  22.   --keepalive try to restart the proxy if it crashed
  23.   --max-open-files maximum number of open files (ulimit -n)
  24.   --event-threads number of event-handling threads (default: 1)
  25.   --lua-path=<...> set the LUA_PATH
  26.   --lua-cpath=<...> set the LUA_CPATH


  27. [root@localhost ~]# mysql-proxy --help-proxy
  28. Usage:
  29.   mysql-proxy [OPTION...] - MySQL Proxy

  30. proxy-module
  31.   -P, --proxy-address=<host:port> listening address:port of the proxy-server (default: :4040)
  32.   -r, --proxy-read-only-backend-addresses=<host:port> address:port of the remote slave-server (default: not set)
  33.   -b, --proxy-backend-addresses=<host:port> address:port of the remote backend-servers (default: 127.0.0.1:3306)
  34.   --proxy-skip-profiling disables profiling of queries (default: enabled)
  35.   --proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
  36.   -s, --proxy-lua-script=<file> filename of the lua script (default: not set)
  37.   --no-proxy don\'t start the proxy-module (default: enabled)
  38.   --proxy-pool-no-change-user don\'t use CHANGE_USER to reset the connection coming from the pool (default: enabled)
  39.   --proxy-connect-timeout connect timeout in seconds (default: 2.0 seconds)
  40.   --proxy-read-timeout read timeout in seconds (default: 8 hours)
  41.   --proxy-write-timeout write timeout in seconds (default: 8 hours)


点击(此处)折叠或打开

  1. --help-all :获取全部帮助信息;
  2. -proxy-address-=host:port :代理服务监听的地址和端口;
  3. --admin-address=host:port :管理模块监听的地址和端口;
  4. --proxy-backend-addresses=host:port :后端mysql服务器的地址和端口;
  5. --proxy-read-only-backend-addresses=host:port :后端只读mysql服务器的地址和端口;
  6. --proxy-lua-script=file_name :完成mysql代理功能的Lua脚本;
  7. --daemon :以守护进程模式启动mysql-proxy;
  8. --keepalive :在mysql-proxy崩溃时尝试重启之;
  9. --log-file=/path/to/log_file_name :日志文件名称;
  10. --log-level=level :日志级别;
  11. --log-use-syslog :基于syslog记录日志;
  12. --plugins=plugin:在mysql-proxy启动时加载的插件;
  13. --user=user_name :运行mysql-proxy进程的用户;
  14. --defaults-file=/path/to/conf_file_name : 默认使用的配置文件路径;其配置段使用[mysql-proxy]标识;
  15. --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"
具体的:

点击(此处)折叠或打开

  1. [root@mysql-proxy ~]# mysql-proxy
  2. --daemon
  3. --log-level=debug
  4. --user=mysql-proxy
  5. --keepalive
  6. --log-file=/var/log/mysql-proxy.log
  7. --plugins=\"proxy\"
  8. --proxy-backend-addresses=\"192.168.182.128:3306\"
  9. --proxy-read-only-backend-addresses=\"192.168.182.129:3306\"
  10. --proxy-lua-script=\"/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua\"
  11. --plugins=admin --admin-username=\"admin\" --admin-password=\"admin\"
  12. --admin-lua-script=\"/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua\"


启动后查看进程

点击(此处)折叠或打开

  1. [root@localhost proxy]# ps -ef|grep mysql
  2. 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
  3. 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
  4. root 6529 5443 0 16:06 pts/1 00:00:00 grep mysql

点击(此处)折叠或打开

  1. [root@localhost proxy]# netstat -naputl|grep mysql-proxy
  2. tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 6521/mysql-proxy
  3. tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 6521/mysql-proxy
  4. 4040是proxy端口;4041是admin端口;也就是管理

5.  对mysql数据库赋权能够进入

点击(此处)折叠或打开

  1. 在master\\slave上给存在的用户赋权
  2. master:
  3. mysql> grant all on *.* to root@\'%\' identified by \'root\';
  4. slave:
  5. 配置了同步上面会同步过去


6. 测试连接:
测试到4040端口的连接

点击(此处)折叠或打开

  1. [root@mysqldb1 binlog1]# mysql -uroot -proot -h192.168.2.145 -P4040
  2. Warning: Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor. Commands end with ; or \\g.
  4. Your MySQL connection id is 14
  5. Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

  6. Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

  7. Oracle is a registered trademark of Oracle Corporation and/or its
  8. affiliates. Other names may be trademarks of their respective
  9. owners.

  10. Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.

  11. mysql> show databases;
  12. +--------------------+
  13. | Database |
  14. +--------------------+
  15. | information_schema |
  16. | mysql |
  17. | nns |
  18. | performance_schema |
  19. | rep |
  20. | test |
  21. | tts |
  22. +--------------------+
  23. 7 rows in set (0.01 sec)

  24. mysql> exit
  25. Bye


测试到4041端口的连接

点击(此处)折叠或打开

  1. [root@mysqldb1 binlog1]# mysql -uadmin -padmin -h192.168.2.145 -P4041
  2. Warning: Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor. Commands end with ; or \\g.
  4. Your MySQL connection id is 1
  5. Server version: 5.0.99-agent-admin

  6. Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

  7. Oracle is a registered trademark of Oracle Corporation and/or its
  8. affiliates. Other names may be trademarks of their respective
  9. owners.

  10. Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.

  11. mysql> show databases;
  12. ERROR 1105 (07000): use \'SELECT * FROM help\' to see the supported commands
  13. mysql> select * from help;
  14. +------------------------+------------------------------------+
  15. | command | description |
  16. +------------------------+------------------------------------+
  17. | SELECT * FROM help | shows this help |
  18. | SELECT * FROM backends | lists the backends and their state |
  19. +------------------------+------------------------------------+
  20. 2 rows in set (0.00 sec)

  21. mysql> SELECT * FROM backends;
  22. +-------------+--------------------+---------+------+------+-------------------+
  23. | backend_ndx | address | state | type | uuid | connected_clients |
  24. +-------------+--------------------+---------+------+------+-------------------+
  25. | 1 | 192.168.2.143:3306 | up | rw | NULL | 0 |
  26. | 2 | 192.168.2.144:3306 | unknown | ro | NULL | 0 |
  27. +-------------+--------------------+---------+------+------+-------------------+
  28. 2 rows in set (0.00 sec)


这里发现144数据库上的state为unknown的状态,这是因为rw-splitting.lua脚本默认有4个链接才启用分离;所以多开启几个终端;
或者可以去修改里面的相关值;


点击(此处)折叠或打开

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22039464/viewspace-1708258/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 初识MYSQL
请登录后发表评论 登录
全部评论

注册时间:2009-07-04

  • 博文量
    29
  • 访问量
    57563