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.

I forgot the password I changed for the root user of MySQL. I'm logged in as root user and when I do
# /opt/lampp/lampp security
it gives result as

XAMPP: Quick security check...
XAMPP: Your XAMPP pages are secured by a password.
XAMPP: Do you want to change the password anyway? [no] n
XAMPP: MySQL has a root passwort set. Fine! :)
XAMPP: The FTP password for user 'nobody' is still set to 'lampp'.
XAMPP: Do you want to change the password? [yes] n
XAMPP: Done.

How do I reset the password. I checked to see, but couldn't find the password in any file (including my.cnf).

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

The skip-grant-tables solution is not a recommended one, for a couple of reasons:

  1. It makes the database vulnerable (even with skip-networking)
  2. It requires taking your database down twice.

A solution which requires taking the database down just once is as follows:

  1. Create a temporary SQL text file, say /tmp/init.sql
  2. Within this file, write:

    SET PASSWORD FOR root@localhost = PASSWORD('the_new_password');

  3. Add the following to your MySQL config file (on Ubuntu this is on /etc/mysql/my.cnf), under the [mysqld] section:

    init-file=/tmp/init.sql

  4. Restart MySQL once. The init file is read and executed upon startup. The password is reset.

  5. Proceed to remove the init-file=/tmp/init.sql entry from my.cnf (do not forget this). Even as the server is up and running.
  6. Remove the /tmp/init.sql file.

There are even more solutions! Please refer to a past blog post of mine. Make sure to check out comment #4 by strcmp

share|improve this answer
    
Shutting down once instead of twice ??? That's slick. +1 !!! –  RolandoMySQLDBA Aug 6 '12 at 17:38
2  
Said comment #4 on linked post shows how to do that with no restart at all. –  Shlomi Noach Aug 6 '12 at 17:40
    
I'll bow out my answer. Hat's off to you today !!! –  RolandoMySQLDBA Aug 6 '12 at 17:53
    
That was a good lesson. I have favorited this quesiton and bookmarked your blog. Thanks again. –  RolandoMySQLDBA Aug 6 '12 at 18:02
    
@Rolando, thanks, and happy to have you visit my blog. But your answer was perfectly valid, I'm sorry you took it off. –  Shlomi Noach Aug 6 '12 at 18:19
show 2 more comments

DISCLAIMER : Not an Ubuntu User

I just recently answered a question like this for MySQL under Windows

MySQL Workbench asking for password

I will try to answer this for Ubuntu

STEP 01) Add the following lines under the [mysqld] header

[mysqld]
skip-grant-tables
skip-networking

STEP 02) At OS prompt, restart mysql

STEP 03) At OS prompt, type mysql and hit enter

STEP 04) At the mysql> prompt, enter this SQL statement (setting mysecretpassword as the password for root@localhost)

mysql> UPDATE mysql.user SET PASSWORD=PASSWORD('mysecretpassword') WHERE user='root' AND host='localhost';
exit

STEP 05) Remove or Comment Out the two lines from STEP 01

[mysqld]
#skip-grant-tables
#skip-networking

STEP 06) At OS prompt, restart mysql

STEP 07) at the OS prompt, type the following:

mysql -uroot -p (hit enter)
Password: (type mysecretpassword and hit enter)

If you get the mysql> prompt, CONGRATULTIONS !!! You can now connect to mysql as root@locahost with that new password.

Give it a Try !!!

share|improve this answer
    
thank you for the answer. I did not try it. But, I know sure it'll work too ;-) –  Ajit S Aug 7 '12 at 11:34
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.