Introduction:

MySQL is an open-source relational database management system used all over the world in various web applications. Installation on an Ubuntu server is a straightforward process, and this guide will walk you through each step to get it up and running efficiently.

This document explores the process of installing on both the Ubuntu local system and the cloud server.

Prerequisites:

Before you begin, ensure that you have:

• Requires Ubuntu OS or a cloud server with root access
• Internet access is required for package downloads

Step 1 (Installation)

Open the terminal and update the package index by running:

1: Update and upgrade your local packages first. 

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

2: How to Install.

sudo apt install mysql-server

After installation, the service should start automatically. Verify its status with:

sudo systemctl status mysql.service

Warning:

For configuring after a fresh installation, you need to run the included security script. However, due to a recent change, you may encounter an error. To resolve this, you'll first need to adjust the root user's authentication method.

In your terminal, run the following command:

sudo mysql

Modify root authentication to use password-based credentials.

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

Note:

Kindly store your password in a safe and secure location for future reference.

Exit the Terminal. 

exit

To enhance the security of your installation, run the security script to install it securely.

sudo mysql_secure_installation

The system will display available response options:

Output

Confirm with Y for Yes, or press any other key for No:

You must choose one of the three different levels of password validation policies.

Press 0 for LOW

Press  1 for MEDIUM

Press  2 for STRONG:




Example:     (Your_password&&*!12)

You'll be prompted to change the password. Press Y to change or N to skip.

Step 3: Creating a Dedicated User and Granting Privileges

Log in from the root first.

sudo mysql -u root -p

Enter the root password when prompted. After entering the password, press Enter. You will log in 

Output… 

Welcome to the MySQL monitor.  Commands end with; or \g.

The following command establishes your database:

create database your_DB_name_here default character set utf8;

The UTF-8 character encoding provides comprehensive multilingual character support.

create user ‘name’@’localhost’ identified by ‘your_password_here’;

To establish a localhost user account, run if you need to specify an IP address or allow access from multiple hosts, replace 'localhost' with the relevant IP, or use '%' for all hosts.

Example:

create user ‘name’@’%’ identified by ‘your_password_here’;

Assign appropriate privileges to the new user to access the database without any hurdles.

Following command:

GRANT ALL PRIVILEGE ON your_database_name_here.*  TO  ‘your_user_name’@’localhost’;

Above command, we give full privileges if you want to give a specific grant, you can modify this command.

Example:

GRANT 

    /* Database structure privileges */

    CREATE, ALTER, DROP,

    /* Data manipulation privileges */ 

    INSERT, UPDATE, DELETE, SELECT,

    /* Advanced privileges */

    INDEX, REFERENCES, RELOAD

ON `your_database_name_here`.* 

TO 'specific_user'@'localhost';

Above the command, you can remove those privileges you don't want to give new users, like delete or update, or any other privileges you need to.

After doing this, you need to refresh the DB privileges.

Following this command:

FLUSH PRIVILEGES;

End your session with this command.

Example:

exit

Test New Role:

mysql -u ‘your_user_name -p

-u Tag means you, the role,

-p Tag means it will ask password before logging in

Output… 

Welcome to the MySQL monitor.  Commands end with; or \g.

How To Allow Remote Connection.

The default configuration only permits connections originating from localhost. Remote connections may be required for certain configurations.

Step 1 — Configure Remote Client Connections

1. Open this file.

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

Find the `bind-address` parameter and change its value to your server's IP address if you want to grant a specific location. If you need to give universal access, then replace with this  `0.0.0.0` to listen on all available network interfaces:

bind-address = 0.0.0.0

Save the changes and exit the editor.

As we discussed above, how to create a remote role.

Log in through root… 

sudo mysql -u root -p

After logging in, run this command to grant remote access (replace placeholders with your values):

To restrict database access to a single authorized IP, execute this GRANT statement with the target address.

create user ‘name’@’123.456.789.0’ identified by ‘your_password_here’;

If you want to use it for multiple Roles, follow this command:

create user ‘name’@’%’ identified by ‘your_password_here’;

After creating the account, execute the required GRANT statement and finalize the changes by flushing privileges as done in prior steps.

Testing Remote Connection:

Now that you've configured to accept remote connections, you can test it from a remote machine using a client application.

For remote database connections? Use the following command:

Replace [values] with your credentials and server details:

sudo mysql -u username -h server_ip -p  

Example:

sudo mysql -u jack -h 123.456.789.0  -p

Conclusion:

Congratulations! Remote access is now configured on your Ubuntu server. Always implement security best practices, including using strong passwords and restricting access to trusted IP addresses, to maintain server security.