1

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 ?

saeid ezzati
  • 855
  • 11
  • 29
  • 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 Jun 30 '18 at 14:57
  • check this previous question https://stackoverflow.com/questions/42865795/shell-script-to-connect-to-mysql-server – Corrupted_S.K Jun 30 '18 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 Jun 30 '18 at 15:02
  • @Kusalananda even after I redirect output into a file directly, didnt save anything into the file and file content is empty yet – saeid ezzati Jun 30 '18 at 15:04
  • 1
    Are you getting error messages or warnings? – Kusalananda Jun 30 '18 at 15:04
  • @Kusalananda I haven't any direct access to shell and only can write my codes in the .sh files and execute them by cronjob and direct output to a file and check results in that file. my snippet don't write any result in 'a.txt' file and this means it doesn't make any error – saeid ezzati Jun 30 '18 at 15:07
  • 1
    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 Jun 30 '18 at 15:12
  • @Kusalananda I used your guide and got this message: `ERROR 1045 (28000): Access denied for user 'allcoin_userdb'@'localhost' (using password: YES)` but I connect to mysql via this config by php. why I can't connect from shell. now what I must do ? – saeid ezzati Jun 30 '18 at 15:16
  • Try using `-h 127.0.0.1` with `mysql`. MySQL treats `localhost` as distinct from `127.0.0.1` (the IP address of `localhost`). – Kusalananda Jun 30 '18 at 15:18
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/174084/discussion-between-saeid-and-kusalananda). – saeid ezzati Jun 30 '18 at 15:22

2 Answers2

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.

Kusalananda
  • 14,885
  • 3
  • 41
  • 52
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

Jonas Bjork
  • 158
  • 3
  • Bash has _no_ issue with variable containing multiple lines. – Kusalananda Jun 30 '18 at 14:59
  • 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 Jun 30 '18 at 15:30
  • @Kusalananda Do we agree on my way of expressing it now since I edited? – Jonas Bjork Jun 30 '18 at 15:32
  • 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. – Kusalananda Jun 30 '18 at 15:57
  • @Kusalananda No I simply mean that putting all lines into one when using multiline $( ) in Bash is a poor way of handling it. – Jonas Bjork Jun 30 '18 at 16:16