connect to mysql db and execute query and export result to variable - bash script

Question

I want to connect to mysql databse and execute some queries and export its result to a varibale, and do all of these need to be done entirely by bash script

I have a snippet code but does not work.

#!/bin/bash

BASEDIR=$(dirname $0)
cd $BASEDIR

mysqlUser=n_userdb
mysqlPass=d2FVR0NA3
mysqlDb=n_datadb

result=$(mysql -u $mysqlUser -p$mysqlPass -D $mysqlDb -e "select * from confs limit 1")

echo "${result}" >> a.txt

whats the problem ?

Is there any reason why you don't just redirect the output of mysql into a file directly? Also, what does not work and in what way (error messages?). You also need to double quote all variable expansions. — Kusalananda
– Kusalananda, Commented Jun 30, 2018 at 14:57
check this previous question stackoverflow.com/questions/42865795/… — Corrupted_S.K
– Corrupted_S.K, Commented Jun 30, 2018 at 15:01
@Corrupted_S.K Unrelated. There is no issue with the -p option in the given code (except they should be using a my.cnf file instead). — Kusalananda
– Kusalananda, Commented Jun 30, 2018 at 15:02
No, it means that the errors are not saved to the output file since you are only redirecting standard output, not standard error. Redirect the error stream with mysql ... 2>a-err.txt. The errors would then be available in a-err.txt. — Kusalananda
– Kusalananda, Commented Jun 30, 2018 at 15:12

Kusalananda · Accepted Answer · 2018-06-30 16:00:07Z

1

The issue was resolved in the chat by using the correct password.

If you further want to get only the data, use mysql with -NB (or --skip-column-names and --batch).

Also, the script needs to quote the variable expansions, or there will be issues with usernames/passwords containing characters that are special to the shell. Additionally, uppercase variable names are usually reserved for system variables.

#!/bin/sh

basedir=$(dirname "$0")

mysqlUser='n_userdb'
mysqlPass='d2FVR0NA3'
mysqlDb='n_datadb'

cd "$basedir" &&
mysql -NB -u "$mysqlUser" -p"$mysqlPass" -D "$mysqlDb" \
      -e 'select * from confs limit 1' >a.txt 2>a-err.txt

Ideally though, you'd use a my.cnf file to configure the username and password.

See e.g.

edited Jun 30, 2018 at 16:00

answered Jun 30, 2018 at 15:37

Kusalananda

15.8k3 gold badges47 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jonas Bjork · Accepted Answer · 2018-06-30 15:31:09Z

0

Do this:

result=$(mysql -u $mysqlUser -p$mysqlPass -D $mysqlDb -e "select * from confs limit 1" | grep '^\|' | tail -1)

The $() statement of Bash has trouble handling variables which contain multiple lines so the above hack greps only the interesting part: the data

edited Jun 30, 2018 at 15:31

answered Jun 30, 2018 at 14:54

Jonas Bjork

1683 bronze badges

5 Comments

Kusalananda Over a year ago

Bash has no issue with variable containing multiple lines.

Jonas Bjork Over a year ago

OK sorry i misspoke. Bash has no trouble doing that but the command $() does. @saeid What's the content of a.txt, what exactly did you type and do you get any error message? It works fine for me.

Jonas Bjork Over a year ago

@Kusalananda Do we agree on my way of expressing it now since I edited?

Kusalananda Over a year ago

No sorry, you still claim that bash has some sort of issue with multi-line strings in variables. I'm not sure from where you get this.

Jonas Bjork Over a year ago

@Kusalananda No I simply mean that putting all lines into one when using multiline $( ) in Bash is a poor way of handling it.

Collectives™ on Stack Overflow

connect to mysql db and execute query and export result to variable - bash script

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related