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.

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?

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

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

Here's a code snippet:

#!/bin/bash

# [...]

set +e # DB locking is not strictly required

# The code in this section tries to ensure that MySQL tables are flushed for a consistent ZFS snapshot.
#
# Use named fifos instead of bash's "coproc mysql -N --unbuffered" as with the latter mysql output is lost after it exits
MI=`mktemp -u -p /root .BKP_LXC_G.XXX` ; MO=`mktemp -u -p /root .BKP_LXC_G.XXX`; rm -f /root/.BKP_LXC_G.*
mkfifo -m 0600 $MI $MO
# Keep the MySQL connection open(and thus the READ lock) until the snapshot is ready
mysql -N --unbuffered <$MI >$MO 2>&1 &
exec 3>$MI ; exec 4<$MO
# Tell MySQL to commit data to disk before snapshotting the FS
echo "SET lock_wait_timeout = 10 ; FLUSH TABLES WITH READ LOCK; SELECT 'MyStrX';" >&3
read -t 15 DB_STR <&4 # This ensures the DB lock has been obtained
if [ "d$DB_STR" != 'dMyStrX' ]; then echo "Error aquiring DB lock: $DB_STR"; fi
set -e ; $SSH zfs snapshot -r $ZFS_LXC@$SNAP_NAME ; set +e
echo "UNLOCK TABLES;" >&3
exec 3>&-
while read -t 1 DB_STR <&4 ; do echo "$DB_STR" ; done # "cat" hangs as it probably does not use NOWAIT
exec 4<&-
rm $MI $MO
set -e

Should not be a problem to clean the fifo's on error by using trap.

This very simple solution for queries which produce one-line results worked for me with GNU bash 5.1 and MariaDB 10.5.

#!/bin/bash

if ! coproc MYSQL { /usr/bin/mysql --batch --silent --unbuffered --database=mydb 2>&1; }; then
        echo 'failed to launch mysql client'
        exit 2
fi

function sqlexec()
{
        local ln
        echo "$1" >&${MYSQL[1]}
        if ! read -t 9 -u ${MYSQL[0]} ln; then
                echo "got no answer from query ($1)" >&2
                return 1
        fi
        if [[ "$ln" =~ ^ERROR ]]; then
                echo "$ln ($1)" >&2
                return 2
        fi
        echo "$ln"
}

if ! sqlexec "SELECT 'sql connection: ok';"; then
        echo 'FAILED check: sql connection'
        exit 2
fi