How to reset MySQL password in Ubuntu 18.04 LTS?

Unable to reset MySQL Password in Ubuntu 18.04 LTS?

First I want to discuss in which case the password will not change. I waste quite a lot of time setting a password which had “@” in between. For example “Password@123” – It simply didn’t work. Only when I removed “@” making “Password123” then it worked. So – If you are using @ in the password, please don’t think there is a problem with the approach, problem is with the password.

Stop the MySQL service

sudo service mysql stop

Create directories and set permissions

As soon as you stop the MySQL service, somehow, directories are deleted. You can re-create it as follows:

sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld

Run the MySQL without grant table

This will allow you to login without the need of input password.

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
[1] 2613

Confirm that MySQL is running (OPTIONAL)

jobs
[1]+  Running     sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

Connect to MySQL

mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Flush Privileges

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Change Password

Assuming that your new password is “your_password_without_at_sign” here is how you can change it.

mysql> USE mysql; 
Database changed
mysql> UPDATE user SET authentication_string=PASSWORD("your_password_without_at_sign") WHERE User='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

mysql> UPDATE user SET plugin="mysql_native_password" WHERE User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Now you have reset the password, you can quick mysql then kill the job running.

mysql> quit                                                                                                                                                                                    
Bye
sudo pkill mysqld                                                                                                                                                        
jobs                                                                                                                                                                     
[1]+  Done       sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking

Start MySQL in Normal Mode

sudo service mysql start
mysql -u root --password=your_password_without_at_sign                                                                                                                                 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql>

This is it! Let me know in comments if you find any problem.

How to reset MySQL password in Ubuntu 18.04 LTS?

4 thoughts on “How to reset MySQL password in Ubuntu 18.04 LTS?

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top