How to create a Database Server using MariaDB on Centos 7

In our project, the database server will run on MariaDB

Prerequisites

• A VPS with Centos 7 installed
• Root access to the VPS

What do we want?

We intend to setup a database server, install phpMyAdmin to help with easier access and secure our hostname – which we use to access phpMyAdmin with an SSL certificate

Procedure

1. Login to the server as root

2. Install MariaDB

[root@db ~]# yum install -y mariadb mariadb-server

3. Start and enable MariaDB

[root@db ~]# systemctl start mariadb

[root@db ~]# systemctl enable mariadb

4. Secure MariaDB installation by running the command below. You’ll be prompted for a few details, yes/no answers. Simply, choose a root password and select Y for all other options prompted

[root@db ~]# mysql_secure_installation

At this point you can use your database server via the command line but I find this so tedious and end up with several inaccuracies, so I prefer installing phpMyAdmin to give me an interface for interacting with the db.

5. Install phpMyAdmin. Since phpMyAdmin needs php to work, we’ll need to install this to together with php-mysql module

[root@db ~]# yum install -y phpmyadmin php php-mysql

Once installed, you’d expect to access phpMyAdmin via your browser, right? But there’s still a small step to go. PhpMyAdmin is by default designed to be accessible via localhost only. If you’d like to access it over the internet, you need to allow external IPs in it’s config file.

6. Open /etc/httpd/conf.d/phpMyAdmin.conf using your favorite editor.

Depending on the Apache version you are running, you’ll need to either add a line on the config file or change lines. Below is the config file that will allow any IP to access phpMyAdmin.

For Apachev2.4 add Require all granted as shown below. For Apache v2.2 change the lines as noted below. You can check your Apache version by typing httpd -v on the terminal.

<Directory /usr/share/phpMyAdmin/> 
AddDefaultCharset UTF-8 
<IfModule mod_authz_core.c> 
# Apache 2.4 
<RequireAny> 
#ADD following line: 
Require all granted 
Require ip 127.0.0.1 
Require ip ::1 
</RequireAny> 
</IfModule> 
<IfModule !mod_authz_core.c> 
# Apache 2.2 
#CHANGE following 2 lines: 
Order Allow,Deny 
Allow from All 
Allow from 127.0.0.1 
Allow from ::1 
</IfModule> 
</Directory> 

7. Restart Apache and access phpMyAdmin by typing on your browser

SERVER_IP/phpmyadmin or SERVER_HOSTNAME/phpmyadmin

NOTE:

Instead of allowing any IP to access phpMyAdmin, you can allow a single IPs only. This is considered more secure.

If you get an error 403 that you do not have permissions to access /phpmyadmin in the server, it means either the config file was not altered well as explained here or you have not installed php and php-mysql module on the server

8. Create a database, a user for your database and give the user privileges on the database

Since we disabled remote root login when we ran mysql_secure_intsllation, you’ll need to login in the server as root and create a user from mysql terminal.

[root@db ~]# mysql -uroot -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 13

Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> CREATE DATABASE sitedb;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE sitedb;

Database changed

 MariaDB [sitedb] >  CREATE USER 'sitedb' IDENTIFIED BY 'password';

Query OK, 0 rows affected (0.00 sec)

 MariaDB [ sitedb ] >GRANT ALL PRIVILEGES ON . TO 'sitedb'@'%' WITH GRANT OPTION; 

Query OK, 0 rows affected (0.00 sec)

 MariaDB [sitedb] > FLUSH PRIVILEGES; 

Query OK, 0 rows affected (0.00 sec)

MariaDB [sitedb] >

9. Now login to phpMyAdmin and create or import your database. Then update your configuration file for the website in the web server to use the details as specified:

Host: SERVER_HOSTNAME

User: User you created in step above

Password: As specified in step above

Database name: As specified in step above.

NOTE: If you need to access the database remotely, you need to enable remote access to the database. To do this, open /etc/my.cnf file and under [mysqld] section, change the line :

bind-address=127.0.0.1 

to

bind-address=database_server_publicIP

You can also use bind-address=0.0.0.0 to allow listening to all interfaces.

10. The system is now working well, the last part is to install SSL for the host name so as to secure your logins to the database. We’ll install free Let’s Encrypt for this. This can be done quite easily using Certbot as described in this document by Jason Cannon from Linux Training Academy. You can also find it’s video here.