Connect to MySQL through command line without using root password?

Question

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.

Do some debugging (-: Try `echo "$MYSQL_PASS"` before you pass it to the `mysql` line. Does it have the correct password? — KM., Jun 15 '12 at 19:46
**-h** is missing. It is also not a good idea to put passwords on a command-line, since they will be visible for everyone calling `ps wwaux`. — Nils, Jun 15 '12 at 20:02

score 27 · Accepted Answer · edited Sep 28 '15 at 23:51

27

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.

edited Sep 28 '15 at 23:51

theherk

105
4

answered Jun 15 '12 at 20:08

kworr

1,055
8
14

1

For mysql the mysql client you can point at a specific config file with `--defaults-extra-file=filename`. This might be useful if you want to place it somewhere non-standard, or just create a temporary file. Is suspect their is a similar option with PostgreSQL, but I am not familiar with that. – Zoredache Jun 15 '12 at 20:09
1

And for the love of happy gilmore's shorts chmod that file `0600` – thinice Jun 15 '12 at 20:12
@kworr I'll aready try that but my MySQL doesn't start changing this parameters at /etc/my.cnf :( didn't know the cause. I'll try again later – ReynierPM Jun 15 '12 at 21:21
It's not the /etc/my.cnf. Updating answer. – kworr Jun 16 '12 at 05:33
@kworr I try your solution but doesn't work at all. The DB is created by empty and doesn't appear in phpMyAdmin or any other GUI tool. I set the data directory in /etc/my.cnf to datadir=/data/var/lib/mysql and set permissions to 0755 at /data/var/lib/mysql and also owner to mysql:mysql, why the database is empty? where is the problem? – ReynierPM Jun 18 '12 at 14:39
@kworr and for the PostgreSQL sentence, if I'll create the DB and not connect to, what's the correct sentence? – ReynierPM Jun 18 '12 at 14:43
(1) Why you are changing datadir? MySQL server process has already populated his own one, you can't create your database in another directory. (2) I can't decipher what you say about PostgreSQL. Showing some logs with errors would help. – kworr Jun 18 '12 at 14:59
@kworr (1) I want the directory data in another external HDD and not in system HDD for that reason I change the datadir. (2) For the PostgreSQL part, you said before I need to create a .pgpass file but this .pgpass sentence contains a database parameter, If I want to create a database like I does with MySQL and not to connect, what will be the correct command? – ReynierPM Jun 18 '12 at 15:10
(1) That's beyond the scope of the question but if you want your datadir reside on another disk you should move it there when server is not running. (2) And again .pgpass file is only about authentication, it's not about creating database. You should create db with CREATE DATABASE. – kworr Jun 18 '12 at 20:28
What if there are is more than one user/password combination ? I would look for a way to supply the answer to -p in the command... -uuser -p db. I I tried a HERE doc (< – sdfor Oct 27 '15 at 15:29

score 4 · Answer 2 · answered Feb 19 '15 at 13:25

4

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!

answered Feb 19 '15 at 13:25

Artistan

151
3

http://blog.geekslikeshinythings.com/2015/02/mysql-cli-password-mysqlconfigeditor.html – Artistan Feb 19 '15 at 13:26
1

Thanks for sharing! There's just one gotcha from this approach. The parameter --login-path has to be the first parameter of the call or else you are going to get the "unknown variable 'login-path=local'" message. – André Augusto Feb 22 '16 at 19:44

score 2 · Answer 3 · answered Jun 16 '12 at 12:02

2

Don't put quotes around the password because if you do the quotes are considered to be part of the password.

answered Jun 16 '12 at 12:02

John Gardeniers

27,262
12
53
108

score 2 · Answer 4 · answered Jun 22 '12 at 12:14

MYSQL_USER="root"
MYSQL_PASSWORD="PASSWORD"
DBNAME="DB_NAME"

mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "CREATE DATABASE $DBNAME;" 2> /tmp/error1

STATUS=$? 
if [ $STATUS -eq 0 ];
then 
    echo -e "Database '$DBNAME' is created"
elif (grep -i "^ERROR 1007" /tmp/error1 > /dev/null);
then
    echo -e "Database '$DBNAME' already exists"
else
    echo -e "Failed to create database '$DBNAME'"
fi

rm -r /tmp/error1

This will do the trick Thanks

score 1 · Answer 5 · answered Jun 16 '12 at 01:08

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

be sure how you write your pass and it doesn't escape

--defaults-extra-file= is a good thing (tm) (c)

score 1 · Answer 6 · answered Jun 26 '19 at 12:03

Similar question exists on StackOverflow.

To summarize my answer from there.

You can export MYSQL_PWD=yourverysecretpassword.

The upside of this method over using a configuration file is that you do not need a separate configuration file to keep in sync with your script. You only have the script to maintain.

There is no downside to this method.

The password is not visible to other users on the system (it would be visible if it is on the command line). The environment variables are only visible to the user running the mysql command, and root.

The password will also be visible to anyone who can read the script itself, so make sure the script itself is protected. This is in no way different than protecting a configuration file. You can still source the password from a separate file if you want to have the script publicly readable (export MYSQL_PWD=$(cat /root/mysql_password) for example). It is still easier to export a variable than to build a configuration file.

E.g.,

$ export MYSQL_PWD=xoF3mafn5Batxasdfuo
$ mysqldump -u root mysql | head
-- MySQL dump 10.13  Distrib 5.6.23, for Linux (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version   5.6.23
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

Connect to MySQL through command line without using root password?

6 Answers6