3

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?

  • I don't know how MAMP works so not submitting as an answer, but does this answer help you? – Derek Downey Jan 24 '13 at 14:25
2

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
| improve this answer | |
1

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 !!!

| improve this answer | |
  • you also need "WITH GRANT OPTION" when recreating root! ;-) – randomx Jan 24 '13 at 17:27
  • GRANT ALL PRIVILEGES ON . to root@localhost IDENTIFIED BY 'mys3cr3t' WITH GRANT OPTION – randomx Jan 24 '13 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.