Monday, January 9, 2017

How to fix the ERROR 1698 (28000): Access denied for user 'root'@'localhost'


After upgrading from MySQL version 5.5 to 5.7, whenever I tried to log in to the database as non sudo user it throws following error. But I was able to log into the system as sudo user without any issues.



firzhan@firzhan-wso2:/etc/mysql$ mysql -u root -padmin 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1698 (28000): Access denied for user 'root'@'localhost'


I followed following steps to do a quick fix

  • First connect to the mysql as sudo user.
  • Check the users presently available in the databases's user table.
  1. SELECT User,Host FROM mysql.user;
    +------------------+-----------+
    | User             | Host      |
    +------------------+-----------+
    | admin            | localhost |
    | root             |    *      |
    | root             | %         |
    | mysql.sys        | localhost |
    | root             | localhost |
  • Delete all the entries/accounts related to root user.
  • mysql> DROP USER 'root'@'localhost';
    Query OK, 0 rows affected (0,00 sec)
    
    mysql> DROP USER 'root'@'*';
    Query OK, 0 rows affected (0,00 sec)
    mysql> DROP USER '%';
    Query OK, 0 rows affected (0,00 sec)
  • Again insert an entry for root user in the table.
  • mysql> CREATE USER 'root'@'%' IDENTIFIED BY '';
    Query OK, 0 rows affected (0,00 sec)
  • Grant relevant permissions for that particular user of the database and make sure to flush the privileges.
  • mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
    Query OK, 0 rows affected (0,00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0,01 sec)
  • Exit and try to reconnect to the terminal without sudo.  

2 comments:

  1. Are you finding an error while receiving the coin in Binance? Looking for the perfect solutions Are you looking for the remedies to erase this error to continue your focus on the work? In order to find the best solutions,you can dial Binance phone number which is functional all the time and users can contact the team at the time of urgency. The team is functional around the clock and is one step away for you.



    ReplyDelete