In this blog post , We have explained how to install MySQL on CentOS 7.

We will also learn to configure MySQL and connect to MySQL.

What is MySQL?

MySQL is an open source database management system.

It is a relational database management system based on Structured query Language.

It is used for wide range of purposes such as Application logging , data warehousing , e-commerce.

Its most commonly used as a part of LAMP (Linux , Apache , MySQL , PHP ) Stack.

Installing MySQL on CentOS 7

Make sure you have sudo or root privileges to execute the below commands.

Update the CentOS default software package repository using the below command.

sudo yum update -y

Then we need to install wget package on the system , Using which we can download the install MySQL packages on the system.

yum install wget -y

Lets go ahead and download the MySQL package from the repository.

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

Then install the download package.

sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm

Now we need to update the yum repository,

yum update -y

Lets install the MySQL packages using the below command.

sudo yum install mysql-server

After the package are installed along with its dependencies , Check the version of MySQL installed on the system using this command.

mysql --version
Install MySQL

Starting MySQL Service

Once the packages are installed , we need to start the MySQL service using the systemd unit file which was created by default post installation.

With the help of systemd service file we can easily manage the MySQL service.

The systemd service file is created under /lib/systemd/system/ directory and configurations are stored in mysqld.service file.

sudo cat /usr/lib/systemd/system/mysqld.service
Install MySQL

To start the MySQL Service,

systemctl start mysql

To check the status of the MySQL,

sudo systemctl status mysql
Install MySQL

To enable the service to start on system boot up,

sudo systemctl enable mysqld
Install MySQL

To restart the MySQL service , run the below command,

systemctl restart mysql

To stop the MySQL service , Execute the below command.

sudo systemctl stop mysql

To disable MySQL to start on the system boot up,

sudo systemctl disable mysql

Configuring MySQL

After the MySQL installation , We will be provided with a script using which we can configure MySQL settings such as root password , removing remote access and sample users.

Run the below script to start the process,

sudo mysql_secure_installation

As We havn’t set the root password yet , Just hit Enter

Install MySQL

It asks us whether to set password for root , Type Y and hit Enter

Set root password? [Y/n] Y
Install MySQL

By default , MySQL installation has an anonymous user , which allows anyone to login to mySQL without having username and password.

We should remove them for the security purposes.Type Y and hit Enter

Remove anonymous users? [Y/n] Y

Root user should be allowed only to connect from localhost , hence we need to disable the remote login for the root user.Type Y and hit Enter

Disallow root login remotely? [Y/n] Y

By default , MySQL comes with database named test which anyone can access it.We should remove this database as well and also the privileges on the test database.

Remove test database and access to it? [Y/n] Y

We need to reload the privileges table for all the above changes to take effect.

Reload privilege tables now? [Y/n] Y

You should get the below response.

Install MySQL

Accessing MySQL Database

Now , We have completed all the required steps , Now Its time to access the MySQL database with the root username and password.

Type the below command to login as root user,

sudo mysql -uroot -p

It asks for the password , Provide the password and hit Enter.

Install MySQL

We have successfully logged in to MySQL database as root user.

To list all the databases, Execute this command,

show databases;
Install MySQL

To list all the users in the MySQL database , Use the below command.

select user from mysql.user;

Using the below command , Create a database.

Lets create a database named testdb.

create database testdb;
Install MySQL

Run the below command , To connect to testdb database,

use testdb;
Install MySQL on Ubuntu

To list all the tables in the database , Run the below command.

For testing , I have connected to mysql database and i am listing all the tables there.

use mysql;
show tables;
Install MySQL on Ubuntu

To create a user , Run the below command.

create user 'testuser'@'localhost' identified by 'Testuser&93';
Install MySQL

To grant read only access to a database for a user,

The below command , Will grant read access to all the tables in that particular database.

grant select on testdb.* to 'testuser'@'localhost';

To grant read access only for particular table in a database,

grant select on testdb.tablename to 'testuser'@'localhost';

To check the permissions granted for a user,

show grants for 'testuser'@'localhost';

To grant full access to a database for a user,

grant all on testdb.* to 'testuser'@'localhost';

To remove access from a database for a user,

revoke select on testdb.* from 'testuser'@'localhost';
revoke all on testdb.* from 'testuser'@'localhost';

Run the below command to delete a database,

drop database testdb;

Run the below command to delete a user,

drop user 'testuser'@'localhost';

To exit from the MySQL datbabase , Type exit and hit Enter.

Conclusion

We have learnt to install and configure MySQL on CentOS 7.

Also Now we should have a good understanding on managing databases , users and granting and revoking permissions for users.

Hope you find it helpful.Please do check out my other articles.