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?

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

2 Answers 2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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