List:Replication« Previous MessageNext Message »
From:Jonathan von Schroeder Date:May 12 2012 1:28pm
Subject:Re: maintain mysql session on bash script
View as plain text  
Hi,
my solution is to connect Mysql's input to a fifo, thus keeping the
connection and the Readlock alive:

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 "INSERT INTO readlock.test () VALUES ();" > ${FIFO}
> echo "SELECTA;" > ${FIFO} #to have output even if the readlock (for some
> reason) isn't in place
> for i in `seq 1 10`;
> do
>  if [ ! -s ${OUT} ]; then
>   echo " Output from mysql-server not yet available"
>   sleep 0.1
>   echo " slept 0.1 seconds"
>   if [ $i -gt 10 ]; then
>    break
>   fi
>  else
>   break
>  fi
> done
> if grep "ERROR 1223 (HY000) at line 2: Can't execute the query because you
> have a conflicting read lock" ${OUT}; 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


In the script i make use of the trap command to ensure that the connection
keeping the Readlock alive is terminated even if the script is suddenly
interrupted.

On 12 May 2012 13:42, ron ramos <nhadie@stripped> wrote:

> Hi All,
>
> I am currently using amazon ec2 for my database and i would like to
> create a script that captures snapshot of my volumes.
>
> I just want t know how i can flush tables with read lock and not lose
> the session while the script is running, e.g. i do these:
>
> mysql -pblahblah -A blahblah -e 'flush tables with read lock;  show
> master status;'"
>
> then create snapshot then
>
> mysql -pblahblah -A blahblah -e ''unlock tables;"
>
> anyone who has successfully done this?
>
> thank you in advance.
>
> Regards,
> Ron
>
> --
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:    http://lists.mysql.com/replication
>
>

Thread
maintain mysql session on bash scriptron ramos12 May
  • Re: maintain mysql session on bash scriptJonathan von Schroeder12 May
    • Re: maintain mysql session on bash scriptJohan De Meersman14 May
      • Re: maintain mysql session on bash scriptron ramos15 May