How to Install MySQL 8 on Ubuntu 20 04

Linux

Install mysql 8 on ubuntu 20.04

MySQL is an open-source relational database management system. SQL is a language programmers use to create, modify and extract data from the relational database, as well as control user access to the database.MySQL implements the relational model and is commonly installed on an integral part of LAMP stacks. It is also used for web databases.

MySQL Server 8.0 was announced in April 2018,including NoSQL Document Store, atomic and crash safe DDL sentences and JSON Extended syntax, new functions, such as JSON table functions, improved sorting etc.

In this tutorial,we will explain the process of installing MySQL on Ubuntu 20.04 LTS server.

Also Read -> How to Install Apache Kafka on Ubuntu 20 04

Step 1: install mysql 8

you can install MySQL server 8.0 from the Ubuntu repository using apt directly.

before that update packages using apt.

sudo apt update
sudo apt install mysql-server

once mysql is installed, you can check service status with below command.

sudo systemctl status mysql

sample output:

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2021-04-20 07:25:21 UTC; 11min ago
   Main PID: 3447 (mysqld)
     Status: "Server is operational"
      Tasks: 37 (limit: 1685)

You can also change startup settings of MySQL after system boot-up through the systemctl command.

sudo systemctl enable mysql

Now you have installed MySQL 8 on your Ubuntu 20.04 system.

Also Read -> How to Install ReactJS on Ubuntu 20 04

Step 2: Secure mysql server

It  provides mysql_secure_installation script that makes it easy to secure access to your MySQL database.you can run that script from terminal.

sudo mysql_secure_installation

You will be asked to opt VALIDATE PASSWORD PLUGIN or not. This enables we to test MySQL Passwords and improve security.

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

press Y if you want to set up the validate password plugin or any other key if you don't want to install it.

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

Next, it prompts to set a password for the MySQL root user

New password:
Re-enter new password:

if you select validate password plugin, the plugin will display the strength of your new password.select Y to conform the 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

Next it prompts to Remove anonymous users,disallow root login remotely and remove to test database etc. you can press Y to all prompts.

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!

Also Read -> How to Install Wine on Ubuntu 20 04

Step 3: Mysql login

you can use the the MySQL client tool installed with the MySQL server in order to connect to your server from the command-line.

On MySQL 8.0, the root user gets authenticated using auth_socket plugin by default whereas the auth_socket plugin is used to authenticate users that connect from localhost via the Unix socket. This implies that you can’t authenticate as root by providing a password.

now you run the following command to log in to the MySQL server as root user

sudo mysql

sample output:

sudo mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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>

Step 4: create database

you can create database and user and set previliges to that user on database.

root@ubuntu20:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> create database lampblogsdb;
Query OK, 1 row affected (0.04 sec)
mysql> create user lampblogs@localhost identified by 'strong_password';
Query OK, 0 rows affected (0.03 sec)
mysql> grant all privileges on lampblogsdb.* to lampblogs@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye

If you want to login to your MySQL server as root using an external program such as phpMyAdmin, then you have to change the authentication method from auth_socket to mysql_native_password.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'strong_password';
mysql> FLUSH PRIVILEGES;
mysql> exit
Bye

Also Read -> How to Install Apache Maven on Ubuntu 20 04

Step 5: Enable mysql remote access (optional)

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.

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

Find the line 'bind-address = 127.0.0.1'and change it as below.

bind-address = 0.0.0.0

Now restart mysql service to effect the changes.

sudo systemctl restart mysql

Now Ubuntu Server will allow remote access to the MySQL Server, But still, you need to configure MySQL users to allow access from any host.when you create a MySQL user, you should allow access from any host.

mysql> CREATE USER 'lampblogs'@'%' IDENTIFIED WITH mysql_native_password BY 'strong_password';

if you want to allow specific ip address, then

mysql> CREATE USER 'lampblogs'@'192.168.1.200' IDENTIFIED WITH mysql_native_password BY 'strong_password';

if firewall service is running on your system, then you can allow remote connections through the firewall.ufw is default tool in ubuntu.To allow access from any ip address on internet, run following command.

sudo ufw allow 3306/tcp

To allow access from specific ip address

sudo ufw allow from 10.8.0.5 to any port 3306

That's it. now you have successfully installed mysql 8 and created database in ubuntu 20.04 system.