Can we connect to mysql as root without password?

Have you noticed MySQL server is now installed on Ubuntu 17.10 and 18.04 without root passwords? The root user can simply run sudo mysql -u root -p and be logged in without passwords. This is pretty scary in a production environment.

Recently I was testing MySQL database server on Ubuntu 17.10 / 18.04 and discovered that MySQL database server now installs on Ubuntu without prompting the root user for password to access the server.

Is this new?

It’s always been the case where MySQL prompts for passwords everytime before access is granted to the server. Apparently, not anymore. Now simply installing the database gives the root access without passwords. and this my not be something everyone wants.

Even after running the command sudo mysql_secure_installation. the root account password is never required. However, other applications and services that depend on MySQL will fail if the root password is needed for authentication.

phpMyAdmin and MySQL Workbench database may fail if MySQL is setup this way. so if you want to run phpMyAdmin and other MySQL tools that requires root authentication, you may want to enable mysql_native_password plugin. follow the steps below to enable it.

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

After digging a bit, I discovered that MySQL uses unix_socket plugin to authenticate. and not passwords. Even if you set a password, it is ignored. To re-enable password authentication, follow the steps below:

Logon to 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 disable 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 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 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 logon with password authentication. and other applications should now work with the root password authentication.

The next time type the commands below to logon

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)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Enjoy!

You may also like the post below:

Hi, I'm Richard.

In my spare time, I research topics that are interesting and worthwhile for users and students who want to try something new. I, too, am a student and my focus here is to help other students and new users get started with managing Ubuntu Linux, Windows, Content Management Systems (CMS) and others.

I try to do my best explaining the topics and detailing the instructions so that anyone can understand. These tutorials may not work in all situations and for all users. However, if you run into trouble, please ask your questions below and I or someone from the community may help you resolve. Thanks for reading and hope you come back.

~Richard

View all posts by Richard