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
|Category||Requirements, Conventions or Software Version Used|
|System||Installed or upgraded Ubuntu 20.04 Focal Fossa|
|Software||MySQL Client, MySQL Server|
|Other||Privileged access to your Linux system as root or via the |
|Conventions|| # - requires given linux commands to be executed with root privileges either directly as a root user or by use of |
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.
- To get started installing it, open a terminal and type the following command:
$ sudo apt install mysql-client
- 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))
- 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.
- To install MySQL Server, execute the following command in terminal:
$ sudo apt install mysql-server
- The first thing you'll want to do after installing your MySQL server is secure it via the following command:
$ sudo mysql_secure_installationYou'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.
- 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.cnfAnd then change the
127.0.0.1, as seen in the screenshot below. After that change is made, you can save your changes and close the file.
- 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
- 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.0with this command:
$ ss -ltn
- 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
ufwcommand 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.
- First, we need to open MySQL. All you need to do is execute the
mysqlcommand with root privileges:
$ sudo mysql
- 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_databasewith the desired name of your database):
mysql> CREATE DATABASE my_database;
- 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_userwith 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
- 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@'%';
- Lastly, save all the changes with this command, and then use the
exitcommand to close the MySQL terminal.
mysql> FLUSH PRIVILEGES; mysql> exit
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.
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.