How to Install and Configure MySQL in Ubuntu 24.04 LTS

MySQL, named after the daughter of co-founder Michael Widenius, is an open-source relational database management system (RDBMS). Supported by Oracle, MySQL is compatible with nearly all platforms, including Linux, UNIX, and Windows. It is often the preferred choice for online publishing and web-based applications, and is used by major companies such as Facebook, YouTube, and Twitter. MySQL is also a key component of the widely used LAMP stack, which includes 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 24.04 LTS system

MySQL installation and security configuration

This section describes 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 the repository index

To install the latest available version of 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.

The system may ask you for the sudo password and 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, depending on your internet speed, the process may take some time.

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, you should change some default settings to increase the security of your installation. These include removing test users and 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

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:

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.

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

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 asks if you want to remove the anonymous test users.

Press y and the Enter key.

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

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.

Type y to remove this test database.

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.

Configuring root to use the MySQL shell

When running the security script, you specified a password for root. However, this user cannot 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

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

Step 2: Check the 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;

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

We aim 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';

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. Run the following command at the MySQL command prompt to reload the grant tables and register your changes:

mysql> FLUSH PRIVILEGES;

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;

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 likely be running automatically. The output of the following command should verify the active status of the service:

$ systemctl status mysql.service

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. 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 connects to MySQL as root, gets the root password, and then returns the MySQL admin version number.

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

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