Install MySQL on Ubuntu 20.04 LTS Linux

In this guide, we will show you how to install MySQL on Ubuntu 20.04 Focal Fossa. There are two separate packages for this on Ubuntu, depending on what you need to do. You can either install the MySQL client package, which is used to connect to MySQL servers, or install the MySQL server software, which can be used to host your own databases. We’ll cover both below.

After installing a MySQL server, we’ll also go over the first steps you’ll need to get started with hosting a database, which includes making a username and password, creating a database, and granting user permissions on that database.

In this tutorial you will learn:

• Install MySQL Client on Ubuntu
• Install MySQL Server on Ubuntu
• How to setup a MySQL database

Setting up a MySQL database and user on Ubuntu 20.04

Install MySQL Client on Ubuntu

Installing the MySQL client on your Ubuntu system will enable you to use the mysql shell command. With that command, you’ll be able to log in to remote MySQL servers, but you won’t actually be running MySQL on your local system.

1. To get started installing it, open a terminal and type the following command:

   $ sudo apt install mysql-client
   

---

---

2. You can confirm that the MySQL client was installed successfully and see what version your system is running with this command:

   $ mysql -V
   mysql  Ver 8.0.19-0ubuntu4 for Linux on x86_64 ((Ubuntu))
   

3. Now you are able to connect to a remote MySQL server by using the following command syntax:

   $ mysql -u USERNAME -p PASSWORD -h HOST-OR-SERVER-IP
   

Install MySQL Server on Ubuntu

If you want to host a MySQL database (or multiple) on your Ubuntu 20.04 system, then you’ll need to install the MySQL Server package. You’ll be able to access the database from the local machine, or from remote clients that are using the MySQL Client to connect.

1. To install MySQL Server, execute the following command in terminal:

   $ sudo apt install mysql-server
   

2. The first thing you’ll want to do after installing your MySQL server is secure it via the following command:

   $ sudo mysql_secure_installation
   

   You’ll be prompted with a few setup questions now. How you answer them will depend on the environment in which you’re deploying this MySQL server. If in doubt, select the most secure settings and choose a strong root password. It’s also best practice to disable remote root logins.

   

   Going through the mysql secure installation prompts

---

---

3. By default, MySQL is only accessible from your local PC. For security reasons, it’s best to keep it this way unless you need to accept remote connections (from a user, an external web server, etc). If you do need to allow remote access, it’s necessary to change a line of the MySQL configuration file. Use nano or your preferred text editor to open this file:

   $ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
   

   And then change the bind-address line to 0.0.0.0 instead of 127.0.0.1, as seen in the screenshot below. After that change is made, you can save your changes and close the file.

   

   Editing the bind-address in order to accept remote connections

4. We’ll need to restart MySQL for these changes to take effect. Now is also a good time to (optionally) enable MySQL to start up automatically after future system reboots. Type the following two commands to accomplish that:

   $ sudo systemctl restart mysql
   $ sudo systemctl enable mysql
   

5. By default, MySQL listens for connections on port 3306. You can confirm that your MySQL service is listening for incoming connections on all interfaces 0.0.0.0 with this command:

   $ ss -ltn
   

   

   We can see that MySQL is listening for remote connections on port 3306

6. Finally, the only thing left to do is make sure that your server’s firewall isn’t blocking incoming connections on port 3306 (default MySQL listening port). You can issue the following ufw command to add an exception in Ubuntu’s default firewall:

   $ sudo ufw allow from any to any port 3306 proto tcp
   

How to setup a MySQL database

If you’ve been following along with us so far, you should have a fully functional MySQL server installed on Ubuntu 20.04, and it should be accessible from remote hosts (if your setup requires you to have that functionality).

To get started utilizing your new MySQL server installation, you’ll need to create a database. MySQL databases require user permissions (unless you just use the root account – not recommended) in order to store data. We’ll get you going with the basics of a database setup in the following steps.

For this example, we’re going to show the steps to setup a new database and user account, allow the account to be logged in from any remote host, and grant that user full permissions (the ability to create, insert, drop, etc) on our database.

1. First, we need to open MySQL. All you need to do is execute the mysql command with root privileges:

   $ sudo mysql
   

2. To get much further, you’ll inevitably have to learn some MySQL commands and syntax, but we’ve got you covered with the bare basics to help you start. Create a new database like so (replacing my_database with the desired name of your database):

   mysql> CREATE DATABASE my_database;
   

---

---

3. Next, we need to create a new user account that will have privileges to the database we just created. The syntax for doing this is as follows:

   mysql> CREATE USER 'my_user'@'%' IDENTIFIED BY 'my_password';
   

   The above command will create a new user named my_user with a password of my_password. This user will be able to connect from anywhere on the internet, as we’ve specified a wildcard % in the command. If we wanted to restrict where it can connect from, we could specify an IP address instead, or allow only local connections with localhost.

4. Next, we need to grant this new user some permissions on our database. We will give the user all permissions on our database with the following command:

   mysql> GRANT ALL PRIVILEGES ON my_database.* to my_user@'%';
   

5. Lastly, save all the changes with this command, and then use the exit command to close the MySQL terminal.

   mysql> FLUSH PRIVILEGES;
   mysql> exit
   

   

   We’ve finished setting up a database and user account in MySQL

You now have a usable MySQL database and a user account that can connect to and edit it. Using the credentials you’ve created, remote systems can login to your MySQL server to store data in the database, as well as retrieve it.

Conclusion

In this guide, we saw how to install MySQL Client and MySQL Server on Ubuntu 20.04 Focal Fossa. Whether you need to connect to a remote database or host a MySQL database yourself, following the steps we’ve outlined should do the job.

We also learned the basics of logging into MySQL and creating a new database and user, and then granting privileges to that user. This is all you need to get started storing and retrieving data in your database from local and remote hosts.