mysql-snapshot-lock.sh

#!/bin/bash -e

FIFO="/tmp/mysql-pipe.$$"
OUT="/tmp/mysql-out.$$"
mkfifo ${FIFO} || exit $?

echo "Connecting to mysql-server"
(mysql <${FIFO} | echo -n) &> ${OUT} &
MYSQL=$!

trap "(rm -f ${FIFO} ${OUT};kill ${MYSQL} 2>&-) 1>&2" 0

echo "Flushing and locking tables"
echo "FLUSH TABLES WITH READ LOCK;" > ${FIFO}

echo "Checking Table-Lock"
echo "CREATE TABLE IF NOT EXISTS mysql.readlocktest (a INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (a));" > ${FIFO}
echo "INSERT INTO mysql.readlocktest () VALUES ();" > ${FIFO}
echo "SELECTA;" > ${FIFO}
for i in {1..10}; do
 if [ ! -s ${OUT} ]; then
  echo " Output from mysql-server not yet available"
  sleep 1
  echo " slept 1 seconds"
  if [ $i -gt 10 ]; then
   break
  fi
 else
  break
 fi
done
if grep "ERROR 1223 (HY000) at line .*: Can't execute the query because you have a conflicting read lock" ${OUT} > /dev/null 2>&1; then
 echo " Lock in place"
else
 echo " Locking failed"
 exit 1
fi

# Now the read lock is in place an we can do our snapshot / backup /
whatever

echo "Unlocking Tables"
rm -f ${FIFO} ${OUT};kill ${MYSQL} 2>&-
trap "" 0

exit $?
# EOF