Mysql Skip Grant Tables To Reset Password [Solved]

Table of Contents

Immediate Fix: Resetting Password via Skip-Grant-Tables

If you are locked out of your MySQL server, the most effective recovery method is to restart the service without the privilege-checking system enabled. Follow these steps precisely to regain access.

Step 1: Stop the MySQL Service

You must stop the running database instance before you can restart it in recovery mode. Use the following command:

sudo systemctl stop mysql

Step 2: Restart MySQL with Skip-Grant-Tables

Start the MySQL daemon manually with the --skip-grant-tables option. This bypasses the authentication process.

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

Note: We include --skip-networking to prevent external connections while the database is wide open.

Step 3: Connect and Reset the Password

mysql -u root

# Inside the MySQL prompt:
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewSecurePassword';
EXIT;

Step 4: Return to Normal Operation

Kill the temporary process and start the service normally to apply the changes and re-enable security.

sudo kill $(sudo lsof -t -i:3306)
sudo systemctl start mysql

Technical Explanation

The --skip-grant-tables option is a startup flag that instructs the MySQL engine to ignore the “mysql” system database, which contains the user permission tables. When this flag is active, the server does not check passwords for any connection.

Under normal circumstances, MySQL reads the user, db, and host tables to verify credentials. By skipping these, you gain root-level access by default. However, because the grant tables aren’t loaded, you must run FLUSH PRIVILEGES; before the ALTER USER command will function.

This method is intended for emergency recovery only. Running a production server with this flag enabled exposes your data to anyone with local or network access to the server port.

Alternative Methods

Depending on your environment or MySQL version, you might prefer different approaches to password recovery. Use the table below to compare your options.

Method	Complexity	Best For
–init-file	Medium	Automated environments where stopping the service is allowed.
SET PASSWORD Command	Low	Users who still have access to a privileged account.
OS Package Manager	High	Situations where the underlying system files are corrupted.

Using an --init-file is often considered safer than --skip-grant-tables because it executes specific commands during startup without opening the server to unauthorized connections during the maintenance window.