How to access MySQL or MariaDB as root without using sudo

Password-based access to the MySQL or MariaDB root account can be necessary for local automation, non-interactive maintenance, or console sessions where administrative SQL access is needed without switching to the operating system root user.

Many Linux package installs bind the database root account to local socket authentication instead of a reusable database password. MySQL commonly uses the auth_socket plugin for that mapping, while MariaDB commonly allows root@localhost to authenticate through unix_socket on Debian or Ubuntu packages. In both cases, the server trusts the operating system user identity that connects through the local socket file.

On current Ubuntu 24.04 packages, MySQL still uses auth_socket for a passwordless local root account, while MariaDB can expose a mixed definition that includes both password and unix_socket authentication until the account is rewritten explicitly. Current MySQL releases use caching_sha2_password for password-based authentication, while MariaDB commonly switches root to mysql_native_password. Keep root limited to 'localhost', use a strong password, and prefer a separate administrative user when shared automation does not need the built-in root account.

1. Open a local shell that has sudo access.

   Socket-authenticated root logins usually require the first session to start as the operating system root user.

2. Start the database client as the operating system root user.

   $ sudo mysql
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 11
   Server version: 8.0.45-0ubuntu0.24.04.1 (Ubuntu)
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql>

   If the client binary is mariadb instead of mysql, run sudo mariadb.

3. Inspect the current authentication definition for 'root'@'localhost'.

   mysql> SHOW CREATE USER 'root'@'localhost'\G
   *************************** 1. row ***************************
   CREATE USER for root@localhost: CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket

   On MySQL packages this commonly shows IDENTIFIED WITH 'auth_socket'. On MariaDB 10.4 and later, SHOW CREATE USER is more reliable than checking only mysql.user.plugin because Debian or Ubuntu packages can keep unix_socket active even when the plugin column shows mysql_native_password.

4. Change the root account to password authentication.

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

   If the server is MariaDB, use:

   mysql> ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('NEW_PASSWORD');
   Query OK, 0 rows affected (0.00 sec)

   Switching root away from socket-only authentication makes the account usable from any local shell that knows the password, so keep the password out of scripts, history files, and shared option files.

   Related: How to create a user in MySQL or MariaDB

5. Verify the updated account definition before leaving the SQL shell.

   mysql> SHOW CREATE USER 'root'@'localhost'\G

   MySQL should show caching_sha2_password. MariaDB should no longer show OR unix_socket after the change.

6. Exit the database shell.

   mysql> exit
   Bye

7. Confirm that password authentication works without sudo.

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

   If the installed client is mariadb, replace mysql with mariadb.

8. Check that root remains limited to local hosts.

   $ mysql -u root -p -e "SELECT user, host FROM mysql.user WHERE user = 'root';"
   Enter password:
   +------+-----------+
   | user | host      |
   +------+-----------+
   | root | localhost |
   +------+-----------+

   Remove or lock any root entry for '%', an IP address, or another hostname before relying on password-based root access.