MySQL 读写分离方案－MySQL Proxy环境部署记录

Mysql的读写分离可以使用MySQL Proxy和Amoeba实现，其实也可以使用MySQL-MMM实现读写分离的自动切换。MySQL Proxy有一项强大功能是实现"读写分离"，基本原理是让主数据库处理写方面事务，让从库处理SELECT查询；Amoeba for MySQL是一款优秀的中间件软件，同样可以实现读写分离，负载均衡等功能。下面重点说下Mysql Proxy:

MySQL Proxy处于客户端应用程序和MySQL服务器之间，通过截断、改变并转发客户端和后端数据库之间的通信来实现其功能。代理服务器是和TCP/IP协议打交道，而要理解MySQL Proxy的工作机制，同样要清楚MySQL客户端和服务器之间的通信协议，MySQL Protocol 包括认证和查询两个基本过程：.................................认证过程包括：1）客户端向服务器发起连接请求2）服务器向客户端发送握手信息3）客户端向服务器发送认证请求4）服务器向客户端发送认证结果................................如果认证通过，则进入查询过程：1）客户端向服务器发起查询请求2）服务器向客户端返回查询结果当然，这只是一个粗略的描述，每个过程中发送的包都是有固定格式的。MySQL Proxy要做的，就是介入协议的各个过程。首先MySQL Proxy以服务器的身份接受客户端请求，根据配置对这些请求进行分析处理，然后以客户端的身份转发给相应的后端数据库服务器，再接受服务器的信息，返回给客户端。所以MySQL Proxy需要同时实现客户端和服务器的协议。由于要对客户端发送过来的SQL语句进行分析，还需要包含一个SQL解析器。可以说MySQL Proxy相当于一个轻量级的MySQL了，实际上，MySQL Proxy的admin server是可以接受SQL来查询状态信息的。MySQL Proxy通过lua 脚本来控制连接转发的机制。主要的函数都是配合MySQL Protocol各个过程的，这一点从函数名上就能看出来：connect_server()read_handshake()read_auth()read_auth_result()read_query()read_query_result()至于为什么采用lua 脚本语言，大概是因为MySQL Proxy中采用了wormhole 存储引擎 的关系吧，这个虫洞存储引擎很有意思，数据的存储格式就是一段lua脚本。

Mysql Proxy的原理图

顺便贴下Mysql Proxy的工作拓扑图

部署MySQL Proxy实现读写分离并提高并发负载的操作记录

0）机器环境

ip地址              角色         主机名182.48.115.237     master      master-node182.48.115.236     slave1      slave-node1182.48.115.238     slave2      slave-node2182.48.115.233     proxy       proxy-node四台机器都关闭防火墙和selinux绑定hosts设置（四台机器都要操作）[root@master-node ~]# vim /etc/hosts......182.48.115.237   master-node182.48.115.236   slave-node1182.48.115.238   slave-node2182.48.115.233   proxy-node

1）Mysql主从复制部署（在三台mysql节点机上部署，本案例是一主两从架构）

mysql安装参考：http://www.cnblogs.com/kevingrace/p/6109679.htmlmysql主从部署参考：http://www.cnblogs.com/kevingrace/p/6256603.html

2）在proxy机器上安装mysql-proxy

1）安装mysql-proxymysql proxy实现读写分离是有lua脚本实现的下载地址1：https://downloads.mysql.com/archives/proxy/#downloads下载地址2: http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-Proxy/[root@proxy-node ~]# wget http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz  [root@proxy-node ~]# tar -zvxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz [root@proxy-node ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy[root@proxy-node ~]# useradd -r mysql-proxy2）提供服务脚本[root@proxy-node ~]# vim /etc/init.d/mysql-proxy#!/bin/bash## mysql-proxy This script starts and stops the mysql-proxy daemon## chkconfig: - 78 30# processname: mysql-proxy# description: mysql-proxy is a proxy daemon for mysql# Source function library.. /etc/rc.d/init.d/functionsprog="/usr/local/mysql-proxy/bin/mysql-proxy"# Source networking configuration.if [ -f /etc/sysconfig/network ]; then    . /etc/sysconfig/networkfi# Check that networking is up.[ ${NETWORKING} = "no" ] && exit 0# Set default mysql-proxy configuration.ADMIN_USER="admin"ADMIN_PASSWD="admin"ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"PROXY_OPTIONS="--daemon"PROXY_PID=/var/run/mysql-proxy.pidPROXY_USER="mysql-proxy"# Source mysql-proxy configuration.if [ -f /etc/sysconfig/mysql-proxy ]; then    . /etc/sysconfig/mysql-proxyfiRETVAL=0start() {    echo -n $"Starting $prog: "    daemon $prog $PROXY_OPTIONS --pid-file=$PROXY_PID --proxy-address="$PROXY_ADDRESS" --user=$PROXY_USER --admin-username="$ADMIN_USER" --admin-lua-script="$ADMIN_LUA_SCRIPT" --admin-password="$ADMIN_PASSWORD"    RETVAL=$?    echo    if [ $RETVAL -eq 0 ]; then        touch /var/lock/subsys/mysql-proxy    fi}stop() {    echo -n $"Stopping $prog: "    killproc -p $PROXY_PID -d 3 $prog    RETVAL=$?    echo    if [ $RETVAL -eq 0 ]; then        rm -f /var/lock/subsys/mysql-proxy        rm -f $PROXY_PID    fi}# See how we were called.case "$1" in    start)        start        ;;    stop)        stop        ;;    restart)        stop        start        ;;    condrestart|try-restart)        if status -p $PROXY_PIDFILE $prog >&/dev/null; then            stop            start        fi        ;;    status)        status -p $PROXY_PID $prog        ;;    *)        echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"        RETVAL=1        ;;esacexit $RETVAL[root@proxy-node ~]# chmod 755 /etc/init.d/mysql-proxy[root@proxy-node ~]# chkconfig --add mysql-proxy3）为服务脚本提供配置文件[root@proxy-node ~]# vim /etc/sysconfig/mysql-proxy# Options for mysql-proxyADMIN_USER="admin"ADMIN_PASSWORD="admin"ADMIN_ADDRESS=""ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"PROXY_ADDRESS=""PROXY_USER="mysql-proxy"PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=182.48.115.237:3306 --proxy-read-only-backend-addresses=182.48.115.236:3306 --proxy-read-only-backend-addresses=182.48.115.238:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"配置参数解释：--daemon：以守护进程模式启动mysql-proxy--proxy-backend-addresses：后端可读写的mysql服务器的地址和端口--proxy-read-only-backend-addresses：后端只读mysql服务器的地址和端口--proxy-lua-script：完成mysql代理功能的Lua脚本4）提供admin.lua文件[root@proxy-node ~]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua--[[ $%BEGINLICENSE%$ Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA $%ENDLICENSE%$ --]]function set_error(errmsg) proxy.response = {type = proxy.MYSQLD_PACKET_ERR,errmsg = errmsg or "error"}endfunction read_query(packet)if packet:byte() ~= proxy.COM_QUERY thenset_error("[admin] we only handle text-based queries (COM_QUERY)")return proxy.PROXY_SEND_RESULTendlocal query = packet:sub(2)local rows = { }local fields = { }if query:lower() == "select * from backends" thenfields = { { name = "backend_ndx",  type = proxy.MYSQL_TYPE_LONG },{ name = "address", type = proxy.MYSQL_TYPE_STRING },{ name = "state", type = proxy.MYSQL_TYPE_STRING },{ name = "type", type = proxy.MYSQL_TYPE_STRING },{ name = "uuid", type = proxy.MYSQL_TYPE_STRING },{ name = "connected_clients",  type = proxy.MYSQL_TYPE_LONG },}for i = 1, #proxy.global.backends dolocal states = {"unknown","up","down"}local types = {"unknown","rw","ro"}local b = proxy.global.backends[i]rows[#rows + 1] = {i,b.dst.name,          -- configured backend addressstates[b.state + 1], -- the C-id is pushed down starting at 0types[b.type + 1],   -- the C-id is pushed down starting at 0b.uuid,              -- the MySQL Server's UUID if it is managedb.connected_clients  -- currently connected clients}endelseif query:lower() == "select * from help" thenfields = { { name = "command",  type = proxy.MYSQL_TYPE_STRING },{ name = "description",  type = proxy.MYSQL_TYPE_STRING },}rows[#rows + 1] = { "SELECT * FROM help", "shows this help" }rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" }elseset_error("use 'SELECT * FROM help' to see the supported commands")return proxy.PROXY_SEND_RESULTendproxy.response = {type = proxy.MYSQLD_PACKET_OK,resultset = {fields = fields,rows = rows}}return proxy.PROXY_SEND_RESULTend5）为了测试更明显，编辑rw-splitting.lua文件中的其中2个数值：[root@proxy-node ~]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua.........if not proxy.global.config.rwsplit then        proxy.global.config.rwsplit = {                min_idle_connections = 1,        //修改这个值，默认为4                max_idle_connections = 1,        //修改这个值，默认为8                is_debug = false        }end........注意：mysql-proxy会检测客户端连接，当连接没有超过min_idle_connections预设值时, 不会进行读写分离, 即查询操作会发生到Master上。6）启动mysql-proxy[root@proxy-node ~]# service mysql-proxy startStarting /usr/local/mysql-proxy/bin/mysql-proxy:           [  OK  ][root@proxy-node ~]# service mysql-proxy statusmysql-proxy (pid  4655) is running...[root@proxy-node ~]# netstat -tunlpActive Internet connections (only servers)Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   tcp        0      0 0.0.0.0:4041                0.0.0.0:*                   LISTEN      4655/mysql-proxy    tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      4655/mysql-proxy 7）连接测试（在任意一台远程客户机上测试连接）[root@slave-node2 ~]# mysql -uadmin -padmin -h182.48.115.233 --port=4041Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.0.99-agent-adminCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> SELECT * FROM backends;+-------------+---------------------+---------+------+------+-------------------+| backend_ndx | address             | state   | type | uuid | connected_clients |+-------------+---------------------+---------+------+------+-------------------+|           1 | 182.48.115.237:3306 | unknown | rw   | NULL |                 0 ||           2 | 182.48.115.236:3306 | unknown | ro   | NULL |                 0 ||           3 | 182.48.115.238:3306 | unknown | ro   | NULL |                 0 |+-------------+---------------------+---------+------+------+-------------------+3 rows in set (0.00 sec)

3）读写分离测试

1）在master-node数据库上创建proxy用户，从服务器也会同步这个操作。mysql> GRANT ALL ON *.* TO 'wang'@'182.48.115.%' IDENTIFIED BY '123456';Query OK, 0 rows affected (0.00 sec) mysql> flush privileges;Query OK, 0 rows affected (0.00 sec) 2）proxy-node机器上使用创建的proxy用户登陆数据库，进行数据操作[root@proxy-node ~]# mysql -uwang -h182.48.115.233 -p123456.......mysql> create database huanqiutest;Query OK, 1 row affected (0.00 sec)mysql> use huanqiutest;Database changedmysql> create table haha( id int(5), name varchar(10));Query OK, 0 rows affected (0.19 sec)mysql> insert into haha values(1,"zhangbao");Query OK, 1 row affected (0.00 sec)mysql> insert into haha values(11,"shibo");Query OK, 1 row affected (0.00 sec)mysql> select * from haha;+------+----------+| id   | name     |+------+----------+|    1 | zhangbao ||   11 | shibo    |+------+----------+2 rows in set (0.01 sec) 注意下面两点：-> 发现使用proxy用户登陆数据库写入的数据只写入master主库，然后再同步到slave从库。如果将从库的slave同步功能关闭，则从库就无法更新数据。-> 登陆从库服务器关闭slave同步功能（stop slave），这时再登陆proxy-noed机器肯定会查询不出数据（能看到表，但是查询不出数据）-> 以上两点真正实现了读写分离的效果！ 3）查看状态，在proxy-node机器上操作，可以看到状态全部为up：[root@proxy-node bin]# mysql -uadmin -padmin -h182.48.115.233 --port=4041....... mysql> SELECT * FROM backends;+-------------+---------------------+---------+------+------+-------------------+| backend_ndx | address             | state   | type | uuid | connected_clients |+-------------+---------------------+---------+------+------+-------------------+|           1 | 182.48.115.237:3306 | up      | rw   | NULL |                 0 ||           2 | 182.48.115.236:3306 | up      | ro   | NULL |                 0 ||           3 | 182.48.115.238:3306 | up      | ro   | NULL |                 0 |+-------------+---------------------+---------+------+------+-------------------+3 rows in set (0.00 sec)

读写分离，延迟是个大问题，要确保主从同步这一环节不要有延迟。
---------------mysql主从同步延迟原理----------------
－> mysql主从同步原理
主库针对读写操作，顺序写 binlog，从库单线程去主库读"写操作的binlog",从库取到 binlog在本地原样执行(随机写),来保证主从数据逻辑上一致.
mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生 binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率比较高，下一步问题来了，slave的 slave_sql_running线程将主库的 DDL和DML操作在 slave实施。DML，DDL的IO操作是随即的，不能顺序的，成本高很多，还有可能slave上的其他查询产生 lock，由于 slave_sql_running也是单线程的，所以 一个 DDL卡住了，需求需求执行一段时间，那么所有之后的DDL会等待这个 DDL执行完才会继续执行，这就导致了延迟.由于master可以并发，Slave_sql_running线程却不可以，所以主库执行 DDL需求一段时间，在slave执行相同的DDL时，就产生了延迟.

-> 主从同步延迟产生原因
当主库的TPS并发较高时，产生的DDL数量超过Slave一个 sql线程所能承受的范围，那么延迟就产生了，当然还有就是可能与 slave的大型 query语句产生了锁等待
首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高
次要原因：读写 binlog带来的性能影响，网络传输延迟

->  mysql主从同步加速
1）sync_binlog在slave端设置为0
2）–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。
3）直接禁用slave端的binlog
4）slave端，如果使用的存储引擎是innodb，innodb_flush_log_at_trx_commit =2

再有就是进行分库分表处理，这样减少数据量的复制同步操作