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

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...

Answer 2

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 3

I know this thread is old, but I was also looking for a comfortable bash mysql session implementation and didn't found something good enough for my needs, so I wrote my own one which I'd like to share with the world.

############### BASIC MYSQL SESSION IMPLEMENTATION FOR BASH (by Norman 

Geist 2015) #############
# requires coproc, stdbuf, mysql
#args: handle query
function mysql_check {
  local handle
  handle=(${1//_/ })
  #has right structure && is still running && we opened it?
  if [[ ${#handle[*]} == 3 ]] && ps -p ${handle[2]} 2>> /dev/null >> /dev/null && { echo "" >&${handle[1]}; } 2> /dev/null; then
    return 0
  fi
  return 1
}

# open mysql connection
#args: -u user [-H host] [-p passwd] -d db
#returns $HANDLE
function mysql_connect {
  local argv argc user pass host db HANDLEID i
  #prepare args
  argv=($*)
  argc=${#argv[*]}

  #get options
  user=""
  pass=""
  host="localhost"
  db=""
  for ((i=0; $i < $argc; i++))
  do
    if [[ ${argv[$i]} == "-h" ]]; then
      echo "Usage: -u user [-H host] [-p passwd] -d db"
      return 0
    elif [[ ${argv[$i]} == "-u" ]]; then
      i=$[$i+1]
      if [[ ${#argv[$i]} -gt 0 ]]; then
    user=${argv[$i]}
      else
    echo "ERROR: -u expects argument!"
    return 1
      fi
    elif [[ ${argv[$i]} == "-p" ]]; then
      i=$[$i+1]
      if [[ ${#argv[$i]} -gt 0 ]]; then
    pass="-p"${argv[$i]}
      else
    echo "ERROR: -p expects argument!"
    return 1
      fi
    elif [[ ${argv[$i]} == "-H" ]]; then
      i=$[$i+1]
      if [[ ${#argv[$i]} -gt 0 ]]; then
    host=${argv[$i]}
      else
    echo "ERROR: -H expects argument!"
    return 1
      fi
    elif [[ ${argv[$i]} == "-d" ]]; then
      i=$[$i+1]
      if [[ ${#argv[$i]} -gt 0 ]]; then
    db=${argv[$i]}
      else
    echo "ERROR: -d expects argument!"
    return 1
      fi
    fi
  done

  if [[ ${#user} -lt 1 || ${#db} -lt 1 ]]; then
    echo "ERROR: Options -u user and -d db are required!"
    return 1;
  fi

  #init connection and channels
  #we do it in XML cause otherwise we can't detect the end of data and so would need a read timeout O_o
  HANDLEID="MYSQL$RANDOM"
  eval "coproc $HANDLEID { stdbuf -oL mysql -u $user $pass -h $host -D $db --force --unbuffered --xml -vvv 2>&1; }" 2> /dev/null
  HANDLE=$(eval 'echo ${'${HANDLEID}'[0]}_${'${HANDLEID}'[1]}_${'${HANDLEID}'_PID}')
  if mysql_check $HANDLE; then
    export HANDLE
    return 0
  else
    echo "ERROR: Connection failed to $user@$host->DB:$db!"
    return 1
  fi
}

#args: handle query
#return: $DATA[0] = affected rows/number of sets; 
#        $DATA[1] = key=>values pairs following
#        $DATA[2]key; DATA[3]=val ...
function mysql_query {
  local handle query affected line results_open row_open cols key val 
  if ! mysql_check $1; then
    echo "ERROR: Connection not open!"
    return 1
  fi
  handle=(${1//_/ })

  #delimit query; otherwise we block forever/timeout
  query=$2
  if [[ ! "$query" =~ \;\$ ]]; then
    query="$query;"
  fi
  #send query
  echo "$query" >&${handle[1]}

  #get output
  DATA=();
  DATA[0]=0
  DATA[1]=0
  results_open=0
  row_open=0
  cols=0
  while read -t $MYSQL_READ_TIMEOUT -ru ${handle[0]} line
  do 
    #WAS ERROR?
    if [[ "$line" == *"ERROR"* ]]; then
      echo "$line"
      return 1
    #WAS INSERT/UPDATE?
    elif [[ "$line" == *"Query OK"* ]]; then
      affected=$([[ "$line" =~ Query\ OK\,\ ([0-9]+)\ rows?\ affected ]] && echo ${BASH_REMATCH[1]})
      DATA[0]=$affected
      export DATA
      return 0
    fi

    #BEGIN OF RESULTS
    if [[ $line =~ \<resultset ]]; then
      results_open=1
    fi

    #RESULTS
    if [[ $results_open == 1 ]]; then
      if [[ $line =~ \<row ]]; then
    row_open=1
    cols=0
      elif [[ $line =~ \<field && $row_open == 1 ]]; then
    key=$([[ "$line" =~ name\=\"([^\"]+)\" ]] && echo ${BASH_REMATCH[1]})
    val=$([[ "$line" =~ \>(.*)\<\/ ]] && echo ${BASH_REMATCH[1]} || echo "NULL")
    DATA[${#DATA[*]}]=$key
    DATA[${#DATA[*]}]=$val
    cols=$[$cols+1]
      elif [[ $line =~ \<\/row ]]; then
    row_open=0
    DATA[0]=$[${DATA[0]}+1]
    DATA[1]=$cols
      fi
    fi

    #END OF RESULTS
    if [[ $line =~ \<\/resultset ]]; then
      export DATA
      return 0
    fi
  done
  #we can only get here
  #if read times out O_o
  echo "$FUNCNAME: Read timed out!"
  return 1
}

#args: handle
function mysql_close {
  local handle
  if ! mysql_check $1; then
    echo "ERROR: Connection not open!"
    return 1
  fi
  handle=(${1//_/ })
  echo "exit;" >&${handle[1]}

  if ! mysql_check $1; then
    return 0
  else
    echo "ERROR: Couldn't close connection!"
    return 1
  fi
}
############### END BASIC MYSQL SESSION IMPLEMENTATION FOR BASH ################################

# Example usage
#define timeout for read command, in case of server error etc.
export MYSQL_READ_TIMEOUT=10

# Connect to db and get $HANDLE
mysql_connect -u mydbuser -d mydb -H mydbserver

#query db and get $DATA
mysql_query $HANDLE "SELECT dt_whatever from tbl_lol WHERE dt_rofl=10"

#close connection
mysql_close $HANDLE

NOTES:

• Save $HANDLE to a new variable after connection to open as many connections as you like
• You can't exchange $HANDLE between bash sessions
• You need linux packages "coproc" "stdbuf" "mysql"
• Return DATA is of a bash array

$DATA[0] = affected rows/number of sets;
$DATA[1] = number of key=>values pairs following;
$DATA[2] = key1;
$DATA[3] = value1;
      [...]
$DATA[n-1] = keyn;
$DATA[n]   = valuen;

• Generally all queries should work, even "SELECT count(*)"

Example how to loop the returned data of a two column query

eg. "SELECT dt_id, dt_name FROM ..."

fields=2
for ((i=2; $i<$((${DATA[0]}*${DATA[1]}*$fields)); i+=$((${DATA[1]}*$fields))))
do
    field1key   = ${DATA[$i]};   #this is "dt_id"
    field1value = ${DATA[$i+1]}; #this is the value for dt_id
    field2key   = ${DATA[$i+2]}; #this is "dt_name"
    field2value = ${DATA[$i+3]}; #this is the value  for dt_name
done