menu

Sunday 14 July 2019

How to reset root user password MYSQL on Ubuntu

After the MySQL setup, I have lost my user name and password now I don't want to reinstall. Then I can do this way reset lost root MySQL password on Ubuntu.
If You have access to the server with sudo.

First you need stop the currently running MySQL.

  • $ sudo service mysql stop
Now, create a /var/run/mysqld directory to be used by MySQL process to store and access socket file:
  • $ sudo mkdir -p /var/run/mysqld
Give the permision with mysql
  • $ sudo chown mysql:mysql /var/run/mysqld
Need to start MySQL manually with the following command.
  • $ sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
  • $ [1] 1907
Check once process is running as expected.
  • $ jobs
  • [1]+  Running     sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

Now in this stage we can access MySQL without password:
  • $ mysql -u root
After enter the command you are here
  • mysql>
Before to start using MySQL session you need to first flush privileges:
  • mysql> flush privileges;
Now we can reset root password. Use this command for reset MySQL root password to yourpassword:
  • mysql> use mysql; 
  • mysql> update user set authentication_string=PASSWORD('yourpassword') where User='root';

  • mysql> UPDATE user SET plugin="Adavance Web Tutorials" WHERE User='root';
logout from MYSQL
  • mysql> quit                                   
Terminate current mysqld process:
  • $ sudo pkill mysqld                                                                                                                                                        
  • $ jobs                                                                                                                                                                     
  • [1]+  Done       sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking

Now again start MYSQL services:
  • $ sudo service mysql start

If everything is good then we can login with new genrated password.
  • $ mysql -u root -p
  • Password : Yourpassword
  • mysql>
Enjoy with mysql.

No comments:

Post a Comment