首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql读写分离方案-MySQL Proxy环境部署记录

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

作者头像
洗尽了浮华
发布2018-01-23 10:19:49
1.9K0
发布2018-01-23 10:19:49
举报
文章被收录于专栏:散尽浮华散尽浮华

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-node
182.48.115.236     slave1      slave-node1
182.48.115.238     slave2      slave-node2
182.48.115.233     proxy       proxy-node

四台机器都关闭防火墙和selinux

绑定hosts设置(四台机器都要操作)
[root@master-node ~]# vim /etc/hosts
......
182.48.115.237   master-node
182.48.115.236   slave-node1
182.48.115.238   slave-node2
182.48.115.233   proxy-node

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

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

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

1)安装mysql-proxy
mysql 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-proxy

2)提供服务脚本
[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/functions

prog="/usr/local/mysql-proxy/bin/mysql-proxy"

# Source networking configuration.
if [ -f /etc/sysconfig/network ]; then
    . /etc/sysconfig/network
fi

# 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.pid
PROXY_USER="mysql-proxy"

# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
    . /etc/sysconfig/mysql-proxy
fi

RETVAL=0

start() {
    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
        ;;
esac

exit $RETVAL

[root@proxy-node ~]# chmod 755 /etc/init.d/mysql-proxy
[root@proxy-node ~]# chkconfig --add mysql-proxy

3)为服务脚本提供配置文件
[root@proxy-node ~]# vim /etc/sysconfig/mysql-proxy
# Options for mysql-proxy
ADMIN_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"
}
end

function read_query(packet)
if packet:byte() ~= proxy.COM_QUERY then
set_error("[admin] we only handle text-based queries (COM_QUERY)")
return proxy.PROXY_SEND_RESULT
end

local query = packet:sub(2)

local rows = { }
local fields = { }

if query:lower() == "select * from backends" then
fields = { 
{ 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 do
local states = {
"unknown",
"up",
"down"
}
local types = {
"unknown",
"rw",
"ro"
}
local b = proxy.global.backends[i]

rows[#rows + 1] = {
i,
b.dst.name,          -- configured backend address
states[b.state + 1], -- the C-id is pushed down starting at 0
types[b.type + 1],   -- the C-id is pushed down starting at 0
b.uuid,              -- the MySQL Server's UUID if it is managed
b.connected_clients  -- currently connected clients
}
end
elseif query:lower() == "select * from help" then
fields = { 
{ 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" }
else
set_error("use 'SELECT * FROM help' to see the supported commands")
return proxy.PROXY_SEND_RESULT
end

proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
resultset = {
fields = fields,
rows = rows
}
}
return proxy.PROXY_SEND_RESULT
end

5)为了测试更明显,编辑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 start
Starting /usr/local/mysql-proxy/bin/mysql-proxy:           [  OK  ]

[root@proxy-node ~]# service mysql-proxy status
mysql-proxy (pid  4655) is running...

[root@proxy-node ~]# netstat -tunlp
Active 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=4041
Warning: 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 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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 changed
mysql> 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

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

本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2017-06-01 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档