How to connect to MySQL or MariaDB root account without sudo

Connecting to a MySQL or MariaDB database as the root user typically requires elevated privileges using the sudo command on Linux systems. This security measure ensures that only authorized users can perform administrative tasks on the database. However, in some cases, such as automated scripts or specific application requirements, you might want to connect to the root account without the need for sudo.

When setting up MySQL or MariaDB, the installation process on some systems automatically assigns authentication to the auth_socket or unix_socket plugin. This means that database user authentication is tied to the system user account. For root, this implies that you have to use sudo to access the database.

To alter this behavior and allow for password authentication, you must change the plugin associated with the root account from auth_socket or unix_socket to mysql_native_password or the equivalent for MariaDB.

1. Log into MySQL or MariaDB with sudo to gain access as root.

   $ sudo mysql -u root -p
   Enter password:

2. Check the current authentication method for the root user.

   SELECT user,host,plugin FROM mysql.user WHERE user='root';

   If the plugin column shows auth_socket or unix_socket, it means you need sudo for authentication.

3. Change the plugin for the root user to use password authentication. For MySQL:

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

   For MariaDB:

   ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING 'YourNewPassword';

   Replace YourNewPassword with a strong password of your choice.

4. Flush the privileges to ensure the changes are applied.

   FLUSH PRIVILEGES;

5. Exit the database.

   EXIT;

6. Try logging into MySQL or MariaDB without sudo using the password.

   $ mysql -u root -p
   Enter password:

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

7. If you face any permission errors, ensure that the user permissions on the database folders are correct.

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

Remember to always back up your database before making any major changes, and avoid using the root user for day-to-day tasks or within applications, as it poses security risks.