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

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

How to transfer Google Cloud Storage One account data to Google Cloud another account?

MySQL Commands, Administration, Backup, Restore