Mysql – Table replication with extra TIMESTAMP column on slave

MySQLreplicationtimestamp

I have a table on master and same table on slave with an extra column like

InsertTime timestamp DEFAULT CURRENT_TIMESTAMP

I want to keep record of time when there is update on slave.

Replication is working fine, but added column InsertTime on slave DB is storing 000.000.00 00-00-00 value. It is not storing current system timestamp. When I issue separate insert statement on slave side, it is storing system time but not with replication. Can anyone suggest me how to achieve system timestamp on slave side?

Other details as follows:

  • MySQL DB: MariaDB 5.5.23
  • Replication Type: Row based
  • Platform: Linux

Best Answer

The behavior you are seeing is normal for row-based replication. Extra columns are valid at the right-hand "side" of the table, but row-based replication operates at a level, on the slave, where automatic timestamps aren't updated.

Statement-based replication has been suggested, but it seems likely to become even more fragile than it already is.

MariaDB 10.1 introduces slave triggers for row-based replication, which could accomplish the purpose, assuming the master does not already have triggers on the table.

Normally, when an event is replicated in row format, triggers do not fire on the slave, because the changes made on the master by the triggers will have replicated with the row events.

MariaDB 10.1 can be configured such that row events will fire on the slave, under one condition: if the event encounters a trigger on the slave, but no trigger fired on the master, because there is no trigger defined on the master, then the slave trigger will fire. Since this requires the master to include new information in the binlog (I can't think of any facet of binary loggong that captures this now) then the master would need to be running 10.1 also.

This capability seems like it could accomplish what you want.

Related Question