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,241FansLike
394FollowersFollow
16SubscribersSubscribe

LATEST ARTICLES

MUST READ

The Ubuntu Cinnamon Remix brings together Linux Mint's Cinnamon desktop with the Ubuntu Core. While some users are welcoming the new flavor of Ubuntu with open arms, others are scratching their heads, wondering where it fits in.
The wait is finally over (almost) for all you Ubuntu fans out there. The latest version of Ubuntu, 20.10 codenamed "Groovy Gorilla," is currently available in the beta version. I have tested out the distro myself, and it is stable enough to take out for a spin.

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.

10 ways to use the Chown command with examples

Any file in a Linux system belongs to a particular owner(s) and a group. It is one of the many features that maintain security in Linux systems. For example; If a file belongs to say UserA, then UserB cannot manipulate the data depending on any additional permissions set unless the user has permission to do that.

The 10 Best Linux Performance Monitoring Tools

Do you want to monitor the performance of your Linux system? Are you looking for some powerful performance monitoring tools to help you out? If you agree, it's your day as we have put together a detailed list of the ten best Linux performance monitoring tools.

The 10 Best Linux Server Distros For Home And Businesses

By the year 2020, it is estimated that there are close to 600 Linux distributions in the market. It includes both servers and Desktop versions; therefore, if you are looking for lightweight Linux distribution for your old PC or a reliable desktop version for employees in your organization, you may be overwhelmed with the number of choices for finding one for your use.