How to install MySQL on Ubuntu 20.04

MySQL is one of the most popular open source databases. It’s known for its performance, reliability and scalability and is used in many different areas, from small web hosting systems to large enterprises. 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 management system.

What are the requirements for installing MySQL on Ubuntu 20.04?

There are relatively few system requirements for installing MySQL on Ubuntu 20.04, which should all be fulfilled on most modern desktop and server systems. It’s important to note that requirements can vary based on the intended purpose and scope of the database. For example, if you want to run an intensive application that uses big databases or complex queries, the RAM and processing required to ensure good performance 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 configuration and firewall settings to ensure smooth communication between the MySQL server and clients. The MySQL server should have a static IP address to avoid any problems with the connection.

Here are the minimum requirements for installing MySQL:

• Processor (CPU): x86-64 architecture, min. 1 GHz (dual-core)
• RAM: min. 1 GB
• Operating system: Ubuntu 20.04, a user account with sudo and root privileges
• Firewall: MySQL port 3306 open
• Hard drive space: min. 500 MB
• Internet connection: Required for downloading packages and connecting 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 management system APT (Advanced Package Tool). After installation, 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 recommended security standards right after installation. 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 precautions to ensure you execute the script correctly. The app will want to change the password for the root account, which is deactivated on Ubuntu by default. To avoid an error, you’ll need to adjust the authentication 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 authenticate the root user, enter the following:

$ mysql -u root -p

After the script is finished, you can change the standard authentication 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

Installing MySQL creates a root user that has all privileges for the MySQL server and complete control over databases, tables and users. To increase security, you should create a user with limited privileges.

To do so, open the MySQL command prompt:

$ sudo mysql

If you’ve set a password as the authentication 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 expression ‘WITH authentication_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 authentication. However, some versions of PHP aren’t compatible 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 privileges

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

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

Privileges are separated with a comma. To give global privileges, replace ‘database.table’ with an asterisk ‘*’.

In the following example, we grant a user permission 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 permission to give the privileges that they have to other users.

Now empty the cache with ‘FLUSH PRIVILEGES’:

mysql> FLUSH PRIVILEGES;

Afterwards, 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

Alternatively, 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