MySQLProxy实现读写分离

最新推荐文章于 2023-09-11 09:07:35 发布

不要海带香菜

最新推荐文章于 2023-09-11 09:07:35 发布

阅读量280

点赞数

本文链接：https://blog.csdn.net/awoyaoc/article/details/81736410

版权

mysql的读写分离的基本原理：

让master（主数据库）来响应事务性操作，让slave（从数据库）来响应select非事务性操作，然后再采用主从复制来把master上的事务性操作同步到slave数据库中。

所以首先配置好主从
主库server1：172.25.53.1
从库server2：172.25.53.2
Mysql-proxy：172.25.53.3

配置Mysql-proxy
[root@server3 ~]# tar zxf 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]# mkdir conf
[root@server3 mysql-proxy]# ls
bin  conf  include  lib  libexec  licenses  share
[root@server3 mysql-proxy]# cd bin/
[root@server3 bin]# ls
mysql-binlog-dump  mysql-myisam-dump  mysql-proxy
[root@server3 bin]# pwd
/usr/local/mysql-proxy/bin
[root@server3 bin]# vim ~/.bash_profile 
[root@server3 bin]# tail -n 3 ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin     ##将proxy添加到环境变量

export PATH
[root@server3 bin]# source ~/.bash_profile       ###是环境变量生效

修改读写分离lua脚本

默认最小4个最大8个以上的客户端连接才会实现读写分离, 现改为最小1个最大2个
这是因为Mysql Proxy会检测客户端连接, 当连接没有超过min_idle_connections预设值时, 不会进行读写分离, 即查询操作会发生到Master上.

[root@server3 mysql-proxy]# vim rw-splitting.lua

这里写图片描述

[root@server3 mysql-proxy]# pwd
/usr/local/mysql-proxy/share/doc/mysql-proxy

[root@server3 mysql-proxy]# mkdir logs
[root@server3 mysql-proxy]# cd conf/
[root@server3 conf]# vim mysql-proxy.conf 
[root@server3 conf]# cat mysql-proxy.conf 
[mysql-proxy]
user=root
proxy-address=0.0.0.0:3306          mysql proxy的监听端口
proxy-backend-addresses=172.25.53.1:3306       指定mysql主机的端口
proxy-read-only-backend-addresses=172.25.53.2:3306        指定只读的mysql主机端口
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua     指定lua脚本，在这里，使用的是rw-splitting脚本，用于读写分离
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log    日志存放路径
log-level=debug
deamon=true                                      采用daemon方式启动
keepalive=true

根据提示解决权限问题,建议使用配置文件的形式启动, 注意配置文件必须是660权限, 否则无法启动.

[root@server3 conf]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf 
2018-08-14 10:26:09: (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:26:09: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2018-08-14 10:26:09: (message) shutting down normally, exit code is: 1
[root@server3 conf]# 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

开启成功端口为6379

在物理机端可登陆数据库

[root@foundation53 ~]# mysql -h 172.25.53.3 -u root -p

这里写图片描述
登陆后
在master端查看3306端口使用情况

[root@server1 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1410 mysql   33u  IPv6   9260      0t0  TCP *:mysql (LISTEN)
mysqld  1410 mysql   49u  IPv6   9279      0t0  TCP server1:mysql->server2:33801 (ESTABLISHED)
mysqld  1410 mysql   53u  IPv6   9343      0t0  TCP server1:mysql->server3:47679 (ESTABLISHED)

我们再开一台物理机窗口，同样进行登陆操作

[root@foundation53 ~]# mysql -h 172.25.53.3 -u root -p

[root@server1 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1410 mysql   33u  IPv6   9260      0t0  TCP *:mysql (LISTEN)
mysqld  1410 mysql   49u  IPv6   9279      0t0  TCP server1:mysql->server2:33801 (ESTABLISHED)
mysqld  1410 mysql   51u  IPv6   9850      0t0  TCP server1:mysql->server3:47680 (ESTABLISHED)
mysqld  1410 mysql   53u  IPv6   9343      0t0  TCP server1:mysql->server3:47679 (ESTABLISHED)

从server2上看
[root@server2 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1400 mysql   31u  IPv6   9225      0t0  TCP *:mysql (LISTEN)
mysqld  1400 mysql   50u  IPv4   9270      0t0  TCP server2:33801->server1:mysql (ESTABLISHED)

开第三个
[kiosk@foundation53 ~]$ mysql -h 172.25.53.3 -u root -p

[root@server1 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1410 mysql   33u  IPv6   9260      0t0  TCP *:mysql (LISTEN)
mysqld  1410 mysql   49u  IPv6   9279      0t0  TCP server1:mysql->server2:33801 (ESTABLISHED)
mysqld  1410 mysql   51u  IPv6   9850      0t0  TCP server1:mysql->server3:47680 (ESTABLISHED)
mysqld  1410 mysql   53u  IPv6   9343      0t0  TCP server1:mysql->server3:47679 (ESTABLISHED)

[root@server2 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1400 mysql   31u  IPv6   9225      0t0  TCP *:mysql (LISTEN)
mysqld  1400 mysql   48u  IPv6   9878      0t0  TCP server2:mysql->server3:36997 (ESTABLISHED)
mysqld  1400 mysql   50u  IPv4   9270      0t0  TCP server2:33801->server1:mysql (ESTABLISHED)

添加插入数据
MySQL [westos]> insert into userlist values ('user3','333');
Query OK, 1 row affected (0.40 sec)

这里写图片描述

[root@server1 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1410 mysql   33u  IPv6   9260      0t0  TCP *:mysql (LISTEN)
mysqld  1410 mysql   49u  IPv6   9279      0t0  TCP server1:mysql->server2:33801 (ESTABLISHED)
mysqld  1410 mysql   51u  IPv6   9850      0t0  TCP server1:mysql->server3:47680 (ESTABLISHED)
mysqld  1410 mysql   53u  IPv6   9343      0t0  TCP server1:mysql->server3:47679 (ESTABLISHED)




[root@server2 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1400 mysql   31u  IPv6   9225      0t0  TCP *:mysql (LISTEN)
mysqld  1400 mysql   48u  IPv6   9878      0t0  TCP server2:mysql->server3:36997 (ESTABLISHED)
mysqld  1400 mysql   50u  IPv4   9270      0t0  TCP server2:33801->server1:mysql (ESTABLISHED)







[root@server3 conf]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf 
    server default db: westos
    client default db: 
    syncronizing
    server default db: 
    client default db: westos
    syncronizing
    server default db: 
    client default db: westos
    syncronizing