How to install MySQL on Ubuntu 20.04

MySQL is one of the most popular open source databases. It’s known for its per­for­mance, re­li­a­bil­i­ty and scal­a­bil­i­ty and is used in many different areas, from small web hosting systems to large en­ter­pris­es. In this article, we’ll explain step by step how to install MySQL on Ubuntu 20.04, so you can set up a reliable database man­age­ment system.

What are the re­quire­ments for in­stalling MySQL on Ubuntu 20.04?

There are rel­a­tive­ly few system re­quire­ments for in­stalling MySQL on Ubuntu 20.04, which should all be fulfilled on most modern desktop and server systems. It’s important to note that re­quire­ments can vary based on the intended purpose and scope of the database. For example, if you want to run an intensive ap­pli­ca­tion that uses big databases or complex queries, the RAM and pro­cess­ing required to ensure good per­for­mance will be higher. You should also have ample space on your hard drive to allow for future growth and the addition of more databases.

You should check your network con­fig­u­ra­tion and firewall settings to ensure smooth com­mu­ni­ca­tion between the MySQL server and clients. The MySQL server should have a static IP address to avoid any problems with the con­nec­tion.

Here are the minimum re­quire­ments for in­stalling MySQL:

• Processor (CPU): x86-64 ar­chi­tec­ture, min. 1 GHz (dual-core)
• RAM: min. 1 GB
• Operating system: Ubuntu 20.04, a user account with sudo and root priv­i­leges
• Firewall: MySQL port 3306 open
• Hard drive space: min. 500 MB
• Internet con­nec­tion: Required for down­load­ing packages and con­nect­ing with the MySQL server

Step-by-step guide for how to install MySQL on Ubuntu 20.04

MySQL can be installed on Ubuntu 20.04 using the package man­age­ment system APT (Advanced Package Tool). After in­stal­la­tion, you need to set up and configure it, for which you’ll need the root password and access to external clients. Below we’ll show you step by step how to install MySQL on Ubuntu 20.04.

Step 1: Update package index

First, it’s a good idea to make sure your list of packages is up to date. You can use the following command for this:

$ sudo apt update

Step 2: Install the MySQL server

Next, install the MySQL server package with APT:

$ sudo apt install mysql-server

To check that the server is running, you can start it manually with the command systemctl.

$ sudo systemctl start mysql.service

Step 3: Configure MySQL

MySQL won’t meet the rec­om­mend­ed security standards right after in­stal­la­tion. To fix this, use the script offered by MySQL to change the settings to better protect the server. That will set the root password, remove anonymous users and limit remote access.

You’ll need to take certain pre­cau­tions to ensure you execute the script correctly. The app will want to change the password for the root account, which is de­ac­ti­vat­ed on Ubuntu by default. To avoid an error, you’ll need to adjust the au­then­ti­ca­tion method of root users.

To do this, start the MySQL command prompt:

$ sudo mysql

Use the ALTER USER command to set a password for root:

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

Close the MySQL command prompt:

mysql> exit

Execute the security script:

$ sudo mysql_secure_installation

To au­then­ti­cate the root user, enter the following:

$ mysql -u root -p

After the script is finished, you can change the standard au­then­ti­ca­tion method again:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

This will enable you to connect with the sudo mysql command again.

Step 4: Set up MySQL users

In­stalling MySQL creates a root user that has all priv­i­leges for the MySQL server and complete control over databases, tables and users. To increase security, you should create a user with limited priv­i­leges.

To do so, open the MySQL command prompt:

$ sudo mysql

If you’ve set a password as the au­then­ti­ca­tion method, use the following:

$ mysql -u root -p

Now create a new user for MySQL:

mysql> CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

Enter your username in the place of “username” and the name of your host in the place of “host”. If you’re running Ubuntu locally, write localhost. The ex­pres­sion “WITH au­then­ti­ca­tion_plugin” is optional. The plugin “auth_socket” has strong security settings and doesn’t require a password for logging in.

Unless otherwise specified, MySQL uses the “caching_sha2_password” plugin for au­then­ti­ca­tion. However, some versions of PHP aren’t com­pat­i­ble with it. You can use the tried and true plugin “mysql_native_password” in its place:

mysql> CREATE USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

Or use the function “alter” for an existing user:

mysql> ALTER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

Step 5: Assign priv­i­leges

Now it’s time to set which priv­i­leges the new user will have. The syntax for this is:

mysql> GRANT PRIVILEGE ON database.table TO 'username'@'host';

Priv­i­leges are separated with a comma. To give global priv­i­leges, replace “database.table” with an asterisk “*”.

In the following example, we grant a user per­mis­sion to create (CREATE), modify (ALTER) and delete (DROP) databases and to insert (INSERT), select (SELECT), update (UPDATE), and delete (DELETE) data in a table.

mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT on *.* TO 'user'@'host' WITH GRANT OPTION;

“WITH GRANT OPTION” grants the user per­mis­sion to give the priv­i­leges that they have to other users.

Now empty the cache with “FLUSH PRIV­I­LEGES”:

mysql> FLUSH PRIVILEGES;

Af­ter­wards, you can close the MySQL command prompt:

mysql> exit

Now you can log in with the new username:

$ mysql -u username -p

Step 6: Test MySQL

Check whether MySQL is running correctly with, for example, the system manager Systemd:

$ systemctl status mysql.service

Al­ter­na­tive­ly, you can connect with the MySQL database. The following command logs you into MySQL and displays the server version:

$ sudo mysqladmin -p -u username version