Access to mysql

Posted July 23, 2019 5.9k views
MySQLLEMPUbuntu 18.04

I installed mysql on an Ubuntu 18.04 server with the Ubuntu Mate Desktop Environment following the instructions in the tutorial “How to install Linux, Nginx, MySQL, PHP on a LEMP server.

In setting up mysql I opted to use the native password for root access and entered the password I had previously set when selecting the level of password validation. I then flushed the privileges and exited. Upon attempting to re-enter mysql I get the following error message:

ERROR 1045 (28000): Access denied for user ‘roor’@'localhost’ (using password: YES)

I have tried every searched for hours for a solution and have tried many to no avail.

What concerns me is the possibility that I incorrectly entered the ALTER USER command. The instructions tell you to use the following command:

ALTER USER 'root’@'localhost’ IDENTIFIED WITH mysqlnativepassword BY 'password’;

At this point, I assumed root@localhost to be the right thing to enter and I replaced the word password with the password I entered earlier. I also put single quote marks around my password.

Can anyone tell me what I did wrong and what can I do to fix it.

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Submit an Answer
1 answer

You could try resetting the root password:

  1. Stop MySQL: sudo service mysql stop
  2. Start it up again in “safe mode”, and opening access to everything (note the ampersand (&) at the end): sudo mysqld_safe --skip-grant-tables &
  3. Connect with the root account: mysql -u root
  4. Set the new password:
mysql> use mysql;
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD("new_password");
mysql> flush privileges;
mysql> quit

Then stop the (very insecure, wide open) service again (sudo service mysql stop), start it up in normal mode (sudo service mysql start) and try logging on (mysql -u root -p).