How to reset or change MySQL's root password: MySQL Password Reset Guide

Question

How to change MySQL root password and username on Ubuntu server? Do I need to stop the mysql service before making any changes? I also installed phpmyadmin, will phpmyadmin update automatically?

P粉306523969 · Answer

The only way that worked for me was the one described here (I'm running ubuntu 14.04). For clarity, here are the steps I followed:

sudo vim /etc/mysql/my.cnf
Add the following lines at the end:
```
[mysqld]

skip-grant-tables
```
sudo service mysql restart
mysql -u root
use mysql
select * from mysql.user where user = 'root'; - look at the top to determine if the password column is called password or authentication_string
UPDATE mysql.user set *password_field from above* = PASSWORD('your_new_password') where user = 'root' and host = 'localhost'; - Use the correct password field from above
FLUSH PRIVILEGES;
exit
sudo vim /etc/mysql/my.cnf
If you want to maintain security standards, delete the line added in step 2.
sudo service mysql restart

Reference: https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

P粉135799949 · Answer

Set/change/reset MySQL's root password on Ubuntu Linux. Enter the following command in the terminal.

Stop the MySQL server: sudo /etc/init.d/mysql stop
(In some cases, if /var/run/mysqld does not exist, you need to create it first: sudo mkdir -v /var/run/mysqld && sudo chown mysql /var/run/mysqld
StartmysqldConfiguration: sudo mysqld --skip-grant-tables &
Log in to MySQL as root: mysql -u root mysql
Replace YOURNEWPASSWORD with your new password:

For MySQL < 8.0

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

If your MySQL uses the new authentication plugin, you need to use: update user set plugin="mysql_native_password" where User='root'; before refreshing permissions.

Note: This method is not considered the most secure way to reset your password, but it works.

For MySQL >= 8.0

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YOURNEWPASSWORD';
FLUSH PRIVILEGES;

last step:

As mentioned in @lambart's comment, you may need to kill the temporary passwordless mysql process you started, i.e. sudo killall -9 mysqld, and then start the normal daemon: sudo service mysql start

References: