How to enable MySQL or MariaDB root login without sudo

MySQL and MariaDB can tie local root access to an operating-system account. A database password enables local root login without sudo when socket authentication currently controls access.

Socket authentication trusts the operating-system identity of the process connecting through the local Unix socket. MySQL uses auth_socket for this mapping, while MariaDB uses unix_socket and can include a password plugin as an alternative. A socket-authenticated account does not need a default database password.

A running local Linux server and working sudo access are prerequisites. Password login gives anyone who knows the password the database root account's full privileges, so a separate user is preferable for applications or shared automation. The change affects 'root'@'localhost'; it does not require remote root access.

1. Open the database shell as the operating-system root user.

   $ sudo mysql
   ##### snipped #####
   mysql>

   On MariaDB, run sudo mariadb instead, and replace mysql with mariadb in the login step.

   ERROR 1045 here means root already uses a database password, so a forgotten one belongs to How to reset MySQL or MariaDB root password. ERROR 2002 means the server is not running or the socket cannot be reached, which a password cannot fix, so start the service first.

2. Check how 'root'@'localhost' currently logs in.

   mysql> SHOW CREATE USER 'root'@'localhost'\G
   *************************** 1. row ***************************
   CREATE USER for root@localhost: CREATE USER `root`@`localhost` IDENTIFIED WITH 'auth_socket' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
   1 row in set (0.02 sec)

   auth_socket means the change applies. MariaDB usually shows IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket instead, where unix_socket means the same thing.

3. Set a password for root.

   Every new root login will need this password, including logins through sudo. A wrong or lost password can lock out new sessions, so keep this session open until the next step succeeds. NEW_PASSWORD is a placeholder for a strong, unique password.

   mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'NEW_PASSWORD';
   Query OK, 0 rows affected (1.54 sec)

   MariaDB uses a shorter statement, which also removes the unix_socket login.

   MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_PASSWORD';
   Query OK, 0 rows affected (0.002 sec)

4. Log in as root without sudo from a second terminal under your normal account.

   $ mysql -u root -p -e "SELECT USER(), CURRENT_USER();"
   Enter password:
   +----------------+----------------+
   | USER()         | CURRENT_USER() |
   +----------------+----------------+
   | root@localhost | root@localhost |
   +----------------+----------------+

   Both values should read root@localhost, and the first session can then be closed with exit. To return to sudo login, run ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket; on MySQL or ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket; on MariaDB from a root session.