How To Install MySQL on Ubuntu 22.04

MySQL is an open source relational management system, that uses structured query language for database access and management. Mysql is commonly used in web applications, data warehousing, e-commerce, and logging applications.

Prerequisites

• Launch the Ubuntu 22.04 instance on Neevcloud

• Access the server using SSH

Update && Upgrade the system

First, ensure your package list is up to date to avoid any issues with outdated packages

sudo apt-get update -y
sudo apt-get upgrade -y

Install MySQL

To install MySQL, use the apt package manage and run the following command from a terminal prompt:

sudo apt-get install mysql-server -y

Start and Enable MySQL

Once the MySQL is installed, start the MySQL server

sudo systemctl start mysql

Enable it to start when the system rebooted

sudo systemctl enable mysql

To check the status

The MySQL server should be started. You can quickly check its current status

sudo systemctl status mysql

The Network status of the MySQL service

The network status of the MySQL service can also be checked by running the ss command at the terminal prompt

sudo ss -antpl | grep mysql

Configure MySQL

When the installation is finished, it’s recommended that you run a security script that comes pre-installed with MySQL. This script will remove some insecure default settings and lock down access to your database system.

you can access normally MySQL database.

sudo mysql

MySQL Secure Installation

Start the interactive script by running, This will take you through a series of prompts where you can make some changes to your MySQL installation’s security options

sudo mysql_secure_installation

This will ask if you want to configure the VALIDATE PASSWORD PLUGIN.

Answer Y for yes, or anything else to continue without enabling.

If you select "Yes," you'll need to choose a password validation level. Note that choosing the highest level 2means your passwords must include numbers, uppercase and lowercase letters, and special characters. Otherwise, you'll encounter errors.

After the security script finishes running, you can proceed to reopen MySQL and revert the root user's authentication method to the default, auth_socket. To verify your identity as the main MySQL user with a password, execute this command

sudo mysql -u root -p 

After executing the commands, please enter your password:

Create a MySQL User

Once you have access to the MySQL prompt, you can create a new user with a CREATE USER statement. To create a user login MySQL just like this [ mysql> ]

CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

• Grant Privileges to MySQL User

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@localhost IDENTIFIED BY 'password1';

Creating a MySQL Database

To create a database in MySQL, follow these steps:

Log into MySQL with the command mysql -u username -p. Replace username with your MySQL username. You'll be prompted to enter your password.

Once logged in, create a new database by executing CREATE DATABASE database_name;. Replace database_name with your desired database name.

To verify the database was created, use SHOW DATABASES;. Your new database should appear in the list.

To create a new database, run the following command from your MySQL console just like [ mysql> ]

CREATE DATABASE database_name;

• User and database permission

Once you have set up a new user and database, you can give them the necessary permissions.

GRANT ALL PRIVILEGES ON database_name.*  TO  'username'@'%';

Now, review MySQL configurations.

sudo mysql -u root -p 

Now check the status databases to run these command

show databases;