MySQL Drop INDEX and REPLICATION

Question

In a MySQL MASTER MASTER scenario using InnoDB

When dropping an index on one instance will the same table on the other instance be available?

What is the sequence of activities? I assume the following sequence:

1. DROP INDEX on 1st instance
2. Added to the binary log
3. DROP INDEX on 2nd instance

Can anyone confirm?

Answer 1

I believe the following will happen:

• Your DROP INDEX (which really runs an ALTER TABLE ... DROP INDEX) runs on the master
• If the ALTER completes successfully the statement will then be added to the binlog and will be run on the slave

This means that the ALTER TABLE on the other machine won't start until the ALTER TABLE has successfully completed on the first (master) machine.

While the ALTER TABLE is running on either machine the table will be readable for a while and then neither readable/writeable as MySQL first makes a copy of the table internally then applies the changes.

From http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates. The temporary table is created in the database directory of the new table. This can be different from the database directory of the original table if ALTER TABLE is renaming the table to a different database.