How to add 'root' MySQL user back on MAMP?

Question

On PhpMyAdmin, I removed 'root' user by mistake. I was also logged in as 'root'. How can I add the user 'root' back, on MAMP?

Answer 1

MySQL's suggested procedures

Assuming you went with a generic install of MySQL, my procedure for MAMP is:

1. Edit your my.cnf, probably located at: /usr/local/my.cnf, adding the lines:

skip-grant-tables
skip-networking

2. Restart mysqld

sudo su -
/usr/local/mysql/bin/mysqladmin shutdown
nohup /usr/local/mysql/bin/mysqld_safe &
exit

3. Change root privs

`mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'password' WITH GRANT OPTION;`

4. Remove the skip lines from the my.cnf

## skip-grant-tables
## skip-networking

5. Restart mysqld again

sudo su -
/usr/local/mysql/bin/mysqladmin shutdown
nohup /usr/local/mysql/bin/mysqld_safe &
exit

Answer 2

DISCLAIMER : Not a MacOS user

Here is something quick-and-dirty and very secure.

Step 01) Create a script to create user

If root had a password (such as mys3cr3t), run this

SQLSTMT="GRANT ALL PRIVILEGES ON *.* to root@localhost"
SQLSTMT="${SQLSTMT} IDENTIFIED BY 'mys3cr3t' WITH GRANT OPTION;"
echo ${SQLSTMT} > /var/lib/mysql/init.sql

If root had no password, run this

SQLSTMT="GRANT ALL PRIVILEGES ON *.* to root@localhost WITH GRANT OPTION;"
echo ${SQLSTMT} > /var/lib/mysql/init.sql

Step 02) Restart mysql using the init.sql file

service mysql restart --init-file=/var/lib/mysql/init.sql

Step 03) Remove /var/lib/mysql/init.sql

rm -f /var/lib/mysql/init.sql

All Done !!! No need to use skip-networking or skip-grant-tables.

In the past, I used to recommend restarting mysql twice.

@Shlomi Noach's answer had a one-restart approach, which I now recommend to others.

Give it a Try !!!