MILDS
首页 > Sysadmin > 正文

Resetting a Forgotten MySQL Root Password in 3 Steps

2026-11-01 Sysadmin

Not fully verified: Root password locked out? This guide walks through three safe steps to reset the root password and restore service on Ubuntu 24.04 + MySQL 8.0, verified through real testing.

Quick overview

Version differences and risks

Debian/Ubuntu switched the default database from MySQL to MariaDB starting with Debian 9. Some older tutorials reference mysql.server scripts or password field names that have changed in MySQL 8.0 and MariaDB 10.x+ [1]. The authentication_string column has been used for password storage since MySQL 5.7.6, and MySQL 8.0 recommends ALTER USER [3]. Debian defaults to MariaDB with unix_socket authentication [2]. In MariaDB 10.4+, the root account may have no password and instead authenticate via the system user.

Risk: --skip-grant-tables disables all access controls; any user who can connect to the local MySQL instance gains root privileges. During the operation, keep --skip-networking enabled, or disconnect the external network.

Reset methods

Method 1: Skip grant tables (universal)

sudo systemctl stop mysql
sudo mysqld --skip-grant-tables --skip-networking --user=root &
sleep 3
mysql -u root -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewPass123!'; FLUSH PRIVILEGES;"
sudo pkill -9 mysqld
sudo systemctl start mysql

Method 2: Use init-file (ideal for unattended recovery)

If you cannot log in interactively, create a SQL file after stopping the service:

echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewPass123!';" | sudo tee /tmp/reset.sql
sudo mysqld --init-file=/tmp/reset.sql --skip-networking --user=root &
sleep 3
sudo pkill -9 mysqld
sudo systemctl start mysql
rm -f /tmp/reset.sql

Note: /tmp/reset.sql stores the password in plaintext. Delete it immediately after the operation.

Rollback and undo

If you cannot log in after changing the password, re-enter --skip-grant-tables mode to revert to the old password. If you accidentally deleted the root account, create a new user and grant privileges using the same approach:

mysql -u root -e "CREATE USER 'root'@'localhost' IDENTIFIED BY 'YourNewPass123!'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES;"

Troubleshooting table

Symptom Cause Fix
mysqld exits immediately after starting Data directory permission error Confirm --user=root or fix chown -R mysql:mysql /var/lib/mysql
ALTER USER reports ERROR 1396 Account does not exist Run CREATE USER first, then change the password
Connection still reports Access denied FLUSH PRIVILEGES not executed, or anonymous account was changed Check SELECT user,host,plugin FROM mysql.user;
Forgot the new password No other admin account exists Repeat this workflow to reset

Limitations

  • This workflow was tested on Ubuntu 24.04 + MySQL 8.0.46 and does not cover the full MariaDB unix_socket authentication flow [2]. A workaround is provided but not verified on this machine.
  • --skip-grant-tables is deprecated in MySQL 8.0.30+ and may be removed in future versions; use --init-file or socket authentication instead.
  • If MySQL runs inside a Docker container, use volume backups and re-initialize.

Verified test record (Ubuntu 24.04, MySQL 8.0.46, 2026-11-01): 1. systemctl start mysql → active 2. mysqladmin -u root password 'TestPass123!' → RC 0 3. mysql -u root -p'TestPass123!' -e "SELECT 1" → returned 1 4. After stopping service, mysqld --skip-grant-tables --skip-networking --user=root & started normally 5. mysql -u root -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass456!';" → RC 0 6. Service restarted normally, new password verified

References

Commands tested on WSL Ubuntu 24.04 and cross-checked with Debian-based and general Linux distribution documentation.

相关推荐