I have a MySQL server with an user with a password. I want to execute some SQL queries in shell scripts without specifying the password like this:
config.sh
:
MYSQL_ROOT="root" MYSQL_PASS="password"
mysql.sh
:
source config.sh mysql -u$MYSQL_ROOT -p$MYSQL_PASS -e "SHOW DATABASES"
How can I simplify the whole process in order to execute SQL queries without specifying the -p
and -u
argument etc.?
#SELECT THE ORG SHORT NAMES $select_org = "SELECT id, short_name FROM $org_table"; $org_handle = $connect->prepare($select_org); $org_handle will have the resultset. Show activity on this post. For example, select all the name field in table tablename of database dbname and redirect all name to /tmp/all_name.
You can write
mysql -u "$MYSQL_ROOT" -p"$MYSQL_PASS" -e "SHOW DATABASES"
If [password is] given, there must be no space between
--password=
or-p
and the password following it. If no password option is specified, the default is to send no password.
to pass empty strings as separate arguments. Your comment below indicates that the client will still ask for a password, though. Probably it interprets the empty argument as a database name and not as the password. So you could try the following instead:
mysql --user="$MYSQL_ROOT" --password="$MYSQL_PASS" -e "SHOW DATABASES"
But even if there is a way, I'd still suggest you use a ~/.my.cnf
file instead. Arguments on the command line are likely included in a process listing generated by ps -A -ocmd
, so other users can see them. The .my.cnf
file, on the other hand, can (and should) be made readable only by you (using chmod 0600 ~/.my.cnf
), and will be used automatically. Have that file include the following lines:
[client] user=root password=
Then a simple mysql -e "SHOW DATABASES"
will suffice, as the client will obtain its credentials from that file.
See 6.1.2.1. End-User Guidelines for Password Security for the various ways in which you can provide a password, and their respective benefits and drawbacks. See 4.2.3.3. Using Option Files for general information on this .my.cnf
file
As MvG suggested (recommended in the MySQL manual 4.2.2 connecting and 6.1.2.1 security guidelines) you should use a file. The password on the command line may be unsafe since ps may show it to other users. The file does not have to be .my.cnf, can be an ad-hoc option file for your script in a temporary file:
OPTFILE="$(mktemp -q --tmpdir "${inname}.XXXXXX")$" trap 'rm -f "$OPTFILE"' EXIT chmod 0600 "$OPTFILE" cat >"$OPTFILE" <<EOF [client] password="${MYSQL_PASS}" EOF mysql --user="$MYSQL_ROOT" --defaults-extra-file="$OPTFILE" -e "SHOW DATABASES"
The first lines create a safe temp file, then put the options then use it. trap will protect you form OPTFILE lying around in case of interrupts.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With