How to execute a MySQL command from a shell script?

how to execute sql query in shell script
shell script to retrieve data from mysql database
mysql command line
run sql script from command line linux
mysql-js shell
run mysql script from command line linux
shell script to connect to database and execute query
execute multiple mysql query in shell script

How can I execute an SQL command through a shell script so that I can make it automated?

I want to restore data I have collected in a SQL file using a shell script. I want to connect to a server and restore data. The command works when executed separately via SSH command line.

This is the command I use:

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

This is the shell script code that creates the file ds_fbids.sql and pipes it into mysql.

perl fb_apps_frm_fb.pl
perl fb_new_spider.pl ds_fbids.txt ds_fbids.sql
mysql -h dbservername -u username -ppassword dbname < ds_fbids.sql

What is the correct way to do this?

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

How to execute a MySQL command from a shell script?, How to connect to MySQL database and run SQL query from the Linux command​-line (execute query from shell) or Bash script. An important consideration for accessing mysql from a shell script used in cron, is that mysql looks at the logged in user to determine a .my.cnf to load. That does not work with cron. It can also get confusing if you are using su/sudo as the logged in user might not be the user you are running as.

Use this syntax:

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

MySQL: Run Query from Bash Script or Linux Command Line , MySQL Shell provides commands which enable you to modify the execution environment of the code editor, Execute a script file using the active language. So with MySQL Shell in SQL mode, you could now execute the script in the /tmp/mydata.sql file from either interactive mode or batch mode using any of these three commands: source /tmp/mydata.sql; source /tmp/mydata.sql \. /tmp/mydata.sql The command \source /tmp/mydata.sql is also valid, but in interactive mode only.

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';"

MySQL Shell 8.0 :: 3.1 MySQL Shell Commands, The mysql command line has option to execute the SQL statement and quit. This is also useful for running sql queries from a shell script or the  Executing shell commands from within the MySQL command line client Now that you’re started up the MySQL command line client you can execute SQL commands etc. If you want to execute a shell command, use the ! command followed by the shell command.

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.

MySQL/MariaDB: Run SQL Queries From A Shell Prompt , The MySQL command line client allows you to quickly and easily run sql queries from a console session, as well as load sql script files etc. Once you have  Q) How to connect to mysql database from a bash script in unix or linux and run sql queries? Bash scripting helps in automating things. We can automate running sql queries by connecting to the mysql database through a shell script in unix or linux system. Here we will see how to run a small sql query in mysql database through a script.

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.

Executing shell commands from within the MySQL command line client, Using mysql in Shell Scripts Problem You want to invoke mysql from within a within a shell script, possibly postprocessing the results with other commands. In this article i will show the most useful, from my point of view, options of the MySQL command-line client and show how to run multiple SQL queries to a database from a Bash script. Cool Tip: List MySQL users, their passwords and granted privileges from the command-line prompt! Read more → MySQL: Execute SQL Queries From The Linux Shell. Execute SQL query from the Linux command-line: $ mysql -u USER-pPASSWORD-e "SQL_QUERY"-pPASSWORD: This is not a typo!

Using mysql in Shell Scripts, Q) How to connect to mysql database from a bash script in unix or linux and run sql queries? Bash scripting helps in automating things. We can  The mysql client typically is used interactively, like this: Press CTRL+C to copy. shell> mysql db_name. However, it is also possible to put your SQL statements in a file and then tell mysql to read its input from that file. To do so, create a text file text_file that contains the statements you wish to execute.

Execute Mysql Command in Bash / Shell Script, This is a job for a "here document": mysql -u user << END_CMDS use mydb delete from mydata where row='xx' END_CMDS. The "here  Q. I have job (executed from shell script) where I need to run mysql query from a shell script. I want to run mysql from within a shell script rather than use it interactively by typing mysql -u user -p dbnane command. ADVERTISEMENTS How do I use mysql in Shell Scripts? A. I recommend using … Continue reading "Howto: Use mysql or run mysql queries from shell script"

How to create a script to run mysql commands, Yet another answer mysql -uroot -pmysql -B --skip-column-names -e'SELECT TABLE_NAME AS "Table Name", table_rows AS "Quant of Rows",  Inside a shell script, you execute the first step, the MySQL client then finish the query and exits, releasing all the locks you just added. In this cases is better to use P* languages (PHP/Python/Perl) instead, since they only connect once, and disconnects whenever you want or when the program ends.

Comments
  • Thanks for a quick response. I tired by putting the password in the command line itself. The real problem is I am putting this command in .sh file and then executing this shell script. The command in the file is not executed on command line, but the same command works perfectly fine when I execute only the command on command line.
  • + mysql -h dbservername -u user-name -ppassword dbname</br> : No such file or directoryids.sql</br> + $'\r' : command not found2: This is the error message I got
  • Ok, then I would infer that your current working directory is not where the ids.sql file is located. Also you may have embedded newlines in your script.
  • I do have new lines embedded in my shell script after every command. All that my shell script contain are 3 command line command which I dont want to run separately so i created a shell script to make them run without my intervention and I put newline afetr every commmand. Is that is causing problem ?
  • best to avoid -p if the password is null or empty string, perhaps you can update your post? :)
  • I was landed to this page from google and this is the solution I expected(matching with the title of the question).
  • Some more details about the options from the manual: -B is for batch, print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file. Batch mode results in nontabular output format and escaping of special characters. -s is silent mode. Produce less output. -e is to execute the statement and quit
  • Thanks for your help! :)
  • Could it run with a heredoc?
  • @zx1986 Yes and No, to HEREDOC. Depends on how you mean to use it. Using it to replace the "command1;command2;....;commandn" part of this answer will not work. Using it to replace the use of the redirected file in the OP's syntax can work. I've addressed that issue in my answer to this question.
  • Query within double("") quotes was what i need to do. Thanks
  • I see, and I take it you do need to include the semicolon at the end of the query?