Mysqlaudit

MySQL Administration tool build in ruby for audit all tables with triggers, this is a change log for SQL transactions executed sentences as INSERT, UPDATE, and DELETE.

Useful for:

Log all changes on table.
Rollback changes.
Detecting unnecessary transactions.

Installation

Install this tool executing the following command:

$ gem install mysqlaudit

Usage

This is a simple tutorial to understand this tool, please, follow next instructions:

Install test enviroment

Create test database and table:

CREATE DATABASE audit CHARACTER SET utf8;

USE audit;

CREATE TABLE foo (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  email char(255) NOT NULL,
  name VARCHAR(45) NOT NULL,
  birthday TIMESTAMP NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY id_UNIQUE (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Install audit table and triggers on foo table:

$ mysqlaudit install --host 127.0.0.1 --user root --pass admin --schema audit --table foo

The following query catch all changes of data with trigger audit:

INSERT INTO foo (email, name, birthday) VALUES ('[email protected]','Fulano','1980-04-01');
INSERT INTO foo (email, name, birthday) VALUES ('[email protected]','Mengano','1979-03-28');
INSERT INTO foo (email, name, birthday) VALUES ('[email protected]','Jaimito','1980-06-15');
UPDATE foo SET name = 'Zutano' WHERE id = 3;
DELETE FROM foo WHERE id = 2;

To apply rollback with specific transaction, this generate SQL output:

Get all changes:

You change data, could reverse with simple statements:

$ mysqlaudit rollback --host 127.0.0.1 \
                      --user root \
                      --pass admin \
                      --schema audit \
                      --table foo \

Output of last executed command:

/* 2013-07-11 07:25:25 */ INSERT INTO foo (id, email, name, birthday) VALUES (2, '[email protected]', 'Mengano', '1979-03-28 00:00:00');
/* 2013-07-11 07:26:26 */ UPDATE users SET name = 'Jaimito' WHERE id = 3;
/* 2013-07-11 07:27:27 */ DELETE FROM foo WHERE id = 1;
/* 2013-07-11 07:27:27 */ DELETE FROM foo WHERE id = 2;
/* 2013-07-11 07:27:27 */ DELETE FROM foo WHERE id = 3;

Get deleted data:

You delete data, could reverse with simple insert statements:

$ mysqlaudit rollback --host 127.0.0.1 \
                      --user root \
                      --pass admin \
                      --schema audit \
                      --table foo \
                      --statement delete

Output of last executed command:

/* 2013-07-11 07:25:25 */ INSERT INTO foo (id, email, name, birthday) VALUES (2, '[email protected]', 'Mengano', '1979-03-28 00:00:00');

Get updated data:

You updated data, could reverse with simple update statements to change new for old value:

$ mysqlaudit rollback --host 127.0.0.1 \
                      --user root \
                      --pass admin \
                      --schema audit \
                      --table foo \
                      --statement update

Output of last executed command:

/* 2013-07-11 07:26:26 */ UPDATE users SET name = 'Jaimito' WHERE id = 3;

Get inserted data:

You inserted data, could reverse with simple delete statements:

$ mysqlaudit rollback --host 127.0.0.1 \
                      --user root \
                      --pass admin \
                      --schema audit \
                      --table foo \
                      --statement insert

Output of last executed command:

/* 2013-07-11 07:27:27 */ DELETE FROM foo WHERE id = 1;
/* 2013-07-11 07:27:27 */ DELETE FROM foo WHERE id = 2;
/* 2013-07-11 07:27:27 */ DELETE FROM foo WHERE id = 3;

Uninstall test enviroment:

Uninstall all triggers and drop audit table:

$ mysqlaudit uninstall --host 127.0.0.1 \
                       --user root \
                       --pass admin \
                       --schema audit \
                       --table foo \
                       --drop-audit-table

Drop testing table and database:

DROP TABLE foo;
DROP DATABASE audit;

Config file example:

development:

test:

production:
  host: localhost
  user: root
  password: admin
  database: audit

Execute command with config file and enviroment:

$ ENVIRONMENT=production mysqlaudit install -c config.yml --table foo

For more information:

$ mysqlaudit --help

Warning

Do not use this tool in production before testing it.
Please, use when do you need.
The author is NOT responsible for misuse use of this tool.

Contributing

Fork it
Create your feature branch (git checkout -b my-new-feature)
Commit your changes (git commit -am 'Add some feature')
Push to the branch (git push origin my-new-feature)
Create new Pull Request