Monday, May 27, 2019

How to reset root MySQL password on Ubuntu 18.04 Bionic Beaver Linux?

After Installing MySQL on Ubutnu 18.04, Fix “Access denied for user ‘root’@’localhost'” Error?

How to reset root MySQL password on Ubuntu 18.04 Bionic Beaver Linux?

Recently, I came across an issue when installing mysql-server on Ubuntu 18.04. What I found is that
after I installed mysql-server using my standard approach of sudo apt install mysql-server, and after
running mysql_secure_installation, my root user was denied access to MySql when trying to access
with mysql -u root -p. I would always get the following error:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Fixing Access denied for user ‘root’@’localhost’ Ubuntu
After hours of trying to come up with the right solution, this is what I did to fix:

Stop MySQL Server from running:

sudo service mysql stop

We need to create a directory for MySQL to store access socket files so that we may use the mysqld
command. Without this directory, the command will fail.

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

Manually start MySQL using the following command:

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

The command we just ran will allow us to now access MySQL without having a password. For what
ever reason, when MySQL was installed, the password we installed it with did not get applied to to
the root user. Now you can manually change your password using the following `mysql` commands

Log in to MySql without a password:

mysql -u root

Flush Privileges to apply the pending privileges to the root user created during the installation process:


Update the password of the root user using the three following commands:

mysql> USE mysql;
mysql> UPDATE user SET authentication_string=PASSWORD("{PASSWORD_HERE}") WHERE User='root';

mysql> UPDATE user SET plugin="mysql_native_password" WHERE User='root';

End you MySQL session:

mysql> quit

Now that you’ve successfully reset your password, you will want to terminate mysqld:

sudo pkill mysqld

Restart your MySQL Server:

sudo service mysql start

No comments:

Post a Comment