Handling Error 1698 During MySQL Login

Users may encounter ERROR 1698 (28000): Access denied for user 'root'@'localhost' when trying to log into the MySQL database with the root user. This error is often encountered on systems like Ubuntu, where MySQL utilizes the Unix auth_socket plugin for authentication by default.

Background: The auth_socket plugin relies on system user credentials for authentication. By querying the mysql.user table, you can verify that the root user is registered with this plugin:

SELECT User, Host, plugin FROM mysql.user;

Possible Solutions:

Option 1: Configure root User to Use mysql_native_password Plugin
This method involves setting the root user to use the more traditional mysql_native_password plugin:

UPDATE user SET plugin='mysql_native_password' WHERE User='root';
FLUSH PRIVILEGES;
sudo service mysql restart

Option 2: Create a New Database User (Recommended)
An alternative approach is to create a new database user using your system username. This provides a more manageable and secure option:

CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY 'YOUR_PASSWD';
GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
FLUSH PRIVILEGES;
sudo service mysql restart

Note that with Option 2, you will connect to MySQL using your system username:

mysql -u YOUR_SYSTEM_USER

Additional Considerations:

• In MySQL versions 8.x.x, the auth_socket plugin may have been replaced by caching_sha2_password. Consult MySQL documentation for the appropriate authentication plugin and login method.
• In Debian 9 (Stretch), the auth_socket plugin is renamed to unix_socket. Adjust the SQL command accordingly.