Ubuntu MySQL ERROR 1698 (28000)

Ubuntu MySQL ERROR 1698 (28000): Access denied for user 'root'@'localhost'

 This is a quick note about MySQL root access error in Ubuntu even though the password is correct.

TLDR;
use sudo!
sudo mysql 

After installing MySQL in ubuntu (sudo apt install mysql-server), you can run secure installation utility command to set the root password.

sudo mysql_secure_installation utility

I'm not going to explain this in detail since this post is not about the installation but the error.

After setting the root password with the above util, the expectation is to use the below command and log into MySQL as root.

mysql -u root -p

For Ubuntu, this doesn't seem to be working. The reason is, in the new MySQL installation in Ubuntu uses something called Socket Peer-Credential Pluggable Authentication. This authentication mechanism authenticates clients that connect from the local host through the Unix socket file. Basically, it means the MySQL user also requires access to the Unix socket file itself. Unix socket is a file that OS uses to communicate with programs. 

Therefore, if you used sudo, it will grant access to the socket, so as to the MySQL itself. 

Accessing without sudo

To access without using sudo powers, we can create a new MySQL user with the name of the system user.

eg: let's say my system user is thilinaj

1. login to mysql from root (sudo mysql) and select mysql database.

USE mysql;

2. Create user

CREATE USER 'thilinaj'@'localhost' IDENTIFIED WITH auth_socket;

3. Grant permissions

GRANT ALL PRIVILEGES ON *.* TO 'thilinaj'@'localhost' WITH GRANT OPTION;

4. Reload previleges and exit

FLUSH PRIVILEGES;

exit

5. Next time you login, you can just type in mysql and you're in

The cool thing about the auth_socket plugin is you don't have to provide the password. That is because the authentication is taken care of by the OS auth socket.

Accessing root user without sudo

To use the root user without the sudo access, we can use the following workaround (not recommended).

1. Log in to mysql using sudo

sudo mysql -u root

2. Once logged in, switch to mysql database

USE mysql;

3. Update the authentication mechanism for root

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

4. Reload the privileges and exit.

FLUSH PRIVILEGES;

exit

5. Sometimes you may need to 

Next time when you log in, you wouldn't need to use the sudo keyword. 

Application users

When creating application users, you'll have to use the caching_sha2_password to authenticate.

eg: 

CREATE USER 'appuser'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'apppassword';

CREATE USER 'appuser'@'%' IDENTIFIED WITH caching_sha2_password BY 'apppassword';

Some of the common types of authentication plugins

Following are the types of authentication mechanisms available and a little bit of information about them.

• auth_socket 
• Uses OS authentication socket to authenticate the MySQL user. So, the username should be similar to the OS user. The mysql root can be only accessed by the root user of the OS.
• caching_sha2_password
• Uses a strong SHA 256 algorithm to hash the password. Also, it supports server-side caching for the passwords, so it has better performance. This is the default authentication plugin after MySQL 8.0.
• sha256_password
• Same as caching_sha2_password but without caching.
• mysql_native_password 
• Relies on SHA1 algorithm which is relatively weak. Therefore not recommended.

MySQL supports few more authentication plugins. You can find them on this page.

These plugins can be activated and deactivated in the configurations. MySQL documentation would provide more information on this.