mysql-proxy学习之配置文件

原创 2012年03月29日 13:41:55

mysql-proxy启动时需要一个配置文件,你可以将你的全部选项放在你的配置文件里面,这样启动时比较方便,比如下面这样:


  /usr/local/sbin/mysql-proxy
--proxy-address=192.168.0.2:3306
--proxy-backend-addresses=127.0.0.1:3000

--proxy-backend-addresses=192.168.0.2:4000

--proxy-lua-script= /usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua  

当然你可以将--proxy-address=192.168.0.2:3306
--proxy-backend-addresses=127.0.0.1:3000

--proxy-backend-addresses=192.168.0.2:4000

放在一个文件中,而不是每次都输入,放在一个文件中然后在admin.lua包含进来就可以。

mysql-proxy配置文件除了对集群的IP和监听端口配置外主要完成以下几个函数的重写:

下面以一个别人的例子说明

local commands    = require("proxy.commands") //需要包含库,和C++有点相似
local tokenizer   = require("proxy.tokenizer")
local lb          = require("proxy.balance")

if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                max_idle_connections = 30,
                is_debug = false
        }
end



--local is_in_transaction       = false

function connect_server() //初始化连接服务器函数
        local is_debug = proxy.global.config.rwsplit.is_debug

        local rw_ndx = 1
  local min_cur_idle_connections=1000000

        -- init all backends
        for i = 1, #proxy.global.backends do
                local s        = proxy.global.backends[i]
                local pool     = s.pool -- we don't have a username yet, try to find a connections which is idling
                local cur_idle = pool.users[""].cur_idle_connections
                if min_cur_idle_connections > cur_idle
                then
                 min_cur_idle_connections=cur_idle
                 rw_ndx=i
                end
end

proxy.connection.backend_ndx = rw_ndx

        if is_debug then
                print()
                print("[connect_server] " .. proxy.connection.client.src.name)
                print("[server] " .. proxy.global.backends[proxy.connection.backend_ndx].dst.name)
        end

end


function   read_handshake()//初始化连接产生后(通过connect_server()函数),握手(handshake)信息由server发送到client。
function read_auth()               // 读取Client的认证信息时调用

function read_auth_result( auth )  //读取Client的认证信息时调用
local is_debug = proxy.global.config.rwsplit.is_debug
        if is_debug then
                print("[read_auth_result] " .. proxy.connection.client.src.name)
        end
        if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
                -- auth was fine, disconnect from the server
                proxy.connection.backend_ndx = 0
        elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF then
                -- we received either a
                --
                -- * MYSQLD_PACKET_ERR and the auth failed or
                -- * MYSQLD_PACKET_EOF which means a OLD PASSWORD (4.0) was sent
                print("(read_auth_result) ... not ok yet");
        elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then
                -- auth failed
        end
end




function read_query( packet )//读取Client的query请求时调用
        local is_debug = proxy.global.config.rwsplit.is_debug
        local cmd      = commands.parse(packet)
        local c        = proxy.connection.client
        
        local tokens

        -- looks like we have to forward this statement to a backend
        if is_debug then
                print("[read_query] " .. proxy.connection.client.src.name)
                print("  current backend   = " .. proxy.connection.backend_ndx)
                print("  client default db = " .. c.default_db)
                print("  client username   = " .. c.username)
                if cmd.type == proxy.COM_QUERY then
                        print("  query             = "        .. cmd.query)
                end
        end
        
                if cmd.type == proxy.COM_QUIT then
                -- don't send COM_QUIT to the backend. We manage the connection
                -- in all aspects.
                proxy.response = {
                        type = proxy.MYSQLD_PACKET_OK,
                }
        
                if is_debug then
                        print("  (QUIT) current backend   = " .. proxy.connection.backend_ndx)
                end

                return proxy.PROXY_SEND_RESULT
        end

        proxy.queries:append(1, packet, { resultset_is_needed = true })
        
        if not is_in_transaction and
           cmd.type == proxy.COM_QUERY then
                tokens     = tokens or assert(tokenizer.tokenize(cmd.query))

                local stmt = tokenizer.first_stmt_token(tokens)

                if stmt.token_name == "TK_SQL_SELECT" then
                
                        local is_insert_id = false
                        
    for i = 1, #tokens do
                                local token = tokens[i]        
                                   local utext = token.text:upper()
                                   if token.token_name == "TK_LITERAL"
                                      then
                                              if utext == "@@LAST_INSERT_ID" then
                                                      is_insert_id = true
                                              end
                                         elseif token.token_name == "TK_FUNCTION"
                                          then
                                                   if utext == "LAST_INSERT_ID" then
                                                      is_insert_id = true
                                              end
                                          end                                         
                                
                        end
                                                        
                                                        
                if not is_insert_id
                 then local backend_ndx = lb.idle_ro()

                                if backend_ndx > 0 then
                                        proxy.connection.backend_ndx = backend_ndx
                                end
                        else
                                print("   found a SELECT LAST_INSERT_ID(), staying on the same backend")
                        end
                end
        end
        
                if proxy.connection.backend_ndx == 0 then
                -- we don't have a backend right now
                --
                -- let's pick a master as a good default
                --
                proxy.connection.backend_ndx = lb.idle_failsafe_rw()
        end
        
        
        -- by now we should have a backend
        --
        -- in case the master is down, we have to close the client connections
        -- otherwise we can go on
        if proxy.connection.backend_ndx == 0 then
                return proxy.PROXY_SEND_QUERY
        end

        local s = proxy.connection.server
        
        
                if cmd.type ~= proxy.COM_INIT_DB and
           c.default_db and c.default_db ~= s.default_db then
                print("    server default db: " .. s.default_db)
                print("    client default db: " .. c.default_db)
                print("    syncronizing")
                proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db, { resultset_is_needed = true })
        end
        
                -- send to master
        if is_debug then
                if proxy.connection.backend_ndx > 0 then
                        local b = proxy.global.backends[proxy.connection.backend_ndx]
                        print("  sending to backend : " .. b.dst.name);
                        print("    is_read_only[slave server]         : " .. tostring(b.type == proxy.BACKEND_TYPE_RO));
                        print("    server default db: " .. s.default_db)
                        print("    server username  : " .. s.username)
                end
                print("    in_trans        : " .. tostring(is_in_transaction))
                print("    COM_QUERY       : " .. tostring(cmd.type == proxy.COM_QUERY))
        end

        return proxy.PROXY_SEND_QUERY
end
        
        
function read_query_result(
inj ) //读取query结果时调用
        local is_debug = proxy.global.config.rwsplit.is_debug
        local res      = assert(inj.resultset)
          local flags    = res.flags

        if inj.id ~= 1 then
                -- ignore the result of the USE <default_db>
                -- the DB might not exist on the backend, what do do ?
                --
                if inj.id == 2 then
                        -- the injected INIT_DB failed as the slave doesn't have this DB
                        -- or doesn't have permissions to read from it
                        if res.query_status == proxy.MYSQLD_PACKET_ERR then
                                proxy.queries:reset()

                                proxy.response = {
                                        type = proxy.MYSQLD_PACKET_ERR,
                                        errmsg = "can't change DB ".. proxy.connection.client.default_db ..
                                                " to on slave " .. proxy.global.backends[proxy.connection.backend_ndx].dst.name
                                }

                                return proxy.PROXY_SEND_RESULT
                        end
                end
                return proxy.PROXY_IGNORE_RESULT
        end

        is_in_transaction = flags.in_trans
        local have_last_insert_id = (res.insert_id and (res.insert_id > 0))

        if not is_in_transaction and
           not have_last_insert_id then
                -- release the backend
                proxy.connection.backend_ndx = 0
        elseif is_debug then
                print("(read_query_result) staying on the same backend")
                print("    in_trans        : " .. tostring(is_in_transaction))
                print("    have_insert_id  : " .. tostring(have_last_insert_id))
        end
end

        
function disconnect_client()
        local is_debug = proxy.global.config.rwsplit.is_debug
        if is_debug then
                print("[disconnect_client] " .. proxy.connection.client.src.name)
        end

        -- make sure we are disconnection from the connection
        -- to move the connection into the pool
            if proxy.connection.backend_ndx == 0
            then
                    for i = 1, #proxy.global.backends do
                    local s = proxy.global.backends[i]        
          local pool     = s.pool
                 local cur_idle = pool.users[""].cur_idle_connections
                 pool.max_idle_connections = proxy.global.config.rwsplit.max_idle_connections
                 if is_debug then
                   print ("cur_idle="..cur_idle )
                   print ("pool.max_idle_connections = "..pool.max_idle_connections)
                      end
                       if s.state ~= proxy.BACKEND_STATE_DOWN and
            cur_idle > pool.max_idle_connections then
          -- try to disconnect a backend
           proxy.connection.backend_ndx = i
           return
          end
        end
      end                
end



        
        

转MySQL Proxy的几篇文章

最近打算用MySQL的读写分离,从网上摘录了几篇。   MySQL主从复制(Master-Slave)与读写分离(MySQL-Proxy)实践   原址如下: http://heylinux...
  • edwzhang
  • edwzhang
  • 2013年01月07日 10:06
  • 34479

ProxySQL--灵活强大的MySQL代理层

本文是我在学习和验证ProxySQL的过程中,从初识(对其机制猜想或者仅凭几次命令的结果臆断其原理),到逐渐深入(模拟各种场景测试、抓包分析、与作者交流)过程中的思路和方法和结论的记录。ProxySQ...
  • kai404
  • kai404
  • 2016年09月25日 22:16
  • 12381

mysql配置mysql-proxy读写分离

mysql配置读写分离 在这里你再也不用担心高手省略了大段代码了,因为我也是新手。 下面开整 先安装lua Lua 是一个小巧的脚本语言。Lua由标准C编写而成,代码简洁优美,几乎在所有操作系统和平...
  • e421083458
  • e421083458
  • 2014年02月22日 20:34
  • 26986

[原创] MySQL Proxy 学习笔记

[原创] MySQL Proxy 学习笔记作者:heiyeluren时间:2008-1-28博客:http://blog.csdn.net/heiyeshuwu 【 测试平台 】服务器端:OS:Fre...
  • heiyeshuwu
  • heiyeshuwu
  • 2008年01月28日 00:27
  • 25651

主从读写分离----mysql-proxy0.8.5安装与配置

主从读写分离----mysql-proxy0.8.5安装与配置
  • wangxiaofei2006
  • wangxiaofei2006
  • 2016年08月22日 16:07
  • 1648

mysql配置mysql-proxy读写分离

mysql配置读写分离 在这里你再也不用担心高手省略了大段代码了,因为我也是新手。 下面开整 先安装lua Lua 是一个小巧的脚本语言。Lua由标准C编写而成,代码简洁优美,几乎在所有操作系统和平...
  • e421083458
  • e421083458
  • 2014年02月22日 20:34
  • 26986

使用mysql-proxy 快速实现mysql 集群 读写分离

目前较为常见的mysql读写分离分为两种:  1、 基于程序代码内部实现:在代码中对select操作分发到从库;其它操作由主库执行;这类方法也是目前生产环境应用最广泛,知名的如DISCUZ X2。优...
  • tianwei7518
  • tianwei7518
  • 2014年01月16日 15:56
  • 1351

MySQL主从复制(Master-Slave)与读写分离(MySQL-Proxy)实践

MySQL主从复制(Master-Slave)与读写分离(MySQL-Proxy)实践   原址如下: http://heylinux.com/archives/1004.html ...
  • zhu1289303556
  • zhu1289303556
  • 2016年03月09日 16:51
  • 651

Centos6.4 x86_64下MySQL Proxy0.8.5安装测试实现读写分离

一、mysql-proxy简介 MySQL Proxy是一个处于MySQL Client端和MySQL Server端之间的简单程序,是mysql官方提供的MySQL中间件服务,可以将其理解为一个连...
  • Celeste7777
  • Celeste7777
  • 2015年10月06日 17:37
  • 990

mysql-proxy数据库中间件架构

一、mysql-proxy简介 mysql-proxy是mysql官方提供的mysql中间件服务,上游可接入若干个mysql-client,后端可连接若干个mysql-server。 它使用my...
  • xinzhi8
  • xinzhi8
  • 2017年05月21日 14:57
  • 604
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql-proxy学习之配置文件
举报原因:
原因补充:

(最多只允许输入30个字)