Amazing bash scriptaculousness: Find out how long a MySQL schema change will take

#!/bin/bash
(while(true); do  \
(mysql -e 'show innodb status \G' | grep undo\ log\ entries ; sleep 1 ;  \
mysql -e 'show innodb status \G' | grep undo\ log\ entries ) |    \
egrep '[0-9][0-9][0-9][0-9]‘ |awk ‘{print $10;}’ ; done ) | \
perl -e ‘$t = ROWS_IN_TABLE; while(1) { \
$n ++; $nn; $a = <>; $b = <>; $nn += ($b-$a); \
printf “Rate: %d, avg: %d, %0.3f%% complete, done in %d sec\n”, \
$b-$a, $nn/$n, ($b/$t)*100, ($t-$b)/($nn/$n); }’;

God, altering tables is the biggest craptastic part of MySQL. Luckily this takes some of the mystery out of an otherwise harrowing experience.

+10,000 usefulness points if you use MySQL with any scale

Hat tip @michaelmontano

views