How to Install MySQL on CentOS 7

MySQL is one of the most widely used database management systems for websites and server applications. It’s an essential tool that every webmaster should learn. That’s why in this tutorial, we will show you how to install MySQL on CentOS 7 through an SSH connection.

How to Install MySQL on a CentOS 7 Server?

There are three steps needed when you want to install MySQL on CentOS 7 — download the MySQL repository, install it, and check its status. We’ll go through each step in greater detail.

1. Download and Prepare the MySQL Repository

Before we begin learning how to install MySQL, make sure that either your VPS or dedicated server is running on CentOS 7 and you have root access to it. You can learn how to connect to your server via SSH by following our tutorial.

1. We need to update our system by typing in the following command:

   sudo yum update

2. After the system is updated, it is time to download the MySQL repositories. You can also view other repository versions here.

   sudo wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

3. At the end of the download, you should see a confirmation message that .rpm file was saved.
   
4. Now, we need to prepare the repository so we could later install MySQL packages from it. Simply enter:

   sudo rpm -Uvh mysql80-community-release-el7-3.noarch.rpm

5. Once the output indicates that the update and installation are complete, you will be able to install MySQL.

2. Install MySQL

1. Use the following yum install command to install MySQL:

   sudo yum install mysql-server

2. The script will return with a list of packages and ask you for confirmation to download and install them. Type y and press ENTER for each of the requests.
   
3. You’ll need to do this a few times, but you’ll eventually see the Complete! message at the end of the installation — it means that you have successfully installed MySQL on your server.

3. Start MySQL and Check its Status

1. Once you have MySQL ready on CentOS 7, it does not automatically start right after the installation. Therefore, you need to start it manually through the following command:

   sudo systemctl start mysqld

2. You will get no response once MySQL starts so to check if it is working properly, use the command below:

   sudo systemctl status mysqld

3. It will output the information about the MySQL process:

If you see that MySQL is active and running like in the screenshot above, you have successfully installed and started MySQL on your server.

How to Configure MySQL?

Now that you’re all set up, we’re going to show you some useful commands and tweaks you should know when working with MySQL.

Changing MySQL Root User Password

When installing MySQL on CentOS 7, a temporary root password is generated. Issue the command below to see it:

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

In order to change it, follow these steps:

1. Firstly, run the following command:

   sudo mysql_secure_installation

2. Enter the temporary password, and the following message will pop up:

   The existing password for the user account root has expired. Please set a new password.
   New password:
   Re-enter new password:

3. Now, type in a new strong password.
   

Follow the on-screen instructions and hit y to finish the setup. You have successfully changed the root user password.

Checking Current MySQL Version

Once you’ve MySQL installed on CentOS 7, you can test if everything was set up correctly by checking its version. Type in the following command:

mysql -u root -p

Enter the root password that you’ve created, and the response will be as below:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.20

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Resetting the MySQL Root Password

If you want to reset your password, the process is pretty straightforward. Follow the steps below to change your MySQL root password:

1. Stop the MySQL server with this command:

   sudo systemctl stop mysqld

2. Restart MySQL in safe mode, or without a password by typing in:

   sudo mysqld_safe --skip-grant-tables

3. Press ENTER if the MySQL server has started but seems to be hanging.
4. Use the following command to reconnect to MySQL as root:

   mysql -uroot

5. To change the root password, use the following commands:

   USE MYSQL;
   UPDATE USER SET PASSWORD=PASSWORD(“newpassword”) WHERE USER=’root’;
   FLUSH PRIVILEGES;
   EXIT

6. Replace the newpassword with a strong password of your own.
7. Finally, restart MySQL using:

   sudo systemctl start mysqld

8. You can now login to MySQL with your new password.

Creating a New MySQL User, Database

As the root user in MySQL, you have full access to all of the databases.

However, if you work with a team, there are cases where you might need to apply some restrictions. You’d either have to build a new database or create users with custom permissions.

Here’s how to quickly solve this issue:

1. Use the following command to create a new database:

   CREATE DATABASE newdb

2. Change newdb with the name of the database.
3. Create a new user by typing the following line:

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

4. Change the variables for both username and password to create your new user.
5. If you want to delete a certain user instead, use the command:

   DROP USER ‘username’@‘localhost’

Managing MySQL User Permissions

Grant the new user access to a database with by typing in:

GRANT ALL PRIVILEGES ON newdb.* TO 'username'@'localhost'

You can also grant privileges individually, including:

• SELECT – users can read through the database using the select command
• CREATE – they can generate new tables
• DROP – allows users to remove tables
• DELETE – users can take out rows from tables
• INSERT – lets users add in rows into tables
• UPDATE – enable them to update the rows
• GRANT OPTION – they can grant or remove the privileges of other users

For example, to grant the CREATE privilege, you’d need to type:

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

On the other hand, if you want to remove someone’s access, use the following:

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

You can also check what current privileges a user has:

SHOW GRANTS username

Finally, once you’ve finished managing your users, it’s a good idea to reset all the privileges by using this command for your changes to take effect:

FLUSH PRIVILEGES

Other Useful MySQL Commands

MySQL also has a list of other helpful commands. Simply enter \h or help to view the list shown below:

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear command.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'
mysql>

Conclusion

You have learned how to install MySQL on a CentOS 7 server. By installing MySQL, you are able to store your databases and manage them efficiently on your server.

Be sure to check out our other VPS hosting tutorials and leave a comment down below if you have any questions.