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

Question

How to change MySQL root password and username in ubuntu server? Do I need to stop the mysql service before making any changes?

I also have phpmyadmin setup, will phpmyadmin update automatically?

Answer 1

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

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 correct password from above

8. Refresh permissions;

9. quit

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

11. If you want to maintain security standards, delete the line added in step 2.

12. sudo service mysql restart

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

Answer 2

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

1. Stop the MySQL server: sudo /etc/init.d/mysql stop
2. (In some cases, if /var/run/mysqld does not exist, you must create it first: sudo mkdir -v /var/run/ mysqld && sudo chown mysql /var/run/mysqld
3. StartmysqldConfiguration: sudo mysqld --skip-grant-tables &
4. Log in to MySQL as root: mysql -u root mysql
5. 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 does work.

For MySQL >= 8.0

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

last step:

As pointed out 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:

1. Set/Change/Reset MySQL root password on UbuntuLinux
2. How to reset Root password (v5.6)< /a>
3. How to reset Root password (v8.0)< /a>