MySQL – Retrieve “lost” MySQL user name and password (Unix/Linux)

Problem
You are trying to log into MySQL but you have the wrong credentials and need admin/root access

Solution
To remove the password requirement we need to start MySQL with the –skip-grant-tables. In a terminal session:

  • Stop the MySQL service

sudo service mysql stop

  • Locate and edit the /etc/my.cnf
  • Add line to [mysqld] block

skip-grant-tables

Screen Shot 2016-08-09 at 9.01.04 PM

  • Save and exit file editor
  • Start the service

sudo service mysqld stop

  • Connect to MySQL using myql command with no other parameters

mysql

  • View users in database

mysql> SELECT user FROM mysql.user;

  • update user account with new password (in this case we had root so we are updating root)

mysql> use mysql;

  • Redefine user root password.

mysql> UPDATE user SET Password = PASSWORD(‘mynewpassword’) WHERE User = ‘root’;

  • You may need to use the FLUSH PRIVILEGES; command after the update of the mysql.user password.
  • Exit mysql and re-edit file my.cnf.

sudo vi /etc/my.cnf

  • Rem line with # or remove line.

#skip-grant-tables

  • Save and exit.
  • Restart MySQL service.

sudo service mysqld restart

  • Check service status.

sudo service mysqld status

  • Connect to database.

mysql -u root -p

  • Type new password when prompt.

Tested Platform
Linux GNU 2.x
MySQL

Hits: 50

Leave a Reply