How to execute a MySQL command from a shell script?
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"