How to Install MySQL on Ubuntu

MySQL is an open-source database management system often installed as part of the popular LAMP stack (Linux, Apache, MySQL, PHP). It is a relational database that uses SQL (Structured Query Language).

In short, the installation process is very easy:

1. Update your package index.

sudo apt update

2. Install the mysql-server package.

sudo apt install mysql-server

3. Run the built-in security script.

sudo mysql_secure_installation

Below we'll explain in more detail how to install MySQL on an Ubuntu server and perform basic setup.

Prerequisites

To follow this guide, you will need:

• A cloud server running Ubuntu 22.04.

• A non-root user with sudo privileges.

Step 1: Install MySQL

To install MySQL on Ubuntu 22.04, first update your server's package index using apt:

sudo apt update

Install the default package:

sudo apt install mysql-server

MySQL will be installed, but it will not prompt you to set a password or make other configuration changes. Since this leaves MySQL insecure, we will address the setup in the next step.

Step 2: Configure MySQL

If you are installing MySQL on your Ubuntu server for the first time, you need to run the internal security script. It changes settings like remote root access and default users, which are not secure by default.

sudo mysql_secure_installation

This command will guide you through a series of prompts to make changes to your MySQL security settings.

First, you'll be asked if you want to configure the Validate Password Plugin, which can be used to test the strength of MySQL passwords. Regardless of your choice, you will then need to set a password for the MySQL root user. Create a secure password, enter it, and then confirm it.

You can now press Y and then ENTER to accept the default values for all subsequent questions. This will remove some anonymous users along with the test database and disable remote root logins. All changes will then be applied to your MySQL.

Note that although you set a password for the root user, this user is not configured for password authentication when connecting to the MySQL shell. If desired, you can change this setting by performing step 3.

Step 3 (Optional): Configure Authentication and Privileges

The MySQL root user is set to authenticate using the auth_socket plugin by default, rather than with a password. In many cases, this improves security and usability, but it complicates things if you need to allow an external program (like phpMyAdmin) to access MySQL.

To use a password to connect to MySQL as the root user, you need to change the authentication method from auth_socket to caching_sha2_password. To do this, open the MySQL command line:

sudo mysql

Then check which authentication method each of your MySQL accounts uses:

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

In this example, you can see that the root user uses auth_socket authentication.

To configure password authentication, run the ALTER USER command. Be sure to replace password with your own password and remember that this command will change the root user's password you set in step 2:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

Then run FLUSH PRIVILEGES to apply the changes:

FLUSH PRIVILEGES;

Check the authentication methods for each user again to ensure that the root user no longer uses auth_socket:

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

After confirming that the root user now authenticates with a password, you can exit the MySQL shell:

exit

Some people find it more convenient to connect to MySQL through a separate user. To create one, reopen the MySQL shell:

sudo mysql

Note: If you have enabled password authentication for the root user as described above, you will need to use a different command to access the shell. The above command will launch your MySQL client with ordinary user permissions, so to gain administrator privileges for your database, run:

mysql -u root -p

Now create a new user and choose a strong password:

CREATE USER 'hostman'@'localhost' IDENTIFIED BY 'password';

Then grant the new user the necessary privileges. For example, you can give the user access to all tables in the database and the right to add, modify, and delete user rights with the following command:

GRANT ALL PRIVILEGES ON *.* TO 'hostman'@'localhost' WITH GRANT OPTION;

Note that at this stage you do not need to rerun the FLUSH PRIVILEGES command. This command is only necessary when modifying the access tables using INSERT, UPDATE, or DELETE statements. Since you created a new user rather than modifying an existing one, FLUSH PRIVILEGES is not required here.

Now exit the MySQL shell:

exit

Finally, let's test our MySQL.

Step 4: Test MySQL

Regardless of how you installed it, MySQL should have started automatically. Check its status with the following command:

systemctl status mysql.service

If MySQL is not running, you can start it with:

sudo systemctl start mysql

For additional verification, you can try connecting to the database using the mysqladmin tool. This client allows you to run administrative commands. For example, the following command tells mysqladmin to connect to MySQL as the root user (-u root), prompt for a password (-p), and return the version:

sudo mysqladmin -p -u root version

This way, you can ensure that MySQL is running and working.

 

Congratulations! Your server now has a basic MySQL setup running and configured. You can now start working with your databases.