How to Reset MySQL Root Password on Linux VPS (Ubuntu / Debian / AlmaLinux)

Why Root Password Lockouts Happen on Linux VPS

Losing the MySQL root administrative password blocks all database provisioning, phpMyAdmin root logins, and WP-CLI administration. Fortunately, Linux allows system administrators with root SSH access to bypass the user privilege table safely and assign a new administrative credential.

Step-by-Step Root Password Reset Procedure

Step 1: Stop the Running MySQL Daemon

sudo systemctl stop mariadb  # or sudo systemctl stop mysql

Step 2: Start MySQL in Safe Mode (Bypass Permissions)

Use the --skip-networking flag to ensure external hackers cannot connect while authentication is disabled:

sudo mysqld_safe --skip-grant-tables --skip-networking &

Step 3: Connect to MySQL Shell Without Password

mysql -u root

Step 4: Reload Privileges and Update Root Password

Inside the MySQL/MariaDB command prompt, execute the following SQL commands:

FLUSH PRIVILEGES;

-- For MySQL 8.0 / 8.4:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourNewStrongPassword123!';

-- For MariaDB 10.4+:
ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('YourNewStrongPassword123!');

FLUSH PRIVILEGES;
EXIT;

Step 5: Terminate Safe Mode and Restart Normal Service

# Kill safe mode daemon
sudo killall -v mysqld

# Start standard database service
sudo systemctl start mariadb  # or sudo systemctl start mysql

# Test login with new password
mysql -u root -p

Fixing Authentication Plugin Mismatches (auth_socket vs mysql_native_password)

Modern Ubuntu installations default the MySQL root user authentication method to auth_socket, allowing the Linux root user to log in without a password via local UNIX sockets while rejecting TCP/phpMyAdmin password logins. To enable dual password authentication:

# Configure MySQL 8.0 password authentication
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'StrongPassword123!';

# Create dedicated administrative superuser for phpMyAdmin
CREATE USER 'dba_admin'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'dba_admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Securing MySQL Daemon with mysql_secure_installation

After restoring administrative access, run the native hardening utility to remove anonymous accounts and disable remote root logins:

sudo mysql_secure_installation

Resetting MySQL Root Password on cPanel & WHM Servers

If you manage a cPanel & WHM dedicated server or VPS, you can reset the MySQL root password directly from the WHM interface without stopping the daemon:

1. Log in to WHM (WebHost Manager) as root.
2. In the left search bar, type MySQL Root Password (under the SQL Services section).
3. Enter your new password and click Change Password. WHM automatically updates the internal password and synchronizes service credentials across cPanel.

Configuring ~/.my.cnf for Passwordless Root CLI Administration

Create a secure local configuration file in your root home directory (/root/.my.cnf) so you can run administrative commands without typing passwords in shell history:

[client]
user = root
password = YourSecurePassword123!

Secure the file: chmod 600 /root/.my.cnf. Now, typing mysql connects instantly and securely.

Frequently Asked Questions

Troubleshooting Access Denied for User ‘root’@’localhost’

If you receive Access denied for user 'root'@'localhost' (using password: YES) after resetting your password, verify that the user record in the mysql.user table has plugin = 'mysql_native_password' or caching_sha2_password rather than auth_socket.

Following this emergency password reset procedure ensures that administrators can regain full database access safely without corrupting production tables or risking unauthorized access.

Remember to test SSH key authentication and keep root administrative credentials backed up securely in a password vault to prevent future lockout scenarios on Linux cloud instances.

Maintaining isolated administrative accounts with dedicated SSH key authentication guarantees both emergency recovery capabilities and robust server security for production web applications.