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