How to connect to MySQL or MariaDB root account without sudo

You can only connect to root account for MySQL and MariaDB if you are logged in to the system as root user or by using sudo. If not, you will get an access denied error even if you entered the correct password for your root MySQL account.

$ mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

1. Connect to MySQL / MariaDB as root or other administrative user account.

   $ sudo mysql -u root -p
   [sudo] password for user: 
   Enter password: 
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 45
   Server version: 8.0.22-0ubuntu0.20.10.2 (Ubuntu)
   
   Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql> 

2. Configure root user to use mysql_native_password authentication plugin.

   Amysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY //;
   Query OK, 0 rows affected (0.01 sec)

   Change localhost and password accordingly.

3. Reload the grant tables for MySQL / MariaDB server.

   mysql> FLUSH PRIVILEGES;
   Query OK, 0 rows affected (0.00 sec)

4. Log in again to root MySQL / MariaDB account from normal system account to test.

   $ mysql -u root
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 46
   Server version: 8.0.22-0ubuntu0.20.10.2 (Ubuntu)
   
   Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql>