How to install and configure MySQL on Ubuntu

In this tutorial, we shall see how to install the MySQL server on Ubuntu. We will also detail steps to configure MySQL security options, adjusting authentication methods, and creating a new user.

MySQL is one of the most common open-source relational database management system (RDBMS), based on the Structured Query Language (SQL), which is a programming language used to manage data held in a database.

It is fast, straightforward to use even for newbies getting into backend development, and integrated into various stack packages like Xampp, Lampp, and Wamp.

In this particular post, we will show you how to install the MySQL server in your Ubuntu system. Additional information includes configuring the security options, adjusting authentication methods, and creating a new user. Our Ubuntu release of choice in this tutorial is Ubuntu 20.04 LTS, which is the latest release as of writing this article.

Installing MySQL on Ubuntu

Requirements

You will need to have elevated privileges (root) on your system. You can do this by executing the command below.

sudo su

Procedure

As of writing this post, the current MySQL Linux release is version 5.7. To get started with installation, follow the steps below:

Step 1) Ensure all packages and repositories in your system are up to date. You can do so by running the command below:

sudo apt update

Update your system packages
Update your system packages

Step 2) Now, we will install MySQL via the apt package manager. Execute the command below.

sudo apt install mysql-server

Installing MySQL Server
Installing MySQL Server

Step 3) After a successful installation, the mysql-service should start automatically. You can confirm that by executing the command below:

sudo systemctl status mysql

You should get an output similar to that on the image below.

MySQL Status
MySQL Status

In any case that the service is not running, execute the command below:

sudo /etc/init.d/mysql start

Verify MySQL installation (optional)

You can verify your installation by running the command below, which will output the MySQL version and distribution installed in your system.

mysql --version

MySQL Version Distribution
MySQL Version Distribution

Secure your MySQL server

Now that MySQL server is installed successfully, you need to set a few parameters to ensure the security of the server and databases that you configure in the future.

In other cases, after the MySQL Server package installation is complete, the mysql-secure-installation utility will launch automatically. However, if this is not the case for you, execute the command below:

sudo mysql_secure_installation

You will see a prompt asking you whether to VALIDATE PASSWORD PLUGIN. It enhances MySQL server security by checking the strength of user passwords allowing users to set only strong passwords. Press Y to accept VALIDATION or the RETURN key to skip.

Accept Password Validation Plugin
Accept Password Validation Plugin

Next, you should see a prompt to set the root password. Enter your password and hit enter. Note, for security reasons, whatever you type in the console is not displayed.

Enter the MySQL root password
Enter the MySQL root password

Next, you should see a prompt asking you whether to remove all anonymous users, enter Y for YES. For any other prompts from here, enter Y for YES.

Remove anonymous users
Remove anonymous users

Login as root and adjust user authentication

MySQL server comes with a client-side utility that allows you to access and interact with the database from the Linux Terminal.

Typically, after a fresh install of MySQL on Ubuntu without performing any configurations, users accessing the server are authenticated using the authentication socket (auth_socket) plugin.

The use of auth_socket hinders the server from authenticating a user using a password. Not only does it raise security concerns, but it also hiders users from accessing the database using external programs like phpMyAdmin. We need to change the authentication method from auth_socket to the use of mysql_native_password.

To do this, we will need to open the MySQL console. Run the following command on the Linux Terminal.

sudo mysql

MySQL console
Console

Now, we need to check the authentication method used by the database on different users. You can do so by running the command below.

SELECT user,authentication_string,plugin,host FROM mysql.user;

Check user authentication methods
Check user authentication methods

From the image above, we can confirm that the root user indeed is authenticated using the auth_socket plugin. We need to switch to the use of ‘password authentication’ using the ‘ALTER USER’ command below. Ensure you use a secure password (should be more than eight characters combining numbers, strings, and special symbols) since it will replace the password you set when executing the command ‘sudo mysql_secure_installation’ above. Run the command below.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

Change root authentication
Change root authentication

Note, the highlighted text in the image above is where you will enter your secure password. Enclose it in between single tags. Now, we need to reload the grant tables and update the changes to the MySQL-server. Do this by executing the command below.

FLUSH PRIVILEGES;

Flush privileges
Flush privileges

Once done, we need to confirm that the root use no longer uses auth_socket for authentication. Do so by running the command below again.

SELECT user,authentication_string,plugin,host FROM mysql.user;

Check root authentication
Check root authentication

From the image above, we see the root authentication method has changed from ‘auth_socket’ to ‘password.’

Since we have changed the authentication method for root, we can’t use the same command we used before to open the MySQL console. That is, ‘sudo mysql.’ We will need to include the username and password parameters, as shown below.

mysql -u root -p

The ‘-u’ indicates the user, which is ‘root’ for our case and ‘-p’ stands for ‘password,’ which the server will prompt you to enter once you hit the Enter key.

mysql -u -p
mysql -u -p

Creating a new user

Once everything is set up, you can create a new user whom you will grant the appropriate privileges. In our case here, we will create a user ‘tuts_fosslinux’ and assign rights over all the database tables and permission to change, remove and add user privileges. Execute the commands below the line by line.

CREATE USER 'tuts_fosslinux'@'localhost' IDENTIFIED BY 'strong_password';

GRANT ALL PRIVILEGES ON *.* TO 'tuts_fosslinux'@'localhost' WITH GRANT OPTION;

The first command will create the new user, and the second one assigns the needed privileges.

Create new user and grant permissions
Create new user and grant permissions

We can now test our new user by running the command below.

mysql -u tuts_fosslinux -p

Test our new user
Test our new user

Install MySQL-Server on the Ubuntu Server

Installing MySQL-server on the Ubuntu server is no much difference from the steps described above. However, since a server is accessed remotely, we also need to enable remote access for our server.

To install the database and configure security options, just run the following commands line by line on the Terminal.

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation

After a successful installation, we will need to enable remote access. Logically, all we need to do is open a port on the Ubuntu server firewall for the MySQL server to communicate. By default, MySQL service runs on port 3306. Run the commands below.

sudo ufw enable
sudo ufw allow mysql

Enable remote access
Enable remote access

To enhance the reliability and accessibility of our MySQL databases, we can configure MySQL-server service to start running on boot. To do so, execute the command below.

sudo systemctl enable mysql

Enable mysql service on boot
Enable MySQL service on boot

Now we will need to configure our server’s interfaces. It will enable the server to listen to remotely accessible interfaces. We will have to edit the ‘mysqld.cnf’ file. Run the commands below.

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

Configure bind address
Configure bind-address

By default, the bind-address is to ‘127.0.0.1.’ Add bind address for your public Net Interface and another for Service Net interface. You can configure your bind-address as ‘0.0.0.0’ for all Ip-Addresses.

Conclusion

I hope you enjoyed this tutorial on setting up the MySQL server on your Ubuntu system. If you are just getting started with MySQL and all you need is to create a simple database and user, the steps given above should be of great help. If you find this article resourceful, feel free to share the link with a friend.

Arun Kumar
Arun did his bachelor in computer engineering and loves enjoying his spare time writing for FOSS Linux. He uses Fedora as the daily driver and loves tinkering with interesting distros on VirtualBox. He works during the day and reads anything tech at night. Apart from blogging, he loves swimming and playing tennis.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

STAY CONNECTED

23,648FansLike
360FollowersFollow
16SubscribersSubscribe

LATEST ARTICLES

How to install CMake on Ubuntu

CMake is a cross-platform free and open-source software tool designed to build, test, and package the software. CMake uses a simple platform and compiler-independent configuration files to control the software compilation process.

How to install Lightworks on Ubuntu

Even though Linux may not get a native installer of video editing software like Adobe Premiere or Final Cut Pro, that doesn't mean there are no industry standards tools available. Lightworks is non-linear editing (NLE) video mastering app for Windows, Linux, and macOS. Installing it on Ubuntu is simple due to deb package availability.

How to install DaVinci Resolve on Fedora

Davinci Resolve is a professional application used for color correction, video editing, visual effects, and motion graphics. It is one of the extensively used software by movie industries located in Hollywood.

The 10 Best Programming Languages for Hacking

One of the significant entities we have in Cyber Security is Ethical Hacking (ETH). It is the process of detecting and finding flaws or vulnerabilities in a system that a hacker would exploit.

5 Ways to Open a Terminal in Ubuntu

Even though Ubuntu supports many applications with amazing Graphical User Interfaces (GUI), there are always reasons why users prefer using the Terminal to perform different tasks.

How to install Wine on Fedora Workstation

Linux distributions are becoming more and more popular every day, and Fedora Workstation is not left behind. This popularity brings forth the need to run Windows applications on Linux distros like Fedora. Windows has quite some excellent Software that is not available for Linux.

MUST READ

Linux is growing faster than ever. As per the latest report, there is a drop in the Windows 10 market share for the first time, and Linux's market share has improved to 2.87% this month. Most of the features in the list were rolled out in the Pop OS 20.04. Let's a detailed look into the new features, how to upgrade, and a ride through video.
Elementary OS 5.1 Hera has received a point release with a handful of new features and bug fixes, and we will be reviewing the significant changes in this article. For those new to elementary OS, this Ubuntu-based Linux distribution uses their inhouse built Pantheon desktop environment and AppCenter.

7 Best Ways to Kill Unresponsive Programs in Linux

For dealing with a frozen app or desktop, you can't use the CTRL+ALT+DEL in Linux system. Instead, there are powerful alternatives that come in handy in frustrating situations. We pick the best methods available for you.

VIDEO: Linux Lite 4.8 Features and Desktop Tour

Linux Lite eases Windows 7 users transition to Linux much more comfortable by offering simple software like Team Viewer, VLC, Firefox, TimeShift backup utility, and a full Microsoft Office compatible office suite in LibreOffice.

What’s New in Ubuntu 20.04 LTS “Focal Fossa”

Ubuntu 20.04 LTS is out now! This is the successor to Ubuntu 18.04, which was released in April 2018 and still has three years of official support left. Code-named Focal Fossa, the new edition has all the bells and whistles included in it, making it one of the best Ubuntu releases yet. Let's find out more.

5 Best Ways to Free up Hard Disk Space in Ubuntu

Hard disk space can easily get filled up with cached package files, old kernels, and other obsolete files that occupy unwanted hard disk space. Here are top five best and safer ways to clean and free up hard disk space in Ubuntu. We show you Terminal and GUI way of cleaning up system.