Ubuntu - MySQL Can't set password for root account, even though all command are successful

After a fresh install of MySQL or MariaDB on an Ubuntu server, you can run the "mysql" command line without being asked for your password, even if you have successfully changed the password. While you still cannot access root account from other software like Navicat, php ... the error encountered is "Access denied for user 'root'@'localhost' (using password: YES)"

What happened ?

you can run the "mysql" command without any password because by default, the root account is configured to login via the AUTH_SOCKET plugin. So MySQL Command-Line Client can always connect successfully without being asked for a password while other software can't connect even if the correct password is entered.

Security issue?

In a way, the mysql root account should only be accessed from the server, and a person who already has root privileges with Ubuntu can obviously optionally reset the mysql root account password. So this default configuration should not be a problem.

However, if you are a strict person or are familiar with other operating systems like Centos, you will be very uncomfortable with this configuration. If so, bring it back to the same as mysql on Centos by following the steps below.

Solving problems

Access the MySQL command-line. 

mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 8.0.21-0ubuntu20.0 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Switch to the 'mysql' database

use mysql;

Verify current status

select Host,User,authentication_string,plugin from mysql.user;

You can see: The MySQL ROOT account has no password configured and using the authentication plugin named auth_socket.

Now configure the ROOT account to use mysql_native_password plugin

ALTER USER root@localhost IDENTIFIED WITH mysql_native_password;

Set new password for root account

ALTER USER root@localhost IDENTIFIED BY 'newpassword';

Flush privileges

FLUSH PRIVILEGES;

exit;

Done

Problem solved !