For years, MySQL used to be the most favorite RDBMS for developers worldwide. After the acquisition of MySQL by Sun Microsystems, and later by Oracle, the original founders of MySQL forked MySQL and created another RDBMS called MariaDB. It is actually a perfect replacement for MySQL and has more features and better performance than MySQL. MariaDB is gaining a lot of popularity these days, and many big companies have already migrated to MariaDB from MySQL. In this post, we will discuss how to install MariaDB in a Centos 6 cheap vps hosting server, how to configure and manage the database etc.

Let’s begin to Install MariaDB

In any Red Hat based systems, the installation of any software has been made super easy with the help of the package manager called yum. Centos, being inherited from RedHat, has many built in repos through which yum installs and updates the packages. In addition, it also allows adding third party repos to install specific software. MariaDB thus comes with its own repo for its installation.First, create the file /etc/yum.repos.d/MariaDB.repo.

Now copy and paste the following lines into the file /etc/yum.repos.d/MariaDB.repo. The contents vary based on the OS version and its architecture. Hence, make sure you copy the correct repo.

On a Centos 6 – 64 bit system

On a Centos 6 – 32 bit system

Save and quit the file. Now it’s time to start with the installation.

The above step will install MariaDB in the server. Let’s now start MariaDB and see if all is good. Do not get confused seeing the startup script name as mysql and not mariadb.

Post Installation Security Steps

Now that MariaDB started fine without errors, let’s proceed with further steps. By default, the root password for MariaDB is set as blank which is a great security risk. In addition, there are a few more security practices to be done post installation. The built in script ‘/usr/bin/mysql_secure_installation’ takes care of all those security holes, which includes the following:

  • Reset MariaDB root password
  • Remove anonymous users
  • Disallow MariaDB root login from network
  • MariaDB has a default test database with public access, the script removes the database and access to it
  • Finally reload the privilege tables to make sure that the changes made have gotten into effect

So now, let’s run the script mysql_secure_installation and answer with ‘yes’ for the questions asked and also set a strong root password when asked.

Once the steps about how to install MariaDB are completed, we need to restart MariaDB for the changes to get into effect. You also need to add MariaDB to the startup, so that the service will be automatically started when the server reboots.

 

Logging in to MariaDB

MariaDB is almost similar to MySql in its execution. The commands and syntax are mostly the same, hence whoever worked in MySql will not have any difficulty switching to MariaDB. Let’s see how to connect to the MariaDB prompt as user root.

The basic commands used in MariaDB are same as that of MySql. I am listing a few below for your easiness. Make sure you end your queries with a semicolon at the end, or the query will not execute and MariaDB will return an error.

  • create database      – To create a new database
  • show databases      – List all databases created
  • use database           – To switch to that database
  • create table             – To create a table within the database to which it is switched
  • insert into               – To insert values into a table
  • update                     – To modify the values in a table
  • select                       – To retrieve data from a table
  • drop database       –  To remove a database

In the example given below, I am going to create a database named ‘school’. The database will have a single table named ‘student’ with fields – ‘id’ which is integer, ‘name’ which is varchar, ‘age’ which is again integer etc. MariaDB supports a lot of data types which are documented in their knowledge base. I will enter the values to the table, modify the name of one student from the table, view the details from the table and then drop the database.

 

Connecting to a database

After install MariaDB, to connect to a database, you require a username and password. You can always connect as the root user, but using it always is not a good idea and is a security risk, hence you should create specific users with strong passwords for individual databases. You can create a user, assign required privileges to the database, set password etc all using a single ‘grant’ command as root user. The following image shows how a user named ‘testuser’ with password ‘fsdfe$@#@d’ has been created and all privileges have been assigned towards the database ‘school’.

For any changes to be made in the database, we can login as the user ‘testuser’. This gives more security as anyone who works on a database has their access limited to that single database, and do not even see the other databases created in the server.

screenshot of Install MariaDB in Centos 6 process

 

Apart from command line, you can also connect to a MariaDB database using tools like Database Workbench, or using web interface like PhpMyAdmin. You can also connect from your PHP code using mysql_connect tag as follows.

You need to replace HOST with your actual hostname or IP Address, USERNAME with your database username and PASSWORD with your database user’s password.

On coming to the end of this post, we sincerely hope this post helped you to Install MariaDB in Centos 6.

Leave a Reply

Your email address will not be published. Required fields are marked *