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.
User1 :
I have part of what I was looking for.\n\nKeep the mysql connection open using fd=3 for writing:\n\n\n\nexec 3> >(mysql)\necho \"SELECT 1;\" >&3\necho \"SELECT 2;\" >&3\nexec 3>&-\n\n\n\nKeep the mysql connection open using fd=3 for reading:\n\n\n\nexec 3< <(echo \"SELECT 1;SELECT 2;\"|mysql|sed '1d')\nwhile read <&3\ndo\n echo $REPLY\ndone\n\n\n\nNote: sed '1d' removes the header.\n\nIs there any way to merge these so you can write to one fd and read from another?",
2010-08-13T16:22:09
conny :
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.\n\nbash4-4.1$ coproc MYSQL mysql -B -uroot \n[1] 10603\nbash4-4.1$ jobs\n[1]+ Running coproc COPROC MYSQL mysql -B -uroot &\nbash4-4.1$ echo 'show databases;' | MYSQL\nDatabase\ninformation_schema\n...\n\n\nThe 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...",
2010-08-12T00:41:06
NORMAN GEIST :
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.\n\n############### BASIC MYSQL SESSION IMPLEMENTATION FOR BASH (by Norman \n\nGeist 2015) #############\n# requires coproc, stdbuf, mysql\n#args: handle query\nfunction mysql_check {\n local handle\n handle=(${1//_/ })\n #has right structure && is still running && we opened it?\n if [[ ${#handle[*]} == 3 ]] && ps -p ${handle[2]} 2>> /dev/null >> /dev/null && { echo \"\" >&${handle[1]}; } 2> /dev/null; then\n return 0\n fi\n return 1\n}\n\n# open mysql connection\n#args: -u user [-H host] [-p passwd] -d db\n#returns $HANDLE\nfunction mysql_connect {\n local argv argc user pass host db HANDLEID i\n #prepare args\n argv=($*)\n argc=${#argv[*]}\n\n #get options\n user=\"\"\n pass=\"\"\n host=\"localhost\"\n db=\"\"\n for ((i=0; $i < $argc; i++))\n do\n if [[ ${argv[$i]} == \"-h\" ]]; then\n echo \"Usage: -u user [-H host] [-p passwd] -d db\"\n return 0\n elif [[ ${argv[$i]} == \"-u\" ]]; then\n i=$[$i+1]\n if [[ ${#argv[$i]} -gt 0 ]]; then\n user=${argv[$i]}\n else\n echo \"ERROR: -u expects argument!\"\n return 1\n fi\n elif [[ ${argv[$i]} == \"-p\" ]]; then\n i=$[$i+1]\n if [[ ${#argv[$i]} -gt 0 ]]; then\n pass=\"-p\"${argv[$i]}\n else\n echo \"ERROR: -p expects argument!\"\n return 1\n fi\n elif [[ ${argv[$i]} == \"-H\" ]]; then\n i=$[$i+1]\n if [[ ${#argv[$i]} -gt 0 ]]; then\n host=${argv[$i]}\n else\n echo \"ERROR: -H expects argument!\"\n return 1\n fi\n elif [[ ${argv[$i]} == \"-d\" ]]; then\n i=$[$i+1]\n if [[ ${#argv[$i]} -gt 0 ]]; then\n db=${argv[$i]}\n else\n echo \"ERROR: -d expects argument!\"\n return 1\n fi\n fi\n done\n\n if [[ ${#user} -lt 1 || ${#db} -lt 1 ]]; then\n echo \"ERROR: Options -u user and -d db are required!\"\n return 1;\n fi\n\n #init connection and channels\n #we do it in XML cause otherwise we can't detect the end of data and so would need a read timeout O_o\n HANDLEID=\"MYSQL$RANDOM\"\n eval \"coproc $HANDLEID { stdbuf -oL mysql -u $user $pass -h $host -D $db --force --unbuffered --xml -vvv 2>&1; }\" 2> /dev/null\n HANDLE=$(eval 'echo ${'${HANDLEID}'[0]}_${'${HANDLEID}'[1]}_${'${HANDLEID}'_PID}')\n if mysql_check $HANDLE; then\n export HANDLE\n return 0\n else\n echo \"ERROR: Connection failed to $user@$host->DB:$db!\"\n return 1\n fi\n}\n\n#args: handle query\n#return: $DATA[0] = affected rows/number of sets; \n# $DATA[1] = key=>values pairs following\n# $DATA[2]key; DATA[3]=val ...\nfunction mysql_query {\n local handle query affected line results_open row_open cols key val \n if ! mysql_check $1; then\n echo \"ERROR: Connection not open!\"\n return 1\n fi\n handle=(${1//_/ })\n\n #delimit query; otherwise we block forever/timeout\n query=$2\n if [[ ! \"$query\" =~ \\;\\$ ]]; then\n query=\"$query;\"\n fi\n #send query\n echo \"$query\" >&${handle[1]}\n\n #get output\n DATA=();\n DATA[0]=0\n DATA[1]=0\n results_open=0\n row_open=0\n cols=0\n while read -t $MYSQL_READ_TIMEOUT -ru ${handle[0]} line\n do \n #WAS ERROR?\n if [[ \"$line\" == *\"ERROR\"* ]]; then\n echo \"$line\"\n return 1\n #WAS INSERT/UPDATE?\n elif [[ \"$line\" == *\"Query OK\"* ]]; then\n affected=$([[ \"$line\" =~ Query\\ OK\\,\\ ([0-9]+)\\ rows?\\ affected ]] && echo ${BASH_REMATCH[1]})\n DATA[0]=$affected\n export DATA\n return 0\n fi\n\n #BEGIN OF RESULTS\n if [[ $line =~ \\<resultset ]]; then\n results_open=1\n fi\n\n #RESULTS\n if [[ $results_open == 1 ]]; then\n if [[ $line =~ \\<row ]]; then\n row_open=1\n cols=0\n elif [[ $line =~ \\<field && $row_open == 1 ]]; then\n key=$([[ \"$line\" =~ name\\=\\\"([^\\\"]+)\\\" ]] && echo ${BASH_REMATCH[1]})\n val=$([[ \"$line\" =~ \\>(.*)\\<\\/ ]] && echo ${BASH_REMATCH[1]} || echo \"NULL\")\n DATA[${#DATA[*]}]=$key\n DATA[${#DATA[*]}]=$val\n cols=$[$cols+1]\n elif [[ $line =~ \\<\\/row ]]; then\n row_open=0\n DATA[0]=$[${DATA[0]}+1]\n DATA[1]=$cols\n fi\n fi\n\n #END OF RESULTS\n if [[ $line =~ \\<\\/resultset ]]; then\n export DATA\n return 0\n fi\n done\n #we can only get here\n #if read times out O_o\n echo \"$FUNCNAME: Read timed out!\"\n return 1\n}\n\n#args: handle\nfunction mysql_close {\n local handle\n if ! mysql_check $1; then\n echo \"ERROR: Connection not open!\"\n return 1\n fi\n handle=(${1//_/ })\n echo \"exit;\" >&${handle[1]}\n\n if ! mysql_check $1; then\n return 0\n else\n echo \"ERROR: Couldn't close connection!\"\n return 1\n fi\n}\n############### END BASIC MYSQL SESSION IMPLEMENTATION FOR BASH ################################\n\n# Example usage\n#define timeout for read command, in case of server error etc.\nexport MYSQL_READ_TIMEOUT=10\n\n# Connect to db and get $HANDLE\nmysql_connect -u mydbuser -d mydb -H mydbserver\n\n#query db and get $DATA\nmysql_query $HANDLE \"SELECT dt_whatever from tbl_lol WHERE dt_rofl=10\"\n\n#close connection\nmysql_close $HANDLE\n\n\nNOTES:\n\n\nSave $HANDLE to a new variable after connection to open as many connections as you like\nYou can't exchange $HANDLE between bash sessions\nYou need linux packages \"coproc\" \"stdbuf\" \"mysql\"\nReturn DATA is of a bash array\n\n\n\n$DATA[0] = affected rows/number of sets;\n$DATA[1] = number of key=>values pairs following;\n$DATA[2] = key1;\n$DATA[3] = value1;\n [...]\n$DATA[n-1] = keyn;\n$DATA[n] = valuen;\n\n\n\n\nGenerally all queries should work, even \"SELECT count(*)\"\n\n\nExample how to loop the returned data of a two column query\n\neg. \"SELECT dt_id, dt_name FROM ...\"\n\nfields=2\nfor ((i=2; $i<$((${DATA[0]}*${DATA[1]}*$fields)); i+=$((${DATA[1]}*$fields))))\ndo\n field1key = ${DATA[$i]}; #this is \"dt_id\"\n field1value = ${DATA[$i+1]}; #this is the value for dt_id\n field2key = ${DATA[$i+2]}; #this is \"dt_name\"\n field2value = ${DATA[$i+3]}; #this is the value for dt_name\ndone\n",
2016-05-11T14:31:56
Delian Krustev :
Here's a code snippet:\n#!/bin/bash\n\n# [...]\n\nset +e # DB locking is not strictly required\n\n# The code in this section tries to ensure that MySQL tables are flushed for a consistent ZFS snapshot.\n#\n# Use named fifos instead of bash's "coproc mysql -N --unbuffered" as with the latter mysql output is lost after it exits\nMI=`mktemp -u -p /root .BKP_LXC_G.XXX` ; MO=`mktemp -u -p /root .BKP_LXC_G.XXX`; rm -f /root/.BKP_LXC_G.*\nmkfifo -m 0600 $MI $MO\n# Keep the MySQL connection open(and thus the READ lock) until the snapshot is ready\nmysql -N --unbuffered <$MI >$MO 2>&1 &\nexec 3>$MI ; exec 4<$MO\n# Tell MySQL to commit data to disk before snapshotting the FS\necho "SET lock_wait_timeout = 10 ; FLUSH TABLES WITH READ LOCK; SELECT 'MyStrX';" >&3\nread -t 15 DB_STR <&4 # This ensures the DB lock has been obtained\nif [ "d$DB_STR" != 'dMyStrX' ]; then echo "Error aquiring DB lock: $DB_STR"; fi\nset -e ; $SSH zfs snapshot -r $ZFS_LXC@$SNAP_NAME ; set +e\necho "UNLOCK TABLES;" >&3\nexec 3>&-\nwhile read -t 1 DB_STR <&4 ; do echo "$DB_STR" ; done # "cat" hangs as it probably does not use NOWAIT\nexec 4<&-\nrm $MI $MO\nset -e\n\nShould not be a problem to clean the fifo's on error by using trap.",
2022-01-25T01:22:52