EmergencyEMERGENCY? Get 24/7 Help Now!

Change user password in MySQL 5.7 with “plugin: auth_socket”

 | March 16, 2016 |  Posted In: MySQL

PREVIOUS POST
NEXT POST

change user password in MySQLIn this blog, we’ll discuss how to use “plugin: auth_socket” to change user password in MySQL 5.7. In

In Debian/Ubuntu it is pretty common to install MySQL/Percona Server with an empty password for the root user. After everything is configured and tested, then a password is set. This is not a good practice in production servers (or soon-to-be production servers), but you can do it for your own test servers. With regards to authentication, things have changed a bit in 5.7, and methods that worked before now need a different procedure.

Let’s say that you install 5.7 and don’t specify a password. You will see the following:

OK, the password is empty. Let’s change it:

That doesn’t work, it’s still empty:

But why? Let’s check the warnings:

The problem is in the note coded 1699. This user is using an authentication plugin that doesn’t support a password. Which one could it be?

Ok, auth_socket. If you install 5.7 and don’t provide a password to the root user, it will use the auth_socket plugin. That plugin doesn’t care and doesn’t need a password. It just checks if the user is connecting using a UNIX socket and then compares the username.

If we want to configure a password, we need to change the plugin and set the password at the same time, in the same command. First changing the plugin and then setting the password won’t work, and it will fall back to auth_socket again. So, run:

So, the correct way to do this is to run the following:

And now, it works 🙂

If your deployments use empty passwords, and you change them later on, remember to update your scripts/recipes!

PREVIOUS POST
NEXT POST
Miguel Angel Nieto

Miguel joined Percona in October 2011. He has worked as a System Administrator for a Free Software consultant and in the supporting area of the biggest hosting company in Spain. His current focus is improving MySQL and helping the community of Free Software to grow.

17 Comments

  • thanks a lot for this post. I have spent the last 24 hours recovering from failed upgrade from ubuntu 14.04 to 16.04. The upgrade of mysql failed, and left the database inaccessible. At least one of the problems turned out to be that the root password was “”. Your process above allowed me to login to the db again.

    If you ever get to Portland ORE, I will buy you a beer. Cheers!

  • If the commande “ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘test’;” doesn’t work you can try :

    UPDATE mysql.user SET authentication_string = PASSWORD(‘test’), plugin = ‘mysql_native_password’ WHERE User = ‘root’ AND Host = ‘localhost’;
    FLUSH PRIVILEGES;

    It worked for me.

    Thanks for the hint, anyway !

  • I had the same problem as Huck after upgrading Ubuntu to 16.04. It’s frustrating when you need access to your database but you can’t. I’m using GnuCash with a MySQL database. This post solved my problems.

    Thank you, you saved me from a lot of frustration and searching for a solution.

  • fresh install percona in ubuntu 14.04 :
    i cant get root password
    and i tried to login into
    mysql -u root -p
    always fail
    how to login to mysql root with blank password (fresh install)
    i didn’t understand socket auth things
    thanks

  • Thanks a lot, this worked perfectly for Ubuntu 16.04.
    This is actually the only thing that really works out of the box with a simple statement… there are lots of posts on Stackoverflow and other sites with complex solutions (that don’t work) and require you to start mysql in safe mode.

  • Many thanks Miguel! I was having problems accessing our Mantis Bug Tracking DB after an upgrade from Ubuntu 12.04 to 16.04. We had a blank password. None of the other methods that I found on Google worked – until I found this one.

  • Using MariaDB and i have sort of the same problem.
    It seems my installation is useing the auth_socket plugin but the query’s listed did not help me change/set the password.
    At the moment i don’t have root acces.

    ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘test’

    comes back with the error:

    ALTER: command not found

    and:

    UPDATE mysql.user SET authentication_string = PASSWORD(‘test’), plugin = ‘mysql_native_password’ WHERE User = ‘root’ AND Host = ‘localhost’;

    comes back with the following:

    ERROR 1054 (42S22): Unknown column ‘‘root’’ in ‘where clause’

    This is after logging in to MariaDB with sudo mysql.
    Should be root acces to the database server.
    Also i only get to see an -> when i execute the query:

    SELECT user,host FROM mysql.user

Leave a Reply