How to execute a MySQL command from a shell script

Question

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   lt   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  lt  ds fbids sql   What is the correct way to do this

User · Accepted Answer

You need to use the -p flag to send a password   And it s tricky because you must have no space between -p and the password     mysql -h  server-name  -u  root   -pXXXXXXXX   database-name   lt   filename sql    If you use a space after -p it makes the mysql client prompt you interactively for the password  and then it interprets the next command argument as a database-name     mysql -h  server-name  -u  root  -p  XXXXXXXX   database-name   lt   filename sql  Enter password   lt you type it in here gt  ERROR 1049  42000   Unknown database  XXXXXXXX    Actually  I prefer to store the user and password in    my cnf so I don t have to put it on the command-line at all    client  user   root password   XXXXXXXX   Then     mysql -h  server-name   database-name   lt   filename sql      Re your comment   I run batch-mode mysql commands like the above on the command line and in shell scripts all the time   It s hard to diagnose what s wrong with your shell script  because you haven t shared the exact script or any error output   I suggest you edit your original question above and provide examples of what goes wrong   Also when I m troubleshooting a shell script I use the -x flag so I can see how it s executing each command     bash -x myscript sh

User · Answer

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   lt   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

User · Answer

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

User · Answer

Use   echo  your sql script     mysql -u -p -h db name

User · Answer

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 lt  lt 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

User · Answer

You forgot -p or --password   the latter is better readable    mysql -h   server name   --user  user   --password  password   --database  database name   lt   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

User · Answer

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

User · Answer

Use this syntax   mysql -u  user -p passsword -Bse  command1 command2      commandn

User · Answer

mysql -h  hostname  -u usr name -pPASSWD  db name   lt  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   lt  sql script file  gt  out file

User · Answer

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  lt host gt  -u lt user gt  -p lt password gt  database -e      SELECT   FROM blah WHERE foo  bar

User · Answer

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

User · Answer

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

User · Answer

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  gt temp sql  lt  lt 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  gt temp sql  lt  lt  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

User · Answer

bin sh  Procedures   update  Scheduled at   Every 00 05   v path  etc database jobs v cnt 0  MAILTO  indd abc in joanson abc in sturt abc in  touch   v path db db log log    test mysql -uusername -ppassword -h111 111 111 111 db name -e  CALL functionName     gt   v path db db log log 2 gt  amp 1 if        -eq 0     then    v cnt  expr  v cnt   1    mail -s  db Attendance Update has been run successfully   MAILTO  lt   v path db db log log  else    mail -s  Alert   db Attendance Update has been failed   MAILTO  lt   v path db db log log    exit fi

[mysql] How to execute a MySQL command from a shell script?

Examples related to mysql

Examples related to shell

Examples related to ssh