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.