About MySQL

Databases can store a vast number of records effectively which makes the information simple and easy to find. Other than that, adding or amending new data to the existing one is also relatively simple to do. This article is all about the installation of MySQL on Ubuntu, which is the most extensively used Linux distro in the public cloud with machine learning capability. It’s easy for beginners because it’s built on Debian’s design and structure. It’s designed for enterprise servers, desktops, the cloud, and the IoT.

MySQL is an Oracle-developed relational database management system (RDBMS) focused on structured query language (SQL). Databases consist of collections of assembled data. SQL is a programming language that allows programmers to create, change, and retrieve data from relational databases, as well as manage user access. SQL is used by MySQL databases. Further installation of MySQL is explained in proper steps.

Installation of MySQL on Ubuntu

Below are the steps mentioned to install MySQL on your Ubuntu server.

Step1: Installing MySQL on Ubuntu

The apt package repository can be used for the installation of MySQL on Ubuntu 20.04. At the time of writing this article, the default version of the MySQL repository for Ubuntu was version 8.0.28.

Before installing, first, you need to update the packages of your server. Run the below-mentioned command to update all packages.

$ sudo apt update

Run the command mentioned below to complete the installation of the “MYSQL-server” package.

$ sudo apt install mysql-server

MySQL will be installed but will not require you to create a password or modify any other settings. We’ll deal with this next because it makes your MySQL installation insecure.

Step 2: Verify the Installation of MySQL

To verify its installation, run the below-mentioned command to check its status:

$ systemctl status mysql.service

Step 3: Configuration of MYSQL on Ubuntu

You should run the DBMS’s inbuilt security script on the new MySQL installation. For remote root logins and sample users, this script overrides some of the less secure default configurations.

Run the below-mentioned command for security script:

$ sudo mysql_secure_installation

A series of questions will be asked where you can adjust the security options for MySQL installation. The first step will ask if you want to apply the “password validation” plugin, which may be used to evaluate the strength of new MySQL users’ passwords before approving them.

If you enable the “Validate Password” plugin, the MySQL user you create that validates with a password must use a password that complies with the rules you provide. Passwords must be at least “8 characters” long and comprise a combination of uppercase, lowercase, and numeric letters. Then choose the policy level. After this, setting a password for the root user of MySQL will be asked, regardless of whether you opt to utilize the “Validate Password” Plugin or not. Enter and then confirm the following safe password.

This script will then prompt you to delete the testing database, delete the anonymous user/users, and restrict the root user/users from accessing the local machine. All questions should be answered with a “Y” (yes).

Step 4: Login as a Root User

The MySQL client program, installed as a dependency of the MySQL server package, can be used to connect with the MySQL server via the command line.

The root user of MySQL in Ubuntu running MySQL 5.7 (and later versions) is set as default to verify using the “auth socket” plugin rather than a password. The name of the operating system user who launches the MySQL client must match the name of the user of MySQL supplied in the command for this plugin to work.

$ sudo mysql

The below-mentioned code will be used to create a new user.

$ mysql> CREATE USER ‘USERNAME’@’HOST’ IDENTIFIED BY ‘PASSWORD’

Write the below-mentioned code in front of the “MySQL” prompt to create a new user “Linux”, host “localhost” and password “Linux@123”.

$ mysql> CREATE USER ‘linux’@’localhost’ IDENTIFIED BY ‘Linux@123’

You can give privileges by writing the code mentioned below.

$ mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO ‘linux’@’localhost’ WITH GRANT OPTION;

The below-mentioned code will exit MySQL.

$ mysql> exit

How to Change \ Reset MySQL User Root Password in Linux

Step 1: Log in as the MySQL User

When you boot into your Linux installation, make sure you’re logged in as the same user that normally runs MySQL. Although you can log in as root, once you start the MySQL server, make sure you start it with the --user=mysql option.

Otherwise, the system may create files owned by the root user, which can cause problems.

Step 2: Find the .pid File for the MySQL Service

The next step is to find the .pid file for the MySQL service.

Most systems store them in /var/lib/mysql/, /var/run/mysqld/, or /usr/local/mysql/data/ path. The filename usually starts with mysqld (or your system’s hostname) and ends with the .pid extension.

Step 3: Kill the mysqld Process

Before you create a new root password, stop the MySQL server. To kill the mysqld process, open a command line, and run the following:

kill `cat /mysql-data-directory/host_name.pid`
Code language: JavaScript (javascript)

Replace mysql-data-directory/host_name.pid with the filename you found in the previous step. Ensure to specify the whole path to the file. Also, make sure to use the back-tick key (usually above the tab key) and not a single-quote mark in the beginning of the command.

Step 4: Create the Password File

1. Open your favorite text editor. In this example, we use vim:

sudo vim

2. Next, add the following line in the file:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
Code language: JavaScript (javascript)

Bear in mind to include the single-quote marks and the semicolon. Replace NewPassword with the password you want to use. Finally, make sure to use a strong secure password, like these examples.

The command will work for the machine you’re currently using. If you’re connecting to a different system, replace localhost with the appropriate hostname.

3. Save the file to home/me/mysql-init.

Step 5: Restart the MySQL Server and Apply the New Password

To apply the changes to the password, restart the MySQL server by running the following command in the terminal:

mysqld --init-file=/home/me/mysql-init &
Code language: JavaScript (javascript)

This launches MySQL, and apply the text-file password change. Depending on how you start your server, you may need to add other options (such as --defaults-file before the init command.)

Step 6: Cleaning Up

Lastly, log into your MySQL server using the root account, and verify the new password works. Then, delete the file you created in Step 4.

Conclusion

Databases can store a vast number of records effectively. MySQL is a relational DBMS used to store, edit, and delete data. This article is about how to install the MYSQL database on your Ubuntu system (popular Linux distribution). Follow the steps properly for a successful installation.

Another interesting article can be: A Simple Way To Open A Port In Ubuntu 21.10