MySQL-Proxy实现读写分离-CSDN博客

本文链接：https://blog.csdn.net/DoloresOOO/article/details/98185071

MySQL-Proxy实现读写分离

环境

hostname	ip
proxy	192.168.220.130
master(mysql主，负责写)	192.168.220.128
slave(mysql从，负责读)	192.168.220.129

数据库安装(略)

https://blog.csdn.net/DoloresOOO/article/details/97392449

数据库主从复制(略)

https://blog.csdn.net/DoloresOOO/article/details/98095394

环境配置

安装支持LUA语言环境,3个节点都安装
[root@proxy ~]# yum install lua -y
[root@master ~]# yum install lua -y
[root@slave ~]# yum install lua -y


下载mysql-proxy包
[root@proxy ~]# wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

解压包
[root@proxy ~]# tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz 
[root@proxy ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
[root@proxy ~]# ls /usr/local/mysql-proxy/share/doc/mysql-proxy/
active-queries.lua       ro-balance.lua           tutorial-resultset.lua
active-transactions.lua  ro-pooling.lua           tutorial-rewrite.lua
admin-sql.lua            rw-splitting.lua         tutorial-routing.lua
analyze-query.lua        tutorial-basic.lua       tutorial-scramble.lua
auditing.lua             tutorial-constants.lua   tutorial-states.lua
commit-obfuscator.lua    tutorial-inject.lua      tutorial-tokenize.lua
commit-obfuscator.msc    tutorial-keepalive.lua   tutorial-union.lua
COPYING                  tutorial-monitor.lua     tutorial-warnings.lua
histogram.lua            tutorial-packets.lua     xtab.lua
load-multi.lua           tutorial-prep-stmts.lua
README                   tutorial-query-time.lua

修改环境变量
[root@proxy ~]# echo "export PATH=$PATH:/usr/local/mysql-proxy/bin" >> /etc/profile 
[root@proxy ~]# source /etc/profile

配置文件

[root@proxy ~]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
 40                 min_idle_connections = 4,
 41                 max_idle_connections = 8,
修改为
 40                 min_idle_connections = 1,
 41                 max_idle_connections = 1,
意思是当有一个链接的时候，就实现读写分离的功能。改为1这里进行测试

[root@proxy ~]# mysql-proxy --help-all
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

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)

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

  
#--proxy-read-only-backend-addresses定义只读服务器
#--proxy-backend-addresses定义主服务器
#lua脚本--proxy-lua-script  
[root@proxy ~]# mysql-proxy --proxy-read-only-backend-addresses=192.168.220.129:3306 --proxy-backend-addresses=192.168.220.128:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &
[1] 10327
[root@proxy ~]# 2019-08-02 19:43:24: (critical) plugin proxy 0.8.5 started

[root@proxy ~]# netstat -antup | grep 4040
tcp        0      0 0.0.0.0:4040            0.0.0.0:*               LISTEN      10327/mysql-proxy

mysql-proxy测试

[root@proxy ~]# mysql -uroot -p -P4040 -h 192.168.220.130
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.61-log MySQL Community Server (GPL)

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

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 processlist
    -> ;
+----+------+-----------------------+------+---------+------+-------+------------------+
| Id | User | Host                  | db   | Command | Time | State | Info             |
+----+------+-----------------------+------+---------+------+-------+------------------+
| 12 | root | 192.168.220.130:52136 | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

[root@proxy ~]# lsof -i:3306
COMMAND     PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld    13301 mysql   10u  IPv4  89254      0t0  TCP *:mysql (LISTEN)
mysql-pro 13332  root   11u  IPv4  89312      0t0  TCP proxy:52136->master:mysql (ESTABLISHED)
mysql-pro 13332  root   12u  IPv4  90289      0t0  TCP proxy:52138->master:mysql (ESTABLISHED)
mysql-pro 13332  root   13u  IPv4  90926      0t0  TCP proxy:33581->slave:mysql (ESTABLISHED)