Geek Answers Handbook

Best way to execute multiple MySQL commands with shell script

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

Currently, I can do 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 entering "mysql -h $ host -u $ user -p $ password -e" every time I want to execute a MySQL command?

+6
source share
4 answers

I think you can execute MySQL statements from a text file like

here is the cmds.txt file containing the MySQL commands:

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

To execute them using a 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 do this, you can invoke mysql with the option --verbose.

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

+13
source

There are several ways in linux:

From mysql cli:

 mysql> source mycmds.sql

Use of pipes:

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

Executing commands from a file using pipes or redirection:

 cat file.sql | mysql ... OR mysql .. < file.sql
+6
source

You can use one multi-card file:

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

Just write all your requests separated ; . They will be launched one by one.

+2
source

Please note that you can also use the document HERE for 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

Please note that I used 'EOF' and not EOF in the first line to prevent the script from content, to disable parameter substitution (especially, ` can be problematic)

Also note that there should be no spaces before the final EOF , unless you use <<- and not << - in this case leading tab characters are leading):

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

(Replace ↠ with a tab character).

For more information on SOERE doc syntax, see the bash documentation .

+2
source

More articles:


All Articles