2-17-MySQL读写分离-mysql-proxy

MYSQL读写分离的概述

Mysql作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中，由单台Mysql作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。
因此，一般来说都是通过 主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy/Amoeba）来提升数据库的并发负载能力 这样的方案来进行部署与实施的。
读写分离工作原理：
基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
 

数据内部交换过程：

为什么要读写分离：

面对越来越大的访问压力，单台的服务器的性能成为瓶颈，需要分担负载

1、        主从只负责各自的写和读，极大程度的缓解X锁和S锁争用

2、        从库可配置myisam引擎，提升查询性能以及节约系统开销

3、        增加冗余，提高可用性

实现读写分离的方式：

一般有两种方式实现

应用程序层实现，网站的程序实现

应用程序层实现指的是在应用程序内部及连接器中实现读写分离

 
优点：
A：应用程序内部实现读写分离，安装既可以使用
B：减少一定部署难度
C：访问压力在一定级别以下，性能很好
缺点：
A：架构一旦调整，代码要跟着变
B：难以实现高级应用，如自动分库，分表
C：无法适用大型应用场景
中间件层实现
中间件层实现是指在外部中间件程序实现读写分离
常见的中间件程序：
Mysql-proxy    amoeba    Atlas (360)  Cobar(Alibaba)  TDDL(Taobao)
优点：
A：架构设计更灵活
B：可以在程序上实现一些高级控制，如：透明化水平拆分，failover,监控
C：可以依靠些技术手段提高mysql性能，
D：对业务代码的影响小，同时也安全
缺点：
需要一定的开发运维团队的支持

MYSQL-PROXY概述

MySQL Proxy是一个处于你的client端和MySQL server端之间的简单程序，它可以监测、分析或改变它们的通信。它使用灵活，没有限制，常见的用途包括：负载平衡，故障、查询分析，查询过滤和修改等等。
MySQL Proxy就是这么一个中间层代理，简单的说，MySQL Proxy就是一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过使用lua脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负载平衡。对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多个proxy的连接参数即可。

MySQL Proxy更强大的一项功能是实现“读写分离”，基本原理是让主数据库处理事务性查询，让从库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从库

Lua概述：
Lua 是一个小巧的脚本语言。
Lua的速度是最快的。这一切都决定了Lua是作为嵌入式脚本的最佳选择。
Lua由标准C编写而成，几乎在所有操作系统和平台上都可以编译，运行。Lua并没有提供强大的库，这是由它的定位决定的。所以Lua不适合作为开发独立应用程序的语言。
lua官网：http://www.lua.org/

下载: mysql-proxy
http://dev.mysql.com/downloads/mysql-proxy/

 
端口：  mysql-proxy 默认端口：4040

部署MYSQL-PROXY实现读写分离

 
实验环境：
mysql-proxy服务端：        xuegod63               IP：192.168.1.63
mysql服务器（主，负责写）服务端：xuegod64    IP：192.168.1.64
mysql服务器（从，负责读）客户端：xuegod65    IP：192.168.1.65
部署MYSQL-PROXY服务端XUEGOD62：
安装前需要系统支持LUA语言环境:

1. [root@xuegod62 ~]# yum install lua

安装mysql-proxy:
推荐采用已经编译好的二进制版本，因为采用源码包进行编译时，最新版的MySQL-Proxy对automake，glib以及libevent的版本都有很高的要求，而这些软件包都是系统的基础套件，不建议强行进行更新。
并且这些已经编译好的二进制版本在解压后都在统一的目录内，因此建议选择以下版本：

1. [root@xuegod62 ~]# wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
2. [root@xuegod62 ~]# tar -xf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
3. [root@xuegod62 local]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy

修改系统环境变量：

1. [root@xuegod62 local]# vim /etc/profile
2. export PATH=/usr/local/mysql-proxy/bin/:/usr/local/mysql/bin:$PATH
3. [root@xuegod62 local]# source !$

mysql-proxy 脚本配置文件位置：

1. [root@xuegod62 ~]# ls /usr/local/mysql-proxy/share/doc/mysql-proxy/

 
修改配置文件实现读写分离：

1. [root@xuegod62 ~]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

修改

1. 40                 min_idle_connections = 4,
2. 41                 max_idle_connections = 8,

为

 
#修改默认连接，进行快速测试，默认最小4个以上的客户端连接才会实现读写分离,最大链接数为8。 
注：为了验证试验效果将他改成1 .就是当有一个链接的时候，就实现读写分离的功能。为了清晰的看到读写分离的效果，需要暂时关闭MySQL主从复制功能。

XUEGOD64上创建数据库和表，用于实现写操作：

1. [root@xuegod64 ~]# mysql -uroot -p123456
2. mysql> create database db;
3. mysql> use db;
4. mysql> create table test(id int);
5. mysql> insert into test values(64);
6. mysql> grant all on db.* to user1@'%' identified by '123456';

关闭密码强度审计，在my.cnf添加validate-password=off
XUEGOD65上创建数据库和表，用于实现读操作：

1. [root@xuegod65 ~]# mysql -uroot -p123456
2. mysql> create database db;
3. mysql> use db;
4. mysql> create table test(id int);
5. mysql> insert into test values(65);
6. mysql> grant all on db.* to user1@'%' identified by '123456';

启动服务MYSQL-PROXY服务

1. [root@xuegod63 ~]# mysql-proxy --proxy-read-only-backend-addresses=10.10.10.65:3306 --proxy-backend-addresses=10.10.10.64:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &

 
参数说明：

--proxy-read-only-backend-addresses=192.168.1.64:3306  # 定义后端只读服务器 
--proxy-backend-addresses=192.168.1.63:3306   #定义后端mysql主服务器地址，指定mysql写主服务器的端口
--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &  #指定lua脚本，在这里，使用的是rw-splitting脚本，用于读写分离

当有多个只读服务器时，可以写多个以下参数：

--proxy-read-only-backend-addresses=192.168.1.64:3306  # 定义后端只读服务器
--proxy-read-only-backend-addresses=192.168.1.65:3306  # 定义后端只读服务器
#--proxy-address=192.168.1.62:3307 指定mysql proxy的监听端口，默认为：4040

完整的参数可以运行以下命令查看：

1. [root@xuegod62 ~]# mysql-proxy --help-all

查看proxy是否启动：

1. [root@xuegod62 ~]# lsof -i :4040
2. COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
3. mysql-pro 3144 root    9u  IPv4  19597      0t0  TCP *:yo-main (LISTEN)

测试读写分离

 
测试写操作：

1. [root@xuegod62 ~]# mysql -uuser1 -p123456 -P4040 -h192.168.1.62
2. mysql> use db;
3. mysql> insert into test values(663);  回车，没有报错

 
##看不到64数据，可以看到刚写入的数据，说明写操作成功。 因为是第一个客端连接，还没有启动读写分离，所以select读时，没有看到“6464”数据，而是看到主上“6363“数据。
接下来，多打开几个客户端测试一下读。注：第一个链接，一定是走backend 主mysql服务器的。
测试读操作：

1. [root@xuegod63 ~]# mysql -uuser1 -p123456 -P4040 -h 192.168.1.63

 

1. [root@xuegod64 ~]# mysql -uuser1 -p123456 -P4040 -h 192.168.1.62

 
我们再用62登陆，查看

1. [root@xuegod62 ~]# mysql -uuser1 -p123456 -P4040 -h 192.168.1.62

 
这说明读写分离测试成功。 但是数据还没有保持同步。 保持数据同步，可以通过mysql主从来实现。
扩展： 查看客户端连接状态：

1. mysql> show processlist;

 

每列参数说明：
第一列， id ，一个标识 
user列， 显示当前用户，如果不是 root ，这个命令就只显示你权限范围内的 sql 语 句。 
host 列，显示这个语句是从哪个 ip 的哪 个端口上发出的。可以用来追踪出问题语句的用户。 
db 列，显示这个进程目前连接的是哪个数据库 。 
command 列，显示当前连接的执行的命令，一般就是休眠（ sleep ），查询（ query ），连接（ connect ）。 
time 列，此这个状态持续的时间，单位是秒。 
state 列，显示使用当前连接的 sql 语句的状态，很重要的列， state 只是语句执行中的某一个状态，一个 sql 语句，以查询为例，可能需要经过 copying to tmp table ，Sorting result ， Sending data 等状态才可以完成。 
info 列，显示这个 sql 语句，因为长度有限，所以长的 sql 语句就显示不全，但是一个判断问题语句的重要依据。

部署MYSQL主从并实现读写分离

在之前的基础上配置主从
配置XUEGOD64为MASTER

1. [root@xuegod64 ~]# vim /etc/my.cnf
2. log-bin=mysql-bin-master  #启用二进制日志
3. server-id=1   #本机数据库ID 标示
4. binlog-do-db=db #可以被从服务器复制的库。二进制需要同步的数据库名
5. binlog-ignore-db=mysql  #不可以被从服务器复制的库

授权：

1. mysql> grant replication slave on *.* to slave@10.10.10.65  identified by "123456";

重启mysql:

1. service mysqld restart 
2. [root@xuegod64 ~]# mysql -uroot -p123456
3. mysql> use db;
4. mysql> drop table test;
5. [root@xuegod64 ~]# service mysqld restart

配置XUEGOD65为SLAVE

1. [root@xuegod65 ~]# vim /etc/my.cnf
2. server-id = 2
3. [root@xuegod65 ~]# service mysqld restart
4. [root@xuegod65 ~]# mysql -uroot -p123456
5. mysql> change master to master_host='10.10.10.64',master_user='slave',master_password='123456';
6. mysql> use db;
7. mysql> drop table test;
8. mysql> start slave;
9. mysql> show slave statusG

 
主从同步测试：
1：Mysql主数据库xuegod64插入数据：

1. mysql> use db;
2. mysql> create table admin(id int);
3. mysql> insert into admin values(63);

2：Mysql主从数据库xuegod64xuegod65查看同步的数据：

 
同步后，测试使用MYSQL-PROXY 能否读到同样的数据。
在其他测试机上测试

1. [root@test1~]# mysql -uuser1 -p123456 -P4040 -h 10.10.10.63 -e'select * from db.admin;'

 


停掉从服务器，stop slave 测试插入数据看看读写分离
#可以查看到admin中的63记录，说明mysql+proxy+主从读写分离成功。
插入数据测试：

1. mysql> use db;
2. mysql> insert into admin values(64);

 


在xuegod63,xuegod64上查看都有

当我们SLAVE挂掉后咋样呢
设立mysqlproxy+主主，查看一个宕掉的情况
模拟故障：从服务器挂掉了

1. [root@xuegod65 ~]# service mysqld stop

在mysql-proxy上测试读写

1. mysql> insert into admin values(65);

 
Xuegod63上查看连接状态，确认关闭slave后，读写都是访问xuegod63
 
总结：当停止掉 slave 数据库，proxy 的查询就会转移到 master 上，当把 slave 启动后，proxy 依然在读 master，当有新的链接进来的时候才会重新去读取 slave 的数据。有时可能需要重启下 mysql-proxy。
如果主数据库挂了：
主从也没了，在mysql-proxy上只能查看数据

1. [root@xuegod63 ~]# mysql -uuser1 -p123456 -P4040 -h 10.10.10.63 -e'select * from db.admin;'