MySQL Import Progress

Overview

Needed a progress indicator for an excessively long MySQL import.

Created this down and dirty solution to give a very buggy inaccurate look at the progress.

Expect lots of errors as I have no error checking in here.

Its accurate at least once per table.

Step 1

Populate table counts

ID will need to be changed for what ever their autoinc field is. Will not work with non numeric values.

[bash,n] for i in $(mysql <DATABASE> --batch -e 'show tables;' | grep -v "Tables_in"); do lastrecord=`mysql <DATABASE> --batch -e 'select id from '$i' order by id desc limit 1'|grep -v id`; echo "$i,$lastrecord" >> tablecounts.txt; done

Step 2

Run code and watch the bugs fly

[bash,n]

1. !/bin/bash

currentcommand=`mysql --batch -e 'show processlist' | grep INSERT | head -1` currenttable=`echo $currentcommand | cut -d '\`' -f2` currentrecord=`echo $currentcommand | cut -d "(" -f2 | cut -d "," -f1`

recordcount=`cat tablecounts.txt | grep "^$currenttable," | cut -d "," -f2`

currentpercent=`echo $currentrecord $recordcount | awk '{print $1 / $2*100}'`

1. if [$currenttable -eq ""]
2. then
3. echo "no table being imported"
4. else

echo "$currenttable $currentrecord / $recordcount ($currentpercent)"

1. fi

What to change

Really need a better solution in the future. But this works for what we needed at the time.