Have you noticed MySQL server is now installed on Ubuntu 17.10 and 18.04 without root passwords? So the root user can run sudo mysql -u root -p and be logged in without passwords.
Recently I was testing the MySQL database server on Ubuntu 17.10 / 18.04 and discovered that the MySQL database server now installs on Ubuntu without prompting the root user for a password to access the server.
Is this new?
It’s always been the case where MySQL prompts for passwords every time before access is granted to the server. Not anymore. Now simply installing the database gives the root access without passwords. And this may 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 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. 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)
Enjoy!
Thanks a lot!!
Hi, this tips are great, thank you so much!
Welp, you helped me, thanks.
Thank you very much! I was lost and some other procedures did not work for me!
Thank you very much for your great article. I tried to search for the solution for few days and you just saved me.
Thanks I was very stuck this fixed my exact problem!
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.
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.
thanks, very helpful.
Thank you very much, it was driving me crazy that i was able to log in without password.
Does this procedure work also for non-root user?
Thank you very much.
Thanks
shaktik