How to execute a MySQL command from a shell script?

by

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

More Related Contents:

  1. Suppress warning messages using mysql from within Terminal, but password written in bash script
  2. How do I know when my docker mysql container is up and mysql is ready for taking queries?
  3. Access mysql remote database from command line
  4. Linux shell script for database backup
  5. Laravel MySql DB Connection with SSH
  6. MySQL connection over SSH tunnel
  7. Codeigniter – multiple database connections
  8. Column count doesn’t match value count at row 1
  9. How to add levenshtein function in mysql?
  10. How to search JSON data in MySQL?
  11. MySQL > Table doesn’t exist. But it does (or it should)
  12. Is the primary key automatically indexed in MySQL?
  13. How do I modify a MySQL column to allow NULL?
  14. Connect to mysql in a docker container from the host
  15. Is there ANY_VALUE capability for mysql 5.6?
  16. Set NOW() as Default Value for datetime datatype?
  17. Fetching linked list in MySQL database
  18. How to skip certain database tables with mysqldump?
  19. Using MySql with Entity Framework 4 and the Code-First Development CTP
  20. ‘Access denied for user ‘root’@’localhost’ (using password: NO)’
  21. MySQL treats ÅÄÖ as AAO?
  22. Count with IF condition in MySQL query
  23. mysql_fetch_row() vs mysql_fetch_assoc() vs mysql_fetch_array() [duplicate]
  24. Mysql select where not in table
  25. Using DISTINCT and COUNT together in a MySQL Query
  26. Which DATATYPE is better to use TEXT or VARCHAR?
  27. How long is the SHA256 hash?
  28. how to trim leading zeros from alphanumeric text in mysql function
  29. Correct way to keep pooled connections alive (or time them out and get fresh ones) during longer inactivity for MySQL, Grails 2 app
  30. MySQL: how to index an “OR” clause

Leave a Comment