How to reset MySQL root password in Ubuntu in latest Ubuntu versions 18.04 and above




I have forget my MySQL root password and not able to login in phpMyAdmin. I am very bad in MySQL commandline. After long struggle while Googling, I found this solution in Stackoverflow -

But this will work only latest Ubuntu version because of enhance security of MySQL.

 First, as root, stop the mysql server. 


systemctl stop mysql

Next, create a /var/run/mysqld directory to be used by MySQL process to store and access socket file: 

mkdir -p /var/run/mysqld

chown mysql:mysql /var/run/mysqld

Check that the newly created directory do exist. Now manually start MySQL with the following linux command and options: 

/usr/sbin/mysqld --skip-grant-tables --skip-networking &

Confirm that the process is running as expected: 

jobs

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

 Leave that terminal running and open another terminal, not as root.

Now access MySQL database without password: 


mysql -u root -p
mysql>

Using the MySQL session first flush privileges: 

FLUSH PRIVILEGES;

Next, reset root password. The following commands will reset MySQL root password: 

mysql> USE mysql;
mysql> UPDATE user SET authentication_string=PASSWORD("LKYbak0r") WHERE User='root';
mysql> UPDATE user SET plugin="mysql_native_password" WHERE User='root';

Quit MySQL session: 

mysql> exit

Now switch to the root terminal and terminate current mysqld process: 

pkill mysqld

jobs

[1]+ Done sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking //

 Lastly, start MYSQL database: 


systemctl start mysql

You should now able to connect to mysql as root. 

mysql -u root -p

Source: Stackoverflow

Popular posts from this blog

WordPress Themes and Plugins Installation Problem

How to Convert PEM KeyFile into PPK KeyFile and vice versa?

How to SSH EC2 instance with ssh-agent along with SSH agent forwarding to SSH private instances in VPC?