Mysql——基于主从复制实现读写分离（mysql-proxy）

一、读写分离的概念

什么是读写分离

Mysql作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中，由单台Mysql作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。因此，一般来说都是通过主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力这样的方案来进行部署与实施的。

• MySQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。

• 使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。

为什么要读写分离？

大型网站为了软解大量的并发访问，除了在网站实现分布式负载均衡，远远不够。到了数据业务层、数据访问层，如果还是传统的数据结构，或者只是单单靠一台服务器来处理如此多的数据库连接操作，数据库必然会崩溃，特别是数据丢失的话，后果更是不堪设想。这时候，我们会考虑如何减少数据库的连接。

• 减少数据库的连接方法：利用主从数据库来实现读写分离，从而分担主数据库的压力。在多个服务器上部署mysql，将其中一台认为主数据库，而其他为从数据库，实现主从同步。其中主数据库负责主动写的操作，而从数据库则只负责主动读的操作（slave从数据库仍然会被动的进行写操作，为了保持数据一致性），这样就可以很大程度上的避免数据丢失的问题，同时也可减少数据库的连接，减轻主数据库的负载。

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

二.Mysql实现读写分离的步骤（基于主从复制）

前提：

首先进行主从复制，这里只显示结果，具体过程请看之前的博客

meng1是主，meng3是从。

在meng3中查看，显示 Slave_IO_Running和Slave_SQL_Running都是YES，说明成功

• 测试：主从复制

  在meng1（主）上创建库，进入库，创建表
  

在meng3上面可以看到表结构，则主从复制成功

读写分离的配置过程：

第一步：配置代理端meng2

meng2做的代理端：

装mysql-proxy安装包并将其放至指定目录

[root@meng2 conf]# systemctl stop mysqld         #  (如果之前开启过mysqld，关掉，因为占用端口3306)
[root@foundation11 Desktop]# scp -r mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz  root@172.25.11.2:/mnt     #在物理机将需要的安装包拷贝给虚拟机

[root@meng2 mnt]# cd /mnt
[root@meng2 mnt]# ls
[root@meng2 mnt]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@meng2 mnt]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit  /usr/local
[root@meng2 mnt]# cd /usr/local/
[root@meng2 local]# ll

修改名称也可以做个软连接

[root@meng2 local]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit  mysql-proxy
[root@meng2 local]# ll

建立目录存放读写分离的配置文件和日志

[root@meng2 local]# cd mysql-proxy/
[root@meng2 mysql-proxy]# mkdir conf
[root@meng2 mysql-proxy]# mkdir log

修改mysql发生读写分离时的最大最小值

[root@meng2 mysql-proxy]# cd share/doc/mysql-proxy/
[root@meng2 mysql-proxy]# vim rw-splitting.lua 
 40                 min_idle_connections = 1,      ##最小连接数
 41                 max_idle_connections = 2,      #最大连接数，最大连接数大于2也就是等于3时发生读写分离

创建配置文件并进行编写

[root@meng2 mysql-proxy]# cd /usr/local/mysql-proxy/conf/
[root@meng2 conf]# vim mysql-proxy.conf
[root@meng2 conf]# cat mysql-proxy.conf

[mysql-proxy]
proxy-address=0.0.0.0:3306       # mysql-proxy运行ip和端口
proxy-backend-addresses=172.25.11.1:3306     #master：可读写
proxy-read-only-backend-addresses=172.25.11.3:3306    #slave只读
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua     #lua脚本存放位置
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid    
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
plugins=proxy
log-level=debug       ##定义log日志级别，由高到低分别有(error|warning|info|message|debug)
keepalive=true      ##mysql-proxy崩溃时，尝试重启（持续连接
daemon=true      ##打入后台

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

[root@meng2 conf]# chmod 0600  /usr/local/mysql-proxy/conf/mysql-proxy.conf 
启动：
[root@meng2 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

查看端口和进程和日志信息：

[root@meng2 conf]# netstat -tnlp
[root@meng2 conf]# ps aux

查看日志信息

第二步：在master授权登陆用户

mysql> grant insert,update,select  on *.* to wsp@'%' identified by 'Wsp+123ld';     #授权，没有创建这个用户的话，记得先创建
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> flush privileges;       #刷新权限表
Query OK, 0 rows affected (0.01 sec)

没图：～～～～～～～～这部的

第三步：测试在真机（注意，要触发读写分离，最起码要三个连接数，也就是要用三个shell在真机连接数据库）

物理机登陆数据库：

[root@foundation11 Desktop]# mysql -h 172.25.11.3 -uwsp  -pWsp+123ld

MySQL [(none)]> show databases;

meng1建立库和表：

mysql> create database meng;

mysql> use meng

mysql> create table  userlist(username varchar(10) not null,password varchar(15) not null);


mysql> desc userlist;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(15) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

在meng3和真机可以看到：

meng1插入数据：

mysql> insert into userlist values('user1','123');
Query OK, 1 row affected (0.01 sec)

mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)

在meng3和真机可以看到(此时没有关闭主从复制)：

关闭meng3的主从复制：

mysql> stop slave;

meng1插入数据：

mysql> insert into userlist values('user2','123');

mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |    |
| user2    | 123      |
+----------+----------+

meng3和真机查看的内容是一样的，没有看到插入的user2的信息

mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+

说明读取得是meng3上面的数据，写是在meng1上面

注意：这个实验测试不是很严谨，应该在物理机插入数据，然后查看在关闭主从复制，查看数据。此时查看的是只读slave上面的数据库数据，写入的数据在master那一台数据库上。