How to Install and Configure MySQL on CentOS 7

MySQL is one of the most popular and practical database management systems that serves as an open-source relational client-server model for websites and server applications. It also uses SQL (Structured Query Language), which is the most common standard language for accessing databases, to manage its data. MySQL is one of the important components of the open-source enterprise stack called LAMP (Linux, Apache, MySQL, PHP). LAMP is a web development platform that uses Linux as an operating system, Apache as a web server, MySQL as a relational database management system, and PHP as an object-oriented programming language.

MySQL is written in C and C++ and runs on almost all operating systems including Linux, Unix, and Windows. Although it has a wide range of uses, MySQL is most frequently used for websites and online publications.

Familiarity with MySQL and how to use and configure MySQL is useful and necessary for every webmaster. Since the CentOS operating system is one of the popular operating systems that most developers prefer, for this reason, we decided to teach how to install and configure MySQL on CentOS 7 in this article, so that you can use the efficient features of MySQL.

In this article, we will first introduce you to the advantages of MySQL, then we will provide the necessary instructions for installing and configuring MySQL on CentOS7.

Advantages of MySQL

Although MariaDB was designed as an alternative to MySQL with the development of its capabilities, and today most CentOS7 users prefer MariaDB, with its advantages and continuous growth, MySQL maintains its loyal users and competes strongly with other database management systems. Some of the prominent advantages of MySQL are as follows:

• Reliable, fast and scalable
• Ease of configuration and easy learning
• Open source and access to the original code
• high efficiency
• high security
• Compatible with all Windows and Linux operating systems and other web applications and web servers
• Quickly provide the most useful solutions for managing large databases

Instructions for installing and configuring MySQL on CentOS7

To install MySQL on CentOS7, you need to perform the three main steps of downloading, installing, checking the status, and finally, configuring MySQL on the CentOS7 operating system. In the following, we will explain each step clearly and guide you on how to install and configure MySQL on CentOS7. Before starting the installation of MySQL, consider the prerequisites necessary to execute the MySQL installation instructions on CentOS7.

prerequisites

• Linux system or Linux VPS running CentOS7
• A non-root user with Sudo permission

Access to the command line interface (Menu > Applications > Utilities > Terminal or press the Ctrl+Alt+T buttons at the same time)

Step 1: Updating the system

First, it is better to update your system by running the following command:

sudo yum update

Step 2: Downloading MySQL setup packages

You cannot use the yum command to download MySQL repository packages on CentOS7 because running the yum install mysql command installs MariaDB on your operating system instead of installing MySQL. For this reason, you must refer to the MySQL community Yum Repository to install MySQL repository packages. So launch your system browser and search for the following address in the address bar of your browser:

https://dev.mysql.com/downloads/repo/yum/

At the address we provided, you will get a list of MySQL setup packages in the Yum repository. Find the desired version of CentOS in the provided list and select the download option. By clicking on the download button, you will be redirected to the registration page where you will be invited to create an account; if you wish, you can register, Otherwise, continue the process of downloading the MySQL repository by choosing the “No thanks, just start my download” option.

Copy the link location by right-clicking on the desired version of RedHat Enterprise. Another way is to change the version number in the following commands.

Note: The gray text below the RedHat Enterprise Linux7/oracle Linux7 titles is the version number.

Therefore, according to the desired version you found, modify the following command:

curl -sSLO https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm

After executing the above command, if the release ID is correct, the Linux system will communicate with the site and download the MySQL startup files.

Step 3: Checking the process of downloading MySQL repository packages

After downloading the rpm file, run themd5sumcommand to ensure that the MySQL repository packages are downloaded correctly:

md5sum mysql80-community-release-el7-7.noarch.rpm

the output will be a string of numbers and letters.

Then, we compare the output of the previous command with the MD5 value provided under the download link on the site through which we downloaded the MySQL repository, to make sure that the steps are correct.

If they match, we will continue the steps, but if they don’t match, it means that there was a problem downloading your file and the file was damaged during the transfer. Therefore, you have to repeat the previous steps to make sure the correctness of the file download process during the review phase.

Step 4: Adding MySQL repository

After making sure that the rpm file is correct, the next step is to install the package by running the following command:

sudo rpm –ivh mysql57-community-release-el7-7.noarch.rpm

By executing the above command, two MySQL yum repositories will be added to the system and you can install MySQL through these repositories.

Step 5: Installing MySQL

To install MySQL on CentOS7 from the MySQL repository you just added, enter the following command:

sudo yum install mysql-server

Then, by executing the previous command, a list of packages will be provided, and you will be asked to install and download them. Press Y and then Enter to accept the prompt.

Then, to add new software sources, you must accept the security confirmation that is requested about the GPT key by pressing the y button again to complete the download and installation of the software.

After confirming the requests, you will receive the message Complete! It indicates the successful installation of MySQL on your CentOS7 server.

Step 6: Setting up and managing MySQL

Up to this point, you have successfully installed MySQL on CentOS7, but you need to run the following command to start the MySQL service because it is not started by default:

sudo systemctl start mysqld

Step 7: Checking the status of the MySQL service

Since you will not receive a message after starting MySQL. To make sure that the MySQL service is running correctly, use the following command:

sudo systemctl status mysqld

If you see the Active: active (running) message in the output, make sure MySQL is running on CentOS7.

MySQL’s default settings are such that it is activated when the system boots. To change the default settings, use the following command:

sudo systemctl disable mysqld

Step 8: Finding the temporary password of the MySQL root user

When installing MySQL, a temporary password is set for the MySQL root user, to access which you must run the following command:

sudo grep 'temporary password' /var/log/mysqld.log

You will get a temporary password at the exit, so write it down in a safe place as you may need it in situations like securing the installation or changing the password.

Step 9: Configuring MySQL

In this section, we present some essential settings that must be configured in MySQL.

• Setting a new password for the Root user in MySQL

In MySQL, you can change less secure defaults by running security scripts. One of the most important changes in the MySQL default is setting a new password for the MySQL root user account. So to change the temporary default password created for the Root user when MySQL was installed, run the following command:

sudo mysql_secure_installation

After executing the above command, you will be asked to enter the default password(the temporary password you saw in Step 7 for the Root user in MySQL). After entering the default password, you will receive the following message:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:

As you can see, you will be asked to set and enter the new password, which must be a safe and unique password (to set a strong password, it  must contain 8-12 characters and be a combination of numbers, letters, and symbols.)

After receiving confirmation feedback about the strength of the password, you will be asked to change your new password again, since there is no need to change your new password, type NO in the response:

Output

Estimated strength of the password: 100

Change the password for root ? (Press y|Y for Yes, any other key for No) :

Then for the other requests you receive, press the y button and then Enter to confirm the disallowance of other remote users to the root system, the removal of the permission of unknown users, and the inability to access the test database for other users.

• Creating a new database or new user in MySQL

In the previous steps, a root user has been set up to manage and use MySQL with database access permissions. If you are working as a team, it is better to create a new database or define new users with special permissions to increase security.

If you decide to create a new database, run the following command:

CREATE DATABASE newdb

Replace the database name in the newdb field.

Use the following command to create a new user:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'

Replace your desired username and password in the command above.

To delete a specific user, you can enter the following command:

DROP USER ‘username’@‘localhost’

• Resetting MySQL Root password

You may need to change the new password you have set for the MySQL Root user, in this case, you can easily change the MySQL Root password.

1. First, you need to stop the MySQL server by running the following command:

sudo systemctl stop mysqld

2. Then enter the following command to restart the MySQL server in Safe Mode:

sudo mysqld_safe --skip-grant-tables

Note: If it is hanging after starting the MySQL server, press the Enter button.

3. To access the MySQL server through Root permission, enter the following command:

mysql -uroot

4. Run the following command to set a new password:

USE MYSQL;

UPDATE USER SET PASSWORD=PASSWORD(“newpassword”) WHERE USER=’root’;

FLUSH PRIVILEGES;

EXIT

Then enter the new password.

5. To apply the new settings, you need to restart MySQL:

sudo systemctl start mysqld

As a result, you just changed the password you set to a new password.

• Managing MySQL user permissions

You can customize the privileges of users in accessing the database. The following options are used to grant privileges to users:

If you want to allow a user to create a new table, you must execute the following command:

GRANT CREATE ON newdb.* TO 'username'@'localhost'

If you want to block user access to the database, you must enter the following command:

REVOKE permission_type ON newdb.* TO 'username'@'localhost'

To monitor the permissions that users have, you can use the following command:

SHOW GRANTS username

After determining and managing permissions to apply changes, you can reset privileges by running the following commands:

FLUSH PRIVILEGES

Step 9: Check and test MySQL

After installing and configuring MySQL on CentOS7, you can check the MySQL version to make sure that the MySQL service is running correctly. For this purpose, you need to connect to the mysqladmin tool to get information about the client that enables the execution of management commands. So enter the following command to make sure the settings you made are correct:

mysqladmin -u root -p version

OutPut:

mysqladmin  Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)

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.

Server version                   8.0.28

Protocol version               10

Connection                         Localhost via UNIX socket

UNIX socket                       /var/lib/mysql/mysql.sock

Uptime:                                               3 min 2 sec

Threads: 2  Questions: 14  Slow queries: 0  Opens: 133  Flush tables: 3  Open tables: 49  Queries per second avg: 0.076

If the output you receive is similar to the output of our article, then you have successfully installed MySQL on CentOS7.

FAQ

mysqladmin -u root -p version

Conclusion

MySQL is one of the most popular database management systems that plays an effective role in the efficient management of databases as well as your data storage. Nowadays, the use of database management systems such as MySQL has become a necessity for developers. In this tutorial, you have successfully installed and configured MySQL on CentOS7 and you have learned about useful commands for its configuration and management. you can use \h or the help command To get more information about MySQL commands.

Thank you for staying with us until the end of this article.