hasanghaforian hasanghaforian - 9 months ago 36
MySQL Question

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

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
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?

Answer Source

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

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