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

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 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!

Share this post

Comments (31)

  • huck

    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!

    May 25, 2016 at 2:10 pm
  • Miguel Angel Nieto

    Hey Huck, I will let you know! I want that beer 😉

    May 25, 2016 at 3:57 pm
  • Tonya Ohrel

    I could kiss you!

    June 17, 2016 at 6:22 pm
  • Mih

    Oh thank you, it’s perfect. You deserve really a good bier. In France if you come, it will be with pleasure.

    June 21, 2016 at 4:28 pm
  • Miguel Angel Nieto

    Beers and kisses are always welcome 😛 Glad to see you have found the post useful. Thanks for the feedback.

    June 29, 2016 at 3:58 am
  • iow

    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’;

    It worked for me.

    Thanks for the hint, anyway !

    August 4, 2016 at 8:54 am
    • boonsiri

      thank you very much it work for me too.

      August 24, 2016 at 12:38 pm
    • TJ Khara


      When I tried what is suggested in this article I get the following problem:

      mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘RSbj1409’;
      ERROR 1396 (HY000): Operation ALTER USER failed for ‘root’@’localhost’

      When I tried what you have suggested I get this:

      UPDATE mysql.user SET authentication_string = PASSWORD(‘test’), plugin = ‘mysql_native_password’ WHERE User = ‘root’ AND Host = ‘localhost’;
      ERROR 1054 (42S22): Unknown column ‘‘root’’ in ‘where clause’

      Could you please suggest what else I could try to resolve this?


      July 30, 2018 at 2:44 pm
      • Hamid Abbasi

        you should replace all single quotation marks with English keyboard ” ‘ ” .

        November 18, 2018 at 8:53 am
  • khessels

    Thanks man, Worked like a charm…

    August 9, 2016 at 10:56 pm
  • Anders

    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.

    August 15, 2016 at 2:13 pm
  • Matthew Watkins (NCSU)

    Thank you so much, you saved my laptop from being destroyed by an angry fist of death just now

    August 25, 2016 at 9:59 am
  • Bhargava Sai Yenugula

    thank you very much for this post 🙂

    August 28, 2016 at 10:13 am
  • Álvaro Larumbe

    Thanks a lot! You saved a lot of time!

    September 19, 2016 at 3:43 am
  • blogpakhaji

    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

    December 28, 2016 at 2:26 am
  • Sonu Sindhu

    That is exactly what I was looking from an hour or so.
    Thanks man!

    February 3, 2017 at 1:24 pm
  • Penca Seca

    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.

    February 3, 2017 at 1:55 pm
  • Jeremy Morgan

    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.

    October 18, 2017 at 6:07 am
  • Erik Jansen

    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


    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

    November 3, 2017 at 7:46 pm
    • TJ Khara

      Facing a very similar issue with MySQL. Did you figure out what can be done?

      July 30, 2018 at 2:46 pm
  • joe shmoe


    February 2, 2018 at 2:14 am
  • Caribou

    I was looking for 2 hours how to set this ********* password, thank you very much !!!

    April 13, 2018 at 4:39 am
  • Wells

    You saved me!!!!!! Thank you xoxoxoxo

    May 1, 2018 at 3:00 pm
  • Bridge

    Yes, this solution works, great.

    May 3, 2018 at 3:11 am
  • Theo

    Thanks for your solution. I have a fresh installation with ubuntu server 18.04 and phpmyadmin had no permissions at all.

    May 8, 2018 at 4:45 am
  • Jake

    Thank you for this, I could see the problem but was not finding any way to change the authentication type for the root user. Your solution finally worked.

    May 22, 2018 at 11:45 am
  • TJ Khara


    I think this is the closest I have reached to solving this problem, but I’m still not done.

    Here is the error message I see when I do as you’ve suggested in this article:

    mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘test’;
    ERROR 1396 (HY000): Operation ALTER USER failed for ‘root’@’localhost’

    Could you please see what else I could do to resolve this?


    July 30, 2018 at 2:43 pm
  • Raymond

    Finally a solution that works! Thanks so much. In summary I had to do

    sudo mysql -u root
    use mysql;
    ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘test’;

    November 21, 2018 at 9:03 pm
  • Tom

    Thank you Thank you Thank you. I’ve spent hours of research over two days. This is the only thing that worked.

    Fresh install of apt install mysql-server from ubuntu 18.10 repository. No root password (or any access).to mysql

    My Fix:
    sudo mkdir /var/run/mysqld
    sudo chown mysql:mysql /var/run/mysqld
    sudo service mysql stop
    sudo mysqld_safe –skip-grant-tables –user=mysql &
    >ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘your_new_password’;

    The FLUSH PRIVILEGES was a requirement. Throws skip-grant-tables error if you don’t.

    December 4, 2018 at 10:47 am
  • b8RvYtXAH GM

    9Kf7 GVfwe7aObPRI9Ksa8 NHr

    February 25, 2019 at 7:59 am
  • ijf8090

    Works for me on MySQL Server version: 5.7.28-0ubuntu0.18.04.4 (Ubuntu)

    Minor typo
    mysql> SELECT * from user where User=”root”G
    should be
    mysql> SELECT * from user where User=”root”;
    This generate a lot of extra data that make the output hard to read so I did

    SELECT Host,User,plugin,authentication_string from mysql.user where User=”root”;

    Add me to the beer list

    Best regards

    January 21, 2020 at 9:46 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.