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.
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.
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
To start the MySQL Service,
systemctl start mysql
To check the status of the MySQL,
sudo systemctl status mysql
To enable the service to start on system boot up,
sudo systemctl enable mysqld
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
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,
As We havn’t set the root password yet , Just hit Enter
It asks us whether to set password for root , Type Y and hit Enter
Set root password? [Y/n] Y
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.
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.
We have successfully logged in to MySQL database as root user.
To list all the databases, Execute this command,
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;
Run the below command , To connect to testdb database,
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.
To create a user , Run the below command.
create user 'testuser'@'localhost' identified by 'Testuser&93';
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.
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.