[mysql] How to execute a MySQL command from a 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?

This question is related to mysql shell ssh

The answer is


To "automate" the process of importing the generated .sql file, while avoiding all the traps that can be hidden in trying to pass files through stdin and stdout, just tell MySQL to execute the generated .sql file using the SOURCE command in MySQL.

The syntax in the short, but excellent, answer, from Kshitij Sood, gives the best starting point. In short, modify the OP's command according to Kshitij Sood's syntax and replace the commands in that with the SOURCE command:

#!/bin/bash
mysql -u$user -p$password $dbname -Bse "SOURCE ds_fbids.sql
SOURCE ds_fbidx.sql"

If the database name is included in the generated .sql file, it can be dropped from the command.

The presumption here is that the generated file is valid as an .sql file on its own. By not having the file redirected, piped, or in any other manner handled by the shell, there is no issue with needing to escape any of the characters in the generated output because of the shell. The rules with respect to what needs to be escaped in an .sql file, of course, still apply.

How to deal with the security issues around the password on the command line, or in a my.cnf file, etc., has been well addressed in other answers, with some excellent suggestions. My favorite answer, from Danny, covers that, including how to handle the issue when dealing with cron jobs, or anything else.


To address a comment (question?) on the short answer I mentioned: No, it cannot be used with a HEREDOC syntax, as that shell command is given. HEREDOC can be used in the redirection version syntax, (without the -Bse option), since I/O redirection is what HEREDOC is built around. If you need the functionality of HEREDOC, it would be better to use it in the creation of a .sql file, even if it's a temporary one, and use that file as the "command" to execute with the MySQL batch line.

#!/bin/bash
cat >temp.sql <<SQL_STATEMENTS
...
SELECT \`column_name\` FROM \`table_name\` WHERE \`column_name\`='$shell_variable';
...
SQL_STATEMENTS
mysql -u $user -p$password $db_name -Be "SOURCE temp.sql"
rm -f temp.sql

Bear in mind that because of shell expansion you can use shell and environment variables within the HEREDOC. The down-side is that you must escape each and every backtick. MySQL uses them as the delimiters for identifiers but the shell, which gets the string first, uses them as executable command delimiters. Miss the escape on a single backtick of the MySQL commands, and the whole thing explodes with errors. The whole issue can be solved by using a quoted LimitString for the HEREDOC:

#!/bin/bash
cat >temp.sql <<'SQL_STATEMENTS'
...
SELECT `column_name` FROM `table_name` WHERE `column_name`='constant_value';
...
SQL_STATEMENTS
mysql -u $user -p$password $db_name -Be "SOURCE temp.sql"
rm -f temp.sql

Removing shell expansion that way eliminates the need to escape the backticks, and other shell-special characters. It also removes the ability to use shell and environment variables within it. That pretty much removes the benefits of using a HEREDOC inside the shell script to begin with.

The other option is to use the multi-line quoted strings allowed in Bash with the batch syntax version (with the -Bse). I don't know other shells, so I cannot say if they work therein as well. You would need to use this for executing more than one .sql file with the SOURCE command anyway, since that is not terminated by a ; as other MySQL commands are, and only one is allowed per line. The multi-line string can be either single or double quoted, with the normal effects on shell expansion. It also has the same caveats as using the HEREDOC syntax does for backticks, etc.

A potentially better solution would be to use a scripting language, Perl, Python, etc., to create the .sql file, as the OP did, and SOURCE that file using the simple command syntax at the top. The scripting languages are much better at string manipulation than the shell is, and most have in-built procedures to handle the quoting and escaping needed when dealing with MySQL.


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.

I use something like:

mysql --defaults-extra-file=/path/to/specific/.my.cnf -e 'SELECT something FROM sometable'

Just make sure that user and group ownership and permissions are set appropriately and tightly on the .my.cnf file.


mysql -h "hostname" -u usr_name -pPASSWD "db_name" < sql_script_file

(use full path for sql_script_file if needed)

If you want to redirect the out put to a file

mysql -h "hostname" -u usr_name -pPASSWD "db_name" < sql_script_file > out_file

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.


You forgot -p or --password= (the latter is better readable):

mysql -h "$server_name" "--user=$user" "--password=$password" "--database=$database_name" < "filename.sql"

(The quotes are unnecessary if you are sure that your credentials/names do not contain space or shell-special characters.)

Note that the manpage, too, says that providing the credentials on the command line is insecure. So follow Bill's advice about my.cnf.


Use

echo "your sql script;" | mysql -u -p -h db_name

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

I have written a shell script which will read data from properties file and then run mysql script on shell script. sharing this may help to others.

#!/bin/bash
    PROPERTY_FILE=filename.properties

    function getProperty {
       PROP_KEY=$1
       PROP_VALUE=`cat $PROPERTY_FILE | grep "$PROP_KEY" | cut -d'=' -f2`
       echo $PROP_VALUE
    }

    echo "# Reading property from $PROPERTY_FILE"
    DB_USER=$(getProperty "db.username")
    DB_PASS=$(getProperty "db.password")
    ROOT_LOC=$(getProperty "root.location")
    echo $DB_USER
    echo $DB_PASS
    echo $ROOT_LOC
    echo "Writing on DB ... "
    mysql -u$DB_USER -p$DB_PASS dbname<<EOFMYSQL

    update tablename set tablename.value_ = "$ROOT_LOC" where tablename.name_="Root directory location";
    EOFMYSQL
    echo "Writing root location($ROOT_LOC) is done ... "
    counter=`mysql -u${DB_USER} -p${DB_PASS} dbname -e "select count(*) from tablename where tablename.name_='Root directory location' and tablename.value_ = '$ROOT_LOC';" | grep -v "count"`;

    if [ "$counter" = "1" ]
    then
    echo "ROOT location updated"
    fi

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.


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

How do I execute a command line with a secure password?? use the config editor!!!

As of mysql 5.6.6 you can store the password in a config file and then execute cli commands like this....

mysql --login-path=storedPasswordKey ....

--login-path replaces variables... host, user AND password. excellent right!


As stated before you can use -p to pass the password to the server.

But I recommend this:

mysql -h "hostaddress" -u "username" -p "database-name" < "sqlfile.sql"

Notice the password is not there. It would then prompt your for the password. I would THEN type it in. So that your password doesn't get logged into the servers command line history.

This is a basic security measure.

If security is not a concern, I would just temporarily remove the password from the database user. Then after the import - re-add it.

This way any other accounts you may have that share the same password would not be compromised.

It also appears that in your shell script you are not waiting/checking to see if the file you are trying to import actually exists. The perl script may not be finished yet.


Use this syntax:

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

#!/bin/sh
#Procedures = update
#Scheduled at : Every 00.05 

v_path=/etc/database_jobs
v_cnt=0

MAILTO="[email protected] [email protected] [email protected]"
touch "$v_path/db_db_log.log"

#test
mysql -uusername -ppassword -h111.111.111.111 db_name -e "CALL functionName()" > $v_path/db_db_log.log 2>&1
if [ "$?" -eq 0 ]
  then
   v_cnt=`expr $v_cnt + 1`
  mail -s "db Attendance Update has been run successfully" $MAILTO < $v_path/db_db_log.log
 else
   mail -s "Alert : db Attendance Update has been failed" $MAILTO < $v_path/db_db_log.log
   exit
fi

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to shell

Comparing a variable with a string python not working when redirecting from bash script Get first line of a shell command's output How to run shell script file using nodejs? Run bash command on jenkins pipeline Way to create multiline comments in Bash? How to do multiline shell script in Ansible How to check if a file exists in a shell script How to check if an environment variable exists and get its value? Curl to return http status code along with the response docker entrypoint running bash script gets "permission denied"

Examples related to ssh

Starting ssh-agent on Windows 10 fails: "unable to start ssh-agent service, error :1058" How to solve "sign_and_send_pubkey: signing failed: agent refused operation"? key_load_public: invalid format ssh connection refused on Raspberry Pi Getting permission denied (public key) on gitlab Verify host key with pysftp Can't connect to Postgresql on port 5432 Checkout Jenkins Pipeline Git SCM with credentials? How to open remote files in sublime text 3 how to setup ssh keys for jenkins to publish via ssh