mysql读写分离

1.数据库读写分离的需求

什么是读写分离？

为了确保数据库产品的稳定性，很多数据库拥有双机热备功能。也就是，第一台数据库服务器，是对外提供增删改业务的生产服务器；第二台数据库服务器，主要进行读的操作。·

需求：
大多数互联网业务，往往读多写少，这时候，数据库的读会首先称为数据库的瓶颈，这时，当服务器的业务压力过大时，如果对该服务器既读又写，会影响用户体验。如果我们希望能够线性的提升数据库的读性能，消除读写锁冲突从而提升数据库的写性能，那么就可以使用“分组架构”（读写分离架构）。

用一句话概括，读写分离是用来解决数据库的读性能瓶颈的。
但是，不是任何读性能瓶颈都需要使用读写分离，我们还可以有其他解决方案

2.读写分离的实现原理

• 读写分离简单的说是把对数据库读和写的操作分开对应不同的数据库服务器，让主数据库（master）处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库（slave）处理SELECT查询操作。这样能有效地减轻数据库压力，也能减轻io压力。主数据库提供写操作，从数据库提供读操作，其实在很多系统中，主要是读的操作。当主数据库进行写操作时，数据要同步到从的数据库，这样才能有效保证数据库完整性。

• 对只读库（配置低一点），对读写库（配置高速存储：固态硬盘等。）

• 读写分离就是在主服务器上修改，数据会同步到从服务器，从服务器只能提供读取数据，不能写入，实现备份的同时也实现了数据库性能的优化，以及提升了服务器安全。

3.优缺点

优点：
（1）分摊服务器压力，提高机器的系统处理效率

读写分离适用于读远比写的场景，如果有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能并不高，而主从只负责各自的写和读，极大程度的缓解X锁和S锁争用；

（2）增加冗余，提高服务可用性
当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务。

(4)读具有高性能高可靠性和可伸缩
只读服务器，因为没有写操作，会大大减轻磁盘IO等性能问题，大大提高效率；只读服务器可以采用负载均衡，主数据库发布到多个只读服务器上实现读操作的可伸缩性。

缺点：

(1)数据的实时性差
数据不是实时同步到自读服务器上的，当数据写入主服务器后，要在下次同步后才能查询到。

(2)数据量大时同步效率差
单表数据量过大时插入和更新因索引,磁盘IO等问题，性能会变的很差。

(3)同时连接多个（至少两个）数据库
至少要连接到两个数据数据库，实际的读写操作是在程序代码中完成的，容易引起混乱

4.mysql读写分离的实现方法

目前较为常见的Mysql读写分离分为两种：

（1）基于程序代码内部实现
在代码中根据select 、insert进行路由分类，这类方法也是目前生产环境下应用最广泛的。优点是性能较好，因为程序在代码中实现，不需要增加额外的硬件开支，缺点是需要开发人员来实现，运维人员无从下手。

（2）基于中间代理层实现

代理一般介于应用服务器和数据库服务器之间，代理数据库服务器接收到应用服务器的请求后根据判断后转发到后端数据库，有以下代表性的程序。

mysql_proxy mysql_proxy是Mysql的一个开源项目，通过其自带的lua脚本进行sql判断。

Atlas是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。

Amoeba由阿里巴巴集团在职员工陈思儒使用序java语言进行开发，阿里巴巴集团将其用户生产环境下，但是他并不支持事物以及存数过程。

注意： 不是所有的应用都能够在基于程序代码中实现读写分离，像一些大型的java应用，如果在程序代码中实现读写分离对代码的改动就较大，所以，像这种应用一般会考虑使用代理层来实现。

3.mysql_proxy对读写分离的实现

实验说明:

server	ip	功能
server1	172.25.19.121	master(写）
server2	172.25.19.122	slave（读）
server3	172.25.19.123	mysql-proxy(中间件)

在server3:
(1)获得mysql-proxy安装包,解压包

[root@mysql3 ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@mysql3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local
[root@mysql3 ~]# cd /usr/local
[root@mysql3 local]# ls
bin  games    lib    libexec                                sbin   src
etc  include  lib64  mysql-proxy-0.8.5-linux-el6-x86-64bit  share
[root@mysql3 local]# ln -s ./mysql-proxy-0.8.5-linux-el6-x86-64bit/  mysql-proxy
[root@mysql3 local]# ls
bin  games    lib    libexec      mysql-proxy-0.8.5-linux-el6-x86-64bit  share
etc  include  lib64  mysql-proxy  sbin                                   src
[root@mysql3 local]# cd mysql-proxy

(2)编辑配置文件

[root@mysql3 mysql-proxy]# mkdir conf
[root@mysql3 mysql-proxy]# cd conf
[root@mysql3 conf]# vim mysql-proxy.conf
[mysql-proxy]
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.19.121:3306  ##以主从配置的服务ip为主 master为写 slave为读
proxy-read-only-backend-addresses=172.25.19.122:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
plugins=proxy
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
log-level=debug
keepalive=true
daemon=true

(3)开启日志

[root@mysql3 mysql-proxy]# mkdir log
[root@mysql3 mysql-proxy]# ls
bin  conf  include  lib  libexec  licenses  log  share
[root@mysql3 mysql-proxy]# cd log/
[root@mysql3 log]# ls
[root@mysql3 log]# touch mysql-proxy.log
[root@mysql3 log]# ls
mysql-proxy.log

（4） 配置lua脚本

[root@mysql3 log]# cd ..
[root@mysql3 mysql-proxy]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

##设定启动读写分离的最大最小连接数
 40                 min_idle_connections = 1,
 41                 max_idle_connections = 2,

（5）启动服务

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

[root@mysql3 ~]# yum install lsof.x86_64  ##lsop监控tcp握手##master：授权登录访问用户

在master：

[root@mysql1 mysql]# mysql -uroot -pWestos+001
mysql> grant insert,update,select on *.* to lin@'%' identified by 'Westos+0019';

物理机测试

[root@foundation19 mysql]# mysql -p 172.25.19.123 -ulin -pWestos+0019 ##多次连接才能启动读写分离  

server3上查看是否启动：
[root@mysql3 ~]# lsof -i:3306