In this article, We are going to Perform How to install MySQL 5.7 on Ubuntu 20.04 LTS using command line, creating new user and enabled remote access.

Introduction

MySQL is the world’s most popular open source relational database management system. It is widely used with web server like apache2,Nginx,IIS,etc.

MySQL has client/Server architecture , supports InnoDB storage engine,can be installed on various operating system like Ubuntu,Debian,CentOS,Windows.,etc.

Step #1: Add MySQL APT Repository in Ubuntu

Ubuntu comes with default package repositories. So,if we want to add/install latest repositories then we have to add/install package repositories.

Update the System Packages

sudo apt update

Install wget on ubuntu if not installed

sudo apt install wget -y

Below are commands to add/download latest MySQL APT repository using command line,

wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb

then, below command is to install above downloaded apt repository,

sudo dpkg -i mysql-apt-config_0.8.12-1_all.deb

Select Ubuntu Bionic option and click on Ok.

By default it shows MySQL 8.0, Click on First option .

Select MySQL 5.7 server and click on OK.

Confirm that showing MySQL 5.7 on First option and Click on OK.

Step #2: Update MySQL Repository

Update apt repository

sudo apt-get update

Search MySQL 5.7 package using MySQL apt cache and select 5.7.30-1ubuntu18.04 to install

sudo apt-cache policy mysql-server

Output:

mysql-server:
Installed: (none)
Candidate: 8.0.25-0ubuntu0.20.04.1
Version table:
8.0.25-0ubuntu0.20.04.1 500
500 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages
500 http://security.ubuntu.com/ubuntu focal-security/main amd64 Packages
8.0.19-0ubuntu5 500
500 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu focal/main amd64 Packages
5.7.35-1ubuntu18.04 500
500 http://repo.mysql.com/apt/ubuntu bionic/mysql-5.7 amd64 Packages

Now install MySQL client 5.7.33 as output shown above

sudo apt install -f mysql-community-client=5.7.35-1ubuntu18.04 

Step #3: How to Install MySQL 5.7 on Ubuntu 20.04 LTS

Error:

The following packages have unmet dependencies:
mysql-community-server : Depends: mysql-client (= 5.7.35-1ubuntu18.04) but it is not going to be installed
E: Unable to correct problems, you have held broken packages.

Solution:

sudo apt install -f mysql-client=5.7.35-1ubuntu18.04

Install MySQL 5.7 on Ubuntu 20.04 LTS using below command,

sudo apt install -f mysql-community-server=5.7.35-1ubuntu18.04

Installation process will prompt default password for root user and again same password.

Install mysql-server=5.7.35 package also

sudo apt install -f mysql-server=5.7.35-1ubuntu18.04

Step #4: Secure MySQL Installation

MySQL Server comes with a script mysql_secure_installation this can do multiple security related operations,

Run the below script on command prompt, asks below options.

$ mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

VALIDATE PASSWORD PLUGIN 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 plugin?

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: 1
Using existing password for root.

Estimated strength of the password: 50
Change the password for root ? ((Press y|Y for Yes, any other key for No) : No

... skipping.
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!

Step #5: Login to MySQL

Now, Login to MySQL 5.7 Server using below command and use password to login entered during installation.

mysql -u root -p

Output:

mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.30 MySQL Community Server (GPL)

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.

Now, We have successfully covered How to Install MySQL 5.7 on Ubuntu 20.04 LTS.

Step #6: Create MySQL Remote User

First, Login to MySQL Server with root user using command line, Below is command is to create user , here i am creating user “fosstechnix“.

mysql> CREATE USER 'fosstechnix'@'%' IDENTIFIED BY 'FOSSTechNix@123';

Next, assign the privileges to database with below command , here i am assigning all databases privileges to user fosstechnix,

If you want to assign privileges to specific database replace ” .” with database name.

mysql> GRANT ALL PRIVILEGES ON * . * TO 'fosstechnix'@'%';

OR

if you want to allow “fosstechnix” user  to give privileges to other user.

 mysql> GRANT ALL PRIVILEGES ON *.* TO 'fosstechnix'@'%' WITH GRANT OPTION;

To take effect reload the privileges using below command,

mysql> FLUSH PRIVILEGES;

Exit from MySQL prompt

mysql> exit

Step #7: How to Enable MySQL Remote Access in Ubuntu 20.04

By default, In MySQL database server remote access is disabled for security reason.

To enable remote connections of MySQL Server, we have to change bind-address in MySQL configuration file.

Open the /etc/mysql/mysql.conf.d/mysqld.cnf file

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

Below the [mysqld] section find the Line,

[mysqld]
bind-address  = 127.0.0.1

And replace it to

 bind-address = 0.0.0.0

Output:

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
bind-address = 0.0.0.0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

Restart the MySQL Server to take effect.

 sudo systemctl restart mysql

Step #8: Start/Restart/Stop MySQL Server Using Command Line

Below are commands to start/restart/stop MySQL 5.7 on Ubuntu 20.04 LTS

sudo systemctl start mysql
sudo systemctl restart mysql
sudo systemctl stop mysql

To check the status of mysql service on ubuntu

sudo systemctl status mysql

Conclusion:

In this article, We have performed ,How to Install MySQL 5.7 on Ubuntu 20.04 LTS System, creating new user and enabled remote access.

Related Articles:

• How to Install MySQL 8.0 on Ubuntu 18.04/16.04
• How to Install MySQL 8 on CentOS 8
• How to Install MariaDB on Ubuntu 18.04/16.04 LTS

Reference:

MySQL Official Documentation