Reset MySQL root password in LAMPP server on Ubuntu

Question

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).

Answer 1

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

Answer 2

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