How to execute a MySQL command from a shell script?

Question

You need to use the -p flag to send a password. And it's tricky because you must have no space between -p and the password.

$ mysql -h "server-name" -u "root" "-pXXXXXXXX" "database-name" < "filename.sql"

If you use a space after -p it makes the mysql client prompt you interactively for the password, and then it interprets the next command argument as a database-name:

$ mysql -h "server-name" -u "root" -p "XXXXXXXX" "database-name" < "filename.sql"
Enter password: <you type it in here>
ERROR 1049 (42000): Unknown database 'XXXXXXXX'

Actually, I prefer to store the user and password in ~/.my.cnf so I don't have to put it on the command-line at all:

[client]
user = root
password = XXXXXXXX

Then:

$ mysql -h "server-name" "database-name" < "filename.sql"

---

Re your comment:

I run batch-mode mysql commands like the above on the command line and in shell scripts all the time. It's hard to diagnose what's wrong with your shell script, because you haven't shared the exact script or any error output. I suggest you edit your original question above and provide examples of what goes wrong.

Also when I'm troubleshooting a shell script I use the -x flag so I can see how it's executing each command:

$ bash -x myscript.sh

Use this syntax:

mysql -u $user -p$passsword -Bse "command1;command2;....;commandn"

All of the previous answers are great. If it is a simple, one line sql command you wish to run, you could also use the -e option.

mysql -h <host> -u<user> -p<password> database -e \
  "SELECT * FROM blah WHERE foo='bar';"

How to execute an SQL script, use this syntax:

mysql --host= localhost --user=root --password=xxxxxx  -e "source dbscript.sql"

If you use host as localhost you don't need to mention it. You can use this:

mysql --user=root --password=xxxxxx  -e "source dbscript.sql"

This should work for Windows and Linux.

If the password content contains a ! (Exclamation mark) you should add a \ (backslash) in front of it.

The core of the question has been answered several times already, I just thought I'd add that backticks (`s) have beaning in both shell scripting and SQL. If you need to use them in SQL for specifying a table or database name you'll need to escape them in the shell script like so:

mysql -p=password -u "root" -Bse "CREATE DATABASE \`${1}_database\`;
CREATE USER '$1'@'%' IDENTIFIED BY '$2';
GRANT ALL PRIVILEGES ON `${1}_database`.* TO '$1'@'%' WITH GRANT OPTION;"

Of course, generating SQL through concatenated user input (passed arguments) shouldn't be done unless you trust the user input.It'd be a lot more secure to put it in another scripting language with support for parameters / correctly escaping strings for insertion into MySQL.

mysql -h "hostname" -u usr_name -pPASSWD "db_name" < sql_script_file

(use full path for sql_script_file if needed)

If you want to redirect the out put to a file

mysql -h "hostname" -u usr_name -pPASSWD "db_name" < sql_script_file > out_file