Edit a user password in mysql database through CLI

Question

Ok, i'm kinda mixed up right now and need some help. I have an admin user which i changed the password from. But when i try to log in, the new password is not accepted and the old one is also not accepted anymore.

So i thought, let's alter the admin user through the sql database.

I'm on CentOS7 so i installed phpmyadmin through yum install. i edited the config file, restarted httpd and all i got is a blank page.

So, that didn't work out. So i thought that it would be possible to edit the password of the admin user through cli. I know how to login into mysql on cli and how to show the databases of it, but how do i edit the content of a specific user in a table?

• matigo: Are you still able to connect as root through the CLI? Or is it the root password that you changed? 🤔
• Alex: No, i am able to login as root into mysql and i can select a database which has users in it. I want to change the password of a user in a database.

Answer 1

MySQL supports a number of authentication plugins and, on newer versions, you are sometimes expected to specify which authentication method should be used.

For example, if you are using mysql_native_password, then a password change would look like this:

ALTER USER 'admin'@'host' IDENTIFIED WITH mysql_native_password BY 'superSecretPassword!123';

There are a number of methods described in the MySQL documentation but, generally, most installs use basic or mysql_native_password.