利用mysql-proxy实现mysql的读写分离

最新推荐文章于 2021-12-18 15:15:10 发布

love_sunshine_999

最新推荐文章于 2021-12-18 15:15:10 发布

阅读量248

点赞数

本文链接：https://blog.csdn.net/love_sunshine_999/article/details/81661722

版权

mysql的读写分离

概述：

MySQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。
使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，
对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上

安装包：

mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

实验环境：

Mater端：server1
Slave端：server2
mysql-proxy端：server3
做此实现之前需要先将server1与server2基于GTID的主从复制做好

实验：

[root@server3 ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz 
[root@server3 ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server3 ~]# cd mysql-proxy-0.8.5-linux-el6-x86-64bit
[root@server3 mysql-proxy-0.8.5-linux-el6-x86-64bit]# ls
bin  include  lib  libexec  licenses  share
[root@server3 mysql-proxy-0.8.5-linux-el6-x86-64bit]# cd ..
[root@server3 ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server3 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
[root@server3 ~]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# ls
bin  include  lib  libexec  licenses  share
[root@server3 mysql-proxy]# cd bin/
[root@server3 bin]# pwd
/usr/local/mysql-proxy/bin
[root@server3 bin]# vim ~/.bash_profile

这里写图片描述

[root@server3 bin]# source ~/.bash_profile

[root@server3 bin]# cd 
[root@server3 ~]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# ls
bin  conf  include  lib  libexec  licenses  share
[root@server3 mysql-proxy]# mkdir conf/
[root@server3 mysql-proxy]# cd conf/
[root@server3 conf]# ls
[root@server3 conf]# pwd
/usr/local/mysql-proxy/conf
[root@server3 conf]# vim mysql-proxy.conf
####################
写入：
[mysql-proxy]
user=root
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.52.1:3306
proxy-read-only-backend-addresses=172.25.52.2:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
log-level=debug
daemon=ture
keepalive=ture

这里写图片描述

[root@server3 conf]# cd ..
[root@server3 mysql-proxy]# ls
bin  conf  include  lib  libexec  licenses  share
[root@server3 mysql-proxy]# cd share/doc/
[root@server3 doc]# ls
mysql-proxy
[root@server3 doc]# cd mysql-proxy/
[root@server3 mysql-proxy]# ls
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@server3 mysql-proxy]# pwd
/usr/local/mysql-proxy/share/doc/mysql-proxy
##更改最小连接数与最大连接数
[root@server3 mysql-proxy]# vim rw-splitting.lua

这里写图片描述

[root@server3 mysql-proxy]# cd ..
[root@server3 doc]# cd ..
[root@server3 share]# cd ..
[root@server3 mysql-proxy]# pwd
/usr/local/mysql-proxy
[root@server3 mysql-proxy]# mysql-proxy  --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf 
2018-08-14 10:13:50: (critical) mysql-proxy-cli.c:326: loading config from '/usr/local/mysql-proxy/conf/mysql-proxy.conf' failed: permissions of /usr/local/mysql-proxy/conf/mysql-proxy.conf aren't secure (0660 or stricter required)
2018-08-14 10:13:50: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2018-08-14 10:13:50: (message) shutting down normally, exit code is: 1
##授权
[root@server3 mysql-proxy]# chmod  660 /usr/local/mysql-proxy/conf/mysql-proxy.conf 
[root@server3 conf]# mysql-proxy  --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf 
[root@server3 conf]# ps ax
  PID TTY      STAT   TIME COMMAND
  .................
 1495 ?        S      0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-fi
 1496 ?        S      0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-fi
 1497 pts/0    R+     0:00 ps ax
 ##可查看到3306端口
 [root@server3 conf]# netstat -antlp
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:6379                0.0.0.0:*                   LISTEN      1017/redis-server 0 
tcp        0      0 0.0.0.0:80                  0.0.0.0:*                   LISTEN      1111/nginx          
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      1010/sshd           
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      1095/master         
tcp        0      0 0.0.0.0:3306 #              0.0.0.0:*                   LISTEN      1496/mysql-proxy    
tcp        0      0 172.25.52.3:22              172.25.52.250:41900         ESTABLISHED 1254/sshd           
tcp        0      0 172.25.52.3:36196           172.25.52.1:3306            ESTABLISHED 1496/mysql-proxy    
tcp        0      0 172.25.52.3:22              172.25.52.250:41972         ESTABLISHED 1311/sshd           
tcp        0      0 :::22                       :::*                        LISTEN      1010/sshd           
tcp        0      0 ::1:25                      :::*                        LISTEN      1095/master         
tcp        0      0 :::11111                    :::*                        LISTEN      1199/ricci          
##在master端给root用户授权：
mysql>  grant all privileges on *.* to root@'%' identified by 'HYM*hyf5532'; Query OK, 0 rows affected, 1 warning (0.14 sec)

测试：

[root@foundation52 Desktop]# mysql -h 172.25.52.1 -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.17-log MySQL Community Server (GPL)

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)]> quit
Bye
[root@foundation52 Desktop]# mysql -h 172.25.52.2 -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.17 MySQL Community Server (GPL)

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)]> quit
Bye
##第一次登陆
[root@foundation52 Desktop]# mysql -h 172.25.52.3 -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.17-log MySQL Community Server (GPL)

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 test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.00 sec)

排错：

##如果查看进程只有一个
[root@server3 conf]# ps ax
  PID TTY      STAT   TIME COMMAND
  ..............
 1462 ?        S      0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-fi
##解决方案:
1.杀死进程 多刷新几次
2.检查编写配置文件

##登陆1.2可以  但登陆3时登陆不上 
 [root@foundation52 Desktop]# mysql -h 172.25.52.3 -u root -p
Enter password: 
^Z  
[1]+  Stopped                 mysql -h 172.25.52.3 -u root -p
##此时原因有两个：

1.没有给root用户授权：
#在master端授权
mysql>  grant all privileges on *.* to root@'%' identified by 'HYM*hyf5532'; Query OK, 0 rows affected, 1 warning (0.14 sec)

2.配置文件编写错误：
 vim /usr/local/mysql-proxy/conf/mysql-proxy.conf

[root@server1 ~]# mysql -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> insert into test.userlist values('user2','222');
Query OK, 1 row affected (0.14 sec)

mysql> quit
Bye
[root@server1 ~]# yum install lsof -y
[root@server1 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2019 mysql   29u  IPv6  10827      0t0  TCP *:mysql (LISTEN)
mysqld  2019 mysql   49u  IPv6  10935      0t0  TCP server1:mysql->server2:50381 (ESTABLISHED)
mysqld  2019 mysql   51u  IPv6  11077      0t0  TCP server1:mysql->server3:36198 (ESTABLISHED)

##再打开一个shell 
##第二次登陆
[kiosk@foundation52 ~]$ mysql -h 172.25.52.3 -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.17 MySQL Community Server (GPL)

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 test.usrlist
    -> ;
ERROR 1146 (42S02): Table 'test.usrlist' doesn't exist
MySQL [(none)]> select * from test.userlist
    -> ;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
+----------+----------+
2 rows in set (0.00 sec)

##查看到增加了一条连接信息
[root@server1 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2019 mysql   29u  IPv6  10827      0t0  TCP *:mysql (LISTEN)
mysqld  2019 mysql   49u  IPv6  10935      0t0  TCP server1:mysql->server2:50381 (ESTABLISHED)
mysqld  2019 mysql   51u  IPv6  11077      0t0  TCP server1:mysql->server3:36198 (ESTABLISHED)
mysqld  2019 mysql   55u  IPv6  11074      0t0  TCP server1:mysql->server3:36196 (ESTABLISHED)
##此后无论再登陆几次 都不会增多连接信息 因为文件中已经设定过最大限制连接数为2

##先在主机中插入数据：
MySQL [(none)]> insert into test.userlist values('user3','333');
Query OK, 1 row affected (0.09 sec)
##再在slave端查看是否同步
##必须是在slave端查看  因为主从复制是指master端数据同步到slave端   如果master端有数据 那么slave端 必定有数据 所以直接查看slave端的数据 
[root@server2 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
| user3    | 333      |
+----------+----------+
3 rows in set (0.00 sec)

##注意：一直不要断开
[root@server3 ~]# yum install -y tcpdump
##抓取数据
[root@server3 ~]# tcpdump -i eth0 port 3306
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
************************************************************************
##在主机中插入数据
MySQL [(none)]> insert into test.userlist values('user3','333');
Query OK, 1 row affected (0.09 sec)

MySQL [(none)]> insert into test.userlist values('user4','44');
Query OK, 1 row affected (0.16 sec)

MySQL [(none)]> insert into test.userlist values('user5','555');
Query OK, 1 row affected (0.16 sec)
***********************************************************************
##查看到有新增的连接数据
12:02:36.189903 IP 172.25.52.250.50130 > server3.mysql: Flags [P.], seq 1836413778:1836413830, ack 3262234155, win 237, options [nop,nop,TS val 11887273 ecr 11262929], length 52
12:02:36.189930 IP server3.mysql > 172.25.52.250.50130: Flags [.], ack 52, win 227, options [nop,nop,TS val 11285465 ecr 11887273], length 0
12:02:36.190611 IP server3.36196 > server1.mysql: Flags [P.], seq 2613388824:2613388876, ack 1652841010, win 229, options [nop,nop,TS val 11285466 ecr 10689551], length 52
12:02:36.190740 IP server1.mysql > server3.36196: Flags [.], ack 52, win 227, options [nop,nop,TS val 11288119 ecr 11285466], length 0
12:02:36.348416 IP server1.mysql > server3.36196: Flags [P.], seq 1:12, ack 52, win 227, options [nop,nop,TS val 11288277 ecr 11285466], length 11
12:02:36.348436 IP server3.36196 > server1.mysql: Flags [.], ack 12, win 229, options [nop,nop,TS val 11285623 ecr 11288277], length 0
12:02:36.348596 IP server3.mysql > 172.25.52.250.50130: Flags [P.], seq 1:12, ack 52, win 227, options [nop,nop,TS val 11285624 ecr 11887273], length 11
12:02:36.348684 IP 172.25.52.250.50130 > server3.mysql: Flags [.], ack 12, win 237, options [nop,nop,TS val 11887431 ecr 11285624], length 0
^Z
[2]+  Stopped                 tcpdump -i eth0 port 3306

[root@server3 ~]# cd  /usr/local/mysql-proxy/conf
[root@server3 conf]# vim mysql-proxy.conf 
################
添加：
admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
admin-username=admin
admin-password=westos

这里写图片描述

[root@server3 conf]#mysql-proxy  --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf  --plugins=admin  --plugins=proxy
[root@server3 conf]# ps ax
  PID TTY      STAT   TIME COMMAND

 1560 ?        S      0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-fi
 1561 ?        S      0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-fi
 1562 pts/0    R+     0:00 ps ax
 ##查看到4041 3306端口   
[root@server3 conf]# netstat -antpl
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:6379                0.0.0.0:*                   LISTEN      1017/redis-server 0 
tcp        0      0 0.0.0.0:80                  0.0.0.0:*                   LISTEN      1111/nginx          
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      1010/sshd           
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      1095/master         
tcp        0      0 0.0.0.0:4041#                0.0.0.0:*                   LISTEN      1624/mysql-proxy    
tcp        0      0 0.0.0.0:3306#                0.0.0.0:*                   LISTEN      1635/mysql-proxy    
tcp        0      0 172.25.52.3:22              172.25.52.250:41900         ESTABLISHED 1254/sshd           
tcp        0      0 172.25.52.3:22              172.25.52.250:41972         ESTABLISHED 1311/sshd           
tcp        0      1 172.25.52.3:45033           172.25.52.5:6379            SYN_SENT    1017/redis-server 0 
tcp        0      0 :::22                       :::*                        LISTEN      1010/sshd           
tcp        0      0 ::1:25                      :::*                        LISTEN      1095/master         
tcp        0      0 :::11111                    :::*                        LISTEN      1199/ricci

[root@foundation52 kiosk]# mysql -h 172.25.52.3 -u admin -p -P 4041
Enter password: 
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.52.1:3306 | unknown | rw   | NULL |                 0 |
|           2 | 172.25.52.2:3306 | unknown | ro   | NULL |                 0 |
+-------------+------------------+---------+------+------+-------------------+
2 rows in set (0.01 sec)

排错：
1.如果没有出现4401端口
则执行：
[root@server3 conf]# killall mysql-proxy
[root@server3 conf]# mysql-proxy  --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf  --plugins=admin  --plugins=proxy
[root@server3 conf]# ps ax

2.如果没有出现3306端口
则执行：
[root@server3 conf]# killall mysql-proxy
[root@server3 conf]# mysql-proxy  --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf 

3.如果是空的
MySQL [(none)]> select * from backends;
Empty set (0.00 sec)
则执行 
[root@server3 conf]# killall mysql-proxy
[root@server3 conf]# mysql-proxy  --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf  --plugins=admin  --plugins=proxy
[root@server3 conf]# ps ax
多刷新几次即可

##再打开一个shell
[kiosk@foundation52 ~]$  mysql -h 172.25.52.3 -u admin -p -P 4041
Enter password: 
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.52.1:3306 | up      | rw   | NULL |                 0 |
|           2 | 172.25.52.2:3306 | up      | ro   | NULL |                 0 |
+-------------+------------------+---------+------+------+-------------------+
2 rows in set (0.00 sec)
##如果一直是unkown 那就多连几个shell 多登陆几次

[root@server3 conf]# lsof -i :3306
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 1657 root   11u  IPv4  27934      0t0  TCP *:mysql (LISTEN)
mysql-pro 1657 root   14u  IPv4  28336      0t0  TCP server3:36206->server1:mysql (ESTABLISHED)

love_sunshine_999

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
利用mysql-proxy实现mysql的读写分离

[root@server3 ~]# lsmysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz [root@server3 ~]# lsmysql-proxy-0.8.5-linux-el6-x86-64bitmys...
复制链接

扫一扫