高性能MYSQL---数据库的读写分离(mysql-proxy)

高性能MYSQL—数据库的读写分离(mysql-proxy)

1.什么是读写分离？

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

2.为什么要读写分离？

因为数据库的写操作相对读操作是比较耗时的，所以数据库的读写分离，解决的是数据库的写入，影响了查询的效率。

3.读写分离的实现

实验环境：

• selinux and firewalld disabled
• server1:172.25.254.1（master）
• server2:172.25.254.2（slave）
• server3:172.25.254.3（mysql-proxy）

server1和server2要实现主从复制，这里就不多赘述了，详情见上一篇博文

mysql-proxy配置：

step1 配置mysql-proxy，创建主配置文件：

tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
cd /usr/local/
ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy
cd /usr/local/mysql-proxy
mkdir conf
cd conf/	
vim mysql-proxy.conf	#创建配置文件
写入下面内容：注意不要加注释，否则会报错

  1 [mysql-proxy]
  2 user=root ##运行mysql-proxy用户
  3 proxy-address=0.0.0.0:3306 ##mysql-proxy运行ip和端口
  4 proxy-read-only-backend-addresses=172.25.254.2:3306 ##slave：只读
  5 proxy-backend-addresses=172.25.254.1:3306 ##master：可读写
  6 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua ##lua脚本地址
  7 pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
  8 plugins=proxy
  9 log-file=/usr/local/mysql-proxy/log/mysql-proxy.log #日志位置
 10 log-level=debug #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
 11 daemon=true ##打入后台 
 12 keepalive=true ##mysql-proxy崩溃时，尝试重启（持续连接）

step2 建立目录存放读写分离的日志：

mkdir /usr/local/mysql-proxy/log

step3 修改读写分离配置文件：

vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
修改以下内容：
 40                 min_idle_connections = 1,  #最小连接数
 41                 max_idle_connections = 2,  #最大连接数，最大连接数大于2时发生读写分离

step4 给文件设置权限，再启动mysql-proxy（否则会启动失败）：

chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf	#修改权限
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf	#启动mysql-proxy
cat /usr/local/mysql-proxy/log/mysql-proxy.log	#查看到server1和server2已经添加上 
netstat -antlp	#查看端口3306打开

step5 在master上授权一个新的用户：

mysql> grant insert,update,select on *.* to yy@'%' identified by 'Redhat.123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> use westos;
Database changed

mysql> create table userinfo (
    -> username varchar(10) not null,
    -> phonenum varchar(10) not null);
Query OK, 0 rows affected (0.04 sec)

mysql> desc userinfo;

step6 在proxy端监控3306端口情况，确保读写分离启动：

yum install lsof-4.87-4.el7.x86_64 -y
lsof -i:3306

step7 使用真机通过server3连接数据库，直到lsof监控到读写分离出现：

mysql -h 172.25.254.3 -uyy -pRedhat.123	#打开几个shell多连接几次

注意：真机中如果没有mysql，直接用yum install mariadb-server安装

测试：
step1 master和slave状态都开启：
在真机中通过server3连接数据库：

mysql -h 172.25.254.3 -uyy -pRedhat.123
use westos;
insert into userinfo values ('user1','111');	#插入数据
select * from userinfo;

注意：此时可以成功插入看到数据，并不能判断实现了读写分离

step2 关闭server2（slave）：

stop slave;

在真机插入数据，却不能查看到数据，而在master上可以查看到插入的数据：

insert into userinfo values ('user2','222');
select * from userinfo;

开启server2的slave：

start slave;

在真机中再次select，可以查看到刚才插入的数据：

select * from userinfo;

说明：实现读写分离，读操作是在server2（slave）上

step3 关掉server1（master）：

systemctl stop mysqld

在物理机上连接数据库，发现插入数据失败：

insert into userinfo values ('user3','333');

说明：实现读写分离，写操作在server1（master）上