34

I would like to write a *.sh script to execute multiple MySQL commands.

Currently, what I can do is something like the following

mysql -h$host -u$user -p$password -e "drop database $dbname;"
mysql -h$host -u$user -p$password -e "create database $dbname;"
mysql -h$host -u$user -p$password -e "another MySQL command"
...

Is there a way to avoid typing mysql -h$host -u$user -p$password -e every time I want to execute a MySQL command?

Brian
  • 12,145
  • 20
  • 90
  • 153

5 Answers5

40

I think you can execute MySQL statements from a text file, for example

here is the cmds.txt file which contains MySQL commands:

select colA from TableA;
select colB from TableB;
select colC from TableC;

To execute them using shell script, type

mysql -h$host -u$user -p$password db_dbname < cmds.txt

This way, you separate your MySQL commands from your shell script.

You may want your script to display progress information to you. For this you can invoke mysql with "--verbose" option.

For more information, see https://dev.mysql.com/doc/refman/5.6/en/mysql-batch-commands.html

EricWang
  • 578
  • 5
  • 9
32

You can use a single multiquery:

mysql -h$host -u$user -p$password -e "drop database $dbname;create database $dbname;another MySQL command;"

Simply write all your queries seperated by ;. They will be run one after the other.

baao
  • 71,625
  • 17
  • 143
  • 203
22

Note that you can also use a HERE doc to have the queries within the same script:

mysql -h$host -u$user -p$password db_dbname <<'EOF'
select colA from TableA;
select colB from TableB;
select colC from TableC;
EOF

Note that I've used 'EOF' rather than EOF in the first line in order to prevent the contents of the script to disable parameter substitution (especially the ` can be problematic)

Also note that there should not be any whitespace before the final EOF (except if you use <<- rather than << -- in that case leading tab characters are stripped):

mysql -h$host -u$user -p$password db_dbname <<- 'EOF'
↠select colA from TableA;
↠select colB from TableB;
↠select colC from TableC;
↠EOF

(Replace the ↠ with a tab character).

For more info on the HERE doc syntax, see the bash documentation.

Erwin Wessels
  • 2,972
  • 1
  • 24
  • 17
  • Thanks for the nice solution! Using bash, I was receiving an error if I put a tab character before the ending EOF but without it, all went well. – Omid Ariyan Jun 12 '20 at 11:06
  • Not worked for me. i am trying on the win 10 `cd C:\xampp\mysql\bin && mysql.exe -u admin -padmin123 <<'EOF' select 'IMPORT_DONE' AS IMPORT_STATUS EOF` Its Saying ``'EOF' is not recognized as an internal or external command, operable program or batch file.`` – Aman Deep Sep 11 '21 at 07:06
13

There are several ways, in linux you have:

From the mysql cli: 

mysql> source mycmds.sql

Using Pipes:

echo "SELECT ..; INSERT ..;" | mysql ...

Executing commands from a file using pipes or redirection:

cat file.sql | mysql ... OR mysql .. < file.sql
Dave
  • 860
  • 8
  • 8
  • All of your answers are great. I choose EricWang's answer because I think this is the most structured way of doing this job. – Brian Sep 30 '15 at 15:19
  • The most complete answer. (It contains the answer of EricWang apart from `--version` for progress information.) Also using `.sql` as ending for SQL should be preferred over generic `.txt` to avooid loss of information (unless dynamic format detection is supported). – Radagast Apr 09 '17 at 20:25
0

Different from other answers that reduce repetition, but
there are ways to reduce options(user, host ... -u, -p, -h ...) from each line command

2 ways i know.

1. use my.cnf file

you can store your user information in option files (e.g ~/.my.cnf or etc)

[client] 
user=your_username
password=your_password
# database=database_name

then you can just run mysql command with one option -e and query

mysql -e "drop database $dbname;"
mysql -e "create database $dbname;"
mysql -e "another MySQL command"

2. use mysql_config_editor

you can save login informations with mysql_config_editor

mysql_config_editor set --login-path=mypath1 --host=localhost --user=root --password

then run command just with login-path option

mysql --login-path=mypath1 -e "drop database $dbname;"
mysql --login-path=mypath1 -e "create database $dbname;"
mysql --login-path=mypath1 -e "another MySQL command"
fver1004
  • 303
  • 1
  • 3
  • 9