Shell Script – Get Extract value from mysql show variables

Question

I want to get a value form show variables query using the shell script. This is the command i used.

data=$(mysql -u root -proot -t -e "show variables where variable_name = 'datadir';"  -B --skip-column-names)

Out put result is,

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

So i just want to get the value.

/var/lib/mysql/

This is the Script which i try,

data=$(mysql -u root -proot -t -e "show variables where variable_name = 'datadir';"  -B --skip-column-names)
data2=$(echo $data | tr "+---------+-----------------+" "\n")
data3=$(echo $data2 | tr "|" "\n")
data4=$(echo $data3 | tr "datadir " "\n")
echo $data4

its returning "/v /l b/mysql/".

Instead of fiddling with shell to get rid of the borders, I'd rather make MySQL not print them in the first place. — Benjamin W.
– Benjamin W., Commented May 19, 2020 at 15:26
Get rid of the -t option. It's overriding -B, so you get the borders. — Barmar
– Barmar, Commented May 19, 2020 at 15:34
Thanks border removed. But how to get the remove "datadir" from the result. — Chamara Maduranga
– Chamara Maduranga, Commented May 19, 2020 at 15:47

wcarhart · Accepted Answer · 2020-05-19 15:53:40Z

1

First, don't use the -t and -B options together (in addition, prefer --batch over -B, as -B is deprecated (see Get the sql query result without the table format).

Next, tr is intended to replace single characters only. If you want to replace more characters, you could use sed or just Bash. If you want the second word in a string of characters, you could use cut.

Pure Bash

data=$(mysql -u root -proot -e "show variables where variable_name = 'datadir';"  --batch --skip-column-names)
echo "${data//datadir/}"

sed

data=$(mysql -u root -proot -e "show variables where variable_name = 'datadir';"  --batch --skip-column-names)
sed 's/datadir//g' <<< "$data"

cut

data=$(mysql -u root -proot -e "show variables where variable_name = 'datadir';"  --batch --skip-column-names)
cut -d' ' -f2 <<< "$data"

answered May 19, 2020 at 15:53

wcarhart

2,8131 gold badge26 silver badges49 bronze badges

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

Collectives™ on Stack Overflow

Shell Script – Get Extract value from mysql show variables

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related