MySQL Error – ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

bashMySQLubuntu-18.04

I have a bash script that's supposed to talk to a MySQL database like this:

mysql --login-path=local  -e "DROP SCHEMA mydatabase;"

When the script tries to connect to MySQL, it gives the error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

System info:

ubuntu server 18.04
mysql 5.7.28

MySQL Install Info:

Apt installed mysql, ran mysql_secure_installation

Set root password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';
FLUSH PRIVILEGES;

Created ~/.my.cnf file:

[local]  
user=root  
password=mypassword

I'm new to MySQL and imagine the solution is simple, but I'm having a hard time figuring it out. How can I make this work without changing the bash script?

Best Answer

I was able to make it work using the config editor.

From terminal:

mysql_config_editor set --login-path=local --host=localhost --user=root --password

Related Solutions

mysqldump – How to Use in Cron Job Without Root Password

In order to connect to the mysql server you must provide credentials. You can specify them in a configuration file, pass them via the command line, or simply create account that doesn't require credentials.

Of course the no-password option should never be used, the pass-by command line isn't great because anyone who can run ps may be able to see the command line.

The recommended option is to create a mysql configuration file with the credentials in it and then protect that file with filesystem permissions so only your backup user can access it.

You being able to login to the mysql server while logged in interactively as root seems to suggest that you either don't have a root password set, or that you have a configuration file that is not being found by your script. If you have a .my.cnf you may need to manually point to it. If your root account doesn't have a password set then I would strongly encourage you to fix that.

Update (2016-06-29) If you are running mysql 5.6.6 or greater, you should look at the mysql_config_editor tool that allows you to store credentials in an encrypted file. Thanks to Giovanni for mentioning this to me.

Linux – Connect to MySQL through command line without using root password

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.

Best Answer

Related Solutions

mysqldump – How to Use in Cron Job Without Root Password

Linux – Connect to MySQL through command line without using root password

Related Topic