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.sqlBut 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.
- Can you login as usual without the script? –
-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. – N
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:passwordYou 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 defaultThis 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.
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. – ZoredacheJun 15, 2012 at 20:09
And for the love of happy gilmore’s shorts chmod that file 0600 – thiniceJun 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 – ReynierPMJun 15, 2012 at 21:21
It’s not the /etc/my.cnf. Updating answer. –
@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? – ReynierPMJun 18, 2012 at 14:39
- (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, 2012 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? – ReynierPMJun 18, 2012 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. – kworrJun 18, 2012 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 (<<Label ) but that does not work for a password – sdforOct 27, 2015 at 15:29
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!
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é AugustoFeb 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.
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
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.sqlbe sure how you write your pass and it doesn’t escape
–defaults-extra-file= is a good thing ™ (c)
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 */;source : https://serverfault.com/questions/399262/connect-to-mysql-through-command-line-without-using-root-password