How Can I Recover My Deleted MySQL \'root\' User and Password on macOS MAMP?

Restoring the Deleted 'root' User and Password for MySQL on macOS

MAMP users may accidentally delete the 'root' user on a local MySQL setup. Without additional users, regain access to MySQL can be challenging. Here's a simple solution for macOS:

1. Add 'skip-grant-tables' to my.cnf: Locate the my.cnf file in MAMP's MySQL directory and add the following line to the [mysqld] section:

   skip-grant-tables

2. Restart MySQL: Restart MAMP's MySQL service.
3. Connect to MySQL without a password: In Terminal, type mysql and hit enter. You should be able to connect without a password.

4. Execute the following commands in MySQL:

   DELETE FROM mysql.user 
   WHERE  user = 'root' 
       AND host = 'localhost';
   
   INSERT INTO mysql.user 
   SET user = 'root', 
    host = 'localhost', 
    password = Password('whatevernewpassword'), 
    Select_priv = 'y',
    Insert_priv = 'y',
    Update_priv = 'y',
    Delete_priv = 'y',
    Create_priv = 'y',
    Drop_priv = 'y',
    Reload_priv = 'y',
    Shutdown_priv = 'y',
    Process_priv = 'y',
    File_priv = 'y',
    Grant_priv = 'y',
    References_priv = 'y',
    Index_priv = 'y',
    Alter_priv = 'y',
    Show_db_priv = 'y',
    Super_priv = 'y',
    Create_tmp_table_priv = 'y',
    Lock_tables_priv = 'y',
    Execute_priv = 'y',
    Repl_slave_priv = 'y',
    Repl_client_priv = 'y',
    Create_view_priv = 'y',
    Show_view_priv = 'y',
    Create_routine_priv = 'y',
    Alter_routine_priv = 'y',
    Create_user_priv = 'y',
    Event_priv = 'y',
    Trigger_priv = 'y',
    Create_tablespace_priv = 'y';

5. Exit from MySQL: Type exit to close the MySQL prompt.
6. Remove 'skip-grant-tables' from my.cnf: Edit my.cnf and remove the 'skip-grant-tables' line.
7. Restart MySQL: Restart MAMP's MySQL service.

Now, you should be able to log into MySQL with the new 'root' password you specified.

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