Home >Database >Mysql Tutorial >How Can I Recover My Deleted MySQL \'root\' User and Password on macOS MAMP?

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

Susan Sarandon
Susan SarandonOriginal
2024-12-04 09:35:11773browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn