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?

up vote 7 down vote accepted

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.

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.