Basic Installation and Configuration of MySQL

Basic Installation and Configuration of MySQL

Part 3: The "M" in "LAMP"

In the last article, we quickly discussed how to set up and configure an Apache2 web server. The next part of configuring a LAMP stack is the MySQL database.

According to the documentation, MySQL is the most popular Open Source SQL database management system and is developed, distributed, and supported by Oracle Corporation. The MySQL Database Server is very fast, reliable, scalable, and easy to use and can work in client/server or embedded systems.

Apparently, the official way to pronounce “MySQL” is “My Ess Que Ell” (not “my see-quel”). But they do not mind if you pronounce it as “my see-quel” or in some other localized way. MySQL is also named after co-founder Monty Widenius's daughter, My.

The more you know.

However, you can use other database alternatives in your LAMP stack such as PostgreSQL, MariaDB, or MongoDB (for a NoSQL database).

Developers, such as the one that I work with, love using MySQL and find many uses for it. In a LAMP stack, the MySQL database is used for storing application data.

So how do we install and configure MySQL for use? In this short tutorial, I will configure a MySQL database for use on an Ubuntu Server VM.

Canonical (the software company that provides commercial support and related services for the Ubuntu distribution) provides an easy-to-understand Ubuntu server documentation guide.

Ubuntu Server Documentation

Another source of documentation that I will reference is the MySQL 8.0 Reference Manual. This documentation is more MySQL-specific but is very useful.

MySQL 8.0 Reference Manual

Even if I have configured the service hundreds of times, I like to have the documentation ready on a screen with me. Most times I will be doing this type of project at work and will end up with 20 different distractions before I get this completed and ready for my developer to use.

Let's first pull up the Ubuntu Server documentation on MySQL.

Ubuntu Server - MySQL Guide

The first command looks easy enough....

sudo apt install mysql-server

Install MySQL-Server Command

Do I want to continue? Press Y for Yes.

Do I want to continue?

Now the MySQL packages are installing.

Packages installing

Depending on your connection, the installation takes a minute or two. You will see the progress bar down at the bottom.

Progress Bar for Installing MySQL

Once you receive your terminal prompt again, type in the following command to check the status of the service via systemd:

sudo service mysql status

Check MySQL Status

Then you can check the network status of the MySQL service by sunning the ss command:

sudo ss -tap | grep mysql

Check MySQL Network Status

Once I confirmed that the MySQL service is running correctly, I will run the mysql_secure_installation command. This MySQL Installation-related program is a guided setup to improve the security of your MySQL installation. As stated in the MySQL documentation, this program enables you to improve the security of your MySQL installation in the following ways:

  • You can set a password for root accounts.

  • You can remove root accounts that are accessible from outside the local host.

  • You can remove anonymous-user accounts.

  • You can remove the test database (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with test_.

4.4.2 mysql_secure_installation — Improve MySQL Installation Security

When executed, mysql_secure_installation prompts you to determine which actions to perform.

Let us see it in action....

The command is sudo mysql_secure_installation

mysql_secure_installation command

First question I get is...

Would you like to setup VALIDATE PASSWORD component?

In the paragraph above the question, it states that the VALIDATE PASSWORD component checks for the strength of a password. As a Systems Administrator with security in mind, this seems like a good feature. Let me press Y for yes.

VALIDATE PASSWORD component

The next question I receive is to select which level of password validation policy I would like to implement. My choices are as follows:

LOW: At least 8 characters in length.

MEDIUM: At least 8 characters in length, numeric, mixed case, and special characters

STRONG: At least 8 characters in length, numeric, mixed case, and special characters, and no dictionary words.

That's going to be STRONG for me. I want at least 8 characters, mixed case, numbers, special characters, and no dictionary words.

 level of password validation policy

Now time to set up a password for the root account. As you can see in the image below, my password had an estimated score of 100. Since I am happy with my password, I will then select Y to continue.

Root User Password

Now I am asked if I want to remove Anonymous Users. If this was a test database with no data of any value, I wouldn't care about this. But since this is database will be used in production, let's go ahead and remove Anonymous Users.

Remove Anonymous Users

I am now asked if I should only allow the root user to connect from the 'localhost'. This means that the root user can only log in to the MySQL service from the local virtual machine. If a user connects to the virtual machine via SSH and then to the root account on MySQL service, that will still be allowed. I am going to select Y for this.

Roos User Local Machine

Then I am asked if I want to remove the test database. I will leave it for now and select N.

Remove Test Database

The next question asks me to Reload the privilege tables. This will ensure that all changes made so far will go into effect.

Reload the privilege tables

Looks like I am all done with the mysql_secure_installation program!

mysql_secure_installation completed

Now time to log in to the MySQL service and see what users are listed. The command I will use is sudo mysql -u root -p. I will then be prompted to enter my password.

Login to MySQL

Let me try that again! Then follow it up with this SQL command to get the list of users: SELECT DISTINCT User from mysql.user;

Root MySQL login and get list of users

I will not create my user account and request a randomly generated password. This will be the user account that I give to my developer, assuming his name is also Brandon (Which would be strange!):

CREATE USER brandon@localhost IDENTIFIED BY RANDOM PASSWORD;

Create MySQL user

Then I will grant my user account all privileges, which will make it an Administrator account. I will then need to flush the privileges so they go into effect:

GRANT ALL PRIVILEGES ON *.* TO brandon@localhost;

Followed by:

FLUSH PRIVILEGES;

Apply privileges to the user.

Now I will try to log in to that user account I just created.

sudo myspl -u brandon -p

MySQP login to user account.

From there I can list the tables:

```SHOW DATABASES;"

Show tables

Looks like our MySQL service is ready for use. I will then share the user credentials with my web developer for his use.

Thank you for taking to time to go through this tutorial with me! Please feel free to leave any comments below.

Did you find this article valuable?

Support Brandon's Cloud Blog by becoming a sponsor. Any amount is appreciated!