Learn why MySQL Server has no password for Root on Ubuntu Linux

|

|

MySQL server now installs on Ubuntu 17.10 and 18.04 without asking for root passwords, which may result in failed operations for applications requiring root password for authentication, such as phpMyAdmin. This can be addressed by enabling the mysql_native_password plugin, which allows root password setup and enabling password authentication.

This article explains why MySQL Server has no root password on Ubuntu Linux.

This article explains that MySQL Server has no root password by default on Ubuntu Linux. As of Ubuntu 17.10 and 18.04, the MySQL server is installed without prompting the user for a root password. This means the root user can log in to MySQL without a password by running the command ‘sudo mysql -u root -p.’ While this may seem unusual to some users, it’s not new.

MySQL has always prompted passwords before granting access to the server, but now, the root user can access the server without a password. This can be problematic for some applications and services that depend on MySQL for authentication.

To solve this issue, users can enable the mysql_native_password plugin to allow root authentication.

phpMyAdmin and MySQL Workbench database may fail if MySQL is set up this way. So, if you want to run phpMyAdmin and other MySQL tools that require root authentication, you may want to enable the mysql_native_password plugin.

This brief tutorial will show students and new users how to set a root password for MySQL and allow password authentication.

After digging, I discovered that MySQL uses the unix_socket plugin to authenticate, not passwords. So, even if you set a password, it is ignored.

To re-enable password authentication, follow the steps below:

Login to the MySQL server by running the commands below

sudo mysql -u root

Notice no password?

That should get you into the database server. After that, run the commands below to turn off plugin authentication for the root user.

USE mysql;
UPDATE user SET plugin='mysql_native_password' WHERE User='root';
FLUSH PRIVILEGES;
exit;

Restart and run the commands below to set a new password.

sudo systemctl restart mysql.service

After that, run the commands below to secure the MySQL server and create a new root password.

sudo mysql_secure_installation

When prompted, answer the questions below by following the guide.

  • Enter current password for root (enter for none): Just press Enter
  • Set root password? [Y/n]: Y
  • New password: Enter the password
  • Re-enter new password: Repeat password
  • Remove anonymous users? [Y/n]: Y
  • Disallow root login remotely? [Y/n]: Y
  • Remove test database and access to it? [Y/n]:  Y
  • Reload privilege tables now? [Y/n]:  Y

You should now be able to log on with password authentication. Other applications should now work with root password authentication.

The next time, type the commands below to log

sudo mysql -u root -p

Then, type the password to sign on

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22-0ubuntu18.04.1 (Ubuntu)

Enjoy!

Like this:



12 responses to “Learn why MySQL Server has no password for Root on Ubuntu Linux”

  1. ninsky Avatar
    ninsky

    Thanks a lot!!

  2. Fernando Avatar
    Fernando

    Hi, this tips are great, thank you so much!

  3. Jkt44 Avatar
    Jkt44

    Welp, you helped me, thanks.

  4. Peter Avatar
    Peter

    Thank you very much! I was lost and some other procedures did not work for me!

  5. Sam Avatar
    Sam

    Thank you very much for your great article. I tried to search for the solution for few days and you just saved me.

  6. Chris Avatar
    Chris

    Thanks I was very stuck this fixed my exact problem!

  7. Jerk0 Avatar
    Jerk0

    Don’t give people sudo or root if you are scared. This does nothing to improve security since the root user can reset the mysql password or destroy the data anyway still.

  8. ersin Avatar
    ersin

    We don’t give terminal root access to people, we serve them application such as php which can be run under the root privileges. This is very nasty. Thanks for the solution.

  9. Motlik Avatar
    Motlik

    thanks, very helpful.

  10. Ivan Pedrero Avatar
    Ivan Pedrero

    Thank you very much, it was driving me crazy that i was able to log in without password.

  11. Alberto Avatar
    Alberto

    Does this procedure work also for non-root user?

  12. Shakti kumar sah Avatar
    Shakti kumar sah

    Thank you very much.
    Thanks
    shaktik

Leave a Reply to Jkt44 Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.