ERROR 1698 28000 Access denied for user root localhost

Question

I m setting up a new server and keep running into this problem   When I try to login to the MySQL database with the root user  I get the error       ERROR 1698  28000   Access denied for user  root   localhost    It doesn t matter if I connect through the terminal SSH   through PHPMyAdmin or a MySQL Client  e g  Navicat  They all fail   I looked in the mysql user table and get the following    ------------------ -------------------    user               host                 ------------------ -------------------    root                                     root               127 0 0 1             amavisd            localhost             debian-sys-maint   localhost             iredadmin          localhost             iredapd            localhost             mysql sys          localhost             phpmyadmin         localhost             root               localhost             roundcube          localhost             vmail              localhost             vmailadmin         localhost             amavisd            test4 folkmann it     iredadmin          test4 folkmann it     iredapd            test4 folkmann it     roundcube          test4 folkmann it     vmail              test4 folkmann it     vmailadmin         test4 folkmann it    ------------------ -------------------    As you can see  root should have access   The Server is quite simple  as I have tried to troubleshoot this for a while now    It s running Ubuntu 16 04 1 LTS with Apache  MySQL and PHP  so that it can host websites  and iRedMail 0 9 5-1  so that it can host mail   Login in to the MySQL database works fine before I install iRedMail  I also tried  just installing iRedMail  but then root  also doesn t work     If someone could tell me how I fix my MySQL login problem or how I install iRedMail  on top of an existing MySQL install  And yes I tried the Installation Tips and I can t find those variables in the config files

User · Answer

There is a good and regularly updated guide on how to set a new password for the latest MySQL.

https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04

It would be best to read whole topic from the link above but in short, this maybe could help,

Run the security script:

sudo mysql_secure_installation

Detailed Info for "mysql_secure_installation"

After that, you can change password by following the next steps

sudo mysql

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

Detailed Info for changin root user password

If you have a problem maybe you will need to reinstall MySql.

User · Answer

os Ubuntu18 04  mysql 5 7   add the skip-grant-tables to the file end of mysqld cnf cp the my cnf   sudo cp  etc mysql mysql conf d mysqld cnf  etc mysql my cnf    reset the password    base       sudo service mysql stop   base       sudo service mysql start  base       mysql -uroot Welcome to the MySQL monitor   Commands end with   or  g  Your MySQL connection id is 2 Server version  5 7 25-0ubuntu0 18 04 2  Ubuntu   Copyright  c  2000  2019  Oracle and or its affiliates  All rights reserved   Oracle is a registered trademark of Oracle Corporation and or its affiliates  Other names may be trademarks of their respective owners   Type  help   or   h  for help  Type   c  to clear the current input statement   mysql gt  use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A  Database changed  3 warnings mysql gt  update mysql user set authentication string password  newpass   where user  root  and Host   localhost   Query OK  1 row affected  1 warning  0 00 sec  Rows matched  1  Changed  1  Warnings  1  mysql gt  update user set plugin  mysql native password    Query OK  0 rows affected  0 00 sec  Rows matched  4  Changed  0  Warnings  0  mysql gt   flush privileges  Query OK  0 rows affected  0 00 sec   mysql gt  quit Bye    remove theskip-grant-tables from my cnf    base       sudo emacs  etc mysql mysql conf d mysqld cnf   base       sudo emacs  etc mysql my cnf                   base       sudo service mysql restart     open the mysql    base       mysql -uroot -ppassword  mysql   Warning  Using a password on the command line interface can be insecure  Welcome to the MySQL monitor   Commands end with   or  g  Your MySQL connection id is 3 Server version  5 7 25-0ubuntu0 18 04 2  Ubuntu   Copyright  c  2000  2019  Oracle and or its affiliates  All rights reserved   Oracle is a registered trademark of Oracle Corporation and or its affiliates  Other names may be trademarks of their respective owners   Type  help   or   h  for help  Type   c  to clear the current input statement   mysql gt      check the password policy   mysql gt  select   validate password policy   ----------------------------      validate password policy    ----------------------------    MEDIUM                        ----------------------------  1 row in set  0 00 sec    mysql gt  SHOW VARIABLES LIKE  validate password     -------------------------------------- --------    Variable name                          Value     -------------------------------------- --------    validate password dictionary file                 validate password length               8          validate password mixed case count     1          validate password number count         1          validate password policy               MEDIUM     validate password special char count   1         -------------------------------------- --------  6 rows in set  0 08 sec      change the config of the validate password   mysql gt  set global validate password policy 0  Query OK  0 rows affected  0 05 sec   mysql gt  set global validate password mixed case count 0  Query OK  0 rows affected  0 00 sec   mysql gt  set global validate password number count 3  Query OK  0 rows affected  0 00 sec   mysql gt  set global validate password special char count 0  Query OK  0 rows affected  0 00 sec   mysql gt  set global validate password length 3  Query OK  0 rows affected  0 00 sec   mysql gt  SHOW VARIABLES LIKE  validate password     -------------------------------------- -------    Variable name                          Value    -------------------------------------- -------    validate password dictionary file                validate password length               3         validate password mixed case count     0         validate password number count         3         validate password policy               LOW       validate password special char count   0        -------------------------------------- -------  6 rows in set  0 00 sec    note you should know that you error caused by what  validate password policy   you should decided to reset the your password to fill the policy or change the policy

User · Answer

I found my solution after hours of research here   Stop MySQL  sudo service mysql stop   Make MySQL service directory   sudo mkdir  var run mysqld   Give MySQL user permission to write to the service directory   sudo chown mysql   var run mysqld   Start MySQL manually  without permission checks or networking   sudo mysqld safe --skip-grant-tables --skip-networking  amp    Log in without a password    mysql -uroot mysql   update password  UPDATE mysql user SET authentication string PASSWORD  YOURNEWPASSWORD    plugin  mysql native password  WHERE User  root  AND Host      EXIT    Turn off MySQL   sudo mysqladmin -S  var run mysqld mysqld sock shutdown   Start the MySQL service normally   sudo service mysql start

User · Answer

This has happened to me as well  The problem is with the mysql repo that comes already with the linux distro  So when you simply do    sudo apt install mysql-server  it installs mysql from their default repo which gives this problem  So to overcome that you need to uninstall that installed mysql    sudo apt remove mysql  --purge --auto-remove  Then download mysql repo from official mysql website MySQL APT Repo  Follow their documentation on how to add repo and install it  This gives no issue  Also as answered by  zetacu  you can verify that mysql root now indeed uses mysql native password plugin

User · Answer

step 1  sudo mysql -u root -p  step 2  USE mysql   step 3  ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY  admin    Here  admin  is your new password  yo can change it   step 4  exit   Thanks  You are done

User · Answer

Let me solve it for ya   Get into the sql  Enter the following thing   SET GLOBAL validate password length   4  SET GLOBAL validate password mixed case count   0  SET GLOBAL validate password number count   0  SET GLOBAL validate password policy   LOW  SET GLOBAL validate password special char count   0  ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY  test    Only for testing purposes

User · Answer

I would suggest to remove the Mysql connection -  UPDATE-This is for Mysql version 5 5 if your version is different  please change the first line accordingly  sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-5 5 mysql-client-core-5 5 sudo rm -rf  etc mysql  var lib mysql sudo apt-get autoremove sudo apt-get autoclean   And Install Again But this time set a root password yourself  This will save a lot of effort   sudo apt-get update sudo apt-get install mysql-server

User · Answer

Some systems like Ubuntu  mysql is using by default the UNIX auth socket plugin  Basically means that  db users using it  will be  quot auth quot  by the system user credentias  You can see if your root user is set up like this by doing the following    sudo mysql -u root   I had to use  quot sudo quot  since is new installation  mysql gt  USE mysql  mysql gt  SELECT User  Host  plugin FROM mysql user    ------------------ -----------------------    User               plugin                   ------------------ -----------------------    root               auth socket               mysql sys          mysql native password     debian-sys-maint   mysql native password    ------------------ -----------------------   As you can see in the query  the root user is using the auth socket plugin There are 2 ways to solve this   You can set the root user to use the mysql native password plugin You can create a new db user with you system user  recommended   Option 1    sudo mysql -u root   I had to use  quot sudo quot  since is new installation  mysql gt  USE mysql  mysql gt  UPDATE user SET plugin  mysql native password  WHERE User  root   mysql gt  FLUSH PRIVILEGES  mysql gt  exit     sudo service mysql restart  Option 2   replace YOUR SYSTEM USER with the username you have    sudo mysql -u root   I had to use  quot sudo quot  since is new installation  mysql gt  USE mysql  mysql gt  CREATE USER  YOUR SYSTEM USER   localhost  IDENTIFIED BY  YOUR PASSWD   mysql gt  GRANT ALL PRIVILEGES ON     TO  YOUR SYSTEM USER   localhost   mysql gt  UPDATE user SET plugin  auth socket  WHERE User  YOUR SYSTEM USER   mysql gt  FLUSH PRIVILEGES  mysql gt  exit     sudo service mysql restart  Remember that if you use option  2 you ll have to connect to mysql as your system username  mysql -u YOUR SYSTEM USER  Note  On some systems  e g   Debian stretch   auth socket  plugin is called  unix socket   so the corresponding SQL command should be  UPDATE user SET plugin  unix socket  WHERE User  YOUR SYSTEM USER   Update  from  andy s comment seems that mysql 8 x x updated replaced the auth socket for caching sha2 password I don t have a system setup with mysql 8 x x to test this  however the steps above should help you to understand the issue  Here s the reply  One change as of MySQL 8 0 4 is that the new default authentication plugin is  caching sha2 password   The new  YOUR SYSTEM USER  will have this auth plugin and you can login from the bash shell now with  quot mysql -u YOUR SYSTEM USER -p quot  and provide the password for this user on the prompt  No need for the  quot UPDATE user SET plugin quot  step  For the 8 0 4 default auth plugin update see  https   mysqlserverteam com mysql-8-0-4-new-default-authentication-plugin-caching sha2 password

User · Answer

The first sudo mysql -u root -p  SHOW VARIABLES LIKE  validate password     we will see something like this   -------------------------------------- --------    Variable name                          Value     -------------------------------------- --------    validate password check user name      ON         validate password dictionary file                 validate password length               8          validate password mixed case count     1          validate password number count         1          validate password policy               MEDIUM     validate password special char count   1         -------------------------------------- --------   we need to change these rows   validate password length validate password number count validate password policy validate password special char count   SET GLOBAL validate password policy LOW  SET GLOBAL validate password length 4  SET GLOBAL validate password number count 0  SET GLOBAL validate password special char count 0   SHOW VARIABLES LIKE  validate password        we will see  -------------------------------------- -------    Variable name                          Value    -------------------------------------- -------    validate password check user name      ON        validate password dictionary file                validate password length               4         validate password mixed case count     1         validate password number count         0         validate password policy               LOW       validate password special char count   0        -------------------------------------- -------   now exit from MySQL exit  sudo mysql -u root -p  and now you can write your password 4 or more only letters  ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY  new-password    exit   sudo mysql -u root -p  your new password is in DB for user  root

User · Answer

You want to access MySQL with root user but you re not providing root s correct password   If you need to set a new password for root  MySQL s site has great documentation on how to do it  http   dev mysql com doc refman 5 7 en resetting-permissions html  I ll not show the process in here because MySql documentation on the above link it s clear and concise

User · Answer

in my case   dev Dev-007    mysql -u root -p Enter password   ERROR 1698  28000   Access denied for user  root   localhost    I am sure my password was correct otherwise error code would be ERROR 1045  28000   Access denied for user  so i relogin using sudo   dev Dev-007    sudo mysql -u root -p   this time it worked for me   see the docs  and then change root password   mysql gt  alter user  root      identified with mysql native password by  me123   Query OK  0 rows affected  0 14 sec   mysql gt     then restart server using sudo  etc init d mysql restart

User · Answer

Check here   NEW Version of MYSQL does it this way   In the new my-sql if the password is left empty while installing then it is based on the auth socket plugin   The correct way is to login to my-sql with sudo privilege     sudo mysql -u root -p   And then updating the password using     ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY  new-password     Once this is done stop and start the mysql server      sudo service mysql stop    sudo service mysql start   For complete details you can refer to this link   Do comment for any doubt

User · Answer

First step  go to  etc phpmyadmin config inc php then uncomment lines where you find AllowNoPassword   Second step  login to your mysql default account  mysql -u root -p use mysql  update user set plugin    where user  root   flush privilege    and that s all

User · Answer

After hours of struggle with no solution here  this worked for me then I found a youtube video where it says the password column is now called authentication string   So I was able to change my password as follows  First get into mysql from terminal  sudo mysql   then inside mysql type whatever after mysql    mysql gt  use mysql mysql gt  update user set authentication string PASSWORD  mypass   where user  root   mysql gt  flush privileges  mysql gt  quit    at this point you are out of mysql back to your normal terminal place  You need to restart mysql for this to take effect  for that type the following   sudo service mysql restart   Refer to this video link for better understanding

User · Answer

I was having this issue on an Debian 8 VM that I was interacting with through Putty on my Windows 10 desktop   I tried the various suggestions on here but nothing quite worked and I am running MariaDB on the Debian host   In the end I found that I couldn t start the db server in safe mode but I didn t need to and the following commands actually worked for me i e  allowing a newly created MySql user to log into the MySql MariaDB server   sudo service mysql restart sudo mysql   logs in automatically into MariaDB use mysql  update user set plugin    where user  your user name   flush privileges  exit  sudo service mysql restart   restarts the mysql service   If the above doesn t quite work for you  follow the steps outlined in zetacu s post above  zetacu  then follow my steps   Now you should be able to use a remote terminal client and securely log into mysql using the command   mysql -u your user name -p    type in the password when prompted

User · Answer

That worked for me   mysql --user root mysql CREATE USER  some user      IDENTIFIED BY  some pass   GRANT ALL PRIVILEGES ON     TO  some user      WITH GRANT OPTION  FLUSH PRIVILEGES

User · Answer

No need of sudo  The database is initialised with 2 all-privilege accounts  the first one is  root  which is inaccessible and the second one with your user name  check with command whoami    To enable access to root account  you need to login with your user name  mysql -u   whoami    and manually change password for root  use mysql  set password for  root   localhost    password  YOUR ROOT PASSWORD HERE    flush privileges  quit   Login as  root   mysql -u root -p

User · Answer

I have done the following steps to get rid of this issue  Login into the MySQL in your machine using  sudo mysql -p -u root  and hit the following queries  1  CREATE USER  jack   localhost  IDENTIFIED WITH mysql native password BY   lt  lt any password gt  gt     2  GRANT ALL PRIVILEGES ON     TO  jack   localhost    3  SELECT user plugin host FROM mysql user WHERE user    root    ------ ------------- -----------    user   plugin        host          ------ ------------- -----------     root   auth socket   localhost     ------ ------------- -----------   4  ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY   lt  lt any password gt  gt     5  FLUSH PRIVILEGES   Please try it once if you are still getting the error  I hope this code will help you a lot

User · Answer

I also faced the same issue at the first time   Now it is fixed   First  you copy the  etc mysql mysql conf d mysqld cnf file and past in to  etc mysql my cnf   You can do it by command   sudo cp  etc mysql mysql conf d mysqld cnf  etc mysql my cnf   Now let s Rest the password   Use the following commands in your terminal   sudo service mysql stop  sudo service mysql start sudo mysql -u root   Now you are inside the mysql console   Then let s write some queries to reset our root password   USE mysql update mysql user set authentication string password  newpass   where user  root  and Host   localhost   update user set plugin  mysql native password    flush privileges  quit   Now we can clean  etc mysql my cng   Open the above file in your editor and remove the whole lines inside the file   After that let s restart mysql   sudo mysql service restart    Now let s use mysql with newly created password   sudo mysql -u root -p   Finally enter your newly created password

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

Examples related to mysql

Examples related to iredmail