How to Recover a Deleted MySQL \'root\' User and Password on macOS?

How to Restore Deleted 'root' User and Password for MySQL

Restoring a deleted MySQL 'root' user on macOS can be a daunting task, especially when there are no other users available to grant permissions. This article provides a step-by-step solution using the terminal, allowing you to recreate the 'root' user with a new password.

Steps to Restore 'root' User:

1. Configure my.cnf File:

   • Navigate to your MySQL configuration file (usually located at /usr/local/mysql/my.cnf).
   • Add the following line to the end of the [mysqld] section:

skip-grant-tables

2. Restart MySQL:

   • In the terminal, run the command:

sudo launchctl unload /Library/LaunchDaemons/com.mysql.mysql.plist
sudo launchctl load /Library/LaunchDaemons/com.mysql.mysql.plist

3. Log in to MySQL without a Password:

   • Open the terminal and type:

mysql

4. Delete the Existing 'root' User:

   • Run the following query:

DELETE FROM mysql.user 
WHERE  user = 'root' 
       AND host = 'localhost';

5. Create a New 'root' User:

   • Run this query to create a new 'root' user:

INSERT INTO mysql.user 
SET user = 'root', 
    host = 'localhost', 
    password = PASSWORD('newpassword'), 
    ... (Set all necessary privileges here)

6. Exit MySQL:

   • Type exit to exit the MySQL command line.

7. Remove 'skip-grant-tables' from my.cnf:

   • Go back to the my.cnf file and remove the previously added line:

skip-grant-tables

8. Restart MySQL Again:

   • Repeat step 2 to restart MySQL.

After completing these steps, you will have successfully restored the MySQL 'root' user and can log in using the new password specified in the query.

The above is the detailed content of How to Recover a Deleted MySQL \'root\' User and Password on macOS?. For more information, please follow other related articles on the PHP Chinese website!