MySQL – How to Reset Root Password in LAMPP Server on Ubuntu

MySQLpassword-recovery

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

Best Answer

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

Related Question