How to Recover My MySQL \'root\' User and Password on MAMP/macOS?

Restoring the 'root' User and Password for MySQL on MAMP/macOS

If you've accidentally deleted the 'root' user in your local MAMP/MySQL setup on macOS, panic not! Here's a quick and dirty solution that will restore your access.

To begin, you'll need an administrator account with SysAdmin rights. Once you have that, follow these steps:

1. Open /Applications/MAMP/conf/my.cnf in a text editor.
2. Under the [mysqld] section, add the line skip-grant-tables.
3. Save the changes and restart MySQL by running the following command in Terminal:

sudo /Applications/MAMP/Library/bin/mysql.server restart

4. Once MySQL is restarted, run the following command in Terminal:

mysql -u root

5. You should now be able to log in to MySQL without a password.
6. Run the following SQL commands to recreate the 'root' user:

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

INSERT INTO mysql.user 
SET user = 'root', 
    host = 'localhost', 
    password = Password('your-new-password'), 
    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';

7. Exit MySQL and restart it again to apply the changes:

exit
sudo /Applications/MAMP/Library/bin/mysql.server restart

You should now have regained access to your MySQL database using the 'root' user and your new password.

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