Skip to main content

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

Ubuntu MySQL CLI



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.


Labels:

Comments

  1. AddisonStewartSeptember 26, 2024 at 3:47 AM

    The "ERROR 1698 (28000)" in Ubuntu's MySQL usually occurs when access is denied to the 'root' user due to authentication via Unix socket rather than a password. By default, MySQL in Ubuntu often requires using sudo with MySQL commands to access the root account. To fix this, either update the authentication method or use sudo when logging in. Handling access denied for user root localhost properly ensures smoother MySQL administration in Ubuntu environments.

    ReplyDelete
  2. naniJuly 31, 2025 at 7:49 PM

    Nice Article!

    Thanks for sharing with us 🙂

    ucat coaching

    ReplyDelete

Post a Comment

Popular posts from this blog

VLC skins

It's been a while from my first blog post, so today I thought to make a blog post about skins in VLC player. I think you all are familiar with VLC player if not, here's the link for the website www.videolan.org/vlc/index.htm l In brief, VLC player is a free and opensource player that can play almost all the types of media formats. Even though it is a such a nice player, it looks kind of old and rusty, like the windows classic look. But you can make it prettier by just adding some new skins to it.  You can download skins for the player from here http://www.videolan.org/vlc/skins.php Then place the downloaded skin file (.vlt file) in the " skin " folder in the installation directory.           eg:- in windows:    C:\Program Files\VideoLAN\VLC\skins           or in linux systems:  ~/.local/share/vlc/skins Then, open the VLC player and go to the preferences. ...
2 comments
Read more

Java, how to create a list with a single element

 I wanted to create a Java List with a single element. Yet, I wanted to add more elements later. So, I was looking for a couple of ways to do this. So far there are multiple elegant ways to create a list with a single element with a one-liner. Not so much for a modifiable list though. Here's what I gathered so far. Followings are a few ways of creating a list with strictly a single entry. Can't add more elements. 1. Collections.singletonList() This returns an immutable list that cannot be modified or add more elements. // An immutable list containing only the specified object. List<String> oneEntryList = Collections. singletonList ( "one" ) ; oneEntryList.add( "two" ) ; // throws UnsupportedOperationException 2.  Arrays.asList() This returns a fixed-size list consisting of the number of elements provided as arguments. The number of elements provided would be an array hence the size is fixed to the length of the array. // Returns a fixed-size list List...
Post a Comment
Read more

Find command: How to find and delete 0 bytes files in Linux

In Linux, "find" command is a powerful command that we can use not only to find files. I've used it for different requirements, so thought of posting some of the convenient usages of find commands in a series of posts. For some application issue, I've got one of my directories in Ubuntu system swarmed with empty files with 0 bytes. Some of the files got data in them but some of them were empty. I just wanted to delete the empty files and it wasn't possible to identify them by file names or dates. So find command comes to rescue. find . -size 0 -type f  If I needed to isolate the files by file extension, we can use something like this. find . -size 0 -type f -name "*.tar" You can get the file count by piping it to wc command. find . -size 0 -type f -name "*.tar" | wc -l The awesome thing about the find command is you can use it for deleting files just by adding the -delete flag to the command. find . -size 0 -type f -name "*.tar" -dele...
Post a Comment
Read more