I'm trying to create a log of changes in a given table in a database. To do this, I'm going to get the info about all the fields in the original table and create a new one using a function.

In addition to the fields in the original table, the new table should contain the time of the change, the type of the change (insert, delete, update) and an identification of the user that made the change.

I've figured out where I can get the information about the columns of the original table in MySQL (table INFORMATION_SCHEMA.Columns), but I don't know where I can find the information related to the user.

Can anyone please point me in the right direction?

2 Answers 2


If you want to avoid playing with plugins, I have just thing for you.

You should enable the general log as a MySQL table because it records the following:

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `user_host` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
1 row in set (0.07 sec)


Column #2 user_host has the user and host of the command issued.

The rest of the generale log table gives you

  • what command was executed
  • when it was executed
  • process ID of the processlist at the time the command was executed

You may find the CSV Storage Engine annoying for a live log. You can convert it to MyISAM. Then, you can index the event_time. As an option, you can create a prefix index the user_host as well.

Rather than reinvent the wheel, I will not put the implementation details in this post because I have already written posts on how to go about doing this:

Give it a Try !!!


The right direction for this is to use an audit logging plugin. There are some choices out there. Please check out the following post for more information


My personal preference is the Percona Audit Plugin but you should be using Percona Server (MySQL Alternative) for this.

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.