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.
- 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:
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 the
md5sumcommand to ensure that the MySQL repository packages are downloaded correctly:
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:
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:
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.
- First, you need to stop the MySQL server by running the following command:
sudo systemctl stop mysqld
- 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.
- To access the MySQL server through Root permission, enter the following command:
- 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.
- 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:
|CREATE||Provides the user with the privilege of creating new tables.|
|DELETE||Allows users to delete rows from tables.|
|DROP||Grants permission to delete tables.|
|SELECT||Grants permission to read the database.|
|INSERT||It offers the possibility of adding new rows to tables.|
|GRANT OPTION||Allows users to remove or grant permissions to other users.|
|UPDATE||It allows updating the rows of tables in the database.|
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:
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
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.
Running the command
mysqladmin -u root -p versionchecks the version and installation of MySQL on CentOS7.
By default, MySQL files are stored in the /var/lib/mysql directory. Of course, as long as you have not made any changes to the configuration.
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.