Reset MySQL root password in Ubuntu

To recover or reset MySQL root password in Ubuntu, you should simply follow some steps. This process may vary according to the operating system. Ubuntu offerts a possibility to restore the root password via dpkg, which is a native package management system.

Recovering the database root access: reset MySQL root password in Ubuntu

First, we need to stop the MySQL server:

sudo service mysql stop

Now we will run dpkg-reconfigure , to reset the mysql password:

sudo dpkg-reconfigure mysql-server-5.5

It will prompt for the new password, just like in the new installation of MySQL. You must type the new password in this screen.

Now, restart the MySQL server

sudo service mysql start

Now you will be able to login using the new mysql root password:

mysql -u root -p

Change a MySQL user password

To change an user password, you must have the current MySQL root password. You should follow these steps we will explain. In these examples we will use localhost , for local users connecting from the same server. If the user is authorized to connect remotely, we would have to replace it with its IP address.

SET PASSWORD FOR '[user-name]'@'localhost' = PASSWORD('[new-password]');

There is an alternative method. You can also change the password directly in the mysql users table:

use mysql;
UPDATE mysql.user SET Password=PASSWORD('[new-password]') WHERE USER='[user-name]' AND Host='localhost';

The expected success output is:

mysql> UPDATE mysql.user SET Password=PASSWORD('test123test') WHERE USER='user1' AND host='localhost';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Afterwards, you must flush privileges, so that the changes take effect:

flush privileges;

Afterwards, you can exit mysql, and try to login with the new defined user password:

mysql -u [user-name] -p

If you happen to use root from an external host or IP, you should  reset mysql root password manually. Just change [user-name] by root and specify the IP address authorized for the root user.

This tutorial applies either for a cheap dedicated server with Ubuntu, or for a vps hosting Ubuntu server.