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?
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
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.
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.
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.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
--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).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.
my.cnf fileyou 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"
mysql_config_editoryou 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"
