Reset MySQL root password in LAMPP server on Ubuntu

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

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

• It makes the database vulnerable (even with skip-networking)

• It requires taking your database down twice.

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

• Create a temporary SQL text file, say /tmp/init.sql

-
Within this file, write:

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

-
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

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

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

• 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

StackExchange Database Administrators Q#22053, answer score: 5