Trouble installing MySQL on Ubuntu 20.04 — asks for root password

[Wrichik Basu]

I wanted to install MySQL on my laptop running Ubuntu 20.04, and was following this website for instructions. I executed the following commands:

~$ 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:

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:

~$ 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:

~$ 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.

 

[optimisticsoda]

If mysql is still installed:

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:

sudo apt list --installed | grep mysql

if anything pops up, simply remove it and it's config files, e.g.

sudo apt purge mysql-common

Then after everything is uninstalled, do the following:

sudo rm /var/lib/mysql

Afterwards you can reinstall mysql and, again, you should be able to log in

 

[Wrichik Basu]

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?

 

[optimisticsoda]

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

 

[Wrichik Basu]

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:

The port is correct; I checked it:

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:

~$ 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.

 

[pbuk]

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'.

~$ 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).

$ 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).

 

[Wrichik Basu]

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.

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.

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).

$ 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!