1

After changing mysql password, I did executed skip-grant-tables, so when I try to login to mysql without password I cannot login. It gives access denied message. Why is that

Following are the steps I did. First I installed mysql and changed the password

1) sudo yum install mysql-community-server
2) sudo service mysqld start
3) sudo grep 'temporary password' /var/log/mysqld.log
4) mysql -uroot -p
5) ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

Then tried to login with skip grant tables
6) sudo service mysqld stop
7) mysqld_safe --skip-grant-tables &
Now I get Access denied message

Bimz
  • 21
  • 4
  • You need to add some more details about what you did and tried. You cannot "execute" skip-grant-tables, you can start the server with it. Did you do that? Also, why did you do that? (If you changed your password, you know it, right? So what are you trying to achieve with that?) How are you trying to connect? My best guess is that you are connection from some remote client (which MySQL will not allow, otherwise, anyone could connect to your server without password). – Solarflare Apr 27 '18 at 16:30
  • In here this scenario is used by an installation script. So the password change can be done by some other party and when installing I need to use skip grant tables since I'm not aware of the password set by them. – Bimz May 02 '18 at 04:46
  • Thanks for the edit. You may be running into the problem described [here](https://stackoverflow.com/q/41984956/6248528) – Solarflare May 02 '18 at 11:46

1 Answers1

0

I recently upgrade my Ubuntu 16.04 to 18.04 and this has worked for me:

  1. First, connect in sudo mysql

    sudo mysql -u root

  2. Check your accounts present in your db

    SELECT User,Host FROM mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| admin            | localhost |
| debian-sys-maint | localhost |
| magento_user     | localhost |
| mysql.sys        | localhost |
| root             | localhost |

  3. Delete current root@localhost account 

    mysql> DROP USER 'root'@'localhost';
Query OK, 0 rows affected (0,00 sec)

  4. Recreate your user

    mysql> CREATE USER 'root'@'%' IDENTIFIED BY '';
Query OK, 0 rows affected (0,00 sec)

  5. Give permissions to your user (don't forget to flush privileges)

    mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0,00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0,01 sec)

  6. Exit MySQL and try to reconnect without sudo.

This will make you login.

Karthik
  • 105
  • 10