How to Keep a MySQL Connection Open in Bash

Question

I have a bash script that calls MySQL several times. Instead of having to reconnect to MySQL, is there a way to keep the connection open? Ideally, the connection would close if the script exits early. I'm thinking named pipes would work but they would stay open.

Here's a quick pseudo-example of what I hope to find:

openMySQL
executeMySQL "SELECT 1"
exit 1
executeMySQL "SELECT 2"

I'm looking for the openMySQL and executeMySQL functions where the MySQL connection will actually close during the exit 1.

Answer 1

I have part of what I was looking for.

Keep the mysql connection open using fd=3 for writing:

exec 3> >(mysql)
echo "SELECT 1;" >&3
echo "SELECT 2;" >&3
exec 3>&-

Keep the mysql connection open using fd=3 for reading:

exec 3< <(echo "SELECT 1;SELECT 2;"|mysql|sed '1d')
while read <&3
do
  echo $REPLY
done

Note: sed '1d' removes the header.

Is there any way to merge these so you can write to one fd and read from another?

Answer 2

To the best of my understanding your question: coproc's available in zsh/ksh and also bash v4+ might be similar to what you have in mind, e.g.

bash4-4.1$ coproc MYSQL mysql -B -uroot 
[1] 10603
bash4-4.1$ jobs
[1]+  Running                 coproc COPROC MYSQL mysql -B -uroot &
bash4-4.1$ echo 'show databases;' | MYSQL
Database
information_schema
...

The command is kept running in the background, its stdin/stdout can accessed, it will finish (as a result its standard input closing/EOFing) as soon as the current shell exists...