MySQL is an open-source relational database that is free to use for anyone around the world. It is commonly installed together with the LAMP (Linux, Apache, MySQL and PHP) stack. It uses structured query language (SQL for short) to manage its data and implements the relational model.
This article will show you how to install the MySQL database server on Ubuntu operating system. We will go through the process for Ubuntu 18 and Ubuntu 20 versions. Before we get started, let’s go through the prerequisites.
What will you need?
- A server running Ubuntu 18 or Ubuntu 20 operating system
- Basic understanding of Linux commands
- A root user account or a user with root privileges
Step 1: Install MySQL
Before installing the server, it is recommended to update the package index on your server. To do this, enter the following:
sudo apt update
You can install the MySQL server by using the Ubuntu operating system package manager. This will install MySQL and all its dependencies.
sudo apt install mysql-server
For fresh installations of MySQL, we recommend using the secure installation utility. This should start automatically but if not, enter the following command:
sudo mysql_secure_installation utility
This prompts you to define the MySQL root password and other security-related properties, which includes removing remote access to the root user and resetting the root password.
Step 2: allow remote access to the server.
Iptables are usually turned On, which prevents users from connecting the MySQL server remotely. If you want to connect to the MySQL database from another machine, you need to allow the port from your firewall settings (the default port is 3306).
Note: this is not needed if the application that uses MySQL is running on the same server.
sudo ufw enable
sudo ufw allow mysql
Step 3: Start MySQL service
Once the installation is complete, you can start the service by running the following command. If the service is already active, you will be given a message informing you that the service is already up and running.
sudo systemctl start mysql
Step 4: Configuring the interfaces
MySQL does not listen to any remotely accessible interfaces by default. Therefore you will need to make some changes to the configurations file. We will use the nano text editor to make the following changes:
Enter the following data:
bind-address = 127.0.0.1 ( The default. )
bind-address = aaa.aaa.aaa.aaa ( The IP address of your Public Net interface. )
bind-address = bbb.bbb.bbb.bbb ( The IP address of your Service Net interface. )
bind-address = 0.0.0.0 ( All IP addresses. )
Save and close the configurations file. Press CTRL + x, press y and press ENTER.
Step 5: Launch at reboot
To ensure that the database server launches after a reboot, run the following command:
sudo systemctl enable mysql
Restart the MySQL service:
sudo systemctl restart mysql
Step 7: Test the MySQL server.
Regardless of how you installed it, MySQL should be running perfectly by now. To test this, we should enter the following command.
systemctl status mysql.service
If you see an output similar to the one below, your server is running perfectly:
mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2021-04-28 12:56:48 UTC; 6min ago
Main PID: 10382 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 1137)
That’s all for installing the MySQL server on your Ubuntu system. However, in the next section of the article, we will go through the process of how to use MySQL: basics.
How to use MySQL
Start the MySQL shell.
There is more than one way to work with the MySQL server, but we will focus on the most basic and compatible approach in this section of the article: the MySQL shell.
To enter the MySQL shell, enter the following command from your terminal:
/usr/bin/mysql -u root –p
Now you will be prompted to enter a password. Here you should enter the password that you set up during the installation. If you didn’t set a password, then press Enter.
You should see the MySQL shell prompt starting from mysql>.
Setting the root password.
If you entered the shell without entering a password, or if you would like to change the root password, you can do it using the following steps.
- For versions earlier than MySQL 5.7
UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root';
- For versions later than MySQL 5.7
UPDATE mysql.user SET authentication_string = PASSWORD('password') WHERE User = 'root';
Instead of password, enter your new password. To make the changes to be effective, enter the following command:
Creating a Database
Database server and database has a big difference, even though it’s used interchangeably. MySQL is a database server that tracks databases and controls access to them. In order to create a database, log into the MySQL shell and run the following command. Be sure to change the testdb name with your database name.
CREATE DATABASE testdb;
To test if the database was created successfully, enter the following command. It’s a query to list all databases.
Congratulations on successfully installing MySQL on your Ubuntu system. We hoped that this article was of help and that you can directly start using MySQL to control your databases.
If you face any problems along the way, let us know in the comments below. We value your feedback, and for any query you have, we will help you.