MySQL: How to reset or change the MySQL root password?

Question

How do I change the MySQL root password and username in ubuntu server? Do I need to stop the mysql service before setting any changes? I have a phpmyadmin setup as well, will phpmyadmin get updated automatically?

Accepted Answer

Set / change / reset the MySQL root password on Ubuntu Linux. Enter the following lines in your terminal.

- Stop the MySQL Server: `sudo /etc/init.d/mysql stop`

- (In some cases, if `/var/run/mysqld` doesn't exist, you have to create it at first: `sudo mkdir -v /var/run/mysqld && sudo chown mysql /var/run/mysqld`

- Start the `mysqld` configuration: `sudo mysqld --skip-grant-tables &`

- Login 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 new auth plugin, you will need to use: `update user set plugin="mysql_native_password" where User='root';` before flushing privileges.

Note: on some versions, if `password` column doesn't exist, you may want to try: `UPDATE user SET authentication_string=password('YOURNEWPASSWORD') WHERE user='root';`

Note: This method is not regarded as the most secure way of resetting the password, however, it works.

For MySQL >= 8.0

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

Last step:

As noted in comments by @lambart, you might need to kill the temporary password-less mysql process that you started, i.e. `sudo killall -9 mysqld` and then start normal daemon: `sudo service mysql start`

References:

- Set / Change / Reset the MySQL root password on Ubuntu Linux

- How to Reset the Root Password (v5.6)

- How to Reset the Root Password (v8.0)

MySQL: How to reset or change the MySQL root password?

Solution

Problem

Related problems