Mysql – Cannot change MySQL root password

MySQLpassword

I have run the following commands found here.

shell> mysql
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass'

But when I go to login as root, I can type any string as a password, and it will still let me login.

How can I enforce a password for root, and not allow anonymous users by simply typing mysql

Best Answer

The following worked for me:

I ran through the command line wizard to set this up:
shell> mysql_secure_installation

Then entered the following:
shell> mysql
ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

Credit goes to this question/answer.

Related Solutions

Mysql – Cannot change root access in MySQL to %

SHOW GRANTS only gives you back whatever you are connected as, which was root@localhost.

root@localhost and root@'%' and completely different users. Just do

SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR root@'%';

root@localhost lets you connect from the DB Server via mysql.sock (the socket file)
root@'%' lets you connect via TCP/IP, but you must explicitly connect with that protocol. Otherwise, mysqld connects you as root@localhost. This is true even if you attempted to connect using 127.0.0.1.

SUGGESTION #1

For the sake of security, you should use netblocks instead of '%'. For example, if you web servers at 10.1.2.20, 10.1.2.30, and 10.1.2.40, you should create

GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.1.2.%'
IDENTIFIED BY PASSWORD '*fdfgdgdggfggfgfg' WITH GRANT OPTION;

instead of having a remote root.

SUGGESTION #2

If you really want the remote root, here is what you do

UPDATE mysql.user SET host='%'
WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;

That will do what you want, but remote root is not recommended.

SUGGESTION #3

Since root@'%' already exists, then remove the root@localhost.

DELETE FROM mysql.user WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;

This will leave root@'%' as the only root user, but you must connect explicitly with TCP/IP.

Mysql – How to disable anonymous login

I figured it out. While /etc/mysql/my.cnf didn't have a password keypair stored, there was a password stored in /root/.my.cnf.

As soon as I commented out the password in /root/.my.cnf, I was not allowed to log in without a password (which is what I expected).