This is a short guide on how to install and provide a basic configuration for MySQL with an initial user and database.

I’m providing the steps for a Ubuntu 20.04 but should not be too much different from any other distribution.

From the terminal run

apt search mysql-server

The result should be something similar to this

$ apt search mysql-server
Sorting... Done
Full Text Search... Done
default-mysql-server/focal 1.0.5ubuntu2 all
  MySQL database server binaries and system database setup (metapackage)

default-mysql-server-core/focal 1.0.5ubuntu2 all
  MySQL database server binaries (metapackage)

mysql-server/focal-updates,focal-security 8.0.25-0ubuntu0.20.04.1 all
  MySQL database server (metapackage depending on the latest version)

mysql-server-8.0/focal-updates,focal-security 8.0.25-0ubuntu0.20.04.1 amd64
  MySQL database server binaries and system database setup

mysql-server-core-8.0/focal-updates,focal-security 8.0.25-0ubuntu0.20.04.1 amd64
  MySQL database server binaries

We need to install the mysql-server-8.0

sudo apt install -y mysql-server-8.0

Job done, all we need to do at this stage is to verify that is actually installed and run the initial configuration that will consists in:

• run the mysql_secure_installation
• create a database
• create a user and grant privileges

1 – run the mysql_secure_installation

from the command line type

sudo mysql_secure_installation

You will be asked a series of question and in particular

• Would you like to setup VALIDATE PASSWORD component? This is up to you, for my guide here I just type no
• New Password: here we are deciding which will be the MySQL root password (please don’t chose “password”, also if is a test)
• Remove anonymous users? (Press y|Y for Yes, any other key for No) – It’s up to you but usually is a Y
• Disallow root login remotely? (Press y|Y for Yes, any other key for No) – It depends on your configuration, usually you don’t want to allow root to authenticate remotely to your MySQL server, but if like in this case is a test and learning and a virtual machine in VirtualBox I would say why not
• Remove test database and access to it? (Press y|Y for Yes, any other key for No): pretty obvious to say Y
• Reload privilege tables now? (Press y|Y for Yes, any other key for No) : I would say Y

All done! Now we have our initial basic configuration and what is needed at this stage is the creation of an initial database and a user with read/write permission on that table(because we don’t want to give root permission to everyone).

2 – Create the MySQL user, no wait…let’s fix it first

Let’s create the user then, from the command line execute this command:

mysql -uroot -p<the password done at step "New Password above">

ops we got an error like this

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

The reason is that is a new installation so we need to adjust few minor things before actually login using the command line. The root is set by default to authenticate using the auth_socket so we have 2 options

1 – is to change the authentication method

2 – bind the system user (the one you use for login to the shell of Ubuntu to be clear) to a db user

Option 1 – change the MySQL root authentication method:

sudo mysql -uroot 

Once logged in (please note that at this step we have to set the root password again)

USE mysql;
UPDATE user SET plugin='mysql_native_password' WHERE User='root';
ALTER user 'root'@'localhost' IDENTIFIED by '<your_password>';
FLUSH PRIVILEGES;
exit;

Option 2 – bind the system user to a MySQL database user:

sudo mysql -uroot 

USE mysql;
CREATE USER 'your_system_user'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'your_system_user'@'localhost';
UPDATE user SET plugin='auth_socket' WHERE User='your_system_user';
FLUSH PRIVILEGES;
exit;

The option you want to use is up to you, generally the MySQL server is not running on the machine you have your application or web app but is always (or in most cases) remotely accessible by a user and host defined. Something like the image below where User 1 and User 2 will be granted access to different databases tables with different permissions within the MySQL instance.

3 – Create the MySQL user

For creating the MySQL user just login to the mysql shell

mysql -uroot -p<your_password>

and run

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;
exit;

The commands above will create the “newuser” that actually has all the permission on all the database and tables (some sort of admin) that for now is enough for our testing.

Let’s login with the new created user

 mysql -unewuser -p

and create a database using the “create database” command

create database playground;
Query OK, 1 row affected (0.01 sec)

with the “show database” command we can verify that playground is there

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| playground         |
| sys                |
+--------------------+

Now we can create a simple test table and insert a record

use playground;
create table test_table(
   id INT NOT NULL AUTO_INCREMENT,
   title VARCHAR(100) NOT NULL,
   creation_date DATETIME,
   PRIMARY KEY ( id )
);

Then we can add few records

insert into test_table(title, creation_date) values ("title_1", now()), ("title_2", now()), ("title_3", now());

and we can select

mysql> select * from test_table;
+----+---------+---------------------+
| id | title   | creation_date       |
+----+---------+---------------------+
|  1 | title_1 | 2021-07-07 08:48:34 |
|  2 | title_2 | 2021-07-07 08:48:34 |
|  3 | title_3 | 2021-07-07 08:48:34 |
+----+---------+---------------------+
3 rows in set (0.00 sec)

And this is the end of this small guide.