你好,游客 登录 注册 搜索
背景:
阅读新闻

MariaDB Proxy读写分离的实现

[日期:2014-05-06] 来源:Linux社区  作者:1757513075 [字体: ]

为服务脚本提供配置文件/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))

linux
相关资讯       MariaDB  Proxy  读写分离 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款