Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

2 Answers 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
share|improve this answer
add comment

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

share|improve this answer
    
you also need "WITH GRANT OPTION" when recreating root! ;-) –  randy melder Jan 24 '13 at 17:27
    
GRANT ALL PRIVILEGES ON . to root@localhost IDENTIFIED BY 'mys3cr3t' WITH GRANT OPTION –  randy melder Jan 24 '13 at 17:28
    
@randymelder Just added it, Thank You. –  RolandoMySQLDBA Jan 24 '13 at 17:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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