How to Install MySQL Server on CentOS

Here, we will illustrate the basic installation of MySQL database server on CentOS Linux.

Note: CentOS 7 has altered MYSQL with MariaDB. To indicate this, some instructions are stated in here for MariaDB.  

Installing Database Application

Follow the steps stated here, in order to install the database core server.

MySQL Installation

Start the installation of MySQL server through the centOS package manager, by running the following commands:

sudo yum install mysql-server

sudo /sbin/service mysqld start

After this, run the following command:

sudo /usr/bin/mysql_secure_installation

Now, you need to press enter so as to give NO password for root, whenever asked. In order to secure your new MySQL server (to some extent), answer all the questions, which is asked by the program, to “Yes”. If the questions enable you to set the root password, you need to disable remote root logins, eliminate anonymous users, remove the test database that the installer included, and then reload the privileges to make all changes in effect.

MariaDB Installation

sudo yum install mariadb-server mariadb

Allow Remote Access

With iptables enabled, if you want to connect to the MySQL database from another device, you are required to open a port in the server firewall. This is not required if the application, using MySQL, is running on the same device.

To open the port 3306, follow the below stated rules:

-I INPUT -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT

-I OUTPUT -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT

Start and Stop the Database Service

When the installation is completed, you can start the service by using the following commands. If the system is already started, a pop-up message will appear, which states that the service is already running.

To start the MySQL, use the following command:

sudo /sbin/service mysqld start

To stop the MySQL, use the following command:

sudo /sbin/service mysqld stop

Start and Stop MariaDB

To start MariaDB, use the following command:

sudo systemctl start mariadb.service

To stop MariaDB, use the following command:

sudo systemctl stop mariadb.service

Launching at Reboot

Enable the chkconfig utility to allow the launch of database server after a reboot. Use the below stated command for this.

Enable chkconfig on MySQL

sudo chkconfig mysqld on

Enable chkconfig on MariaDB

sudo systemctl enable mariadb.service

 

The MySQL shell

There is more than one way to work with a MySQL server, but here we will focus on the most basic and compatible approach: The `mysql` shell. At the command prompt, run the following command to launch the `mysql` shell and enter it as the root user:

/usr/bin/mysql -u root -p

A prompt message will appear for a password, enter the password that you have set at installation or, if you have not set one, simply press the enter to submit No password. The following `mysql` shell prompt should appear:

mysql>

Set the root password

Since you have just installed your MySQL database server, the root account within MySQL has no password set yet. You should change that by running the following commands:

/usr/bin/mysqladmin -u root password 'new-password'

/usr/bin/mysqladmin -u root --password='new-password' -h hostname-of-your-server 'new-password'

Note: Here, we have displayed SQL commands in all capitals, but you can also mention them in lowercase. Conventionally, the commands are shown in capital letters, which lets them stand apart from field names and other data, which is being manipulated.

Viewing users

As stated in the above section, MySQL stores the user information in its own database, which is named as “mysql”. Inside that database, the user information is in a "table", a dataset, which is named as "User". If you want to see what type of users are set-up in MySQL table, or dataset, named "user".

SELECT User, Host, Password FROM mysql.user;

The list below lists the descriptions for the parts of that command:

SELECT: this states MySQL that you are asking for data.

·         User, Host, Password – this tells MySQL the required fields you want it look in. these fields are the categories for the data in a table. In this case, you are searching for the username, the encrypted password and the host associated with the username.

·         FROM mysql.user -  it tells MySQL to acquire the data from the MySQL database and the user table.

·         Semicolon (;) - the command always ends with a semicolon.

Ending SQL queries with a semicolon

All SQL queries end with a semicolon. In MySQL, a query is not processed until you put a semicolon. This simply means that you can break-up queries in multiple lines for their easy readability. For example: the previous command also works, if your put it on multiple lines in the 'mysql' shell, as stated:

mysql> SELECT User, Host, Password

    -> FROM mysql.user;

When you press enter after entering the password, you will get a new line. The “>” symbol signifies that you are still in the middle of a statement. You can put semicolon to end a command, if you forget to type it on the same line as the command.

User Hosts

For the above query, following is the example output.

SELECT User, Host, Password FROM mysql.user;

+------------------+-----------+-------------------------------------------+

| User             | Host      | Password                                  |

+------------------+-----------+-------------------------------------------+

| root             | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |

| root             | demohost  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |

| root             | 127.0.0.1 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |

|                  | %         |                                           |

+------------------+-----------+-------------------------------------------+

 

Users are usually related to a host with which they connect. The “root” user in this example is defined for localhost, for the IP address of localhost, and the hostname of the server. Usually, you need to set a user for only one host, from which you usally connect. If you are running an application on the same device as that of MySQL server, by default it connects to localhost. Any new user, which you create should have “local host” in their “host” field. Moreover, if your application connects distantly, the host entry for which MySQL is looking is the IP address or DNS hostname of the remote device.

Create a Database

Database server and an actual database differs from each other to some extent; but often they are used interchangeably. At one hand, MySQL is a database server, which means it keeps track of database and controls their accessing. On the other hand, actual database is one, where all the data is kept stored. Usually, this data is accessed by the applications when they interact with MySQL.

In order to create a database, login to 'mysql' shell, and run the following command. Here, you need to replace 'demodb' with the name of the database you want to create: 

CREATE DATABASE demodb;

Your database is created now, and you can verify this by running a query. See the following example.

SHOW DATABASES;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| demodb             |

| mysql              |

+--------------------+

3 rows in set (0.00 sec)

 

Adding Users and Permissions

When applications are connected to the database with the help of root user, they usually have more advantages than actually required. If you want, you can create a new user, which is being used by the applications in order to connect to the new database.

To create a new user, run the following command in the `mysql` shell:

CREATE USER 'demouser'@'localhost' IDENTIFIED BY 'demopassword';

Also, you can verify that the user was created by running that "SELECT" query again:

SELECT User, Host, Password FROM mysql.user;

+------------------+-----------+-------------------------------------------+

| User | Host | Password                                                   |

+------------------+-----------+-------------------------------------------+

| root     | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19         |

| root     | demohost  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19         |

| root     | 127.0.0.1 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19         |

| demouser | localhost | *0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6         |

+------------------+-----------+-------------------------------------------+

 

Grant Database User Permissions

The new created user can be used to login to MySQL, But, you cannot use this user to make any database changes. For this, you need to give full permissions for the new database by running the stated command.

GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost;

Now, allow the changes to take their place by flushing the privileges.

FLUSH PRIVILEGES;

To verify this, run the following command:

SHOW GRANTS FOR 'demouser'@'localhost';

If you are required to rebuild the server, MySQL returns the commands that are required to reproduce that user's permissions. Override the previous command with another command, which is the grant that you ran the new database.

+-----------------------------------------------------------------------------------------------------------------+

| Grants for demouser@localhost                                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'demouser'@'localhost' IDENTIFIED BY PASSWORD '*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6' |

| GRANT ALL PRIVILEGES ON `demodb`.* TO 'demouser'@'localhost'                                                    |

+-----------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

 

Revoking the Privileges

Sometimes, you need to remove privileges from a user for some other reason. For example, you were granting 'All' privileges to 'demouser' @ localhost. But instead of granting only the demodb database, you have granted all other database too:

+-----------------------------------------------------------------------------------------------------------------+

| Grants for demouser@localhost                                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'demouser'@'localhost' IDENTIFIED BY PASSWORD '*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6' |

| GRANT ALL PRIVILEGES ON *.* TO 'demouser'@'localhost'                                                           |

+-----------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

 

To correct this, you can use a revoke statement, followed by GRANT statement to apply correct privileges.

 

REVOKE ALL ON *.* FROM demouser@localhost;

GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost;

SHOW GRANTS FOR 'demouser'@'localhost';

 

+-----------------------------------------------------------------------------------------------------------------+

| Grants for demouser@localhost                                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'demouser'@'localhost' IDENTIFIED BY PASSWORD '*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6' |

| GRANT ALL PRIVILEGES ON *.* TO 'demouser'@'localhost'                                                           |

+-----------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

 

Now, the user has correct permission, and as a result of this, your database server is more secure. 

Was this answer helpful?

 Print this Article

Also Read

How to Install and Configure CSF (Config Server Firewall)

Installation Process of CSF cd /usr/src rm -fv csf.tgz wget...

How To Create SSL Certificate on Nginx for Ubuntu 14.04

Prerequisites Before starting, you need to setup some basic things on your server. Firstly, you...

How To Install Plesk on Centos

Step 1: Install all necessary packages (for CentOS)   yum install wget Step 2: Start Plesk...

Powered by WHMCompleteSolution