Monday, 17 October 2011

MySQL: User Activity Tracking (Bandwidth)

Recently I was presented a question whether it would be possible on MySQL to track the user activity in terms of queries and the amount of data downloaded by the individual user from the database. 


Thus in the quest I came across a brilliant tool named “MySQL Proxy”. This tool provides a very strong functionality for MySQL database, which includes multiple MySQL server load balancing, query injection, and much more. This tool provided a very robust solution for the request with the usage of Lua scripting. Rather me extending more benefits about the tool in the post, I provide all the references below for the readers to explore more benefits of the tool themselves and here I would like to present my solution to record user activity in MySQL using MySQL Proxy with the following Lua script.

Note: This script also provides a section where user can be prohibited to download more data than the specified limit.
-- measures bandwidth by user = or {}

local session_user 
local sqlQuery
local before_bytes_sent = 0
local after_bytes_sent  = 0
local before_bytes_recevied = 0
local after_bytes_recevied = 0

function read_auth()
    session_user = proxy.connection.client.username[session_user] =[session_user] or 0

function read_query (packet )
    -- just to show how we can block a user query
    -- when the quota has been exceeded
    if[session_user] > 10000 
       and session_user ~= 'root'
        return error_result('you have exceeded your query quota')  
    sqlQuery = string.sub(packet, 2)[session_user ] =[session_user] + packet:len()

    proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "SHOW SESSION STATUS LIKE '%Bytes%'", {resultset_is_needed = true} )
    proxy.queries:append(2, packet,{resultset_is_needed = true})
    proxy.queries:append(3, string.char(proxy.COM_QUERY) .. "SHOW SESSION STATUS LIKE '%Bytes%'", {resultset_is_needed = true} )

    return proxy.PROXY_SEND_QUERY

function read_query_result(inj)
 if ( == 4) then
     return proxy.PROXY_IGNORE_RESULT
        if ( == 1) or ( == 3) then
                for row in inj.resultset.rows do
                        if (row[1] == "Bytes_sent") and ( == 1) then
        before_bytes_sent = row[2]
   if (row[1] == "Bytes_sent") and ( == 3) then
        after_bytes_sent = row[2]
   if (row[1] == "Bytes_received") and ( == 1) then
        before_bytes_recevied = row[2]
   if (row[1] == "Bytes_received") and ( == 3) then
        after_bytes_recevied = row[2]
             if ( == 3) then
     print("Bytes sent before: " .. before_bytes_sent)
     print("Bytes sent after: " .. after_bytes_sent)
     print("Bytes received before: " .. before_bytes_recevied)
     print("Bytes received after: " .. after_bytes_recevied)
     print("Net Bytes sent: " .. (after_bytes_sent - before_bytes_sent))
     print("Net Bytes received: " .. (after_bytes_recevied - before_bytes_recevied))
     print("Username: " .. session_user)
     print("Query: " .. sqlQuery) 
     print("DateTime: " .."%Y-%m-%d %H:%M:%S"))
     insert_log_query(session_user,"%Y-%m-%d %H:%M:%S"), sqlQuery, (after_bytes_sent - before_bytes_sent), (after_bytes_recevied - before_bytes_recevied))
                return proxy.PROXY_IGNORE_RESULT

function insert_log_query(username, date_time, query , net_bytes_sent, net_bytes_recived)
      print(username, date_time, query , net_bytes_sent, net_bytes_recived)
      proxy.queries:append(4, string.char(proxy.COM_QUERY) .. "INSERT INTO `employees`.`user_log` (`username`, `date_time`, `query`, `bytes_sent`, `bytes_recived`) VALUES ('" ..
      username .. "','" .. date_time .. "',\"" .. query .. "\"," ..  net_bytes_sent .. "," .. net_bytes_recived .. ");", {resultset_is_needed = true})
      return proxy.PROXY_SEND_QUERY 

This script uses the "SHOW SESSION STATUS" results for the recording of bytes sent and recevied. This also illustrates the power of MySQL Proxy which enables use to inject addtional queries to the database and process their results to our needs without any affects visible to the end user.


Download Solution
Download solution


No comments: