Logging in MySQL as root works only on sudo

I am on Ubuntu 20.04 and installed the default version of mysql. But I can't seem to login as root without using sudo. Is this a restriction on the newer version ? I am sure it wasn't like this on previous versions of Ubuntu - 14.04, 12.04 etc.

mysql --user=root -p
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

.

sudo mysql --user=root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 80
Server version: 8.0.22-0ubuntu0.20.04.3 (Ubuntu)

1 Answer

This is intentional functionality. The only person who should ever be permitted to connect to the MySQL server as root should be the owner of the server. Nobody else. Naturally, this does create problems for people who wish to have a single account that can do literally anything with the MySQL instance, such as developers and weekend server admins.

What I would suggest is this: Create an account in MySQL that has elevated permissions that only you use. If this MySQL installation is also used by applications and websites, create individual accounts for each of those as well, granting adequate permissions only to the databases that they need.

This is how you can create your elevated and normal accounts in MySQL:

0. Log into MySQL as root

1. Create a new admin user:

   CREATE USER 'admin'@'localhost' IDENTIFIED BY 'SuperSecretPassword!123';
   GRANT ALL ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

   If you will be connecting to MySQL from outside the server, also do this:

   CREATE USER 'admin'@'%' IDENTIFIED BY 'SuperSecretPassword!123';
   GRANT ALL ON *.* TO 'admin'@'%' WITH GRANT OPTION;

2. Create application-specific accounts (for example, WordPress):

   CREATE USER 'wp'@'localhost' IDENTIFIED BY 'SuperSecretPassword!123';
   GRANT ALL ON `wp`.* TO 'wp'@'localhost';

   Do this for each application, ensuring they cannot access each other's database.

3. Flush privileges to ensure everything is set:

   FLUSH PRIVILEGES;

Hope this helps 👍🏻