How to access MySQL or MariaDB as root without using sudo

Accessing the MySQL or MariaDB root account often requires using the sudo command, especially on Linux systems. This is a default security measure to prevent unauthorized access to critical database operations. However, in certain cases, you may need to access the root account directly without the requirement of sudo, such as for application scripts or non-interactive database management tasks.

The default configuration in many MySQL or MariaDB installations ties root authentication to the system account using the auth_socket or unix_socket plugin. This setup forces root users to authenticate through the system, which means using sudo is mandatory. To bypass this and allow password-based login, you need to reconfigure the root authentication method.

By changing the authentication plugin from auth_socket or unix_socket to mysql_native_password, root access can be allowed through a password instead of system-based authentication. This allows you to log in directly to MySQL or MariaDB without needing sudo, making it useful for automated processes or external applications that require root-level access.

1. Open a terminal window.
2. Log in to MySQL or MariaDB using sudo as the root user.

   $ sudo mysql -u root -p
   Enter password:

   You will need to use the root password for this.

3. Check the current authentication plugin for the root user.

   mysql> SELECT user, host, plugin FROM mysql.user WHERE user = 'root';
   +------+-----------+-----------------+
   | user | host      | plugin          |
   +------+-----------+-----------------+
   | root | localhost | auth_socket     |
   +------+-----------+-----------------+

   If the plugin column shows auth_socket or unix_socket, you need to change it to allow password authentication.

4. Change the plugin for the root user to mysql_native_password.

   mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'YourNewPassword';

   Replace YourNewPassword with a strong password of your choice.

5. Flush the privileges to apply the changes.

   mysql> FLUSH PRIVILEGES;

6. Exit the database.

   mysql> EXIT;

7. Try logging into MySQL or MariaDB without using sudo.

   $ mysql -u root -p
   Enter password:

   You should now be able to access the database without using sudo.

8. If you face any permission errors, check and adjust the file permissions.

   $ sudo chown -R mysql:mysql /var/lib/mysql/