Friday, April 12, 2024
HomeHow toHow to Install and Use MySQL 8 on Ubuntu 22.04

How to Install and Use MySQL 8 on Ubuntu 22.04

MySQL is a free, open-source, relational database management platform powered by Oracle Cloud. It is very popular due to its proven reliability, quick processing, ease and flexibility. It uses Structured Query Language to add, access, and manage a database’s content. MySQL 8.0 stores its meta-data into a proven transactional storage engine called InnoDB. It works on client/server architecture and can be installed on all major operating systems, including Ubuntu, Windows, CentOS, and Debian.

This tutorial will show you how to install MySQL 8 on Ubuntu 22.04 server.

Prerequisites

  • A server running Ubuntu 22.04.
  • A root password is set up on your server.

Getting Started

First, update and upgrade all system packages to the latest version by running the following command:

apt update -y
apt upgrade -y

Once all the packages are updated, you can proceed to the next step.

Install MySQL 8 Ubuntu 22.04

By default, the latest version of the MySQL server is included in the Ubuntu default repository. You can install it by running the following command:

apt install mysql-server -y

Once the MySQL server is installed, you can verify the MySQL version using the following command:

mysql --version

You should see the MySQL version in the following output:

mysql  Ver 8.0.30-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

Once all the packages are updated, you can proceed to the next step.

Manage MySQL Service

By default, the MySQL service is managed by systemd. You can easily start, stop and verify the status of the MySQL using the systemctl command.

To start the MySQL service, run the following command:

systemctl start mysql

To stop the MySQL service, run the following command:

systemctl stop mysql

You can verify the status of the MySQL service using the following command:

systemctl status mysql

You should see the following output:

? mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2022-08-21 12:47:24 UTC; 28s ago
    Process: 26157 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 26185 (mysqld)
     Status: "Server is operational"
      Tasks: 41 (limit: 2242)
     Memory: 359.8M
        CPU: 1.383s
     CGroup: /system.slice/mysql.service
             ??26185 /usr/sbin/mysqld

Aug 21 12:47:23 ubuntu2204 systemd[1]: Starting MySQL Community Server...
Aug 21 12:47:24 ubuntu2204 systemd[1]: Started MySQL Community Server.

By default, MySQL listens on port 3306. You can check it with the following command:

ss -antpl | grep -i mysql

You should see the MySQL listening port in the following output:

LISTEN 0      70         127.0.0.1:33060      0.0.0.0:*    users:(("mysqld",pid=26185,fd=21))                       
LISTEN 0      151        127.0.0.1:3306       0.0.0.0:*    users:(("mysqld",pid=26185,fd=23))                       

Once all the packages are updated, you can proceed to the next step.

Secure MySQL Installation

Next, it is always a good idea to run mysql_secure_installation script to enable some extra security features including, set a new MySQL root password, remove anonymous user and disable remote login.

mysql_secure_installation

Answer all the questions as shown below:

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: 0
Please set the password for root here.

You will be asked to set a new password as shown below:

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.

Type Y and hit the Enter key to remove the anonymous user.

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.

Type Y and press the Enter key to disallow remote root login.

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

Type Y and press the Enter key to remove test database.

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

Once you are finished, you can proceed to the next step.

Set MySQL root Password

By default, the MySQL root password is not set. To set it, connect to the MySQL shell:

mysql

Once you are connected to the MySQL shell, set the MySQL password with the following command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'securepassword';

Next, run the following command to save the changes:

mysql> FLUSH PRIVILEGES;

Next, exit from the MySQL shell using the following command:

mysql> EXIT;

Next, log in to the MySQL shell again to verify the root password:

mysql -u root -p

Once you are logged in, you will get into the MySQL shell as shown below:

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.30-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2022, 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> 

Once you are finished, you can proceed to the next step.

Create a Database and User in MySQL

Let’s create a database named db1 using the following command:

mysql> CREATE DATABASE db1;

You can verify your created database using the following command:

mysql> SHOW databases;

You will get the following output:

+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

To change the database to db1, run the following command:

mysql> USE db1;

To create a user named dbuser, run the following command:

mysql> CREATE USER 'dbuser'@'%' IDENTIFIED BY 'password';

To grant all privileges to dbuser on all databases, run the following command:

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

To save the changes, run the following command:

mysql> FLUSH PRIVILEGES;

You can exit from the MySQL shell using the following command:

mysql> EXIT;

Once you are finished, you can proceed to the next step.

Uninstall MySQL Server

If you want to remove the MySQL server from your server, run the following command:

apt remove mysql-server --purge

Next, remove all unwanted packages using the following command:

apt autoremove

Conclusion

In this post, you learned how to install MySQL 8 on Ubuntu 22.04. You also learned how to manage MySQL service, and create a database and user. You can now start creating new databases and database users in MySQL.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here


spot_img

Most Popular