A better way to execute multiple MySQL commands us

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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