Trouble installing MySQL on Ubuntu 20.04 — asks for root password

In summary, the user was not able to log in because they had already set a password during installation. They were able to log in using the root user, but were cautious and wanted to proceed with caution.
  • #1
Wrichik Basu
Science Advisor
Insights Author
Gold Member
2,120
2,692
I wanted to install MySQL on my laptop running Ubuntu 20.04, and was following this website for instructions. I executed the following commands:
Code:
~$ sudo apt update
~$ sudo apt install mysql-server
~$ sudo mysql_secure_installation
After installation, I found that I could log into root user without using any password. This felt a little unsafe, so following this answer on askubuntu.com, I changed the authorization plugin such that I am requested a password every time I type mysql -u root -p.

Since then, I cannot log into the root user any more. I am sure that I am entering the correct password, but it just doesn't let me in, and shows this error message:
Code:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
I thought a re-installation would help. So I uninstalled it using the following commands:
Code:
~$ sudo apt remove mysql-server
~$ sudo apt autoremove
Then I again installed it, but when I try to execute sudo mysql_secure_installation, I am asked for a password. Weird, there should be no password set because this is a fresh installation. I tried entering the previous password, and got this error:
Bash:
~$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:
Error: Access denied for user 'root'@'localhost' (using password: YES)
I tried entering no password, and got the same message as above, with the "YES" changed to "NO".

So, I cannot proceed with the installation.

Tried restarting the system so that temporary files are deleted, but no avail.

All help is appreciated.
 
Computer science news on Phys.org
  • #2
If mysql is still installed:
Code:
sudo apt purge mysql-server
This will not only remove the mysql-server package, but also all it's config files etc.
If you get a prompt asking to remove databases, click yes.
Then you can install mysql again and it should allow you to login as root without password.If mysql is not installed, things become a little more tricky:
First make sure nothing is installed concerning mysql:
Code:
sudo apt list --installed | grep mysql
if anything pops up, simply remove it and it's config files, e.g.
Code:
sudo apt purge mysql-common
Then after everything is uninstalled, do the following:
Code:
sudo rm /var/lib/mysql
Afterwards you can reinstall mysql and, again, you should be able to log in
 
Last edited:
  • Like
  • Love
Likes jack action, Greg Bernhardt, sysprog and 2 others
  • #3
Thanks a lot, @optimisticsoda, and also, welcome to Physics Forums! I followed the instructions, and they worked like a charm. Could finally install and set up the server.

One question: as before, I can login as the root user by just typing sudo mysql -u root. Is this safe enough and can I leave it at this?
 
  • #4
Thanks :)

Whether you can leave it at that or need to change depends on the situation. If others can login on the server, you should definitely create a password for the root user.
Otherwise you can leave it as is.

Oh and just sudo mysql works too
 
  • Like
Likes sysprog and Wrichik Basu
  • #5
I have just installed the MySQL workbench, and it came with a default connection to the root user with port 3306. When I click on it, I get this error:

1610485480306.png


The port is correct; I checked it:
SQL:
mysql> SHOW GLOBAL VARIABLES LIKE 'PORT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)
Basically, this is the command where the error is being thrown:
Code:
~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Note that I have already set a password during installation.

This is exactly the place where I screwed up the first time, so I want to proceed with caution.
 
  • #6
Wrichik Basu said:
One question: as before, I can login as the root user by just typing sudo mysql -u root. Is this safe enough and can I leave it at this?
Yes this is safe enough, the sudo there shows that only a (Linux) user with root privileges can do this, and if you have Linux root privileges then you can do anything on that server anyway so there is no point adding more 'security'.

Wrichik Basu said:
Code:
~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Note that I have already set a password during installation.

This is exactly the place where I screwed up the first time, so I want to proceed with caution.
This shows that without the sudo you can't attatch to MySQL as the root user, proving what I said above. Because you are not running mysql_workbench with Linux root privileges that means it can't attach to MySQL. Understanding root privileges and sudo is fundamental to administering any Linux at the console and along with file access permissions is the biggest thing you need to get your head around when moving from Windows.

You should only use the MySQL root user to create other users. To do this use the following (you can see from the prompt that I am using MariaDB which for these purposes is functionally equivalent to MySQL).
Code:
$ sudo mysql -u root
MariaDB [(none)]> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit
Bye
$
You then need to configure MySQL Workbench to use the admin user with the password; I can't help you with this, I don't use MySQL Workbench (I use phpMyAdmin instead).
 
Last edited:
  • Like
Likes sysprog and Wrichik Basu
  • #7
pbuk said:
Yes this is safe enough, the sudo there shows that only a (Linux) user with root privileges can do this, and if you have Linux root privileges then you can do anything on that server anyway so there is no point adding more 'security'.
Understood.
pbuk said:
This shows that without the sudo you can't attatch to MySQL as the root user, proving what I said above. Because you are not running mysql_workbench with Linux root privileges that means it can't attach to MySQL.
Actually I tried executing sudo mysql-workbench, but even then it could not connect to the root user.
pbuk said:
You should only use the MySQL root user to create other users. To do this use the following (you can see from the prompt that I am using MariaDB which for these purposes is functionally equivalent to MySQL).
Code:
$ sudo mysql -u root
MariaDB [(none)]> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit
Bye
$
You then need to configure MySQL Workbench to use the admin user with the password; I can't help you with this, I don't use MySQL Workbench (I use phpMyAdmin instead).
This is good advice; I created the second user and granted all privileges. Now I can connect to it via MySQL workbench, and I can execute queries normally. Thanks!
 

Related to Trouble installing MySQL on Ubuntu 20.04 — asks for root password

What is MySQL and why is it important?

MySQL is a popular open-source relational database management system. It is important because it allows users to store, manage, and retrieve large amounts of data in a structured and efficient manner.

Why am I being asked for a root password when installing MySQL on Ubuntu 20.04?

During the installation process, MySQL prompts for a root password. This password is used to secure the root user account, which has administrative privileges and can perform any action on the database server.

How do I set the root password during MySQL installation on Ubuntu 20.04?

When prompted for a root password during MySQL installation, enter a strong password and confirm it. Make sure to remember this password as it will be required for administrative tasks in the future.

Can I change the root password after MySQL installation on Ubuntu 20.04?

Yes, you can change the root password after MySQL installation. To do so, log in to the MySQL server as the root user and use the ALTER USER command to change the password.

What should I do if I forget the root password for MySQL on Ubuntu 20.04?

If you forget the root password for MySQL, you can reset it by following the steps outlined in the official MySQL documentation. This typically involves starting the MySQL server in safe mode and resetting the root password with the SET PASSWORD command.

Similar threads

  • Computing and Technology
Replies
5
Views
1K
  • Computing and Technology
Replies
24
Views
8K
Replies
2
Views
2K
  • Computing and Technology
Replies
1
Views
2K
  • Programming and Computer Science
Replies
6
Views
1K
  • Computing and Technology
Replies
2
Views
3K
Replies
16
Views
2K
Replies
3
Views
2K
  • Programming and Computer Science
Replies
12
Views
9K
  • Computing and Technology
Replies
15
Views
4K
Back
Top