Linux – Connect to MySQL through command line without using root password

bashlinuxMySQLpostgresqlshell-scripting

I'm building a Bash script for some tasks. One of those tasks is create a MySQL DB from within the same bash script. What I'm doing right now is creating two vars: one for store user name and the other for store password. This is the relevant part of my script:

MYSQL_USER=root
MYSQL_PASS=mypass_goes_here

touch /tmp/$PROY.sql && echo "CREATE DATABASE $DB_NAME;" > /tmp/script.sql
mysql --user=$MYSQL_USER --password="$MYSQL_PASS" < /tmp/script.sql
rm -rf /tmp/script.sql

But always get a error saying access denied for user root with NO PASSWORD, what I'm doing wrong? I need to do the same for PostgreSQL.

Best Answer

Both for MySQL and PostgreSQL you can specify your user and password in local config file. .my.cnf for MySQL and .pgpass for PostgreSQL. These files should be in your home directory (i.e. ~/.my.cnf).

.my.cnf:

[mysql]
user=user
password=password

.pgpass:

host:port:database:user:password

You can have a wildcard entry here, substituting any field for *******.

PS: DO NOT EVER SPECIFY A PASSWORD ON THE COMMAND LINE! This can be perfectly visible with ps if your system is not configured to not show processes that belongs to other users.

@thinice: If you want to create those files really secure you should do:

umask 077
touch .my.new.config
umask 022 # or whatever was your default

This way the file would be created with secure permissions from the start and no eavesdropper would have a chance leeching your password.

PostgreSQL will refuse to use the file with permissions higher the 0600 anyway.

Related Topic