How to recover your localhost mysql / phpMyAdmin root password

Recovering your phpMyAdmin/mysql lost root login password

How do you reset your mysqld and phpAdminPassword?

First we need to change the mysql root password (or set to empty if we want to allow a passwordless login). After that we will setup phpMyAdmin to use this  mysql login.

1. On newer Linuxes, e.g. Ubuntu 16.04 LTS phpmyadmin can be installed by the package installer (e.g. apt or dnf) and the installer uses default dbconfig-common settings which creates a (non-root) user ‘phpmyadmin’.

On such systems (re)setting the mysql root password may be as simple as starting mysql as the system root user (which will log you in as mysql ‘root’) and then setting a new password :

$ sudo mysql 
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '1111';

You can easily verify if this is the case by logging into mysql as ‘phpmyadmin’:

$ mysql -u phpmyadmin -p

and then typing:

mysql> SHOW GRANTS FOR 'phpmyadmin'@'localhost';
+--------------------------------------------------------------------+
| Grants for phpmyadmin@localhost                                    |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'phpmyadmin'@'localhost'                     |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO 'phpmyadmin'@'localhost' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

Now if you try the same command for ‘root’@’localhost’ you should get an error because phpmyadmin user is not root! Luckily – now you know what to do (sudo mysql and set the new root pass ! )

2. If you know your current root password but want to change it:

$ mysqladmin -u root -pCURRENTPASSWORD password 'NEWPASSWORD'

…or to allow root login without a password:

 mysqladmin -u root -pCURRENTPASSWORD password ''

Now go to step 4 to edit your phpMyAdmin configuration.

3. If you’ve forgotten your mysql root password it’s a bit more complicated:

$ sudo service mysqld stop   # for older systems
$ sudo systemctl stop mysql  # for newer systems
$ sudo mysqld --skip-networking --skip-grant-tables
$ mysql

Location of mysqld can be /usr/libexec/mysqld or /usr/sbin/mysqld depending on your system.

After the last command you should be logged in to your mysql server. Type these commands at the mysql prompt:

mysql> UPDATE mysql.user SET Password=PASSWORD('aaaa1111') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> QUIT

Back in the terminal.. type this:

$ sudo killall mysqld

At this point you have changed your mysql root password (or set it to empty if you used the mysqladmin command with an empty password).

4. Now you need to tell phpMyAdmin to use the same login credentials when connecting to mysqld. Open the /etc/phpMyAdmin/config.inc.php file in your favorite editor (as root or use sudo) and make sure the following params in this file are set as follows:

$cfg['Servers'][$i]['user']            = 'root';
$cfg['Servers'][$i]['AllowRoot']       = TRUE;        // allow root login
$cfg['Servers'][$i]['AllowNoPassword'] = TRUE;        // ATTENTION! ...

ATTENTION! Only set AllowNoPassword to TRUE if you want to allow root to login with no password, otherwise use FALSE!

5. Once you’ve modified and saved your phMyAdmin config file restart the mysqld daemon:

$ sudo service mysqld restart