MySQL Import Progress - Revision history

Smsldoo at 17:43, 17 February 2012

2012-02-17T17:43:26Z

10.30.6.195: /* Step 2 */

2011-05-04T19:51:11Z

‎Step 2

10.30.6.195: /* Step 2 */

2011-05-04T19:42:43Z

‎Step 2

10.30.6.195: Created page with "==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. Expe..."

2011-05-04T19:32:03Z

Created page with "==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. Expe..."

New page

==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.

<code>[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
</code>

==Step 2==
Run code and watch the bugs fly

<code>[bash,n]
#!/bin/bash
currentcommand=`mysqladmin processlist | grep INSERT`
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}'`
echo "$currenttable $currentrecord / $recordcount ($currentpercent)"
</code>

==What to change==

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

← Older revision		Revision as of 17:43, 17 February 2012
Line 1:		Line 1:
		+	[[Category:Misc]]
	==Overview==		==Overview==

@@ Line 23: / Line 23: @@
 <code>[bash,n]
 #!/bin/bash
-currentcommand=`mysqladmin processlist | grep INSERT | head -1`
+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`
+recordcount=`cat tablecounts.txt | grep "^$currenttable," | cut -d "," -f2`
 currentpercent=`echo $currentrecord $recordcount | awk '{print $1 / $2*100}'`
 echo "$currenttable $currentrecord / $recordcount ($currentpercent)"
 </code>