MySQL Trigger only for a certain mysql user

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 开发者_C百科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

You could branch your trigger function on USER().

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

I would look at these options:

A) Write an audit plugin, which filters events based on the user name.

For simplicity, the user name can be hard coded in the plugin itself, or for elegance, it can be configured by a plugin variable, in case this problem happens again.

See http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html

B) Investigate the --init-connect server option.

For example, call a stored procedure, check the value of user() / current_user(), and write a trace to a log (insert into a table) if a connection from the user was seen.

See http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_init_connect

This is probably the closest thing to a connect trigger.

C) Use the performance schema instrumentation.

This assumes 5.6.

Use table performance_schema.setup_instrument to only enable the statement instrumentation. Use table performance_schema.setup_actors to only instrument sessions for this user.

Then, after the system has been running for a while, look at activity for this user in the following tables:

• table performance_schema.users will tell if there was some activity at all
• table performance_schema.events_statements_history_long will show the last queries executed
• table performance_schema.events_statements_summary_by_user will show aggregate statistics about each statement types (SELECT, INSERT, ...) executed by this user.

Assuming you have a user defined as 'old_app'@'%', a likely follow up question will be to find out where (which host(s)) this old application is still connecting from.

performance_schema.accounts will just show that: if traffic for this user is seen, it will show each username @ hostname source of traffic. There are statistics aggregated by account also, look for '%_by_account%' tables.

See http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html

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)

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.