title: MySQL data version control
body: <p>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)</p>
  
  <p>If so how would I go about doing it?</p>
  
  <p>E.g.</p>
  
  <pre><code>UPDATE `live_db`.`people`
  SET    `live_db`.`people`.`name` = 'bob'
  WHERE  `id` = 1;  
  </code></pre>
  
  <p>Causes this to happen before the update: </p>
  
  <pre><code>INSERT INTO `changes_db`.`people`
  SELECT *
  FROM   `live_db`.`people`
  WHERE  `live_db`.`people`.`id` = 1;  
  </code></pre>
  
  <p>And if you did it again it would result in something like this:</p>
  
  <pre><code>`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 |
  +----+-------+---------------------+
  </code></pre>
  
  <p>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.</p>
  
  <p>I'm using InnoDB and MySQL 5.1.49 but can upgrade if required.</p>