MySql - Progress of an Alter Table

Sometimes you have to run alter table statements on some very large tables and have no idea when, if ever, the process will finish.

If the innodb_file_per_table option has been set in the MySql configuration file, it is possible to follow the progress by comparing the size of the original file with the temporary file created by MySql.

For example, if I want to add the column col1 to the table bigtable1 in the database data1 I will run:

In the database directory MYSQL_DATA_DIR/data1 a file with a name like #sql-977_820.ibd will appear. By comparing the size of this file with the original file ( bigtable1.ibd ) I will get the progress percentage.

To do it in a one-liner:

which returns the progress percentage of the command.

Where

shows the size of the temporary file,

shows the size of the original file, and bc performs the division and prints the progress percentage.