MySQL is a popular open-source database management system that is frequently included in the LAMP stack, which is an acronym that stands for Linux, Apache, MySQL, PHP/Python, and Perl. The relational model is implemented, and SQL, which stands for Structured Query Language, is used to handle the database's contents.
This tutorial will walk you through installing MySQL 8.0 on a server that is running Ubuntu 20.04 LTS. After finishing it, you will have a fully working relational database at your disposal, which you can put to use in the development of your next website or application.
PREREQUISITES
One server operating Ubuntu 20.04 with a non-root administrative account and a UFW-configured firewall are required for you to be able to follow along with this tutorial successfully.
INSTALLATION OF MYSQL IN THE FIRST STEP
Utilizing the APT package repository on Ubuntu 20.04 is required in order to successfully install MySQL. Version 8.0.27 of MySQL is the most recent release that can be downloaded from the official Ubuntu repository.
Before you install it, make sure that the package index on your server has been brought up to date, if you haven't done so recently:
$ sudo apt update
Once that is complete, install the mysql-server package:
$ sudo apt install mysql-server
Confirming that the server is up and running may be done using the systemctl start command:
$ sudo systemctl start mysql.service
MySQL will be installed and started up after you run these instructions, but you won't be prompted to configure it or choose a password. Because of this vulnerability, we will attend to this next because it affects your MySQL installation.
STEP 2: SETTING UP MYSQL
When installing MySQL for the first time, you are required to run the security script that is included with the database management system. This script adjusts a number of the default settings, some of which are less secure than others. These include sample users and remote root logins.
Note that beginning in July 2022, if you attempt to run the MySQL Secure Installation script without first doing further settings, an error will be generated. This is due to the fact that, by default, Ubuntu installations do not have the root MySQL account configured to connect with a password. As a result, this script will attempt to establish a password for that account.
The script will thereafter start an infinite recursion that can be terminated only by closing the terminal window.
To ensure that your MySQL installation is as safe as possible, it is nevertheless recommended that you perform the mysql secure installation script prior to utilizing MySQL for data management. To break this loop, however, you must first alter the authentication mechanism that root MySQL user is using.
FIRST, ACCESS THE MYSQL PROMPT:
$ sudo mysql
The following ALTER USER command will change the root user's authentication method to require a password. Here's an example of switching to the mysql native password authentication method:
$ mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
After making this change, exit the MySQL prompt.
$ mysql> exit
When the security script has finished running, you will be able to restart MySQL and change the authentication method for the root user to auth socket, which is the default configuration. This will restore MySQL to its original state. Execute the following command in order to log in to MySQL as the root user while using a password:
$ mysql -u root -p
After that, you may revert to the standard authentication procedure by using this command:
$ mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
This means you can log in to MySQL as the root user by using the sudo mysql command.
PLEASE MAKE USE OF SUDO TO CARRY OUT THE SECURITY SCRIPT:
$ sudo mysql_secure_installation
You will then be led through a series of prompts that will allow you to alter the security settings for your MySQL installation as the process progresses. In the first stage, you will be asked whether you would want to install the Validate Password Plugin, which is a tool that can be utilized to determine the level of difficulty of new passwords for MySQL users before accepting them.
Each MySQL user that you create and that authenticates using a password must have a password that complies with the policy that you pick when you set up the Validate Password Plugin, if you choose to set it up. If you do not set it up, each MySQL user that you create and that authenticates using a password must have a password that conforms with the policy.
STEP 3: ESTABLISHING A SPECIALIZED MYSQL USER AND GRANTING PRIVILEGES
During the installation process, MySQL will establish a root user account for you to use. This account will give you complete power over your database. This user has access to all of the databases, tables, users, and other resources that are stored on the MySQL server since they have full rights. As a consequence of this, you should never use this account for anything other than administrative tasks. This step will walk you through the process of creating a new user account and giving it access by utilizing the root MySQL user.
The root MySQL user on Ubuntu systems that are running MySQL 5.7 (and later versions) is configured by default to authenticate using the auth socket plugin rather than needing a password. This is the case for subsequent versions of MySQL as well. MySQL 5.7 is the version that first supported this setup.
THE FOURTH STEP IS TO CHECK MYSQL.
MySQL should have started running as soon as it was installed, irrespective of the method you used. Confirm this by checking the current state of it.
$ systemctl status mysql.service
You'll see output that looks somewhat like this:
If MySQL isn't currently running, you may get it going by typing sudo systemctl start mysql on the command line.
You may do an extra check by connecting to the database with the mysqladmin tool, which is a client that enables you to execute administrative commands. This can be done as an additional check. For instance, this statement indicates to join as a MySQL user with the name sammy (-u sammy), to request for a password (-p), and to return the version. When asked, enter the password for the dedicated MySQL user you created, and make sure to replace sammy to the name of that user:
$ sudo mysqladmin -p -u sammy version
The result should look somewhat like this:
