MySQL from the command line - can I practically us

问题:

I'm doing a bash script that interacts with a MySQL datatabase using the mysql command line programme. I want to use table locks in my SQL. Can I do this?

mysql -e "LOCK TABLES mytable"
# do some bash stuff
mysql -u "UNLOCK TABLES"

The reason I ask, is because table locks are only kept for the session, so wouldn't the lock be released as soon as that mysql programme finishes?

回答1:

[EDIT]

nos had the basic idea -- only run "mysql" once, and the solution nos provided should work, but it left the FIFO on disk.

nos was also correct that I screwed up: a simple "echo X >FIFO" will close the FIFO; I remembered wrongly. And my (removed) comments w.r.t. timing don't apply, sorry.

That said, you don't need a FIFO, you could use an inter-process pipe. And looking through my old MySQL scripts, some worked akin to this, but you cannot let any commands write to stdout (without some "exec" tricks).

#!/bin/bash
(
  echo "LOCK TABLES mytable READ ;"
  echo "Doing something..." >&2
  echo "describe mytable;" 
  sleep 5
  echo "UNLOCK  tables;" 
) | mysql ${ARGUMENTS}

Another option might be to assign a file descriptor to the FIFO, then have it run in the background. This is very similar to what nos did, but the "exec" option wouldn't require a subshell to run the bash commands; hence would allow you to set "RC" in the "other stuff":

#!/bin/bash
# Use the PID ($$) in the FIFO and remove it on exit:
FIFO="/tmp/mysql-pipe.$$"
mkfifo ${FIFO} || exit $?
RC=0

# Tie FD3 to the FIFO (only for writing), then start MySQL in the u
# background with its input from the FIFO:
exec 3<>${FIFO}

mysql ${ARGUMENTS} <${FIFO} &
MYSQL=$!
trap "rm -f ${FIFO};kill -1 ${MYSQL} 2>&-" 0

# Now lock the table...
echo "LOCK TABLES mytable WRITE;" >&3

# ... do your other stuff here, set RC ...
echo "DESCRIBE mytable;" >&3
sleep 5
RC=3
# ...

echo "UNLOCK TABLES;" >&3
exec 3>&-

# You probably wish to sleep for a bit, or wait on ${MYSQL} before you exit
exit ${RC}

Note that there are a few control issues:

• This code has NO ERROR CHECKING for failure to lock (or any SQL commands within the "other stuff"). And that's definitely non-trivial.
• Since in the first example, the "other stuff" is within a subshell, you cannot easily set the return code of the script from that context.

回答2:

Here's one way, I'm sure there's an easier way though..

mkfifo /tmp/mysql-pipe
mysql mydb </tmp/mysql-pipe &
(
  echo "LOCK TABLES mytable READ ;" 1>&6 
  echo "Doing something "
  echo "UNLOCK  tables;" 1>&6
) 6> /tmp/mysql-pipe

回答3:

A very interesting approach I found out while looking into this issue for my own, is by using MySQL's SYSTEM command. I'm not still sure what exactly are the drawbacks, if any, but it will certainly work for a lot of cases:

Example:

mysql <<END_HEREDOC
LOCK TABLES mytable;
SYSTEM /path/to/script.sh
UNLOCK TABLES;
END_HEREDOC

It's worth noting that this only works on *nix, obviously, as does the SYSTEM command.

Credit goes to Daniel Kadosh: http://dev.mysql.com/doc/refman/5.5/en/lock-tables.html#c10447

回答4:

Another approach without the mkfifo commands:

cat <(echo "LOCK TABLES mytable;") <(sleep 3600) | mysql &
LOCK_PID=$!
# BASH STUFF
kill $LOCK_PID

I think Amr's answer is the simplest. However I wanted to share this because someone else may also need a slightly different answer.

The sleep 3600 pauses the input for 1 hour. You can find other commands to make it pause here: https://unix.stackexchange.com/questions/42901/how-to-do-nothing-forever-in-an-elegant-way

The lock tables SQL runs immediately, then it will wait for the sleep timer.