Using the triggers I could make the establishment of a historic recordings in MYSQL tables where i detect all changes in my database. My problem is that I want to know which user made ​​the change in my database?

  • 1
    are you asking how to detect changes like CREATE TABLE or ALTER TABLE? Or are you asking about INSERT, UPDATE? Commented Aug 25, 2015 at 14:09
  • @AndrewWolfe I want to know which user made change in my database tables
    – Sarah
    Commented Aug 25, 2015 at 14:22
  • 1
    Do you mean "change" like update set description = 'value' where ID = 647554 Commented Aug 25, 2015 at 14:58
  • @AndrewWolfe when a user update,delete, or insert in my database i want to know which user?
    – Sarah
    Commented Aug 25, 2015 at 15:05
  • This is often done using triggers, here's a good starting point to start reading stackoverflow.com/questions/12563706/… . Also interesting codereview.stackexchange.com/questions/24973/…
    – fvu
    Commented Aug 25, 2015 at 15:18

2 Answers 2


Depends on what version you are.

You can install the audit_plugin:

INSTALL PLUGIN server_audit SONAME 'server_audit';

You can choose two outputs, file or syslog. The output format will be:

20140901 15:19:44,localhost.localdomain,root,localhost,4,133,WRITE,employees,salaries,
20140901 15:19:44,localhost.localdomain,root,localhost,4,133,QUERY,employees,'DELETE FROM salaries LIMIT 100',0


If you enable the audit_plugin (it's right with all logging processes like SLOW LOW, GENERAL LOG...) be careful to not saturate your disks in term of I/O. Depends of your workload, prefer write these types of logs on dedicated disks to minimize the impact on your MySQL instances. You can also throw them to syslog but you should have the architecture to handle them (ELK for instance).


  • Thank you for your answer. Version of Mysql 5.6.17,i will test the plugin.
    – Sarah
    Commented Aug 27, 2015 at 9:10
  • May not show the specific user if all users connect as the same database user. ? Commented Aug 27, 2015 at 13:56
  • You have the source IP/host in each entry. But yes if the MySQL are behind a load balancer, depends of its conf but yes they can all got the same IP/host. Commented Aug 28, 2015 at 8:26

There are several options, but here are two based on two options for how your end-users connect.

First, if your users connect as themselves. For example, if you have a user "annamaria" then in your trigger you can identify the user with the CURRENT_USER() function. Record CURRENT_USER() into your auditing table with each modification and you can track which user did what.

Second, if all users employ the same database connection (very common). In this case you want to get session information about your end user. In this case you would need to query various connection information from mysql.

If you are using MySQL 5.1.7 or later, and if you have your users connecting directly from their PCs to the database, and their PCs have meaningful names like AWOLFE_LAPTOP, then you can put that value into your audit table:

SELECT host FROM information_schema.processlist WHERE id = connection_id()

Apart from the logged-in user name (which your comment makes clear is unavailable) and the client machine ("host") name, I don't know of any reliable way to identify the user without changing your application.

You would need to revise the code that connects the user to the database, and set a session variable correspondingly, right after login. You would have to prompt the user for a user name, or perhaps a username and password. Then you'd use that to fill a session variable you could later use in your trigger.

SELECT @identified_user := usertab.userid from usertab where username = @username_from_input and password = @password_from_input

Security-conscious people please accept the following better-protected solution:

SELECT @identified_user = hashedcredentials(@username_from_input, @password_from_input);

Your primary problem arises from having all users connect as the same database user. ("One Big Database User" model.) Please, just change this!

Good luck!

Andrew Wolfe

  • Thank you for your answer. All users connect with the same identifiers to the database so this is the second solution. Can you please explain to me how can I use sessions ?
    – Sarah
    Commented Aug 27, 2015 at 10:05
  • I've added more information, but please give different database users different database credentials for connecting! Commented Aug 27, 2015 at 13:57
  • I don't have the permissions to change the database.
    – Sarah
    Commented Sep 1, 2015 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.