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?
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
Source: Stackoverflow.com