阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MariaDB Proxy读写分离的实现

228次阅读
没有评论

共计 9805 个字符,预计需要花费 25 分钟才能阅读完成。

MariaDB Proxy 读写分离的实现

1. 创建用于存储数据目录 lvm 设备

MariaDB Proxy 读写分离的实现

MariaDB Proxy 读写分离的实现

MariaDB Proxy 读写分离的实现

MariaDB Proxy 读写分离的实现

 

安装 mysql-proxy-0.8.3

 

MariaDB Proxy 读写分离的实现

MariaDB 的详细介绍:请点这里
MariaDB 的下载地址:请点这里

————————————– 分割线 ————————————–

推荐阅读:

在 CentOS/RHEL/Scientific Linux 6 下安装 LAMP (Apache with MariaDB and PHP) http://www.linuxidc.com/Linux/2013-07/86755.htm

Haproxy+Keepalived 搭建 Weblogic 高可用负载均衡集群 http://www.linuxidc.com/Linux/2013-09/89732.htm

Keepalived+HAProxy 配置高可用负载均衡 http://www.linuxidc.com/Linux/2012-03/56748.htm

CentOS 6.3 下 Haproxy+Keepalived+Apache 配置笔记 http://www.linuxidc.com/Linux/2013-06/85598.htm

Haproxy + KeepAlived 实现 WEB 群集 on CentOS 6 http://www.linuxidc.com/Linux/2012-03/55672.htm

Haproxy+Keepalived 构建高可用负载均衡 http://www.linuxidc.com/Linux/2012-03/55880.htm

————————————– 分割线 ————————————–

为 mysql-proxy 提供 SysV 服务脚本,内容如下所示
# vim /etc/rc.d/init.d/mysql-proxy

#!/bin/bash

#

# mysql-proxy Thisscript starts and stops the mysql-proxy daemon

#

# chkconfig: – 7830

# processname:mysql-proxy

# description:mysql-proxy is a proxy daemon for mysql

 

# Source functionlibrary.

./etc/rc.d/init.d/functions

 

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

 

# Sourcenetworking configuration.

if [-f/etc/sysconfig/network]; then

. /etc/sysconfig/network

fi

 

# Check thatnetworking is up.

[${NETWORKING} = “no”] && exit 0

 

# Set defaultmysql-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”

 

# Sourcemysql-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 werecalled.

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

 

将上述内容保存为 /etc/rc.d/init.d/mysql-proxy,给予执行权限,而后加入到服务列表。

# chmod +x /etc/rc.d/init.d/mysql-proxy

# chkconfig –add mysql-proxy

 

更多详情见请继续阅读下一页的精彩内容:http://www.linuxidc.com/Linux/2014-05/101306p2.htm

为服务脚本提供配置文件 /etc/sysconfig/mysql-proxy,内容如下所示:

# Options formysql-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”

 

其中最后一行,需要按实际场景进行修改,例如:

PROXY_OPTIONS=”–daemon–log-level=info –log-use-syslog –plugins=proxy –plugins=admin–proxy-backend-addresses=172.16.251.68:3306–proxy-read-only-backend-addresses=172.16.251.69:3306–proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua”

其中的 proxy-backend-addresses 选项和 proxy-read-only-backend-addresses 选项均可重复使用多次,以实现指定多个读写服务器或只读服务器。

 

 

 

复制如下内容建立 admin.lua 文件,将其保存至 /usr/local/mysql-proxy/share/doc/mysql-proxy/ 目录中。

–[[$%BEGINLICENSE%$

Copyright (c) 2007, 2012, Oracle and/or itsaffiliates. All rights reserved.

 

This program is free software; you canredistribute it and/or

modify it under the terms of the GNU GeneralPublic License as

published by the Free Software Foundation;version 2 of the

License.

 

This program is distributed in the hope thatit will be useful,

but WITHOUT ANY WARRANTY; without even theimplied warranty of

MERCHANTABILITY or FITNESS FOR A PARTICULARPURPOSE. See the

GNU General Public License for more details.

 

You should have received a copy of the GNUGeneral Public License

along with this program; if not, write to theFree Software

Foundation, Inc., 51 Franklin St, Fifth Floor,Boston, MA

02110-1301USA

 

$%ENDLICENSE%$ –]]

 

functionset_error(errmsg)

proxy.response = {

type = proxy.MYSQLD_PACKET_ERR,

errmsg = errmsg or “error”

}

end

 

functionread_query(packet)

if packet:byte() ~= proxy.COM_QUERY then

set_error(“[admin] we only handletext-based queries (COM_QUERY)”)

return proxy.PROXY_SEND_RESULT

end

 

local query = packet:sub(2)

 

local rows = {}

local fields = {}

 

if query:lower() == “select * frombackends” 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-idis 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 itis managed

b.connected_clients– currently connected clients

}

end

elseif query:lower() == “select * fromhelp” then

fields = {

{name = “command”,

type =proxy.MYSQL_TYPE_STRING },

{name = “description”,

type = proxy.MYSQL_TYPE_STRING },

}

rows[#rows + 1] = {“SELECT * FROMhelp”, “shows this help”}

rows[#rows + 1] = {“SELECT * FROMbackends”, “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

 

仅启动 mysql-proxy 就可以了,不要启动 MariaDB, 否则你只会看到 mysql 的 3306 端口

# service mysql-proxy start

# ss –tnl|egrep “:3306|:4401”

LISTEN 0 128 *:4041 *:* users:((“mysql-proxy”,3592,11))

LISTEN 0 128 *:3306 *:* users:((“mysql-proxy”,3592,10))

MariaDB Proxy 读写分离的实现

1. 创建用于存储数据目录 lvm 设备

MariaDB Proxy 读写分离的实现

MariaDB Proxy 读写分离的实现

MariaDB Proxy 读写分离的实现

MariaDB Proxy 读写分离的实现

 

安装 mysql-proxy-0.8.3

 

MariaDB Proxy 读写分离的实现

MariaDB 的详细介绍:请点这里
MariaDB 的下载地址:请点这里

————————————– 分割线 ————————————–

推荐阅读:

在 CentOS/RHEL/Scientific Linux 6 下安装 LAMP (Apache with MariaDB and PHP) http://www.linuxidc.com/Linux/2013-07/86755.htm

Haproxy+Keepalived 搭建 Weblogic 高可用负载均衡集群 http://www.linuxidc.com/Linux/2013-09/89732.htm

Keepalived+HAProxy 配置高可用负载均衡 http://www.linuxidc.com/Linux/2012-03/56748.htm

CentOS 6.3 下 Haproxy+Keepalived+Apache 配置笔记 http://www.linuxidc.com/Linux/2013-06/85598.htm

Haproxy + KeepAlived 实现 WEB 群集 on CentOS 6 http://www.linuxidc.com/Linux/2012-03/55672.htm

Haproxy+Keepalived 构建高可用负载均衡 http://www.linuxidc.com/Linux/2012-03/55880.htm

————————————– 分割线 ————————————–

为 mysql-proxy 提供 SysV 服务脚本,内容如下所示
# vim /etc/rc.d/init.d/mysql-proxy

#!/bin/bash

#

# mysql-proxy Thisscript starts and stops the mysql-proxy daemon

#

# chkconfig: – 7830

# processname:mysql-proxy

# description:mysql-proxy is a proxy daemon for mysql

 

# Source functionlibrary.

./etc/rc.d/init.d/functions

 

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

 

# Sourcenetworking configuration.

if [-f/etc/sysconfig/network]; then

. /etc/sysconfig/network

fi

 

# Check thatnetworking is up.

[${NETWORKING} = “no”] && exit 0

 

# Set defaultmysql-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”

 

# Sourcemysql-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 werecalled.

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

 

将上述内容保存为 /etc/rc.d/init.d/mysql-proxy,给予执行权限,而后加入到服务列表。

# chmod +x /etc/rc.d/init.d/mysql-proxy

# chkconfig –add mysql-proxy

 

更多详情见请继续阅读下一页的精彩内容:http://www.linuxidc.com/Linux/2014-05/101306p2.htm

MySQL Master

在 MySQL Master 端 (172.16.251.69) 授权

#mysql

mysql> GRANT ALL ON*.* TO ‘admin’@’172.16.%.%’ IDENTIFIED BY ‘admin’;

mysql> FLUSHPRIVILEGES;

 

 

MySQL Slave

MySQL Slave 端的数据库上查看是否有授权的用户

MariaDB Proxy 读写分离的实现

 

测试

下面的操作均在 Maridb Proxy(172.16.251.70)上执行

管理功能测试

此时,若我们还试图以传统的方式连接 Mysql 的话,很显然是会失败的,如下:

# mysql

ERROR 2002(HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock'(2 “No such file or directory”)

 

正确的方式:

[root@station150 ~]#mysql-uadmin -padmin -h172.16.251.70 –port=4041

Welcome to theMariaDB monitor.Commands end with ; or\g.

Your MySQLconnection id is 1

Server version:5.0.99-agent-admin

MySQL [(none)]> SELECT* FROM HELP;

MySQL [(none)]>SELECT * FROM backends;

+————-+——————–+———+——+——+——————-+

| backend_ndx |address| state| type | uuid | connected_clients |

+————-+——————–+———+——+——+——————-+

|1 | 172.16.251.69:3306 | unknown |rw| NULL |0 |

|2 | 172.16.251.68:3306 | unknown |ro| NULL |0 |

+————-+——————–+———+——+——+——————-+

2 rows in set (0.01sec)

 

读写分离测试

通过 MariadbProxy(172.16.251.70)访问 Master MySQL(172.16.251.69)的数据库

# mysql -uadmin-padmin -h172.16.251.70 [–port=3306]

(1)测试写操作

# mysql -uadmin -padmin-h172.16.251.70

MariaDB Proxy 读写分离的实现

 

然后分别去 Master 端和 Slave 端,验证是否能看通过 Mariadb Proxy 新建的 mydb1 数据库

MariaDB Proxy 读写分离的实现

 

通过 Mariadb Proxy 删除 hellodb 数据库及其中的数据,并验证

MariaDB Proxy 读写分离的实现

 

MariaDB Proxy 70
执行批量导入数据的操作:

MariaDB Proxy 读写分离的实现

MariaDB Proxy 读写分离的实现

验证读写分离的最好办法是:分别在 Master(可读写),Slave(只读)上使用 tcpdump 抓包工具,看可以捕捉到哪种操作的数据报文

 

测试读操作

在 Master 上捕获到的和读写操作相关的报文

MariaDB Proxy 读写分离的实现

由于在 Master 上可以进行读写操作,所以当读 (查询) 操作较少的时候,基本上 Master 自身就处理了,

所以想要验证效果就必须批量导入数据或执行查询操作。

 

在 MariadbProxy(70)执行批量读操作

# for i in `seq10`;do mysql -uadmin -padmin -h172.16.251.70 -e “select * fromhellodb.classes where ClassID=$i”;done

下面是批量执行读操作时,在 Slave 上捕获到的和读写操作相关的报文

 

MariaDB Proxy 读写分离的实现

MariaDB Proxy 读写分离的实现

 

朋友,还记得之前在 MariaDBProxy 上执行 SELECT * FROM backends; 时 state 栏的状态信息吗?

刚开执行读写操作的时候,只有 Master(172.16.251.69)的 state 是 up 状态,而在我批量执行读操作之后,

现在 Slave(172.16.251.68)的 state 也变成了 up 状态,表示 Slave 端已接受到了读请求

MariaDB Proxy 读写分离的实现

HAproxy 的详细介绍:请点这里
HAproxy 的下载地址:请点这里

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-20发表,共计9805字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中