DEV Community

Joshua Johnson
Joshua Johnson

Posted on • Originally published at ua1.us on

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

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'
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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/mysqldsudo chown mysql:mysql /var/run/mysqld
Enter fullscreen mode Exit fullscreen mode

Manually start MySQL using the following command:

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

mysql> FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

End you MySQL session:

mysql> quit
Enter fullscreen mode Exit fullscreen mode

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

sudo pkill mysqld
Enter fullscreen mode Exit fullscreen mode

Restart your MySQL Server:

sudo service mysql start
Enter fullscreen mode Exit fullscreen mode

Conclusion

I’m not sure the exact reason why MySQL isn’t properly setting the root password and privileges when prompted for it while installing mysql-server. But I do know this was a pain for me to fix. If you run into this issue and found this tutorial helpful, please come back frequently to check for new tutorials.

The post Tutorial – After Installing MySQL on Ubutnu 18.04, Fix “Access denied for user ‘root’@’localhost'” Error appeared first on UA1 Labs.

Top comments (1)

Collapse
 
prhomhyse profile image
Promise Akpan • Edited

Hi Joshua,
Great stuff. Here is another work around.

Try this:
sudo mysql_secure_installation

Follow the prompt and you can change the root password.

P.S: Ubuntu 18.04 LTS