Run queries from BASH

BASH is not used often for interrogating a database, but it can be done. It would not be regarded as secure to have passwords embedded in the command, but this can be extracted to a file protected by appropriate permissions.

# mysql -h  127.0.0.1 -u dbuser -pletmein -e 'use MyDatabase; select * from version; quit;'
+------------+
| MyDatabase |
+------------+
| v1.0.0.0   |
+------------+

Or

#!/bin/bash
result=`mysql -h 127.0.0.1 --user=dbuser --password=letmein --skip-column-names -e "select * from version.MyDatabase where something = 'value'"`
list=$(echo $result | tr " " "\n")

Or even:-

root@host:~# mysql -u root -pletmein -e 'use ftp; select * from ftpuser \G'
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
      id: 1
  userid: testuser1
  passwd: testpw
     uid: 5500
     gid: 5500
 homedir: /home/testuser1
   shell: /bin/ftponly
   count: 4
accessed: 2015-04-21 14:44:37
modified: 2015-04-21 14:44:40
root@host:~# 
root@host:~# 
root@host:~# mysql -u root -pletmein -e 'use ftp; select * from ftpuser \G' | grep homedir | cut -f2 -d:
Warning: Using a password on the command line interface can be insecure.
 /home/testuser1
root@host:~#

Developing a bit more:-

root@host:~# MYPATH=`mysql -u root -pletmein -e 'select * from ftp.ftpuser \G' | grep homedir | cut -f2 -d:`
Warning: Using a password on the command line interface can be insecure.
root@host:~# echo $MYPATH
/home/testuser1
root@host:~#

To remove the warning message about passwords on the command line, you can use mysql_config_editor:-

root@host:~# mysql_config_editor set --login-path=autologin --host=localhost --user=root --password
Enter password: 
root@host:~#

root@host:~# MYPATH=`mysql --login-path=autologin  -e 'select * from ftp.ftpuser \G' | grep homedir | cut -f2 -d:`
root@host:~# echo $MYPATH
/home/testuser1
root@host:~#

mysql/bashqueries.txt · Last modified: 13/07/2016 14:40 by andrew