How to Add More Columns on Slave Than Master in MySQL

If you need to include additional columns on the slave that the master doesn’t have—for example, to record timestamps or add routing or other localized data—you can add columns to a table on a slave without having to add the columns on the master. MySQL replication supports this scenario by ignoring the additional columns. To actually insert data into the extra columns on the slave, define them to accept default values (an easy way to insert timestamps, for instance) or use a trigger defined on the slave to provide the values.

For statement-based logging, you can create the columns as follows:

• Create the table on the master
  

  CREATE TABLE t1 (a INT, b INT);

  
  
  
• Alter the table on the slave:
  

  ALTER TABLE t1 ADD ts TIMESTAMP;

  
  
  
• Sample insert on the master:
  

  INSERT INTO t1(a,b) VALUES (10,20);

For row-based logging, you must make the new columns appear at the end of the row and have default values. Row-based replication will fail if you add columns in the middle or at the front of the row. As long as you add columns at the end of the table and give them default values, no special consideration needs to be taken regarding which statements to use when replicating, since row-based replication will extract the columns directly from the row being updated, inserted, or deleted.