【原創】MySQLProxy-內部結構

        在 MySQL Proxy 的指令碼元素中有一些基本的內部結構需要知道。其中最主要的結構就是 proxy ，其提供了訪問貫穿指令碼中的許多公共結構的介面，例如連線列表和配置的 backend server 。其他結構，例如來自客戶端的包和返回的結果集等，只有在具體的指令碼函式的上下文環境中才是可以訪問的。 


下表中描述了 MySQL proxy 指令碼元素的公共屬性。 

Attribute	Description
connection	A structure containing the active client connections. For a list of attributes, seeproxy.connection.
servers	A structure containing the list of configured backend servers. For a list of attributes, seeproxy.global.backends.
queries	A structure containing the queue of queries that will be sent to the server during a single client query. For a list of attributes, see proxy.queries.
PROXY_VERSION	The version number of MySQL Proxy, encoded in hex. You can use this to check that the version number supports a particular option from within the Lua script. Note that the value is encoded as a hex value, so to check the version is at least 0.5.1 you compare against0x00501.

proxy.connection 


       proxy.connection 物件是隻讀的，提供了關於當前連線的資訊，其內容被分成了客戶端和伺服器兩張表。這樣就允許你可以既檢查來自於客戶端到 proxy 的連線資訊（儲存在client中），又可以檢查 proxy 到伺服器的連線資訊（儲存在server中）。 


下表描述了 proxy.connection 物件中客戶端和伺服器屬性。 

Attribute	Description
client.default_db	Default database requested by the client
client.username	User name used to authenticate
client.scrambled_password	The scrambled version of the password used to authenticate
client.dst.name	The combined address:port of the Proxy port used by this client (should match the –proxy-address configuration parameter)
client.dst.address	The IP address of the of the Proxy port used by this client
client.dst.port	The port number of the of the Proxy port used by this client
client.src.name	The combined address:port of the client (originating) TCP/IP endpoint
client.src.address	The IP address of the client (originating) TCP/IP port
client.src.port	The port of the client (originating) TCP/IP endpoint
server.scramble_buffer	The scramble buffer used to scramble the password
server.mysqld_version	The MySQL version number of the server
server.thread_id	The ID of the thread handling the connection to the current server
server.dst.name	The combined address:port for the backend server for the current connection (i.e. the connection to the MySQL server)
server.dst.address	The address for the backend server
server.dst.port	The port for the backend server
server.src.name	The combined address:port for the TCP/IP endpoint used by the Proxy to connect to the backend server
server.src.address	The address of the endpoint for the proxy-side connection to the MySQL server
server.src.port	The port of the endpoint for the proxy-side connection to the MySQL server

proxy.global.backends 


       proxy.global.backends 表示部分可寫的，並且其以陣列的形式包含了所有的已配置 backend server 和伺服器後設資料（IP地址，狀態等）的資訊。你可以通過 proxy.connection[“backend_ndx”] 的方式指定當前連線的陣列索引值，backend_ndx 是被有效連線使用了的 backend server 表的索引值。 


下表描述了表 proxy.global.backends 中的每一個入口的屬性值。 

Attribute	Description
dst.name	The combined address:port of the backend server.
dst.address	The IP address of the backend server.
dst.port	The port of the backend server.
connected_clients	The number of clients currently connected.
state	The status of the backend server. See Backend State/Type Constants.
type	The type of the backend server. You can use this to identify whether the backed was configured as a standard read/write backend, or a read-only backend. You can compare this value to the proxy.BACKEND_TYPE_RW andproxy.BACKEND_TYPE_RO.

proxy.queries 


       proxy.queries 物件是一個佇列，用於儲存將要傳送到伺服器的的 query 列表。該佇列不會被自動填入（populated），故如果你不顯式地向該佇列填入內容，query 將會被不做任何修改的傳送到 backend server，同樣，如果你不手動將 query 新增到佇列中，read_query_result() 函式將不會被觸發。 


下面的函式可以用於對 proxy.queries 物件進行操作。 

Function	Description
append(id,packet,[options])	Appends a query to the end of the query queue. The id is an integer identifier that you can use to recognize the query results when they are returned by the server. The packet should be a properly formatted query packet. The optional options should be a table containing the options specific to this packet.
prepend(id,packet)	Prepends a query to the query queue. The id is an identifier that you can use to recognize the query results when they are returned by the server. The packet should be a properly formatted query packet.
reset()	Empties the query queue.
len()	Returns the number of query packets in the queue.

例如，你可以通過使用函式 append() 將一個 query 包附加到 proxy.queries 佇列的尾部： 

proxy.queries:append(1,packet)

       函式 append() 的可選的第三個引數應該包含針對當前包的一些選項設定。為了能夠在函式 read_query_result() 中訪問結果集，需要設定選項 resultset_is_needed 為 true ： 

proxy.queries:append( 1, packet, { resultset_is_needed = true } )

如果該選項設定為 false（預設設定），proxy 將： 

1. 在收到結果集時立刻將其傳送給客戶端
2. 減少記憶體使用（因為結果集不需要為處理在proxy內部儲存）
3. 減少返回結果給客戶端的延遲
4. 從伺服器到客戶端的傳輸資料不作改變

       由此可知，如果你僅僅想要監控被髮送的 query 和基本的統計資訊，那麼在預設模式下速度會更快，也更要求。 

為了在返回資料上進行任何形式的操作，都必須將 resultset_is_needed 設定為 true ，設定後： 

1. proxy 會儲存結果集以備後續處理使用
2. 使能將結果集返回給客戶端前可以進行修改的能力
3. 使能將結果集返回給客戶端前可以丟棄結果集的能力

proxy.response 


       proxy.response 結構被用於在你打算返回你自己的 MySQL response 的情況下，而不返回給客戶端來自伺服器的應答包。該結構中包含了 response 的型別資訊，一個可選的錯誤資訊，以及（行/列）結果集。 


下表描述了 proxy.response 結構的屬性值。 

Attribute	Description
type	The type of the response. The type must be either MYSQLD_PACKET_OK orMYSQLD_PACKET_ERR. If the MYSQLD_PACKET_ERR, you should set the value of themysql.response.errmsg with a suitable error message.
errmsg	A string containing the error message that will be returned to the client.
resultset	A structure containing the result set information (columns and rows), identical to what would be returned when returning a results from a SELECT query.

       當使用 proxy.response 時，或者你設定 proxy.response.type 的值為 proxy.MYSQLD_PACKET_OK ，並構建包含要返回的結果的自定義結果集；或者你設定 proxy.response.type 的值為 proxy.MYSQLD_PACKET_ERR ，並設定 proxy.response.errmsg 值為表示錯誤訊息內容的字串。為了傳送完整的結果集或者錯誤訊息，你應該返回 proxy.PROXY_SEND_RESULT 以觸發在函式中構建內容的返回。 


上述說明的一個具體例子可以參閱 MySQL Proxy 原始碼包中 tutorial-resultset.lua 指令碼： 

if string.lower(command) == "show" and string.lower(option) == "querycounter" then
        ---
        -- proxy.PROXY_SEND_RESULT requires
        --
        -- proxy.response.type to be either
        -- * proxy.MYSQLD_PACKET_OK or
        -- * proxy.MYSQLD_PACKET_ERR
        --
        -- for proxy.MYSQLD_PACKET_OK you need a resultset
        -- * fields
        -- * rows
        --
        -- for proxy.MYSQLD_PACKET_ERR
        -- * errmsg
        proxy.response.type = proxy.MYSQLD_PACKET_OK
        proxy.response.resultset = {
                fields = {
                        { type = proxy.MYSQL_TYPE_LONG, name = "global_query_counter", },
                        { type = proxy.MYSQL_TYPE_LONG, name = "query_counter", },
                },
                rows = {
                        { proxy.global.query_counter, query_counter }
                }
        }


        -- we have our result, send it back
        return proxy.PROXY_SEND_RESULT
elseif string.lower(command) == "show" and string.lower(option) == "myerror" then
        proxy.response.type = proxy.MYSQLD_PACKET_ERR
        proxy.response.errmsg = "my first error"


        return proxy.PROXY_SEND_RESULT

proxy.response.resultset 


       proxy.response.resultset 結構應該被填入返回資料的 row 和 column 資訊。該結構中包含了整個結果集的資訊，其中涉及到的單獨的元素在下面表述。 


下表中描述了 proxy.response.resultset 結構的屬性值。 

Attribute	Description
fields	The definition of the columns being returned. This should be a dictionary structure with thetype specifying the MySQL data type, and the name specifying the column name. Columns should be listed in the order of the column data that will be returned.
flags	A number of flags related to the result set. Valid flags include auto_commit (whether an automatic commit was triggered), no_good_index_used (the query executed without using an appropriate index), and no_index_used (the query executed without using any index).
rows	The actual row data. The information should be returned as an array of arrays. Each inner array should contain the column data, with the outer array making up the entire result set.
warning_count	The number of warnings for this result set.
affected_rows	The number of rows affected by the original statement.
insert_id	The last insert ID for an auto-incremented column in a table.
query_status	The status of the query operation. You can use the MYSQLD_PACKET_OK orMYSQLD_PACKET_ERR constants to populate this parameter.

================ 下面是一些常量定義 =================== 


[[Proxy Return State Constants]] 


       下表中給出的是被 proxy 在內部使用的常量值定義，用於標識傳送到客戶端和伺服器的應答包。所有的常量都在 proxy 表中以常量值的形式可見。 

Constant	Description
PROXY_SEND_QUERY	Causes the proxy to send the current contents of the queries queue to the server.
PROXY_SEND_RESULT	Causes the proxy to send a result set back to the client.
PROXY_IGNORE_RESULT	Causes the proxy to drop the result set (nothing is returned to the client).

       作為常量值，這些實體在 Lua 指令碼中是無條件的可用。例如，在函式 read_query_result() 的結尾，你可能返回常量 PROXY_IGNORE_RESULT ： 

return proxy.PROXY_IGNORE_RESULT

[[Packet State Constants]] 


下面的狀態值用於定義網路包的狀態資訊。這些值也存在於 proxy 表中。 

Constant	Description
MYSQLD_PACKET_OK	The packet is OK
MYSQLD_PACKET_ERR	The packet contains error information
MYSQLD_PACKET_RAW	The packet contains raw data

[[Backend State/Type Constants]] 


下面的常量被用於定義 backend MySQL server 的狀態和型別。這些值也存在於 proxy 表中。 

Constant	Description
BACKEND_STATE_UNKNOWN	The current status is unknown
BACKEND_STATE_UP	The backend is known to be up (available)
BACKEND_STATE_DOWN	The backend is known to be down (unavailable)
BACKEND_TYPE_UNKNOWN	Backend type is unknown
BACKEND_TYPE_RW	Backend is available for read/write
BACKEND_TYPE_RO	Backend is available only for read-only use

[[Server Command Constants]] 


       下表中描述的值被用於客戶端和伺服器進行包交換過中標識包中其餘內容的資訊型別。這些值也存在於 proxy 表中。包型別由傳送包的第一個位元組指定。例如，當為了修改或者監聽的目的攔截來自於客戶端的包時，你需要檢查包的第一個位元組是否為 proxy.COM_QUERY 型別。 

Constant	Description
COM_SLEEP	Sleep
COM_QUIT	Quit
COM_INIT_DB	Initialize database
COM_QUERY	Query
COM_FIELD_LIST	Field List
COM_CREATE_DB	Create database
COM_DROP_DB	Drop database
COM_REFRESH	Refresh
COM_SHUTDOWN	Shutdown
COM_STATISTICS	Statistics
COM_PROCESS_INFO	Process List
COM_CONNECT	Connect
COM_PROCESS_KILL	Kill
COM_DEBUG	Debug
COM_PING	Ping
COM_TIME	Time
COM_DELAYED_INSERT	Delayed insert
COM_CHANGE_USER	Change user
COM_BINLOG_DUMP	Binlog dump
COM_TABLE_DUMP	Table dump
COM_CONNECT_OUT	Connect out
COM_REGISTER_SLAVE	Register slave
COM_STMT_PREPARE	Prepare server-side statement
COM_STMT_EXECUTE	Execute server-side statement
COM_STMT_SEND_LONG_DATA	Long data
COM_STMT_CLOSE	Close server-side statement
COM_STMT_RESET	Reset statement
COM_SET_OPTION	Set option
COM_STMT_FETCH	Fetch statement
COM_DAEMON	Daemon (MySQL 5.1 only)
COM_ERROR	Error

[[MySQL Type Constants]] 


下面的常量被用於對 query 的結果中的資料進行域型別標識。這些值也存在於 proxy 表中。 

Constant	Field Type
MYSQL_TYPE_DECIMAL	Decimal
MYSQL_TYPE_NEWDECIMAL	Decimal (MySQL 5.0 or later)
MYSQL_TYPE_TINY	Tiny
MYSQL_TYPE_SHORT	Short
MYSQL_TYPE_LONG	Long
MYSQL_TYPE_FLOAT	Float
MYSQL_TYPE_DOUBLE	Double
MYSQL_TYPE_NULL	Null
MYSQL_TYPE_TIMESTAMP	Timestamp
MYSQL_TYPE_LONGLONG	Long long
MYSQL_TYPE_INT24	Integer
MYSQL_TYPE_DATE	Date
MYSQL_TYPE_TIME	Time
MYSQL_TYPE_DATETIME	Datetime
MYSQL_TYPE_YEAR	Year
MYSQL_TYPE_NEWDATE	Date (MySQL 5.0 or later)
MYSQL_TYPE_ENUM	Enumeration
MYSQL_TYPE_SET	Set
MYSQL_TYPE_TINY_BLOB	Tiny Blob
MYSQL_TYPE_MEDIUM_BLOB	Medium Blob
MYSQL_TYPE_LONG_BLOB	Long Blob
MYSQL_TYPE_BLOB	Blob
MYSQL_TYPE_VAR_STRING	Varstring
MYSQL_TYPE_STRING	String
MYSQL_TYPE_TINY	Tiny (compatible with MYSQL_TYPE_CHAR)
MYSQL_TYPE_ENUM	Enumeration (compatible with MYSQL_TYPE_INTERVAL)
MYSQL_TYPE_GEOMETRY	Geometry
MYSQL_TYPE_BIT	Bit