Install MySQL in Ubuntu 22.04 LTS Jammy Jellyfish

In this guide we will see how to install MySQL 8.0 in Ubuntu 22.04 Jammy Jellyfish via apt, the default package manager

It is very common in our Linux Support, being the basis for LAMP (Linux, Apache, MySQL, PHP), LEMP (NginX) stacks and several others, necessary for webapps, CMS and e-Commerce

Installing MySQL in Ubuntu 22.04

As a first step, we update the list of available packages via

sudo apt update

We proceed to install MySQL with this command

sudo apt install mysql-server

The server should already be active, let's check if the process is active

sudo service mysql status

In case it is not active we can restart MySQL and repeat the check

sudo service mysql restart

* Stopping MySQL database server mysqld [ OK ].
* Starting MySQL database server mysqld [ OK ]

Let's check the default port of MySQL, the 3306 TCP, which should be listening at this point

sudo apt install net-tools

sudo netstat -tnplu |grep 3306

The command should return the following output, showing port 3306 listening locally

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN -
tcp6 0 0 :::33060 :::* LISTEN -

Configure MySQL

The server is up, for a new installation the configurations used are the default ones, security is poor, at this point it is recommended to run the DBMS configuration script to set up authentications and remove anonymous logins

sudo mysql_secure_installation

The script will guide us in configuring the server security options

First we enable password control for users, we select "strong passwords," which will be checked against a dictionary of the most common passwords

Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of passwords
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: Y

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 file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2

Skipping password set for root as authentication with auth_socket is used by default.
If you would like to use password authentication instead, this can be done with the "ALTER_USER" command.
See https://dev.mysql.com/doc/refman/8.0/en/alter-user.html#alter-user-password-management for more information.

Remove anonymous users, to increase security

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 production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y

Configure local user authentication root, which will be via socket, only on the local machine

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) : Y

Remove test databases, useless in production

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) : Y

Let's reread the privilege tables, which contain the users, so that our changes will take effect immediately.

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

Access MySQL from console

We have finished installing MySQL in Ubuntu 22.04, now we can log in with user root from our server console, and as a test, list databases

sudo mysql -u root

show databases;
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

The output shows us the current databases, we were able to install MySQL in Ubuntu 22.04, we can use with the usual syntax our MySQL, connect our web apps and CMS

MySQL root password

To access remotely we will need to set the MySQL root password. We can use this command, but we recommend using a "strong" password, with numbers, upper and lower case, and special characters.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'LA_MIA_PASSWORD';

Accessing remote MySQL

To access a remote MySQL it is recommended to use an SSH tunnel by redirecting the remote 3306 port of our server, which only listens in localhost, to our local 33006 port

ssh root@IP-o-DNS -L 33006:localhost:3306

Thanks to the tunnel we could connect directly to our port 33006 in localhost or 127.0.0.1 with the root credentials created earlier

For this we can use one of these commands:

mysql -h localhost -P 33006 -u root -p

mysql -h 127.0.0.1 -P 33006 -u root -p

Logging in with MySQL Workbench

MySQL Workbench is a great tool for Database Admin (D.B.A.) MySQL and Operation, allows you to create DBs, manage server configuration, administer users, make backups, and more, and also incorporates the SSH tunneling capability already described above

1. We create a new connection
2. We select Standard TCP/IP over SSH
3. We enter the SSH configurations for our server of
   1. SSH Host name
   2. SSH User name
   3. Password or SSH key
4. The MySQL Database configurations
   1. Hostname
   2. Server Port
   3. Username
   4. Password
5. We test the connection

Want support to configure your database in production? We're by your side with our Linux consulting