MySQL data version control

Is there any way to setup MySQL to every time a row is changed, then a row to another table/database is created with what the data was originally? (with time stamping)

If so how would I go about doing it?

E.g.

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

Causes this to 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 it would result开发者_如何学JAVA in 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 DB needs to have a created time stamp on the rows, and the changes DB needs to have a time stamp of when the live DB row was updated. The changes DB will also have no primary keys and foreign key constraints.

I'm using InnoDB and MySQL 5.1.49 but can upgrade if required.

---

Use a Trigger

MySQL support for triggers started with MySQL version 5.0.2.

---

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;
\\

---

This is how I ended up doing 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 ;

---

Sorry to comment on an old post, but I was looking to solve this exact problem! Thought I would share this information.

This outlines a solution perfectly:

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