sh script for executing mysql script with entering pass

Question

I'd like to execute a mysql script where the user is asked to enter the password dynamically from the command line, if possible with a message Enter your $root password. However it doesn't work as expected. So far I have

mysql -u root -p CREATE DATABASE IF NOT EXISTS Test

moreover when the db is created how to logout from mysql and go back to bash commands?

Answer 1

According to the docs

mysql -u root -p --execute="SELECT User, Host FROM mysql.user"

prompts for a password, executes the given command, and returns to the shell (no logout required).

If you instead want your own prompt you need to script it:

#!/usr/bin/env bash

echo "Enter pwd"
read pwd
mysql -u root -p$pwd --execute="SELECT User, Host FROM mysql.user"

Please note that there must be no space between -p and $pwd because mysql then considers $pwd to be the database's name.

I would not recommend that because the password is then shown in cleartext in the terminal whereas mysql -p shows asterisks instead.

Answer 2

Scripting MySQL queries can be done in couple of ways. PerlDuck's answer already touches on the -e or --execute parameter. Another way would be to use source command from MySQL itself to run an SQL script:

$ mysql -u root -p -e 'source foo.sql'
Enter password:
+------+-------------+
| a    | b           |
+------+-------------+
|    1 | hello world |
+------+-------------+
$ cat foo.sql
use foo_db;
SELECT * FROM foo_tb;

Another way is to redirect the script into mysql's stdin:

$ sudo mysql -u root -p < foo.sql
Enter password:
a   b
1   hello world

Note the difference in formatting in each case. Of course you can also use here-doc or herestring to achieve same results, although using a text file allows executing lengthy queries, while here-docs and here-strings are more useful for moderately short statements.