mysql database schema database-schema alter

Checking progress of MySQL schema change

Is it possible to check the status of a schema change to a MySQL database? i.e. when will ALTER TABLE myTable DROP INDEX myKey; complete?

I have dropped a key using PHPMyAdmin for my 1.8 Gb database and PHPMyAdmin is locked up and accessing the command line shows the old schema for SHOW CREATE TABLE myTable.

Is it possible to confirm that the ALTER is still in progress? Is it possible to estimate when it will complete?

See also:

Create an index on a huge mysql production table without table locking how to make MySQL schema changes on a live server with a master/slave setup running that does not block

Solution

No, there's not much you can do to monitor progress of an ALTER TABLE statement, nor can we know how much time it has left to finish.

You can use a tool like pt-online-schema-change, which does output an estimate of progress and time remaining, but more importantly it does not block access to the table while it's working. This is probably why you're concerned with monitoring progress, because it is anxiety-causing to wait for an ALTER TABLE to release its lock.