Set MySQL Root Password on Ubuntu – Step by Step Guide

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!

Frequently Asked Questions

Why does MySQL on Ubuntu not have a root password by default?

MySQL Server is installed without prompting for a root password on Ubuntu versions 17.10 and 18.04. This allows the root user to log in without a password using 'sudo mysql -u root -p', which can be a security concern for some applications.

How can I set a root password for MySQL on Ubuntu?

To set a root password, first log in to MySQL using 'sudo mysql -u root'. Then, disable the unix_socket plugin by running 'UPDATE user SET plugin='mysql_native_password' WHERE User='root';', followed by 'FLUSH PRIVILEGES;'. Finally, use 'sudo mysql_secure_installation' to set your new password.

What is the mysql_native_password plugin?

The mysql_native_password plugin is an authentication method used by MySQL that allows users to log in using a password. Enabling this plugin for the root user is essential for applications like phpMyAdmin that require password authentication.

What should I do if phpMyAdmin fails to connect to MySQL?

If phpMyAdmin fails to connect, it may be due to the absence of a root password. You can resolve this by enabling the mysql_native_password plugin for the root user and setting a password using the 'mysql_secure_installation' command.

How do I log in to MySQL after setting a root password?

After setting a root password, you can log in to MySQL by using the command 'sudo mysql -u root -p'. You will then be prompted to enter the password you set during the secure installation process.

Categories:

Ubuntu Linux