MySQL Trigger only for a certain mysql user

Question

I'm trying to find out if a specific MySQL User is still in use in our system (and what queries it is executing).

So I thought of writing a trigger that would kick in anytime user X executes a query, and it would log the query in a log table.

How can I do that? I know how to write a query for a specific table, but not for a specific user (any table).

Thanks

Answer 1

The easiest would be to have the trigger always fire, but only logs if the user is X.

Answer 2

You could branch your trigger function on USER().

Answer 3

Yeah, fire away, but use whatever system you have to see what user it is (cookies, session) to log only if the specific user (userID, class) matches your credentials.

Answer 4

There are also other ways you could approach this problem, for example using MySQL proxy

In the proxy you could do interesting things - from logging to transforming queries, pattern matching (check this link also for details on how to test/develop the scripts)

-- set the username
local log_user = 'username'

function read_query( packet )
    if proxy.connection.client.username == log_user and string.byte(packet) == proxy.COM_QUERY then
        local log_file = '/var/log/mysql-proxy/mysql-' .. log_user .. '.log'
        local fh = io.open(log_file, "a+")
        local query = string.sub(packet, 2)
        fh:write( string.format("%s %6d -- %s \n",
            os.date('%Y-%m-%d %H:%M:%S'),
            proxy.connection.server["thread_id"],
            query))
        fh:flush()
    end
end

The above has been tested and it does what it is supposed to (although this is a simple variant, does not log success or failure and only logs proxy.COM_QUERY, see the list of all constants to see what is skipped and adjust for your needs)