Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 0 down vote accepted

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` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `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
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.07 sec)

mysql>

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 !!!

share|improve this answer

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

http://www.mysqlperformanceblog.com/2014/05/20/database-auditing-alternatives-mysql/

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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