How to install MySQL on CentOS

Reference Server

In this tutorial we will learn to install MySQL on CentOS server.

What is MySQL?

MySQL is an open-source relational database management system (RDBMS).
mysql.com

So, lets get started with the installation process. Open terminal and login to your server as root user.

Step 1: Clean up and update yum

Its a best practice to first clean up and update yum and then perform installation.

Clean up yum by typing the following in terminal.

# sudo yum clean all

Now perform the update

# sudo yum -y update

Once the update is done we are ready to install mysql.

Step 2: Install MySQL

To install MySQL use the following command.

# sudo yum install mysql-server

Step 3: Start MySQL

After installing MySQL we have to start it. Use the following command.

# sudo systemctl start mysqld

Note!

To stop MySQL use the following command.

# sudo systemctl stop mysqld

To make MySQL start at boot use the following command.

# sudo systemctl enable mysqld

To restart MySQL use the following command

# sudo systemctl restart mysqld

Step 4: Setup MySQL

Setting up MySQL by running the following command. This will help you to create a login password for MySQL.

# sudo mysql_secure_installation

Or, you can use the following

# sudo /usr/bin/mysql_secure_installation

The prompt will ask you to enter the current root password. Since we installed MySQL a moment ago so, there will be no root password. So, you can leave it by pressing Enter.

Now, you will be asked whether you want to set a password for the root user. It is always recommended to set a password. So, enter Y and follow the given instructions:

New password: password
Re-enter new password: password
Password updated successfully!
Reloading privilege tables..
 ... Success!

Step 5: Login to MySQL Shell and create database

Use the following command to login to MySQL Shell.

# mysql -u root -p
Enter password: "TYPE IN ROOT USER PASSWORD"

mysql>

Once you have successfully logged in its time to create a database. For this use the following SQL command.

mysql> CREATE DATABASE mydb;

In the above example mydb is the name of the database created.

To check the created databases type the following SQL command.

mysql> SHOW DATABASES;

And you will get a similar output.


+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

Step 6: Create a new User for the database

You can create a new user that can later be used by your application to access the database.

To create a new user type in the following command.

mysql> CREATE USER 'mydbuser'@'localhost' IDENTIFIED BY 'mydbuserpassword';

A new user mydbuser is created having password mydbuserpassword.

Step 7: Grant Privileges to the user

Use the following command to grant privileges to the the new user created in the above step.

mysql> GRANT ALL PRIVILEGES ON mydb.* to mydbuser@localhost;

Now use the following command to make the privileges take effect.

mysql> FLUSH PRIVILEGES;

Note!

Use the following command to check the grant of a user.

mysql> SHOW GRANTS FOR 'mydbuser'@'localhost';
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for mydbuser@localhost                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'mydbuser'@'localhost' IDENTIFIED BY PASSWORD '*8F767905A3234ABC20EBACD9112E4AAB2A9227C9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'mydbuser'@'localhost' WITH GRANT OPTION                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

To revoke (or take back) grants from a user use the following command.

mysql> REVOKE ALL ON mydb.* FROM mydbuser@localhost;

Conclusion

Congrats! you have installed MySQL and created a new user. Have fun coding!