How to install MySQL on Ubuntu 20.04 (LAMP)

We will use apt to install it:

$ sudo apt install mysql-server -y

Once done, we will run a security script that comes pre-installed with MySQL. It removes some insecure default settings Start the interactive script by running:

$ sudo mysql_secure_installation

You will now be asked a series of questions:

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No:

This is a component that helps assure you use difficult passwords for database access. Simply enter Y here to indicate YES, followed by [ENTER]

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:

This asks you to decide how difficult you want to make the passwords for hackers to guess. We suggest STRONG, which will make sure that all database passwords are at least 8 characters long, have both upper and lowercase letters, have at least one numeric character, some special characters like !,@, etc., and don't contain common dictionary words. For example mypassword would not be accepted, but mY-pA$w0rD! would be accepted, although we don't suggest using that one.

Simply enter 2 here to indicate STRONG, followed by [ENTER]

Next you will be prompted for a password. This is the password for the root account in the database. Replace MYDBROOTPASS with the password you chose for the root account.

Please set the password for root here.

New password: MYDBROOTPASS

If your password is strong enough, you will be asked to repeat it to make sure you got it right, then you see an estimation of how strong your password is. It should be 100.

Estimated strength of the password: 100 
Do you wish to continue with the password provided?<br>(Press y|Y for Yes, any other key for No) : 

Simply enter Y here to indicate YES, followed by [ENTER]

Next you will see the following:

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a productio
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : 

Simply enter Y here to indicate YES, followed by [ENTER]

Next you will see:

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : 

This makes it so that you cannot login to MySQL as root over a remote connection, and helps further secure your server from attacks.

Simply enter Y here to indicate YES, followed by [ENTER]

Next:

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) :

Simply enter Y here to indicate YES, followed by [ENTER]

Finally:

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) :

Simply enter Y here to make sure your changes take effect, followed by [ENTER]

Finally, let's make sure MySQL is working:

$ sudo mysql

You should see something similar to the following output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 363135
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

As you can see, I installed version 8.0.27. You will likely see a newer version. You should also see that your command prompt is now mysql>. This means we are working directly with MySQL now. So let's exit for a moment.

mysql> exit

Why it Didn't Ask for a Password

Of course, we set up a root password for MySQL, and you just got in without it. This is because the default authentication method for this user is unix_socket instead of password. This is still secure because the only users allowed to log in as the root MySQL user are system users with sudo privileges that are connecting from the console or through an application running with the same privileges. So even though you got in without a password, you will not be able to use the root user to connect from an PHP application. We set a password to be safe in case the default authentication method is changed from unix_socket to password.

Create Your MySQL User

It is generally best never to use the root account, so we will make a different user that has full access to the server. Also, keep in mind that for every application you have on your server that uses a database, you should have a separate user that only has access to the application's database.

$ sudo mysql

mysql> CREATE USER 'MYDBUSER'@'%' IDENTIFIED WITH mysql_native_password BY 'MYDBUSERPASS';

Remember, MYDBUSER should be the user you are creating to manage all databases in general, and MYDBUSERPASS should be a very difficult and long password. Again, we strongly suggest you use a password manager like NordPass to store these complex passwords.

Now we will give this user permission over any database on the server:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'MYDBUSER'@'%' WITH GRANT OPTION;

Note the percentage sign (%). This means this user can access the database remotely, but there is something else we will need to do to enable this later in this tutorial. Normally instead of %, you would enter localhost to keep people from the outside from getting into your database(s).

Create a Database on Our Server

Generally before you install any application that requires MySQL, you will need to create a database for it. This should include a name for the new database, a new user dedicated to that database, and a password for that new user.

mysql> CREATE DATABASE MYAPPDATABASE;
mysql> CREATE USER 'MYAPPUSER'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MYAPPPASS';
mysql> GRANT ALL PRIVILEGES ON MYAPPDATABASE.* TO 'MYAPPUSER'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> exit;

Now see if the database user you just created can login:

mysql -u MYAPPUSER -p

You will be asked to enter the password you just creted for MYAPPUSER. If all went well, you should now be logged in as MYAPPUSER with a mysql> prompt.

Now let's see what you have access to:

mysql> SHOW DATABASES;

You should see the following output:

+--------------------+
| Database           |
+--------------------+
| MYAPPDATABASE      |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

Now let's exit.

mysql> exit

Accessing Your Database

Many people install an application called phpMyAdmin, which does make things easy, but introduces more potential targets for attack. We prefer to use an app to remotely access and manage our databases. If you have. Mac, there is a great free one in the App Store called Sequel Ace. If you are running on Windows, consider HeidiSQL.

---