mysql-proxy实现读写分离

最新推荐文章于 2024-09-13 19:51:04 发布

「已注销」

最新推荐文章于 2024-09-13 19:51:04 发布

阅读量230

点赞数

文章标签： mysql proxy

本文链接：https://blog.csdn.net/hello_xiaozhuang/article/details/81639491

版权

一：mysql主从数据库的配置

要求：配置主从复制： server2 主 172.25.55.2
                   server3 从 172.25.55.3

二：安装配置mysql-proxy

[root@server1 /]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit -C /usr/local/
[root@server1 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy  
[root@server1 local]# mkdir /usr/local/mysql-proxy/conf
[root@server1 local]# mkdir /usr/local/mysql-proxy/log

[root@server1 local]# vim mysql-proxy/conf/mysql-proxy.conf ##编译配置文件

[mysql-proxy]
daemon=true  
user=root     
keepalive=true
plugins=proxy,admin
log-level=info    
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log  
proxy-address=172.25.55.1:3306  
proxy-backend-addresses=172.25.55.2:3306  
proxy-read-only-backend-addresses=172.25.55.3:3306 
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua 
admin-address=172.25.55.1:4041   
admin-username=admin
admin-password=westos
admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua

更改lua脚本
使其快速进入读写分离状态

-- connection pool
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1,  
                max_idle_connections = 2, 

                is_debug = false
        }

开启proxy控制

[root@server1 local]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

根据日志可以查看已经控制到mysql

过滤端口：

[root@server1 mysql-proxy]# netstat -anltp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      936/sshd            
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      1012/master         
tcp        0      0 172.25.55.1:4041            0.0.0.0:*                   LISTEN      1410/mysql-proxy    
tcp        0      0 172.25.55.1:3306            0.0.0.0:*                   LISTEN      1410/mysql-proxy    
tcp        0      0 172.25.55.1:53135           172.25.55.2:3306            TIME_WAIT   -                   
tcp        0      0 172.25.55.1:22              172.25.55.250:55476         ESTABLISHED 1072/sshd           
tcp        0      0 172.25.55.1:53137           172.25.55.2:3306            TIME_WAIT   -                   
tcp        0      0 :::22                       :::*                        LISTEN      936/sshd            
tcp        0      0 ::1:25                      :::*                        LISTEN      1012/master

在master（server1）端给zpy用户权限：

mysql> grant all on test.* to repl@'%' identified by 'westos';
Query OK, 0 rows affected (0.00 sec)

下来我们开始检测在mysql安装lsof：

[root@server2 ~]# yum install -y lsof

重新开一台设备：

[root@foundation28 mysql10.21]# mysql -h 172.25.55.1 -u zpy -p   ##通过访问proxy，直接转到mysql
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.71-log Source distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

如上每登陆一次，使用lsof观察3306 端口，连接一次

[root@server2 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1411 mysql   11u  IPv4   9514      0t0  TCP *:mysql (LISTEN)
mysqld  1411 mysql   31u  IPv4  10201      0t0  TCP 172.25.55.2:mysql->172.25.55.1:53132 (ESTABLISHED)

当连接数大于2（lua脚本中设置的时）；即启动了读写分离机制：
我们可以登陆admin proxy管理用户查看：

[root@foundation55 mysql10.21]# mysql -h 172.25.55.1 -u admin -pwestos -P 4041;
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> select * from backends;
+-------------+------------------+---------+------+------+-------------------+
| backend_ndx | address          | state   | type | uuid | connected_clients |
+-------------+------------------+---------+------+------+-------------------+
|           1 | 172.25.55.2:3306 | up      | rw   | NULL |                 0 |
|           2 | 172.25.55.3:3306 | unknown | ro   | NULL |                 0 |
+-------------+------------------+---------+------+------+-------------------+
2 rows in set (0.00 sec)

MySQL [(none)]> Bye
[root@foundation55 mysql10.21]#