How to Install MySQL on Ubuntu 20.04 | 18.04

This brief tutorial shows students and new users how to install MySQL server on Ubuntu 20.04 | 18.04 LTS.

MySQL is an opensource, fast, secure and scalable relational database management system. It is one key component of the LAMP or LEMP stack that powered countless websites and applications online today.

Although MariaDB sever is now the default database server in most opensource projects, MySQL is the father of MariaDB and both are the same with different names.

For more about MySQL, please check its homepage.

To install the latest version MySQL follow steps below:

Installing MySQL on Ubuntu

MySQL packages come included in Ubuntu repositories. So all one has to do is run the commands below to install the server.

sudo apt update
sudo apt install mysql-server

That should get the server installed.

After the server is installed, the commands below can be used to stop, start and restart the database services.

sudo systemctl stop mysql.service
sudo systemctl start mysql.service
sudo systemctl restart mysql.service

To check the status of the server, run the commands below:

sudo systemctl status mysql.service

That should display similar lines as shown below:

● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2020-04-30 09:04:02 CDT; 5s ago
Process: 4222 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, stat>
Main PID: 4248 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 4657)
Memory: 376.8M
CGroup: /system.slice/mysql.service
└─4248 /usr/sbin/mysqld
Apr 30 09:04:01 ubuntu2004 systemd[1]: Starting MySQL Community Server…
Apr 30 09:04:02 ubuntu2004 systemd[1]: Started MySQL Community Server.

MySQL server comes with a script that allows you to enhance its security by securing the root user with password, removing other insecure settings.

Run the commands below to invoke the script:

sudo mysql_secure_installation

That should prompt you with series of questions. Use the guide below to complete the setup.

Securing the MySQL server deployment.
Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: Y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Please set the password for root here.

New password: 

Re-enter new password: 

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.

All done! 

Even though you configured a password above, when you run the commands below you will be granted access without requiring a password.

sudo mysql

You’ll automatically be granted access.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.19-0ubuntu5 (Ubuntu)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

This happens because the current version 8.0 comes with a feature that provides root authentication via a auth_socket plugin.

This plugin authenticates users who connect from the localhost via socket file without prompting or a password.

This can cause issues with some apps that need to connect to the database via root. To fix that, you’ll need to change the default authentication mechanism from auth_socket to mysql_native_password.

Login back into MySQL console.

sudo mysql

Then run the commands below to change to mysql_native_password.

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

Replace very_strong_password with your strong password you want to use.

The save your changes and exit:

FLUSH PRIVILEGES;
EXIT;

That will do it.

You should be prompted for password when you want to access MySQL console.

Since you don’t want to use MySQL root user for external applications to connect, you should probably create an admin account separate from the root user.

GRANT ALL PRIVILEGES ON *.* TO 'superadmin'@'localhost' IDENTIFIED BY 'very_strong_password';

Install MySQL from Repository

Although MySQL server packages are included with Ubuntu, they may not necessarily be the latest. If you want to always get the latest versions of MySQL server downloaded to your servers, you might want to add its repository.

The link below takes you to the repository file.

https://dev.mysql.com/downloads/repo/apt/

Visit the download page and look for Ubuntu / Debian (Architecture Independent), DEB Package. The click the Download button to get the repository package…

You can run the commands below, updating the version number (0.8.15-1) at time of this writing, with the latest from the file above.

cd /tmp
wget https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb

When you run the commands above, you should get a config prompt

Simply select OK as shown in the image below.

Now that the repository is installed, run the commands below to install

sudo apt update
sudo apt upgrade

That’s it!

Anytime there’s a new version of MySQL server packages, you’ll automatically get them.

Conclusion:

This post showed you how to install MySQL database server on Ubuntu 20.04 | 18.04. If you find any error above, please use the comment form below to report.

Thanks,

You may also like the post below:

How to Install Eclipse IDE on Ubuntu 20.04 | 18.04