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?

Answer 1

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

1. Stop the MySQL Server: sudo /etc/init.d/mysql stop
2. Start the mysqld configuration: sudo mysqld --skip-grant-tables &

In some cases, you've to create the /var/run/mysqld first:

    sudo mkdir -v /var/run/mysqld && sudo chown mysql /var/run/mysqld

3. Login to MySQL as root: mysql -u root mysql
4. Replace YOURNEWPASSWORD with your new password:

For MySQL < 8.0

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

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

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

References:

1. Set / Change / Reset the MySQL root password on Ubuntu Linux
2. How to Reset the Root Password (v5.6)
3. How to Reset the Root Password (v8.0)

Answer 2

The only method that worked for me is the one described here (I am running ubuntu 14.04). For the sake of clarity, these are the steps I followed:

1. sudo vim /etc/mysql/my.cnf

2. Add the following lines at the end:

   [mysqld]
   
   skip-grant-tables

3. sudo service mysql restart

4. mysql -u root

5. use mysql

6. select * from mysql.user where user = 'root'; - Look at the top to determine whether the password column is called password or authentication_string

7. UPDATE mysql.user set *password_field from above* = PASSWORD('your_new_password') where user = 'root' and host = 'localhost'; - Use the proper password column from above

8. FLUSH PRIVILEGES;

9. exit

10. sudo vim /etc/mysql/my.cnf

11. Remove the lines added in step 2 if you want to keep your security standards.

12. sudo service mysql restart

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