Storing the logged queries into mysql client

Question

The client system i am using is (192.168.YY.YY) and the backend address i have mentioned in mysql-proxy is (192.168.XX.XX).I use the following LuaScript to log the queries using mysql-proxy

function read_query( packet )
  b={}
  j=1
      if string.byte(packet) == proxy.COM_QUERY then
        local query = string.sub(packet, 2)
        table.insert(b,query)
        print(b[j])
      end
end

And the above script logs the following results from the mysql of backend server (192.168.XX.XX):

select @@version_comment limit 1
SELECT DATABASE()
select @@version_comment limit 1
select USER()
SELECT DATABASE()
show tables

And i need to store these logged queries into a database of the client system (192.168.YY.YY) .How can the logged queries be stored into the database of the client system.Someone please help me to solve this issue.Thanks !!

Since b and j are initialized to empty/1 at every query, the print works but the b always has only one entry. Also, the client makes requests to mysql-proxy which executes the script then forwards to the backend mysql server, whereas your posts suggests the reverse, so it is not clear what you are trying to do. Finally, what type of database are you trying to log to, and why not use a simple file or even a SQLite DB, on the local system (the one on machine running mysql proxy), and most importantly, what you have you tried? — Oliver, Jul 07 '14 at 13:37
@Schollii I need a database at client system (192.168.YY.YY) to log on the queries.I prefer database more than a file because its easy to retrieve data as query from the database and also several users access the same database at a same time so in this case file doesn't work good. — robin, Jul 08 '14 at 04:06

Oliver · Answer 1 · 2014-07-10T12:13:38.540

1

I don't know how to open a db connection from a mysql proxy script but you could quite easily create a separate Lua script that uses luasocket to listen on a udp port for log mesages that it writes to a file, yor proxy script writes to that port, then there is no risk of simultaneous queries causing race conditions for log file write. The same Lua script could listen on a different port for a command to get the log file. Or make the file accessible via http. You could even add log messages via http post messages then don't even need separate Lua script for log file management. I don't see a need for a db.

To write to a file on client machine your script would use Lua's io.open function and file:write method. Read up on those. As stated near the beginning of my answer and as I think you hinted in one of your comments, you may have to coordinate writing to the log if multiple queries occur simultaneously, but you should first try the simple approach, it may be fine.

edited Jul 10 '14 at 12:13

answered Jul 08 '14 at 04:33

Oliver

27,510
9
72
103

i am in need of a db only,writing into files doesnt help much.Okay if we log on the queries into the file is it possible to keep the file at the client system (192.168.YY.YY) other than keeping in backend server?? – robin Jul 08 '14 at 04:46
@robin Yes storing the file on the client system is in fact the easiest. – Oliver Jul 08 '14 at 11:00
how can i store the file on the client system?Can u pls explain it a little bit? – robin Jul 10 '14 at 03:47
1

Updated to answer about file. – Oliver Jul 10 '14 at 12:14

Storing the logged queries into mysql client

1 Answers1