MySQL data version control

Question

Is there a way to tune MySQL every time a row changes, then a row is created with another table / database with data initially? (time stamped)

If so, how do I do this?

eg.

UPDATE `live_db`.`people` SET `live_db`.`people`.`name` = 'bob' WHERE `id` = 1; 

Makes this happen before the update:

 INSERT INTO `changes_db`.`people` SELECT * FROM `live_db`.`people` WHERE `live_db`.`people`.`id` = 1; 

And if you did it again, this will lead to something like this:

 `live_db`.`people` +----+-------+---------------------+ | id | name | created | +----+-------+---------------------+ | 1 | jones | 10:32:20 12/06/2010 | +----+-------+---------------------+ `changes_db`.`people` +----+-------+---------------------+ | id | name | updated | +----+-------+---------------------+ | 1 | billy | 12:11:25 13/06/2010 | | 1 | bob | 03:01:54 14/06/2010 | +----+-------+---------------------+ 

The live database must have the created timestamp in the rows, and the change database should have a timestamp when the live DB row was updated. The change database will also not have primary keys and foreign key constraints.

I use InnoDB and MySQL 5.1.49, but I can upgrade if necessary.

Answer 1

This is how i did it

 DELIMITER | # Create the log table CREATE TABLE IF NOT EXISTS `DB_LOG`.`TABLE` LIKE `DB`.`TABLE`| # Remove any auto increment ALTER TABLE `DB_LOG`.`TABLE` CHANGE `DB_LOG`.`TABLE`.`PK` `DB_LOG`.`TABLE`.`PK` INT UNSIGNED NOT NULL| # Drop the primary keys ALTER TABLE `DB_LOG`.`TABLE` DROP PRIMARY KEY| #Create the trigger DROP TRIGGER IF EXISTS `DB`.`update_TABLE`| CREATE TRIGGER `DB`.`update_TABLE` BEFORE UPDATE ON `DB`.`TABLE` FOR EACH ROW BEGIN INSERT INTO `DB_LOG`.`TABLE` SELECT `DB`.`TABLE`.* FROM `DB`.`TABLE` WHERE `DB`.`TABLE`.`PK` = NEW.`PK`; END| DELIMITER ; 

Answer 2

Use trigger

MySQL triggers support began with MySQL version 5.0.2.

Answer 3

You can create a trigger :

 DELIMITER \\ CREATE TRIGGER logtrigger BEFORE UPDATE ON live_db.people FOR EACH ROW BEGIN INSERT INTO changes_db.people(id,name,updated) VALUES(OLD.id,OLD.name,now()); END; \\ 

Answer 4

Sorry to comment on an old post, but I was looking for this problem! I think I would share this information.

This perfectly describes the solution:

http://www.hirmet.com/mysql-versioning-records-of-tables-using-triggers