In this article, we will see how to install MySQL on Ubuntu 20.04 LTS Focal Fossa step by step. So that you can use the latest stable version of this native database engine on your Ubuntu server or VPS.
Before Installing MySQL On Ubuntu 20.04
To successfully complete this MySQL Server installation guide on Ubuntu 20.04 LTS. It will only be necessary to satisfy these minimum requirements:
- An updated Ubuntu 20.04 Focal Fossa system.
- A user with sudo permissions.
- Internet access.
How To Install MySQL On Ubuntu 20.04
We are going to install MySQL Server for Ubuntu 20.04 LTS, from the distribution repositories, where we will find a very recent version, so we will update the package lists:
~ $ sudo apt update
The package we need is mysql-server, which we will install with apt :
~ $ sudo apt install -y mysql-server
You can always check the status of MySQL in Ubuntu 20.04 using the command
systemctl status mysql
How To Access MySQL On Ubuntu 20.04
Once the service is installed and running, to access MySQL on Ubuntu 20.04 we will use the MySQL console client, installed as a dependency. We must use sudo to launch the client since the MySQL root user is configured by default with the auth_socket access plugin. Which does not need a password, based on the identity of the Ubuntu 20.04 user that makes the connection?
~$ sudo mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
If you prefer to change the type of access and use password authentication. You can take the opportunity to change the plugin and create a password. You have two password access plugins available, the traditional mysql_native_password or the modern but less compatible with third-party clients and connectors caching_sha2_password.
> alter user root @ localhost identified with mysql_native_password by 'XXXXXXXX';
It is advisable to refresh the permission table after each change of this type:
> flush privileges;
And we can log out:
Now, in order to access the service, you will have to specify the user with password:
~$ mysql -u root -p ... mysql>
If you want to return to the system without a password, simply change the current plugin to auth_socket :
> alter user root @ localhost identified with auth_socket;
Not forgetting to update the permission table:
> flush privileges;
How To Configure MySQL Server On Ubuntu 20.04
To configure MySQL in Ubuntu 20.04 LTS we must take into account that the configuration files are organized under the path / etc / MySQL /. The main MySQL Server configuration file is mydqld.conf, which is located in the mysql.conf.d/ subdirectory.
We will see some configuration example.
How To Remotely Access MySQL On Ubuntu 20.04
To have remote access to MySQL on Ubuntu 20.04 it is necessary to make some adjustments since by default the service only listens to local connections. We edit the mysqld.conf file :
~ $ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
We will look for the directive bind-address :
... bind-address = 127.0.0.1 ...
Just disable this directive by inserting a # character at the beginning of the line:
... #bind-address = 127.0.0.1 ...
Once the changes are saved, it will be necessary to restart the service:
~ $ sudo systemctl restart mysql
Users With Remote Access To MySQL
Here, we need a user to test remote access, since root (root @ localhost) can only make local connections, either because he uses the auth_socket plugin, or because his connections to the local system (localhost) are restricted.
We connect and create a new user:
> create user chacho identified with mysql_native_password by 'XXXXXXXX';
We specify the mysql_native_password plugin since the default plugin is caching_sha2_password and it could happen that the remote client we use is not compatible.
How To Configure Ubuntu 20.04 UFW Firewall For MySQL
If you have the UFW firewall enabled in Ubuntu 20.04, by default it will block remote connections to MySQL Server. Simply add the following rule to allow these connections:
~ $ sudo ufw allow mysql
The change will be immediate.
Test Remote Access To MySQL
From another network system we can make the connection using a client, such as MySQL, specifying the IP address or domain of the Ubuntu 20.04 machine and the user with whom we want to connect:
~ $ mysql -h ubuntu2004.local -u chacho -p
In this example we have connected from another version of Linux using a client that really belongs to the alternative to MySQL, so we configure the user with the compatible plugin.
Now that you know how to install MySQL Server on Ubuntu 20.04 LTS Focal Fossa, you can now work with the native implementation of this database engine on your server or VPS, both for your own developments and for third-party applications that rely on this solid service.
If you have doubts or questions, suggestions for improving the article, you want to report an error, you think it needs an update, or you propose a new topic, leave a comment and I will take a look as soon as possible.