In a previous post, Another Proxy Tutorial, I outlined how you could find and display the originating IP address of a particular query from within MySQL Proxy.
This was a nice illustration of how to accomplish the above, however, in practice, there were a couple of limitations. For one, the queries were “tagged” with the originating IP address, so the query cache would only work for the same query coming from the same IP address. So if you’re using the query cache, then this is not an optimal solution. Secondly, you could only see the IP address for a current query, and not for an idle connection. Thus when issuing a SHOW PROCESSLIST command, one would only see the IP address only for actual queries that are being executed, and not for any of the other idle connections.
This was bothersome to me, so I worked up a better and more efficient solution that addresses both of these concerns.
In this new solution, I implement a new command called SHOW PROXY PROCESSLIST. This leaves SHOW PROCESSLIST output unchanged, and instead, if you want to see the originating IP address of the every connection listed in the SHOW PROCESSLIST output, then simply issue a SHOW PROXY PROCESSLIST command.
Here is an example of the output when using 3 different connections from differing IP addresses:
mysql> SHOW PROXY PROCESSLIST; SHOW PROCESSLIST; +------+---------------------+-------------------+ | Id | IP Address | Time | +------+---------------------+-------------------+ | 2 | 127.0.0.1:49528 | 04/15/09 13:22:14 | | 3 | 192.168.1.100:1322 | 04/15/09 13:22:23 | | 4 | 192.168.1.103:59374 | 04/15/09 13:22:44 | +------+---------------------+-------------------+ 3 rows in set (0.00 sec) +----+------+-----------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+-------+------------------+ | 1 | root | localhost:49526 | NULL | Sleep | 13 | | NULL | | 2 | root | localhost:49529 | NULL | Query | 0 | NULL | SHOW PROCESSLIST | | 3 | root | localhost:49533 | NULL | Sleep | 50 | | NULL | | 4 | root | localhost:49547 | NULL | Sleep | 18 | | NULL | +----+------+-----------------+------+---------+------+-------+------------------+ 4 rows in set (0.00 sec)
Here, you can see I simply called both commands in a single call.
The first output is the SHOW PROXY PROCESSLIST output. In it you can see the “Id” which corresponds to the “Id” in the SHOW PROCESSLIST output. You can also see the corresponding originating IP address along with the client source port, as well as the time stamp of when the connection was created.
To implement this, I simply capture and store the client address information in an internal table. I’m able to capture this from within the read_auth_result() function.
function read_auth_result( auth )
local state = auth.packet:byte()
if state == proxy.MYSQLD_PACKET_OK then
initialize_process_table()
table.insert( proxy.global.process[proxy.connection.server["thread_id"]],
{ ip = proxy.connection.client["address"], ts = os.time() } )
end
end
So when within the function read_auth_result(), if state == proxy.MYSQLD_PACKET_OK, then we know the connection was auth’ed, and hence I log this information at this point.
Note that I’m storing the value of proxy.connection.client["address"] which is the original IP address along with the client source port (e.g., 192.168.1.100:1322). The nice thing about this is you can track down the port information too on the original machine.
(Also note that in MySQL Proxy 0.7.0, there are even more available ports (variables), so you could add more to capture, say the client destination port, or the server source port. For more on this, pelase see Jan Kneschke’s post about this.)
On the remote machine (192.168.1.100), if I run the following command, I can track this connection via netstat:
netstat -an | grep 1322 tcp 0 0 192.168.1.100:1322 192.168.1.101:4050 ESTABLISHED
There is also another internal function to proxy that I use, which is disconnect_client(). This is called when a client disconnects, so if this function is entered for a particular connection, I simply delete that connection entry from the internal table.
function disconnect_client() local connection_id = proxy.connection.server.thread_id if connection_id then -- client has disconnected, set this to nil proxy.global.process[connection_id] = nil end end
The rest of the code is basically to display this information in a nice format.
Here is the full code:
function read_query( packet )
if string.byte(packet) == proxy.COM_QUERY then
if string.sub(packet, 2):lower() == 'select help' then
return show_process_help()
end
if string.sub(packet, 2):lower() == 'show proxy processlist' then
return show_process_table()
end
--debug output
--dump_process_table()
proxy.queries:append(1, packet )
return proxy.PROXY_SEND_QUERY
end
end
function make_dataset (header, dataset)
proxy.response.type = proxy.MYSQLD_PACKET_OK
proxy.response.resultset = {
fields = {},
rows = {}
}
for i,v in pairs (header) do
table.insert(proxy.response.resultset.fields, {type = proxy.MYSQL_TYPE_STRING, name = v})
end
for i,v in pairs (dataset) do
table.insert(proxy.response.resultset.rows, v )
end
return proxy.PROXY_SEND_RESULT
end
function initialize_process_table()
if proxy.global.process == nil then
proxy.global.process= {}
end
if proxy.global.process [proxy.connection.server["thread_id"] ]== nil then
proxy.global.process[proxy.connection.server["thread_id"] ]= {}
end
end
function show_process_table()
initialize_process_table()
local dataset = {}
local header = { 'Id', 'IP Address', 'Time' }
local rows = {}
for t_i, t_v in pairs (proxy.global.process) do
for s_i, s_v in pairs ( t_v ) do
table.insert(rows, { t_i, s_v.ip, os.date('%c',s_v.ts) })
end
end
return make_dataset(header,rows)
end
function show_process_help()
local dataset = {}
local header = { 'command', 'description' }
local rows = {
{'SELECT HELP', 'This command.'},
{'SHOW PROXY PROCESSLIST', 'Show all connections and their true IP Address.'},
}
return make_dataset(header,rows)
end
function dump_process_table()
initialize_process_table()
print('current contents of process table')
for t_i, t_v in pairs (proxy.global.process) do
print ('session id: ', t_i)
for s_i, s_v in pairs ( t_v ) do
print ( '\t', s_i, s_v.ip, s_v.ts )
end
end
print ('---END PROCESS TABLE---')
end
function read_auth_result( auth )
local state = auth.packet:byte()
if state == proxy.MYSQLD_PACKET_OK then
initialize_process_table()
table.insert( proxy.global.process[proxy.connection.server["thread_id"]],
{ ip = proxy.connection.client["address"], ts = os.time() } )
end
end
function disconnect_client()
local connection_id = proxy.connection.server.thread_id
if connection_id then
-- client has disconnected, set this to nil
proxy.global.process[connection_id] = nil
end
end
If you saved this to a file named show-ips.lua, then you would start MySQL Proxy as follows:
mysql-proxy --proxy-lua-script=share/mysql-proxy/show-ips.lua
And then you would connect to the Proxy as follows:
mysql -u root -pmysql -P4040
After that, you’ll be able to issue the commands implemented here such as SHOW PROXY PROCESSLIST and SELECT HELP:
mysql> SELECT HELP; +------------------------+-------------------------------------------------+ | command | description | +------------------------+-------------------------------------------------+ | SELECT HELP | This command. | | SHOW PROXY PROCESSLIST | Show all connections and their true IP Address. | +------------------------+-------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SHOW PROXY PROCESSLIST; +------+---------------------+-------------------+ | Id | IP Address | Time | +------+---------------------+-------------------+ | 5 | 127.0.0.1:51128 | 04/15/09 14:09:42 | | 6 | 192.168.1.100:2341 | 04/15/09 14:10:22 | | 8 | 192.168.1.103:52722 | 04/15/09 14:18:49 | +------+---------------------+-------------------+ 3 rows in set (0.00 sec)
Lastly, note that you can easily integrate these functions to the quan.lua script that the Query Analyzer Proxy uses (for MySQL Enterprise Monitor).