After installing MySQL 8 on Ubuntu 20.04 you will notice there's no way to create databases using tools like DBeaver because the root user is only accessible from the mysql client on the local machine.
A good way to solve this problem is to create a superuser that can administer MySQL and leave the root user as is, so that you can always connect from the mysql client with root should you need to.
This tutorial will show you how to create an admin user that can be used by tools like DBeaver to create new users and databases.
Step 1: Connect to The MySQL Command-Line Client with root
mysql command with sudo so that you connect with the root user:
Step 2: Create a user
Run the following commands to create a user called admin:
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'my very strong password';
You should now be able to connect with DBeaver using admin for the user name and whatever strong password you set above. However, you won’t be able to do anything with the user yet because we haven’t assigned it any privileges.
Run the following commands to grant the user privileges to do anything:
GRANT ALL ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
You should now be able to connect with DBeaver and create new users or databases.
Step 3: Test connection
Let’s test to see if DBeaver can connect to MySQL with the admin user. In the following example, I’m using DBeaver on my Mac connecting to a MySQL server running inside a Ubuntu 20.04 virtual machine on VMware Fusion.
As you can see from the following screenshots. I’m connecting over SSH using the admin@localhost user account.
Notice in the config above, I’m using the 10.1.1.21 as the IP address and dev as the username. This is the IP and login credentials for my Ubuntu 20.04 VM.
Hitting the Test button shows the admin user is able to connect to MySQL. We should now be able to create a database with this user.
Step 4: Create a Database with DBeaver
Let’s test the user can create a database. Expand the tree view for the connection, right click then click Create New Database.
Enter a name for a database, for example helloworld then click OK.
The database should now appear in the databases list.
In this post, we learned that by default the root user on MySQL 8 can’t be used by tools like DBeaver to manage databases. Instead, we created a superuser that has permissions to create users and databases.