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:


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, 2012 at 19:46
  • Can you login as usual without the script?
    – qweet
    Jun 15, 2012 at 19:55
  • 4
    -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, 2012 at 20:02

6 Answers 6


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).





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.

  • 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, 2012 at 20:09
  • 1
    And for the love of happy gilmore's shorts chmod that file 0600
    – thinice
    Jun 15, 2012 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, 2012 at 21:21
  • It's not the /etc/my.cnf. Updating answer.
    – kworr
    Jun 16, 2012 at 5: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, 2012 at 14:39
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!

  • 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. Feb 22, 2016 at 19:44

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



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

rm -r /tmp/error1 

This will do the trick Thanks


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)


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.


$ 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 NAMES utf8 */;

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .