I was challenged with an emergency yesterday:
The customer's site was down, my cell phone was buzzing every 15 seconds, Trillian was chiming new IM's every 5 seconds from the frantic customer, my Inbox was full of emails from this poor guy explaining that he had been experiencing random problems throughout the day. After digging around a little, I found that MySQL was dying, but he couldn't restart it.
The customer was running a mysqldump and had some locked tables. I could see this clearly by running "mysqladmin processlist" . He didn't want to kill Apache because every hit counts. He didn't want to restart mysql because he was actively running a mysqldump that he had been running for a few hours and didn't want to lose the dump. The quick solution was to kill the threads that were causing locks. This one-liner had him going in less than 10 seconds:
mysqladmin processlist | \
grep Locked | \
awk '{ ORS = ","; print $2 }' | \
sed "s/,$//" | \
xargs mysqladmin kill
One thing that people don't think of is that mysqldump does a "--opt" by default in more recent versions of MySQL. "--opt" automatically does a "--lock-tables". Locking your tables in the middle of the day (or whatever time your peek load is), is usually never desirable.