MySQL, which stands for My (co-founder Michael Widenius’ daughter name) Structured Query Language, is an open source relational database management system. This RDBMS is supported by Oracle and runs on almost all platforms such as Linux, UNIX and MS Windows. MYSQL is usually the first choice for online publishing and web-based applications. Facebook, YouTube and Twitter all use MySQL as their RDBMS. MySQL is part of the very popular LAMP stack: Linux, Apache, MySQL and Python/PHP/Perl.

In this article we describe step by step how to proceed:

  • Install the latest version of MySQL available in the apt package repository
  • Run the MySQL security script
  • Configure the root user to access the MySQL shell
  • Finally test that MySQL is running

We have run the commands and procedures mentioned in this article on an Ubuntu 20.04 LTS system

MySQL installation and security configuration

In this section, we describe how to install MySQL from the Ubuntu command line and configure security.

You can open the Ubuntu command line, the terminal, either from the application launcher search bar or by pressing Ctrl+Alt+T.

Step 1: Update repository index

To install the latest available version of a software from the Internet repositories, your local repository index must match it. Run the following command as sudo to update your local repository index:

$ sudo apt-get update

Step 2: Install MySQL server with apt

Please run the following command as sudo to install MySQL from APT repositories.

$ sudo apt-get install mysql-server

Please note that only an authorized user on Ubuntu can add, remove and configure software.

Install MySQL server package

The system may ask you for the password for sudo and also offer you a Y/n option to continue the installation. Type Y and then press Enter; MySQL will then be installed on your system. However, the process may take some time depending on your internet speed.

Step 3: Verify the installation (optional)

You can verify your MySQL installation and also check the version number by running the following command in your terminal:

$ mysql --version

Step 4: Make security configurations by running the provided security script

Whenever you install a new copy of MySQL, there are some default settings that you should change to increase the security of your MySQL installation. These include removing test users, test databases, and allowing remote login by a root user.

Run the following command as sudo to start the security script:

$ sudo mysql_secure_installation

MySQL secure installation

When you run this command, the first thing you will be prompted to do is set up the Validate Password plugin. This allows you to set a secure password for root, depending on the strength of the password you want to choose. Type Y to start the Validate Password plugin and you will get the following prompt:

Password validation policy level

Enter the number of your choice for the password strength and press Enter. The system will then ask you for the new password of root. Type the password and retype it at the following prompts.

Set MySQL root password

The system will then display the strength of the password you provided and also ask you if you want to continue with the password.

Password strength

Type Y for Yes and press Enter.

The system will now ask you a series of questions, one at a time, and you can set the security of your system depending on your answers to these questions.

Series of questions:

The first question will ask you if you want to remove the anonymous test users.

Remove anonymous user

Press y and the Enter key.

The second question will ask you if you want to disallow root login from a remote system. This should normally be your choice, because for a secure system, root should only be allowed to connect from localhost.

We, therefore, recommend that you enter y.

The third question asks if you want to remove the default MySQL database named “test” from your system and also delete access to it.

Remove test database

Type y to remove this test database.

In order for all of your changes configured above to take effect, the system must reload the permissions tables. Enter y and all your security changes will be applied.

Reload database permissions to apply changes

Configuring root to use the MySQL shell

When running the security script, you specified a password for root. However, this user is not allowed to connect to MySQL Shell with the same password. You can configure root to use MySQL Shell by changing its authentication method from the default “auth_socket” to “mysql_native_password”.

Here’s how to do it:

Step 1: Start MySQL Shell

First, start the MySQL shell by running the following command as sudo:

$ sudo mysql

Start MySQL Shell

This will start the MySQL shell so you can work on the MySQL prompt.

Step 2: Check authentication method for MySQL users

At the MySQL prompt, enter the following command, which will allow you to check the authentication method/plugin that all of your MySQL accounts are currently using:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

List of MySQL users

In the above output, you can see that root uses the auth-socket plugin for authentication by default.

Step 3: Change the authentication method for root

Our goal is to have the root user authenticate to MySQL with a password. To do this, run the following command, which will have the root user identified by a mysql_native_password. Please remember that this password must be very strong.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Change authentication method for root user

From now on, your root user will no longer have the password you specified when running the provided security script, but the strong password you specified in the above command.

Step 4: Reload grant tables

Now it’s time to tell the server to use the new permission settings from now on. Run the following command at the MySQL command prompt to reload the grant tables and register your changes:

mysql> FLUSH PRIVILEGES;

Reload Grant tables

Step 5: Recheck authentication method for MySQL users

Now, if you recheck the authentication method for your MySQL user accounts using the following command, you will see that your root user is now using the mysql_native_password plugin for authentication:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

Recheck authentication method

Now that your root user is set up to connect to the MySQL shell with a secure password, you can exit the shell with the exit command as follows:

mysql> exit

Testing if MySQL is running

To test whether MySQL is running on your system or not, you can use one of the following methods:

Method 1: Check the status of mysql.service

After you install MySQL on your system, mysql.service should most likely be running automatically. The output of the following command should verify the active status of the service:

$ systemctl status mysql.service

Check MySQL service status

If for some reason the service is not running, you can use the following command as sudo to start the service:

$ sudo systemctl start mysql

You can use the following command to stop the service again if needed:

$ sudo systemctl stop mysql

Method 2: By connecting to MySQL Admin as root and running any administrative command

MySQL Admin is a client that allows you to perform administrative operations on MySQL. As an example, let’s run one of the administrative commands over it to check if the system is running properly and our root is configured to do so.

$ sudo mysqladmin -p -u root version

This command is to connect to MySQL as root, get the root password, and then return the MySQL admin version number.

Check MySQL version with mysqladmin command

If the command does what it is supposed to do and produces output similar to the above, you can be sure that your MySQL is up and running.

The process of installing and setting up MySQL on Ubuntu may seem cumbersome to some, especially when using the command line. However, if you carefully follow the above steps one by one, you will have no problems having a reliable, secure and stable installation of MySQL running on your Ubuntu.

How to Install and Configure MySQL in Ubuntu 20.04 LTS
Avatar

Karim Buzdar

About the Author: Karim Buzdar holds a degree in telecommunication engineering and holds several sysadmin certifications. As an IT engineer and technical author, he writes for various web sites. He blogs at LinuxWays.