MySQL Replication: Slave with Different Column From Master

Question

I have a Master/Slave replication.

I had to change the name of one of my columns in one of my tables from temp_col to sent_at, this column is not being used yet.

I've issued an alter table on an unused slave in order to change the column name with hope that once the change will finish. I would be able to use this slave (with the desired column name) as a new master.

The thing that I didn't take into consideration is that once the alter table is finished that replication will fail since the query that inserts data into the altered table expects the old column name (temp_col) even if its just inserting null values into it.

Since the alter table took some time, there are a lot of these kind of queries.

Questions

• Is there something i can do to repair the replication?
• How can I tell the slave to "ignore" the old column name?

thanks

Ran

• atxdba: Are these tables MyISAM or InnoDB? How big is the table? What kind of bandwidth do you have between the master and slave (i.e. is it feesible to transfer many gigabytes between them in a short period of time?) These factors can help determine which route to take.
• Ran: innoDB, 130GB size of DB, table has ~80M records, decent connection between the servers

Answer 1

WOW, I remember your last question !!!

If you kept the s_relations_old on the slave, just switch back and start replication.

STOP SLAVE;
ALTER TABLE s_relations RENAME s_relations_bad;
ALTER TABLE s_relations_old RENAME s_relations;
START SLAVE;

You should perform the conversion of the temp_col on the master so that you let that ALTER TABLE replicate to the slave.

You will have to bite the bullet on this one and have some downtime.

• Ran: thanks Rolando! and also thank you for your elaborated previous respond :)