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

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?

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)

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

The official and easy way to reset the root password on an ubuntu server...

If you are on 16.04, 14.04, 12.04:

sudo dpkg-reconfigure mysql-server-5.5

If you are on 10.04:

sudo dpkg-reconfigure mysql-server-5.1

If you are not sure which mysql-server version is installed you can try:

dpkg --get-selections | grep mysql-server

Updated notes for mysql-server-5.7

Note that if you are using mysql-server-5.7 you can not use the easier dpkg-reconfigure method shown above.

If you know the password, login and run this:

UPDATE mysql.user SET authentication_string=PASSWORD('my-new-password') WHERE USER='root';
FLUSH PRIVILEGES;

Alternatively, you can use the following:

sudo mysql_secure_installation

This will ask you a series of questions about securing your installation (highly recommended), including if you want to provide a new root password.

If you do NOT know the root password, refer to this Ubuntu-centric write up on the process.

See for more info:

https://help.ubuntu.com/16.04/serverguide/mysql.html https://help.ubuntu.com/14.04/serverguide/mysql.html

What worked for me (Ubuntu 16.04, mysql 5.7):

Stop MySQL

sudo service mysql stop

Make MySQL service directory.

sudo mkdir /var/run/mysqld

Give MySQL user permission to write to the service directory.

sudo chown mysql: /var/run/mysqld

Start MySQL manually, without permission checks or networking.

sudo mysqld_safe --skip-grant-tables --skip-networking &

On another console, log in without a password.

mysql -uroot mysql

Then:

UPDATE mysql.user SET authentication_string=PASSWORD('YOURNEWPASSWORD'), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';
EXIT;

Turn off MySQL.

sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown

Start the MySQL service normally.

sudo service mysql start

I am sharing the step by step final solution to reset a MySQL password in Linux Ubuntu.

Reference taken from blog (dbrnd.com)

Step 1: Stop MySQL Service.

sudo stop mysql

Step 2: Kill all running mysqld.

sudo killall -9 mysqld

Step 3: Starting mysqld in Safe mode.

sudo mysqld_safe --skip-grant-tables --skip-networking &

Step 4: Start mysql client

mysql -u root

Step 5: After successful login, please execute this command to change any password.

FLUSH PRIVILEGES;

Step 6: You can update mysql root password .

UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';

Note: On MySQL 5.7, column Password is called authentication_string.

Step 7: Please execute this command.

FLUSH PRIVILEGES;