Avoiding to add username and password in each command for running MySQL from script

Question

I know I can do some thing like this in script:

mysql -u root -pPassword mydb -e "select * from foo"

In my script I have a sequence of commands which some of them are periodically related to MySQL and others are independent (so I cannot use Here Documents). It seems it is necessary to add -u root -pPassword part in all commands to avoid getting this error:

 Access denied for user 'user'@'host' (using password: NO)

But is annoying. Is there a way to avoid adding -u root -pPassword part to all commands?

Why not write a wrapper shell script or function that properly sets all the parameters for mysql? — tadman
– tadman, Commented Oct 25, 2016 at 17:30
@tadman Really I did not think to that, because I'm beginner in bash script. — hasanghaforian
– hasanghaforian, Commented Oct 25, 2016 at 17:31
I usually make a script called rmysql that connects as root and looks something like: mysql -u root -pXXXXX $* so you can get in quickly in case of emergency. Lock that script down mode 0700 to avoid others seeing the password. — tadman
– tadman, Commented Oct 25, 2016 at 17:33

Bill Karwin · Accepted Answer · 2016-10-25 17:30:46Z

2

Create a file in your home directory called .my.cnf and add these lines to it:

[client]
user = root
password = <yourpassword>

(Obviously where I wrote "<yourpassword>" you would write your actual password.)

Then you can run mysql commands (or some others like mysqldump or mysqladmin) without specifying user or password.

mysql mydb -e "select * from foo"

If you want to use a different file besides $HOME/.my.cnf you can specify it on your command-line.

mysql --defaults-extra-file=myspecial.cnf mydb -e "select * from foo"

Be careful to change the permissions on that file so it can't be read by other people:

chmod 600 $HOME/.my.cnf

Read more about using option files: http://dev.mysql.com/doc/refman/5.7/en/option-files.html

answered Oct 25, 2016 at 17:30

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

anubhava · Accepted Answer · 2016-10-25 17:36:09Z

2

You can use login-paths via mysql_config_editor command:

$> mysql_config_editor set --login-path=local --host=localhost --user=myuser --password
Enter password: <Password is prompted to be inserted in a more secure way>

Then login from your scripts as:

mysql --login-path=local --database=mydb

As per linked manual:

The best way to specify server connection information is with your .mylogin.cnf file. Not only is this file encrypted, but any logging of the utility execution will not expose the connection information. Thus, no user names, passwords, ports, etc. are visible in the log. This is the preferred method for using MySQL Utilities to connect to servers.

answered Oct 25, 2016 at 17:36

anubhava

790k67 gold badges603 silver badges671 bronze badges

1 Comment

Bill Karwin Over a year ago

+1 this is a good idea too, if you use MySQL 5.6 or later, and you want to store the passwords in an encrypted format. Since the OP was about to put plaintext passwords in their bash script, I figured that wasn't a priority.

riteshtch · Accepted Answer · 2016-10-25 17:31:37Z

1

You could just create a wrapper shell script or an alias like this:

mysql aliased to /path/to/mysql -u root -pPassword mydb

Here is the alias command:

$alias mysql="/path/to/mysql -u root -pPassword mydb"
$

answered Oct 25, 2016 at 17:31

riteshtch

8,7994 gold badges28 silver badges39 bronze badges

Collectives™ on Stack Overflow

Avoiding to add username and password in each command for running MySQL from script

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related